Setting up perl DBD::Oracle

By Brian Fitzgerald

Here are a few notes on setting up perl DBD::Oracle scripts. The objective is to write a script that works across perl versions, Linux versions, and Oracle versions. Assume that you want to create a perl script using only the operating system and an Oracle home.

For the sake of this article, the platforms are Linux 5 and 6. The perl versions found on the operating systems are 5.8.8 and 5.10.1, respectively. The Oracle versions are 11.1, 11.2, and 12.1. The perl versions supplied with the Oracle home are 5.8.3, 5.10.0, and 5.14.1 respectively. Already, there are five distinct perl versions in the mix.

A few of the errors and issues to be worked through are:

  • Can’t locate DBD/Oracle.pm in @INC
  • undefined symbol: PL_charclass
  • libclntsh.so.11.1: cannot open shared object file: No such file or directory
  • Perl command line arguments
  • Taint perl
  • Encapsulation of implementation details in perl modules.

Let’s start with some basics:

First let’s check out basic perl operability on a Linux 5 operating system:

$ unset PERL5LIB
$ unset LD_LIBRARY_PATH
$ unset ORACLE_HOME
$ uname -sro
Linux 2.6.18-274.el5 GNU/Linux
$ /usr/bin/perl -e 'printf "%vd\n", $^V'
5.8.8
$ /usr/bin/perl -e 'use strict'
$

So far, so good. Perl is version 5.8.8 We can access the perl binary and supplied package “strict”. Now let’s try the perl supplied on a Linux 6 system:

$ uname -sro
Linux 3.8.13-16.2.1.el6uek.x86_64 GNU/Linux
$ /usr/bin/perl -e 'printf "%vd\n", $^V'
5.10.1
$ /usr/bin/perl -e 'use strict'
$

Again, so far, so good. Now let’s try DBD::Oracle and /usr/bin/perl. You might get this error:

$ /usr/bin/perl -e 'use DBD::Oracle'
Can't locate DBD/Oracle.pm in @INC (@INC contains: /usr/local/lib64/perl5-
 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl-
 /usr/share/perl5/vendor_perl-
 /usr/lib64/perl5 /usr/share/perl5 .) at -e line 1.
BEGIN failed--compilation aborted at -e line 1.

(I have folded the output with “-“). The error means that module DBD::Oracle is not installed with the operating system. This is commonplace. From here, your options are to ask the system administrator to install DBD::Oracle, download and compile DBD::Oracle yourself, or use the DBD::Oracle found in the Oracle home. In this case, we will use the perl tree in the Oracle home. If you do, you might notice a new error message:

$ /u01/app/oracle/DB/product/12.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
Can't load '/u01/app/oracle/DB/product/12.1.0/db_1/perl/lib/site_perl/5.14.1-
/x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle:- 
libclntsh.so.12.1: cannot open shared object file: -
No such file or directory at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 190.-
 at -e line 1-
Compilation failed in require at -e line 1.-
BEGIN failed--compilation aborted at -e line 1.

Module DBD::Oracle loads ok, but it cannot find the native Oracle database driver libclntsh. The error can be resolved by setting LD_LIBRARY_PATH.

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/12.1.0/db_1/lib
$ /u01/app/oracle/DB/product/12.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

DBD::Oracle compiles on the Linux 6 box.

You can check that Oracle.so sits on top of Oracle client by using ldd:

$ ldd /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/-
auto/DBD/Oracle/Oracle.so
        linux-vdso.so.1 =>  (0x00007fff34469000)
        libclntsh.so.12.1 => /u01/app/oracle/DB/product/12.1.0/db_1/-
lib/libclntsh.so.12.1 (0x00007f8f563ea000)
        libclntshcore.so.12.1 => /u01/app/oracle/DB/product/12.1.0/db_1/-
lib/libclntshcore.so.12.1 (0x00007f8f55e77000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f8f55c6a000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f8f559e6000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8f557c8000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f8f555af000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f8f55399000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f8f55004000)
        libmql1.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libmql1.so (0x00007f8f54d8e000)
        libipc1.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libipc1.so (0x00007f8f54a10000)
        libnnz12.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libnnz12.so (0x00007f8f542eb000)
        libons.so => /u01/app/oracle/DB/product/12.1.0/db_1/lib/libons.so (0x00007f8f540a6000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f8f53e9e000)
        /lib64/ld-linux-x86-64.so.2 (0x00000039d6a00000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f8f53c9c000)

Now to try the same approach on Linux 5 / Oracle 11gR2:

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/11.2.0/db_1/lib
$ /u01/app/oracle/DB/product/11.2.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

It works. Finally, try the perl in the 11gR1 home.

$ export LD_LIBRARY_PATH=/u01/app/oracle/DB/product/11.1.0/db_1/lib
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'printf "%vd\n", $^V'
5.8.3
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
Can't locate DBD/Oracle.pm in @INC (@INC contains: -
/ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/-
5.8.3/x86_64-linux-thread-multi /ade/aime_perl58_amd64/-
perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl .) at -e line 1.
BEGIN failed--compilation aborted at -e line 1.
$

perl did not find module DBD::Oracle. perl seems to be searching under directory tree “/ade”, which does not exist on my system. “/ade” turns up in Google searches, and might be a build environment at Oracle Corp. To fix the search path, we’re going to set PERL5LIB for now.

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3-
/x86_64-linux-thread-multi
$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$

That works.

PERL5LIB has two path components. The tree under subdirectory /5.8.3 contains only text files. The tree under site_perl contains some binary object library files. Both trees need to be searched, or you will get errors.

These PERL5LIB variants also seem to work:

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3
$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl

You can switch the order around like this,

$ export PERL5LIB=/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl-
/5.8.3/x86_64-linux-thread-multi:-
/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/5.8.3

however, if you do, then overall, perl will get more misses while searching.

In general, we need LD_LIBRARY_PATH to find libclntsh and on some platforms, we need PERL5LIB to find DBD::Oracle.

Tracing we find that DBD::Oracle depends on shared object library Oracle.so:

$ /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$ strace -o tr /u01/app/oracle/DB/product/11.1.0/db_1/perl/bin/perl -e 'use DBD::Oracle'
$ grep Oracle.so tr | egrep -v NOENT
stat("/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/-
x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so",-
 {st_mode=S_IFREG|0755, st_size=164513, ...}) = 0
open("/u01/app/oracle/DB/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/-
x86_64-linux-thread-multi/auto/DBD/Oracle/Oracle.so", O_RDONLY) = 3
$

Shared object library Oracle.so is compiled together with the perl executable binary found in the same perl tree. The two should be used together.

Here are a two more errors that can arise from using using the perl binary and DBD::Oracle from two different perl trees:

$ /usr/bin/perl -e 'use DBD::Oracle'
Perl lib version (5.14.1) doesn't match executable '-e' version (v5.10.1)-
 at /u01/app/oracle/DB/product/12.1.0/db_1/perl/lib/5.14.1/-
x86_64-linux-thread-multi/Config.pm line 60.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 22.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/5.14.1/x86_64-linux-thread-multi/DynaLoader.pm line 22.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 160.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 160.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
Compilation failed in require at -e line 1.
BEGIN failed--compilation aborted at -e line 1.
Can't load '/u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/auto/DBI/DBI.so'-
 for module DBI: /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/auto/DBI/DBI.so: -
undefined symbol: PL_charclass at /usr/lib64/perl5/DynaLoader.pm line 200.-
 at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 268
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBI.pm line 268.
Compilation failed in require at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.
BEGIN failed--compilation aborted at /u01/app/oracle/DB/product/12.1.0/db_1/-
perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi/DBD/Oracle.pm line 17.

The best bet is to use DBD::Oracle from the same tree where you run perl.

Note that “use DBD::Oracle” seems to work, and we have not set ORACLE_HOME yet. From now on, we’ll set ORACLE_HOME because that’s the proper way to access the Oracle software. Not setting ORACLE_HOME eventually leads to problems down the road. We will use ORACLE_HOME to derive variable PERL5LIB. PERL5LIB has a perl version subdirectory component (/5.14.1/ for example). We’ll use the perl $^V variable to programmatically derive that subdirectory. Possibly uname could be used to derive /x86_64-linux-thread-multi/, but I have not tested it on other platforms, so for now, I hard-code it.

The standard way to set ORACLE_HOME is with oraenv, so we’ll use that. I find that oraenv sets LD_LIBRARY_PATH reliably, so I will not set LD_LIBRARY_PATH explicitly in my own scripts.

Creating an executable perl script with #! in the top line is out of the question because the path to perl would be different on each platform. Instead, we’ll create a wrapper. The wrapper could be in any shell. bash is used in this example. We want a wrapper that functions like the perl binary itself. Let’s create the wrapper:

$ unset ORACLE_BASE
$ unset ORACLE_HOME
$ unset LD_LIBRARY_PATH
$ ORAENV_ASK=NO . oraenv
The Oracle base has been set to /u01/app/oracle/DB
$ cat perl.bash
#!/bin/ksh

if [ -z "${ORACLE_HOME}" ] ; then
        echo 'You must set ORACLE_HOME'
        exit 1
fi

PERLBIN=${ORACLE_HOME}/perl/bin/perl
PERLV=`${PERLBIN} -e 'printf "%vd", $^V'` || exit 1

export PERL5LIB
PERL5LIB=${ORACLE_HOME}/perl/lib/${PERLV}
PERL5LIB=${PERL5LIB}:${ORACLE_HOME}/perl/lib/site_perl/${PERLV}/x86_64-linux-thread-multi

${PERLBIN} $*

Let’s start building the perl script:

$ cat dbd.pl
use strict;
use warnings;
use DBD::Oracle;
$ ls -l perl.bash dbd.pl
-rw-r--r--. 1 nobody nobody  43 Nov 27 07:05 dbd.pl
-rwxr-xr-x. 1 nobody nobody 375 Nov 27 06:58 perl.bash

Notice a few things about the perl script: (1) there is no #! at the top, (2) all perl scripts use the strict and warnings module, and (3) the script does not need the execute file permission bit set.

Use the wrapper to call the perl script.

$ ./perl.bash dbd.pl
$

The wrapper and the script seem to work. Let’s try something different:

$ ./perl.bash -e 'use DBD::Oracle'
syntax error at -e line 1, at EOF
Execution of -e aborted due to compilation errors.
$

Our wrapper is not passing the command line arguments correctly. To fix the argument handling, change the final line of the script from:

${PERLBIN} $*

to:

${PERLBIN} "$@"

Try again:

$ ./perl.bash -e 'use DBD::Oracle'
$

It seems to work across all our platforms. Let’s try Taint mode.

$ ./perl.bash -T -e 'use DBD::Oracle'
$ ./perl.bash -T dbd.pl
$

It seems to work in the Oracle 12.1 home. Now try the Oracle 11.1 home:

$ ./perl.bash -T dbd.pl
Can't locate strict.pm in @INC (@INC contains:-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
/x86_64-linux-thread-multi-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl/5.8.3-
 /ade/aime_perl58_amd64/perl58/bin/Linux/Opt/lib/site_perl) at dbd.pl line 1.
BEGIN failed--compilation aborted at dbd.pl line 1.
$

Taint (“-T”) does not trust environment variable PERL5LIB, and refuses to search it. To fix that, use the -I (include) command line switch instead. Change:

export PERL5LIB
PERL5LIB=${ORACLE_HOME}/perl/lib/${PERLV}
PERL5LIB=${PERL5LIB}:${ORACLE_HOME}/perl/lib/site_perl/-
${PERLV}/x86_64-linux-thread-multi

${PERLBIN} "$@"

to

unset PERL5LIB

PERLINC="-I ${ORACLE_HOME}/perl/lib/${PERLV}"
PERLINC="${PERLINC} -I ${ORACLE_HOME}/perl/lib/site_perl/${PERLV}"

${PERLBIN} ${PERLINC} "$@"
$ ./perl.bash -T dbd.pl
$

It works across platforms.

We’ll add the -w (warning) switch to identify risky coding practices.

${PERLBIN} -w ${PERLINC} "$@"

Here is an example of a warning:

$ ./perl.bash -e '$x=1'
Name "main::x" used only once: possible typo at -e line 1.
$

Next, we’ll create new directory /home/dba/perl/lib for creating our own collection of administrative perl modules. Let’s create a few perl modules. Here’s a module for getting a sysdba connection.

$ cat lib/Conn.pm
package Conn;

use strict;
use warnings;
use DBD::Oracle qw(:ora_session_modes);

sub new()
{
        my $this = bless {};
        return $this;
}

sub connectSysDba()
{
        my $this = shift;
        my $dbh = DBI->connect('dbi:Oracle:', '/', undef, {
                ora_session_mode        => ORA_SYSDBA,
                RaiseError              => 1
        } );
        $dbh->{ LongTruncOk } = 0;
        $dbh->{ LongReadLen } = 2000000;

        $this->{ DBH } = $dbh;
}

sub dbh()
{
        my $this = shift;
        return $this->{ DBH };
}

1;

The module is designed so that in case of any error, a script using it should fail early and fail loudly.

Here’s a module for getting attributes from v$database.

$ cat lib/Database.pm
package Database;

use strict;
use warnings;
sub new()
{
        my $this = bless {};
        return $this;
}

sub setDbh()
{
        my $this = shift;
        ( $this->{ DBH } ) = @_;
}

sub getVDatabaseHash()
{
        my $this = shift;
        my $sql = q{select * from v$database};
        my $href = $this->{ DBH }->selectrow_hashref( $sql );
        $this->{ DATABASE } = $href;
}

sub logMode()
{
        my $this = shift;
        $this->getVDatabaseHash();
        return $this->{ DATABASE }{ LOG_MODE };
}

sub logModeIsArchiveLog()
{
        my $this = shift;
        $this->logMode();
        return $this->{ DATABASE }{ LOG_MODE } eq 'ARCHIVELOG' ? 1 : 0;
}

1;

Notice that implementation details are hidden and encapsulated in the perl modules.

Here’s an administrative script that could do something depending depending on whether the database is in archivelog mode.

$ cat admin.pl
use strict;
use warnings;
use Conn;
use Database;

my $CONN = Conn->new();
$CONN->connectSysDba();
my $DB = Database->new();
$DB->setDbh( $CONN->dbh() );
if ( $DB->logModeIsArchiveLog() )
{
        printf "Do something with archivelog mode\n";
} else {
        printf "Do something with noarchivelog mode\n";
}

$

Examples:

$ ./perl.bash admin.pl
Do something with archivelog mode
$
$ ./perl.bash admin.pl
Do something with noarchivelog mode
$

The “something” could be backup, restore, flashback, duplicate database, etc. It’s up to you. Here is the finished perl wrapper.

$ cat perl.bash
#!/bin/bash

if [ -z "${ORACLE_HOME}" ] ; then
        echo 'You must set ORACLE_HOME'
        exit 1
fi

PERLBIN=${ORACLE_HOME}/perl/bin/perl
PERLV=`${PERLBIN} -e 'printf "%vd", $^V'` || exit 1

unset PERL5LIB

DBASCR=/home/dba/perl

PERLINC="-I ${ORACLE_HOME}/perl/lib/${PERLV}"
PERLINC="${PERLINC} -I ${ORACLE_HOME}/perl/lib/site_perl/${PERLV}"
PERLINC="${PERLINC} -I ${DBASCR}/lib"

${PERLBIN} -w ${PERLINC} "$@"

Summary:

  • Use perl binary executable and the perl system modules from the same perl tree.
  • Use a shell wrapper to call the perl binary and DBD::Oracle.
  • Encapsulate implementation details in your own perl modules.

 

 

 

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s