how to connect perl with oracle

Here we will connect perl  with oracle.

Install & Configure Perl DBD for Oracle 11.2 on Centos linux.

Steps 1: Install Oracle 11.2 server/client on the hosts. I presume you have successfully installed Oracle 11gR2 in server side PC and Oracle instant client in client side PC.
You can download oracle from download.oracle.com

Steps 2: Install Perl on the host.
Check if perl is already installed on the host:

    # perl -v

Most hosts have perl already installed.
You can download and install perl from http://www.perl.org/get.html.
Step 3: Download PERL DBD-Oracle

Download link: http://search.cpan.org/~pythian/DBD-Oracle-1.44/
Just copy and paste in URL and download.
Step 4: unzip and untar the download DBD-Oracle

    [oracle@host1 tmp]$ gunzip DBD-Oracle-1.44.tar.gz
    [oracle@host1 tmp]$ tar -xvf DBD-Oracle-1.44.tar


Step 5: Create file oci.conf
Create file "oci.conf" at "/etc/ld.so.conf.d/" as root having entry of the location of Oracle LD_LIBRARY_PATH
For this example in mine oci.conf contains :-

"/opt/oracle/instantclient_11_2" of course without quotes.
then run following commands :-

    [root@host1 ~]$ more /etc/ld.so.conf.d/oci.conf
    output should be :- /opt/oracle/instantclient_11_2

    [root@host1 ld.so.conf.d]# ldconfig -v

What is ldconfig (from the man pages)
"DESCRIPTION: ldconfig  creates  the  necessary links and cache to the most recent shared libraries found in the directories specified on the command line, in the file /etc/ld.so.conf, and in the trusted directories (/lib and /usr/lib).  The cache is used by the run-time linker, ld.so or ld-linux.so.  ldconfig checks the header and filenames of the libraries it encounters when determining which versions should have their links updated."

Step 6: Install DBD-Oracle
Go to the directory where u untared the downloaded DBD-Oracle
Note: Make sure u have completed Step 5


    [root@host1 DBD-Oracle-1.44]# perl Makefile.PL -V 11.2.0
    [root@host1 DBD-Oracle-1.44]# make install

 Step 7: Test the install
Login back as Oracle user:
Create a script (dbd_oracle_test.pl) with the text below:

    #!/usr/bin/perl

    $host="serverora11gr2";
    $ora_listener="LISTENER";
    $oracle_sid="orcl";
    $listener_port="1521";
    $ora_user="scott";
    $ora_password="tiger";
    $db_table="emp";


    use DBI;
    use DBD::Oracle;


    my $dbh = DBI->connect("dbi:Oracle:host=$host;port=$listener_port;sid=$oracle_sid",$ora_user, $ora_password)
      or die "Error Connecting to Oracle : " . DBI->errstr;


    my $stm = $dbh->prepare("SELECT ename FROM $db_table")
      or die "Database Error: " . $dbh->errstr;


    $stm->execute()
      or die "Database Error: " . $sth->errstr;

    print "\n";
    while (( $ename ) = $stm->fetchrow_array() )
    { print "Employee name : $ename\n"; }


    print "\n";

    $stm->finish;


    $dbh->disconnect;


then run this  as :-

    $ perl dbd_oracle_test.pl

this will show this output:-

    Employee name : SMITH
    Employee name : ALLEN
    Employee name : WARD
    Employee name : JONES
    Employee name : MARTIN
    Employee name : BLAKE
    Employee name : CLARK
    Employee name : SCOTT
    Employee name : KING
    Employee name : TURNER
    Employee name : ADAMS
    Employee name : JAMES
    Employee name : FORD
    Employee name : MILLER

now its complete