connecting oracle from perl

divyashree · 05-30-2012, 01:27 PM

I was trying to run this command and got this error :

Code:

perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'

Error:

Quote:

Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: /home/oracle/oracle/product/10.2.0/db_2/lib/libnnz10.so: cannot restore segment prot after reloc: Permission denied at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
at -e line 1
Compilation failed in require at -e line 1.
BEGIN failed--compilation aborted at -e line 1.

But while installing DBD:Oracle from source, it installed successfully without any error.

anomie · 05-30-2012, 01:51 PM

Not sure what that error message means, and haven't searched the 'net for it. (Have you?)

I'm using Perl to extract rows from Oracle (via SELECT) statements, and I also utilize the DBI and DBD::Oracle modules.

A few questions:

Have you set up your Oracle environment for the user who is attempting to run that command? For Bourne shell, that includes exporting ORACLE_HOME, ORACLE_SID, ORACLE_BASE, and possibly PATH and LD_LIBRARY_PATH.
How did you install DBD::Oracle, exactly? (Not sure what you specifically mean by "from source".)

For posterity's sake, here's a quick example (YMMV) of a file that can be sourced into a Bourne shell environment:

Code:

# Environment for Oracle client
#
export PATH=/bin:/usr/bin:/usr/lib/oracle/11.2/client64/bin
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export ORACLE_BASE=/usr/lib
export ORACLE_SID=BIGDB
export ORACLE_OWNER=oracle
export NLS_LANG='American_America.WE8ISO8859P1'
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/lib/oracle/11.2/client64/lib

And here's a Perl script that SELECTs rows from a table, after inheriting the above environment:

Code:

#!/usr/bin/perl
 
use warnings ;
use strict ;
use DBI ;

# Make connection to Oracle
#
my $oh = DBI->connect(  'dbi:Oracle:BigDB',
                        'sneaky',
                        '***' ) 
                        or die "DB error: $DBI::errstr" ;
 

# Build and execute query
#
my $query = qq! SELECT * FROM GOOD_FUN_TABLE ! ;
my $pquery = $oh->prepare($query) ;
$pquery->execute ;
 
while (my @row = $pquery->fetchrow_array) {
 
        # Perl gets angry about NULL values, so we get
        # a little help from the neat_list function. 
        #
        print DBI::neat_list(\@row) . "\n" ;
 
}
 
# Close prepared query handle
#
$pquery->finish ; 
 
$oh->disconnect ;

I need to do an LQ wiki topic on Perl + Oracle one of these days. It's a pain in the %#$ to set up, sadly. (Much easier with Python and cx_Oracle. But I like Perl better. I digress.)

divyashree · 05-30-2012, 02:20 PM

Thanks anomie for your response.

I have searched in net,tried some(http://modperlbook.org/html/22-1-2-i...oad-DBD-O.html), but not getting resolved.

All the environment are set in ~.bashrc file of user oracle.

Initially I tried to install through cpan and result is as below:

Code:

install DBD:Oracle
Running install for module 'DBD::Oracle'
Running make for P/PY/PYTHIAN/DBD-Oracle-1.44.tar.gz
  Has already been unwrapped into directory /root/.cpan/build/DBD-Oracle-1.44-CaTjqL
  Has already been made
Running make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/000-report-versions.t ... # Testing with Perl 5.008008, /usr/bin/perl
t/000-report-versions.t ... 1/? #     Carp version is 1.04
#     Config version is undefined
#     DBI version is 1.621
#     Data::Dumper version is 2.131
#     Devel::Peek version is 1.03
#     DynaLoader version is 1.05
#     Encode version is 2.12
#     Exporter version is 5.58
#     ExtUtils::MakeMaker version is 6.62
#     Math::BigInt version is 1.77

#   Failed test 'use Oraperl;'
#   at t/000-report-versions.t line 440.
#     Tried to use 'Oraperl'.
#     Error:  install_driver(Oracle) failed: Can't load '/root/.cpan/build/DBD-Oracle-1.44-CaTjqL/blib/arch/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: /home/oracle/oracle/product/10.2.0/db_2/lib/libnnz10.so: cannot restore segment prot after reloc: Permission denied at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
#  at (eval 29) line 3
# Compilation failed in require at (eval 29) line 3.
# Perhaps a required shared library or dll isn't installed where expected
#  at /root/.cpan/build/DBD-Oracle-1.44-CaTjqL/blib/lib/Oraperl.pm line 55
# Compilation failed in require at (eval 28) line 2.
# BEGIN failed--compilation aborted at (eval 28) line 2.
Bailout called.  Further testing stopped:  can't load Oraperl
# Tests were run but no plan was declared and done_testing() was not seen.
FAILED--Further testing stopped: can't load Oraperl
make: *** [test_dynamic] Error 255
  PYTHIAN/DBD-Oracle-1.44.tar.gz
  /usr/bin/make test -- NOT OK
//hint// to see the cpan-testers results for installing this module, try:
  reports PYTHIAN/DBD-Oracle-1.44.tar.gz
Running make install
  make test had returned bad status, won't install without force
Failed during this command:
 PYTHIAN/DBD-Oracle-1.44.tar.gz               : make_test NO

Then from source I tried as:

Quote:

perl Makefile.PL
make
make install

without error.

I generally use shell scripts in my works.
But since last few weeks ,I am learning perl and I love to use perl in my work whenever possible.

anomie · 05-30-2012, 02:34 PM

FWIW - and I hope you're able to determine otherwise - I was not able to get DBD::Oracle working with RHEL5 and Perl v5.8 at all.

So I actually downloaded the latest Perl source code, compiled it and installed it into a "sandboxed" environment (using an unprivileged user), e.g. /myperl. Then, I installed DBI and DBD::Oracle into the shiny, new /myperl environment.

In order to do all that successfully, I first had to set:

Code:

export PATH=/myperl/bin:/bin:/usr/bin
export PERL5LIB=/myperl/lib

So, the sequence was:

Set the environment above.
Create /myperl, and give your user ownership/write permissions to it.
Download, compile, and install Perl into /myperl.
Install DBI and DBD::Oracle using the cpan(1) command (it is included with Perl).

Then, of course, for Perl scripts that will utilize the new environment, set #!/myperl/bin/perl as the interpreter.

If you decide you're crazy enough (like me) to go to all this trouble, do NOT do any of these steps (except for creating the /myperl directory) as root. If you accidentally trample your Perl v5.8 install, you could potentially screw up your system.