Using Perl DBI module to interact with an oracle database

In this article, I'll demonstrate how we can use the famous Perl DBI module to connect to an Oracle database. To connect to a database, DBI uses database driver modules (DBD) which handles the entire interaction between the queries typed within the Perl script and the actual database connectivity engine or in simple terms the database client. DBI comes available with the database driver for MySQL database by default as MySQL is the more popular database among folks engaged in web development.

The setup:
I'll be working with a Centos 6 system for the purpose of this demonstration and have installed the Oracle 11g database XE edition since it's quite easy to set up. I've also given the oracle user full superuser privileges temporarily.

So, let's get started.

Installing the DBD for Oracle:
Before downloading the package we need to make sure that we are logged in as the oracle user and the oracle user environment is set. To set the environment for Oracle XE edition, run the following script:

. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh 

This sets up environment variables like ORACLE_HOME etc.

Now let's download the DBD:Oracle package from CPAN via wget.

-bash-4.1$ wget http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.64.tar.gz
--2018-03-27 19:05:41--  http://search.cpan.org/CPAN/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.64.tar.gz
Resolving search.cpan.org... 199.15.176.188
Connecting to search.cpan.org|199.15.176.188|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://www.cpan.org/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.64.tar.gz [following]
--2018-03-27 19:05:46--  http://www.cpan.org/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.64.tar.gz
Resolving www.cpan.org... 151.101.2.49, 151.101.66.49, 151.101.130.49, ...
Connecting to www.cpan.org|151.101.2.49|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 444613 (434K) [application/x-gzip]
Saving to: “DBD-Oracle-1.64.tar.gz”

100%[=================================================================================================================================================>] 444,613      287K/s   in 1.5s

2018-03-27 19:05:50 (287 KB/s) - “DBD-Oracle-1.64.tar.gz” saved [444613/444613]


Now let's extract the package and take a look at what's inside.

-bash-4.1$ tar xzf DBD-Oracle-1.64.tar.gz
-bash-4.1$
-bash-4.1$ cd DBD-Oracle-1.64
-bash-4.1$ ls
Changes       dbdimp.c  dbivport.h  hints    lib      Makefile.PL  META.json  mkta.pl  oci.def     Oracle.h   README           README.mkdn  Todo
CONTRIBUTORS  dbdimp.h  examples    INSTALL  LICENSE  MANIFEST     META.yml   oci8.c   ocitrace.h  Oracle.xs  README.help.txt  t            typemap
-bash-4.1$

Before starting with the installation make sure that you have gcc installed as we'll need a C compiler to build the binaries from the source. Also, we need to export the following variables:

export ORACLE_USERID=hr/demo123
export ORACLE_DSN='dbi:Oracle:XE'

export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/xe/lib

The ORACLE_USERID is the user id and password for the user that we will use to connect to the database and ORACLE_DSN is the data source name which is the Oracle DBD name dbi:Oracle and the name of the database instance which is XE in this case. The LD_LIBRARY_PATH variable denotes the location where the installer should look for any library files it may require.

Now, execute the following commands to install the Oracle DBD package:

perl Makefile.PL

make

make test

sudo make install

Once the commands mentioned above complete successfully, check the installed versions of the DBI module and the database driver to ensure that the installation was successful.

-bash-4.1$ perl -e 'use DBI; print $DBI::VERSION,"\n";'
1.609
-bash-4.1$ perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'
1.64
-bash-4.1


The working script:
Given below is a simple Perl script that uses the DBI module to connect to the XE instance and query some records from the table named employees. I executed the script successfully while logged in as the oracle user. I'm not certain if it'll work if executed as a different user.

#!/usr/bin/perl

use DBI;        #connect to database

$dbh=DBI->connect("dbi:Oracle:XE",'hr','demo123') or die "Error $! \n";

##connect function calls bliss function and creates the object $dbh#

$sth=$dbh->prepare("select EMPLOYEE_ID, FIRST_NAME from EMPLOYEES WHERE JOB_ID='SH_CLERK'") or die "Errors $! \n";

##prepare compiles the SQL query#

$res=$sth->execute();

eval {

$res=$sth->execute();
};

if($@) { print $@,"\n"; next; }
else { print "query executed successfully \n"; }

if($res) {

        print "EMPLOYEE_ID\tFIRST_NAME\n";
        while(@row=$sth->fetchrow_array) {
                print "$row[0]\t\t$row[1]\n";
                }

#$sth is also an object and fetchrow_array is a function that works on this object#
#fetchrow_array function fetches each record as a separate array element#
         }

$dbh->disconnect;


Conclusion:
In this article, we demonstrated how to install the database driver for Oracle and use it with the Perl DBI module to interact with an Oracle database.