if you work with perl and want to connect to oracle you might want to DBD::Oracle. this is a oracle database driver for the perl DBI module.
there is no need to install this on a server which hosts an oracle instance. all you need from an oracle perspective is the oracle instant client.
to prepare the installation download the following files ( for your architecture ) from the above link:
- instantclient-basic-linux.x64-11.2.0.3.0.zip
- instantclient-sqlplus-linux.x64-11.2.0.3.0.zip
- instantclient-sdk-linux.x64-11.2.0.3.0.zip
as I did a default mint install I additionally had to install the “Linux kernel AIO access library” package which is required for the instant client to work:
sudo apt-get install libaio1
all you need to do for getting the instant client to work is to unzip the downloaded files and setup your environment. in may case I unzipped the files to my home directory in a folder called oracle:
ls -la ~/oracle/instantclient_11_2/ total 185012 drwxr-xr-x 3 daniel daniel 4096 Aug 31 06:57 . drwxr-xr-x 3 daniel daniel 4096 Aug 31 06:51 .. -rwxrwxr-x 1 daniel daniel 25308 Sep 17 2011 adrci -rw-rw-r-- 1 daniel daniel 437 Sep 17 2011 BASIC_README -rwxrwxr-x 1 daniel daniel 46228 Sep 17 2011 genezi -r--r--r-- 1 daniel daniel 368 Sep 17 2011 glogin.sql lrwxrwxrwx 1 daniel daniel 56 Aug 31 06:57 libclntsh.so -> /home/daniel/oracle/instantclient_11_2/libclntsh.so.11.1 -rwxrwxr-x 1 daniel daniel 52761218 Sep 17 2011 libclntsh.so.11.1 -r-xr-xr-x 1 daniel daniel 7955322 Sep 17 2011 libnnz11.so -rwxrwxr-x 1 daniel daniel 1971762 Sep 17 2011 libocci.so.11.1 -rwxrwxr-x 1 daniel daniel 118408281 Sep 17 2011 libociei.so -r-xr-xr-x 1 daniel daniel 164836 Sep 17 2011 libocijdbc11.so -r-xr-xr-x 1 daniel daniel 1503303 Sep 17 2011 libsqlplusic.so -r-xr-xr-x 1 daniel daniel 1477446 Sep 17 2011 libsqlplus.so -r--r--r-- 1 daniel daniel 2095661 Sep 17 2011 ojdbc5.jar -r--r--r-- 1 daniel daniel 2714016 Sep 17 2011 ojdbc6.jar drwxrwxr-x 4 daniel daniel 4096 Sep 17 2011 sdk -r-xr-xr-x 1 daniel daniel 9352 Sep 17 2011 sqlplus -rw-rw-r-- 1 daniel daniel 441 Sep 17 2011 SQLPLUS_README -rwxrwxr-x 1 daniel daniel 191237 Sep 17 2011 uidrvci -rw-rw-r-- 1 daniel daniel 66779 Sep 17 2011 xstreams.jar
for the environment I created a little file and sourced it:
echo"ORACLE_HOME=/home/daniel/oracle/instantclient_11_2 LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH PATH=$ORACLE_HOME:$PATH TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN" > ~/ora_env.sh mkdir -p ~/oracle/instantclient_11_2/network/admin . ./ora_env.sh
before going on you should test if you can execute sqlplus ( if this does not work something is wrong with your environment ):
sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 31 07:26:46 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter user-name:
as a final test create a tnsnames.ora file with the connection parameters for your database and check if you can establish a connection:
echo "my_oracle_db=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=192.168.10.39)
(PORT=1530)
)
(CONNECT_DATA=
(GLOBAL_NAME=dbs300.local)
(SID=dbs300)
)
)" >> ~/oracle/instantclient_11_2/network/admin/tnsnames.ora
let’s see if it works:
sqlplus my_oracle_usr/oracle@my_oracle_db SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 31 07:53:44 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. |-----------------------------------------------------------------| | This system is for the use of authorized users only. | | Individuals using this computer system without authority, or in | | excess of their authority, are subject to having all of their | | activities on this system monitored and recorded by system | | personnel. | |-----------------------------------------------------------------| Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
as expected.
so far for the oracle part. because DBD::Oracle is a driver for the DBI package you probably need to install it first. you should have cpan installed along with your perl distribution.
which cpan /usr/local/bin/cpan
if it’s not there search with your package manager and install it. for ubuntu/debian based distributions:
apt search cpan
for rpm based distributions:
yum search cpan
cpan requires an initial setup, but as this is straight forward I’ll just mention the important questions/answers:
cpan Would you like to configure as much as possible automatically? [yes] yes What approach do you want? (Choose 'local::lib', 'sudo' or 'manual') [local::lib] sudo
that’s it. now we are ready to install the DBI module:
cpan install DBI quit
for the DBD::Oracle module I decided to just download the source file and to do a manual compilation/installation:
cpan get DBD::Oracle quit cd ~/.cpan/build/DBD-Oracle-1.50* perl Makefile.PL make sudo make install
easy, isn’t it ?
create a little perl script and see if you can execute a test-statement:
echo "#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my @results;
my $db = DBI->connect ( 'dbi:Oracle:host=192.168.10.39;sid=dbs300;port=1530', 'my_oracle_usr/oracle', '' )
or die "Connection failed: ". DBI->errstr;
my $sql = $db->prepare('SELECT \'connected\' FROM DUAL' )
or die "Statement preparation failed: ". $db->errstr;
$sql->execute()
or die "Statement execution failed:".$sql->errstr;
while ( @results = $sql->fetchrow_array() ) {
print "$results[0] \n";
}
1;" >> oracle_test.pl
chmod u+x oracle_test.pl
./oracle_test.pl
connected
time to have fun with perl and oracle :)
