LATEST TOPICS

Simple Perl script to query Oracle database

This post is for the beginners, who are willing to write PERL codes for querying Oracle database.

Prerequisite:

  1. PERL must be installed (it comes by default with Oracle Binaries)
  2. DBI and DBD::Oracle module for PERL must be installed. These PERL modules provide the functionality for communicating with the Oracle database from PERL code.

DBI is the generic Database Interface module that facilitates database communication within the PERL code.

DBD::Oracle is the PERL module  which works with the DBI module to provide access to Oracle databases

 

We can verify, if PERL is installed or not as follow

[oracle@labserver ~]$ which perl
/app/oracle/product/12.1.0/perl/bin/perl
[oracle@labserver ~]$ perl -v

This is perl 5, version 14, subversion 1 (v5.14.1) built for x86_64-linux-thread-multi

Copyright 1987-2011, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

 

DBI module is installed or not can be verified as follows

[oracle@labserver ~]$ perl -e 'use DBI; print $DBI::VERSION,"n";'
1.616

 

DBD::Oracle module is installed or not can be verified as follows

[oracle@labserver ~]$ perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"n";'
1.28

In general the DBI module is shipped by default and you need to install the DBD::Oracle module manually. In case, DB::Oracle is not installed, you can install it using following article.

http://www.pythian.com/blog/another-dbdoracle-intall-kludge-64-bit-linux-and-oracle-11/

 

Example: Query Oracle database from PERL using the DBI module

#!/app/oracle/product/12.1.0/perl/bin/perl
####
#### The first Line indicates the PERL executable
#### to be used for compiling/running the script

#### Next, instructions are provided to use the DBI and DBD::Oracle modules
use DBI;
use DBD::Oracle;

#### Setting the ORATAB Location
$otab="/etc/oratab";

#### Setting ORACLE SID
$dbname="labpa";

#### Extracting Oracle Environment values from ORATAB
open ( OTAB, "<$otab" );
while (  )
{
if ( $_ =~  /^$dbname:/ )
        {
        @oraenv = split (/:/, $_);
        $ENV{"ORACLE_SID"} = $oraenv[0];
        $ENV{"ORACLE_HOME"} = $oraenv[1];

#### Set a Database Handler using the DBI->connect() function from DBI module
#### Database handler is used for the database communication
#### Notice that we are passing dbi::Oracle as the database interface driver
####
        my $dbh = DBI->connect("dbi:Oracle:","user","password")
                       or die "$DBI::errstr";

#### Setting the Statement Handler to be execute SQL against the database
#### Use the prepare() function of the Database Handler to set the SQL statements to be executed
####
        my $sth = $dbh->prepare("select sysdate from dual") or die "$DBI::errstr";

#### Executing the SQL statement against the Oracle database
#### Use the execute() function of the Statement Handler to run query against the database
####
        $sth->execute() or die "couldn't execute statement";

#### Use the fetchrow_array() to get the result of the SQL execution
####
                while (my $row = $sth->fetchrow_array())
                        {
                           print "Database $ENV{ORACLE_SID}-> sysdate: $row n";
                        }
        }
}

Hope, the article is helpful to you.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed