Perl - Oracle Connectivity

arvind_hotdog has asked for the wisdom of the Perl Monks concerning the following question:

hi ppl, Im very new to Perl and Unix. I have a script which is not working in Production environment. Same script for the same is provided below ## Start of Script


my $ORACLE_HOME = "/opt/oracle/app/oracle/product/10g";
my $ORACLE_SID1="DBHOSTNAME";
my $TGT_USER="USER1";
my $TGT_PASS="PASSWORD";


$ENV{ORACLE_HOME}=$ORACLE_HOME;
$ENV{PATH}="$ORACLE_HOME/bin:/usr/sbin";
#$ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib32";
#$ENV{CLASSPATH}="$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbm
+s/jlib";


BEGIN {
    die "ORACLE_HOME not set\n" unless $ENV{ORACLE_HOME};
    unless ($ENV{OrAcLePeRl}) {
       $ENV{OrAcLePeRl} = "$ENV{ORACLE_HOME}/perl";
       $ENV{PERL5LIB} = "$ENV{PERL5LIB}:$ENV{OrAcLePeRl}/lib:$ENV{OrAc
+LePeRl}/lib/site_perl";
       $ENV{LD_LIBRARY_PATH} = "$ENV{ORACLE_HOME}/lib32";
       exec "$ENV{OrAcLePeRl}/bin/perl", $0, @ARGV;
    }
}
use strict;
use warnings;
use DBI;
my $sql;
my $sth;
my $sql_ins;
my $sql_del;
my $sql_upd;
my $sql_com;
my $sth_ins;
my $sth_del;
my $sth_upd;
my $sth_com;
my $dbh_src;
my $dbh_tgt;
my $count_of_rev;
#---------------------------------------- common initialization block 
+- end ----------------------#
print "Variable initialization block executed\n";
#---------------------------------------- Get Account id - Start -----
+-----------------#
my $sh_name=$ARGV[0];
my @acc_row;

$ENV{ORACLE_SID}=$ORACLE_SID1;
$dbh_tgt = DBI->connect("dbi:Oracle:$ENV{ORACLE_SID}", $TGT_USER, $TGT
+_PASS) or die;
print "Connection Successfull\n";
$dbh_tgt->disconnect;
[download]

## End of Script When i run the above script i get the following error. Can't load '/opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /opt/oracle/app/oracle/product/10g/perl/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at sample_test.pl line 45 Can anyone guide me through what might be potential problem causing this issue. Im kind of struck with this for couple of days and deadline is approaching. Thanks in advance for your replies. Hi, I have updated the thread. Sorry for the inconvenience cause and adding a few more details to the thread , the connectivity thing used in the code, i got it as a plug in from one of the sites http://www.orafaq.com/wiki/Perl When i made use of the following line in plugin

 $ENV{LD_LIBRARY_PATH} = "$ENV{LD_LIBRARY_PATH}:$ENV{ORACLE_HOME}/lib3
+2:$ENV{ORACLE_HOME}/lib";
[download]

was causing me the following issue, wrong ELF class: ELFCLASS64 so i had to change he code $ENV{LD_LIBRARY_PATH} = "$ENV{ORACLE_HOME}/lib32"; As per suggestion given in one of the sites

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Update: If you get an ELFCLASS64 error, try setting LD_LIBRARY_PATH to
+ $ORACLE_HOME/lib32 instead.
[download]

But still the other two erorrs are persisting...

Comment on Perl - Oracle Connectivity Select or Download Code

Replies are listed 'Best First'.
Re: Perl - Oracle Connectivity by Tux (Canon) on Jun 24, 2013 at 10:36 UTC
Please use `<code>` tags to format your code. Almost impossible to read your question like this. Have you tried to connect as described in the docs? `DBI->connect ("dbi:Oracle:$ENV{ORACLE_SID}", $TGT_USER, $TGT_PASS) … => DBI->connect ("dbi:Oracle:sid=$ENV{ORACLE_SID}", $TGT_USER, $TGT_PASS) + … or DBI->connect ("dbi:Oracle:", $TGT_USER, $TGT_PASS) …` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]
Re: Perl - Oracle Connectivity by marto (Cardinal) on Jun 24, 2013 at 10:44 UTC
"I have a script which is not working in Production environment." So this script works in a non production environment? If so you need to work out what differences there are between these two systems. Did you install DBD::Oracle properly on this production system, or simply try to copy files between systems? Are you sure the environment variables are set correctly? As an aside, to avoid potential problems I'd leave both the system perl and the perl which ships with Oracle alone and install my own elsewhere.	[reply]
Re: Perl - Oracle Connectivity by sundialsvc4 (Abbot) on Jun 24, 2013 at 13:08 UTC
It seems to me that there’s a very good chance that some installation step was not repeated on the production systems. Generally speaking, you can’t simply copy files from one to another. The actual message is clear: `Can't load '/opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory` Try a command such as `find /opt/oracle -name Oracle\.*` to see if, and if so where, the Oracle driver module is located on the production system. Then, do the same test on the non-production system. One of the steps performed by a CPAN install of the drivers on a target machine is to identify the correct file-path leading to the correct driver. There is an excellent chance that the development machine is slightly more-recent (or, slightly less ...) than the production machine. CPAN module installation must often be repeated after the underlying database drivers have been updated, as vendors often slightly-change filenames and filepaths to enable multiple versions to co-exist.
Re^2: Perl - Oracle Connectivity by arvind_hotdog (Initiate) on Jun 25, 2013 at 06:56 UTC
Hi with respect to your reply, i execute the command find /opt/oracle -name Oracle\.* in Production and in Development environment. Please find the same posted below In Production environment the files are present in the following path `/opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux +-thread-multi/DBD/Oracle.pm /opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux +-thread-multi/auto/DBD/Oracle/Oracle.so /opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux +-thread-multi/auto/DBD/Oracle/Oracle.h /opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i686-linux +-thread-multi/auto/DBD/Oracle/Oracle.bs` [download] `In development environment the files are present in the following path /opt/oracle/11g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/ +DBD/Oracle/Oracle.bs /opt/oracle/11g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/ +DBD/Oracle/Oracle.so /opt/oracle/11g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/auto/ +DBD/Oracle/Oracle.h /opt/oracle/11g/perl/lib/site_perl/5.8.3/i686-linux-thread-multi/DBD/O +racle.pm` [download] Thanks	[reply] [d/l] [select]
Re^3: Perl - Oracle Connectivity by Tux (Canon) on Jun 25, 2013 at 09:34 UTC
Do your Oracle libs, your DBD::Oracle objects and the perl you use all have the same architecture? Like when using 64bit objects, you will need a 64bit perl: $ file /opt/oracle/app/oracle/product/10g/perl/lib/site_perl/5.8.3/i68 +6-linux-thread-multi/auto/DBD/Oracle/Oracle.so $ file /opt/oracle/11g/perl/lib/site_perl/5.8.3/i686-linux-thread-mult +i/auto/DBD/Oracle/Oracle.so $ file `which perl` $ perl -v examples 64bit system: $ locate Oracle.so \| xargs file /pro/3gl/CPAN/DBD-Oracle-svn/blib/arch/auto/DBD/Oracle/Oracle.so: + ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamica +lly linked, BuildID[sha1]=0x634e4eeda1913e3bcc82e83683476ddf93e2d68b, + not stripped /pro/lib/perl5/site_perl/5.14.1/x86_64-linux-ld/auto/DBD/Oracle/Oracle +.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamica +lly linked, BuildID[sha1]=0x672b28a177525e87ea79511e7308a00a4253a3b1, + not stripped /pro/lib/perl5/site_perl/5.16.2/x86_64-linux-ld/auto/DBD/Oracle/Oracle +.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamica +lly linked, BuildID[sha1]=0x9b78d14ec3db186add1217b873eb6e0e244b15d8, + not stripped $ file `which perl` /pro/bin/perl5.16.2: ELF 64-bit LSB executable, x86-64, version 1 (SYS +V), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, Buil +dID[sha1]=0x6141d33c4fed73f117265c8980077086a63f4ea9, not stripped $ ldd /pro/lib/perl5/site_perl/5.16.2/x86_64-linux-ld/auto/DBD/Oracle/ +Oracle.so linux-vdso.so.1 (0x00007fff58bff000) libocci.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libocci.s +o.11.1 (0x00007f7cca5da000) libclntsh.so.11.1 => /usr/lib/oracle/11.2/client64/lib/libclnt +sh.so.11.1 (0x00007f7cc7d48000) libpthread.so.0 => /lib/../lib64/libpthread.so.0 (0x00007f7cc7 +b2c000) libc.so.6 => /lib/../lib64/libc.so.6 (0x00007f7cc7787000) libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f7cc745500 +0) libm.so.6 => /lib64/libm.so.6 (0x00007f7cc715e000) libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7cc6f48000) libnnz11.so => not found libdl.so.2 => /lib64/libdl.so.2 (0x00007f7cc6d43000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f7cc6b2b000) libaio.so.1 => /lib64/libaio.so.1 (0x00007f7cc6928000) /lib64/ld-linux-x86-64.so.2 (0x00007f7ccaa6d000) $ file /usr/lib/oracle/11.2/client64/lib/libocci.so /usr/lib/oracle/11.2/client64/lib/libocci.so: symbolic link to `libocc +i.so.11.1' 32bit system: $ locate Oracle.so \| xargs file /pro/lib/perl5/site_perl/5.10.0/i686-linux-64int/auto/DBD/Oracle/Oracl +e.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), + dynamically linked, not stripped /pro/lib/perl5/site_perl/5.12.2/i686-linux-64int-ld/auto/DBD/Oracle/Or +acle.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), + dynamically linked, BuildID[sha1]=0xffc084157f97d79089c90edc0a6ad681 +bf13cca2, not stripped /pro/lib/perl5/site_perl/5.14.1/i686-linux-64int-ld/auto/DBD/Oracle/Or +acle.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), + dynamically linked, BuildID[sha1]=0x4acbc12ed8f3024310e8b4d43003e05e +8262f98d, not stripped /pro/lib/perl5/site_perl/5.16.3/i686-linux-64int/auto/DBD/Oracle/Oracl +e.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), + dynamically linked, BuildID[sha1]=0x3de5f5ea3f207001b31657f00b5dfc73 +59a072da, not stripped /pro/lib/perl5/site_perl/5.18.0/i686-linux-64int-ld/auto/DBD/Oracle/Or +acle.so: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), + dynamically linked, BuildID[sha1]=0xf68f6a584bf07363d640c8c7a8b7a093 +e987cbd9, not stripped $ file `which perl` /pro/bin/perl5.18.0: ELF 32-bit LSB executable, Intel 80386, version 1 + (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.16, + BuildID[sha1]=0xf4d0718a75c0dbe0d874f0575a4e415a1e6ad96c, not stripp +ed $ ldd /pro/lib/perl5/site_perl/5.18.0/i686-linux-64int-ld/auto/DBD/Or +acle/Oracle.so linux-gate.so.1 (0xb7791000) libocci.so.11.1 => /usr/lib/oracle/11.2/client/lib/libocci.so. +11.1 (0xb7605000) libclntsh.so.11.1 => /usr/lib/oracle/11.2/client/lib/libclntsh +.so.11.1 (0xb560b000) libpthread.so.0 => /lib/libpthread.so.0 (0xb55b7000) libc.so.6 => /lib/libc.so.6 (0xb543d000) libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0xb5350000) libm.so.6 => /lib/libm.so.6 (0xb530e000) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0xb52f0000) libnnz11.so => not found libdl.so.2 => /lib/libdl.so.2 (0xb52eb000) libnsl.so.1 => /lib/libnsl.so.1 (0xb52d1000) libaio.so.1 => /lib/libaio.so.1 (0xb52ce000) /lib/ld-linux.so.2 (0xb7792000) $ file /usr/lib/oracle/11.2/client/lib/libocci.so.11.1 /usr/lib/oracle/11.2/client/lib/libocci.so.11.1: ELF 32-bit LSB shared + object, Intel 80386, version 1 (SYSV), dynamically linked, not strip +ped [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l]
Re^4: Perl - Oracle Connectivity by arvind_hotdog (Initiate) on Jun 25, 2013 at 16:02 UTC
Re^5: Perl - Oracle Connectivity by Tux (Canon) on Jun 25, 2013 at 16:08 UTC
Re: Perl - Oracle Connectivity by prashantktyagi (Scribe) on Jun 24, 2013 at 10:38 UTC
Hi arvind, Welcome to monastery. use <code> tags to separate code from text. See also How do I post a question effectively?	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks