Computing : Oracle under Linux

[oracle-instant]
name=DESY repository for oracle instant-client installs - $basearch
baseurl=http://nims.desy.de/extra/desy-asg/SL/7/$basearch/oracle-instant # ../8/.. for centos 8
enabled=0
gpgcheck=0

# to install v19.6 and remove older packages you'd just need to include the config package, 
# which also contains pre-configured ora-files (sqlnet.ora, tnsnames.ora) and a module file
yum clean all --enablerepo=oracle-instant
yum install oracle-instantclient19.6-config --enablerepo=oracle-instant

# if you don't like installing the config-package for any reason, you'd need to clean up manually:
# remove oracle instant-client installations
yum remove oracle-instantclient\*

# install the packages needed for example
yum clean all --enablerepo=oracle-instant
yum install --enablerepo=oracle-instant oracle-instantclient19.6-tools \
            oracle-instantclient19.6-jdbc oracle-instantclient19.6-odbc oracle-instantclient19.6-devel

# for perl/python extension you'd need in either case:
# to install python3 and perl bindings. 
yum install --enablerepo=oracle-instant perl-DBD-Oracle python36-cx_Oracle

# to install deprecated python2 bindings. 
yum install --enablerepo=oracle-instant cx_Oracle

if $facts['os']['family'] == 'RedHat' and $facts['os']['release']['full'] == '7' {
  include oracleclient
}

	'oracle-instantclient19.6-config',
    'oracle-instantclient19.6-devel',
    'oracle-instantclient19.6-odbc',
    'oracle-instantclient19.6-sqlplus',
    'oracle-instantclient19.6-tools',
    'oracle-instantclient19.6-jdbc',
    'oracle-instantclient19.6-basic',
    'perl-DBD-Oracle',
    'python36-cx_Oracle',
    'sqldeveloper'

# using the module command. 
module load oracle-instant

# it's equivalent to the following
export PATH=/usr/lib/oracle/19.6/client64/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib/oracle/19.6/client64/lib:$LD_LIBRARY_PATH
export FPATH=/usr/lib/oracle/19.6/client64/lib:$FPATH
export ORACLE_HOME=/usr/lib/oracle/19.6/client64
export ORACLE_SID=DESY
export TWO_TASK=desy_db.desy.de

# replace username and database. get passwd interactively or via environment
import cx_Oracle
connection = cx_Oracle.connect("<username>", "<passwd>", "desy_db.desy.de", encoding="UTF-8")
cur = connection.cursor()
for row in cur.execute("select * from <database>"):
    print(row)
connection.close()

# environment
module purge
module load oracle-instant
# run
python3 test-oracle.py 

#!/usr/bin/perl -w
# largely taken from a perl-sample authored by K.Stock
use Getopt::Long;
use DBI;
use strict;

# Default values for options
my ( $trace, $inst, $cache, $delim, $format, $headers, $page_len, $null_str ) =
   ( 0, $ENV{TWO_TASK} || $ENV{ORACLE_SID} || '', '', "\t", 0, 0, 60, '' );

# set accordingly!
my $query = <query>;
my $user = <username>;
my $pass = <password>;

# Set trace level
DBI->trace( $trace );

# Connect to database
my $dbh = DBI->connect( "dbi:Oracle:$inst", $user, $pass,
    { AutoCommit => 0, RaiseError => 1, PrintError => 0 } )
    or die $DBI::errstr;
$dbh->{RowCacheSize} = $cache if $cache; # set fetch cache

# Start statement
my $sth = $dbh->prepare( $query );
$sth->execute;
my $nfields = $sth->{NUM_OF_FIELDS};

# print out any information which comes back
if ( $nfields ) {
    # the statement has output columns
    my ( @col, $col );
    my @name = @{$sth->{NAME}};
    if ( $format ) {
        # build format statements for the data
        my @size = @{$sth->{PRECISION}};

        # First, the header - a list of field names, formatted
        #    in columns of the appropriate width
        my $fmt  = join '|', map { "%-${_}.${_}s" } @size;
        $fmt     = sprintf $fmt, @name;
        $format = "format STDOUT_TOP =\n" . $fmt . "\n";

        # Then underlines for the field names
        $fmt    =~ tr/|/-/c;
        $fmt    =~ tr/|/+/;
        $format .= $fmt . "\n.\n";

        # Then for the data format, a @<<... field per column
        $fmt =~ tr/-+/<|/;
        $fmt =~ s/(^|\|)</$1@/g;
        $format .= "format STDOUT =\n" . $fmt . "\n";

        # Finally the variable associated with each column
        # Why doesn't Perl let us specify an array here?
        $format .= join ', ', map { "\$col[$_]" } 0 .. $#name;
        $format .= "\n.\n";

        eval($format);
    }
    elsif ( $headers ) {
        # Simple headers with underlines
        print map { s/\s+$//; $_ } @name;
        print map { tr//-/c;  $_ } @name;
    }

    # Associate @col with output columns and fetch the rows
    $sth->bind_columns( {}, \( @col[0 .. $#name] ) );
    while ( $sth->fetch ) {
        foreach $col ( @col ) { $col = $null_str if ! defined $col; }
        $format ? write : print @col;
    }
}

# finish off neatly
$sth->finish;
$dbh->disconnect;

__END__ 

# environment
module purge
module load oracle-instant
# run
perl test-oracle.py 

# install the packages needed for example
yum install oracle.19.3-alias oracle.19.3-afs

# using the module command. 
module load oracle/19.3

# or alternatively 
. oraenv

# it's equivalent to the following
export PATH=/opt/oracle/19.3/bin:$PATH
export LD_LIBRARY_PATH=/opt/oracle/19.3/lib:$LD_LIBRARY_PATH
export FPATH=/opt/oracle/19.3/lib:$FPATH
export PYTHONPATH=/opt/oracle/19.3/python3.6/site-packages
export ORACLE_HOME=/opt/oracle/19.3
export ORACLE_SID=DESY
export TWO_TASK=desy_db.desy.de

# instant client
# you don't need a special repository.Make sure to remove older instant client installations
# either use sudo or the package manager (green desktop)
sudo apt install oracle-instantclient19.6-config oracle-instantclient19.6-tools \
            oracle-instantclient19.6-jdbc oracle-instantclient19.6-odbc oracle-instantclient19.6-devel


# full client
sudo apt install oracle.19.3-afs oracle.19.3-alias

# make sure to have libaio1 installed. For the perl-binding you'd also need libaio-dev
sudo apt install libaio1 libaio-dev

# it's possible that the modules are not in your MODULEPATH. If you are getting an error loading the oracle module set
export MODULEPATH=/etc/modulefiles:$MODULEPATH

# to use the clients
module load oracle-instant # OR
module load oracle

# environment
module purge
module load oracle/19.3
# run
python3 test-oracle.py 

# environment
module purge
module load oracle/19.3

# run the test
/opt/oracle/19.3/perl/bin/perl test-oracle.pl

# python
module load oracle/19.3 # or the instant client
pip3 install cx_Oracle --user
python3 test-oracle.py # should work now. 
# Note: Ubuntu 20.04 run python 3.8, all others 3.6. It hence will require pip installs for each of the different python versions. 

# perl
cpan DBI
cpan DBD:Oracle

# Note: when using cpan for the first time it might ask you to add perl-settings to your login script (e.g. ~/.bashrc). Either say yes or set it up yourself:
export PATH=$HOME/perl5/bin:$PATH
export PERL5LIB="$HOME/perl5/lib/perl5${PERL5LIB:+:${PERL5LIB}}"
export PERL_LOCAL_LIB_ROOT="$HOME/perl5${PERL_LOCAL_LIB_ROOT:+:${PERL_LOCAL_LIB_ROOT}}"
export PERL_MB_OPT="--install_base \"$HOME/perl5\""
export PERL_MM_OPT="INSTALL_BASE=$HOME/perl5"

# run the test
perl test-oracle.pl # should work

# environment for the full client
module purge
module load oracle/19.3 
# loading the module should be fully sufficient. Additionally/Alternatively load oraenv:
. oraenv  

# when starting sqldeveloper for the first time you might want to declare the location of the JRE. 
# Specifying something like /usr/lib/jvm/jre-11 sqldeveloper will store the location in ~/.sqldeveloper/<version>/product.conf
# The location stored in ~/.sqldeveloper/<version>/product.conf takes precedence. JAVA_HOME won't have any effect if it's already declared in product.conf
# Alternatively specify JAVA_HOME
. javaenv                                # tries to pick a suitable JRE. If that fails
export JAVA_HOME=/usr/lib/jvm/jre-11     # do it yourself
sqldeveloper

# environment for the instant client
module purge
module load oracle-instant
# loading the module should be fully sufficient. Additionally/Alternatively load oraenv:
. oraenv 
# 
. javaenv
sqldeveloper