I started this post with the intention of having it be a quickie, just showing people how to connect to Oracle with Perl/DBI without having to have Oracle client or even Perl installed, but it’s not that simple. So I decided to rewrite it with detailed instructions.
Here is a list of what I used:
Machine: Sun Solaris SPARC on build and target machines
Oracle: Oracle 10g client (build machine only)
Perl: v. 5.10.0 (build machine only)
Perl modules: DBI, DBD::Oracle (1.22), PAR (1.008), pp (1.025), PerlIO (1.04) (build machine only)
How I did it:
Being the lazy bastard that I am, I decided to write a Perl module that would do my DBI connections for me once I supplied it the SID, username, and password. This module isn’t in CPAN, but you can probably find similar ones in there. Who knows maybe one day I’ll make it available to the public, but for now I will only explain how it works and show the tidbits I used to get it connecting without the Oracle client installed. Let’s call my module MyDB.pm (at the time of this writing, there is no MyDB.pm module in CPAN).
MyDB.pm: exports one subroutine: connect(). connect() takes an array reference of 3 values: SID, username, password. It then uses the given SID to check against %configured_dbs and fetch hostname/IP, port, and alternative SID. It then connects to the DBI. Pretty straight-forward.
Oracle Client: You’ll need the Oracle Client installed in your build machine. Or not. As long as you have access to the following files (the example here is for 10.1 version – your filenames may vary): libclntsh.so.10.1, and libnnz10.so. These are the magic libs that we will carry along with us.
PAR/pp: The Perl Archive Tool is a VERY handy module. It basically allows you to pack modules/files into a regular zip (renamed to .par) and even to create self extracting executables. It comes with tools such as parl to load those packages and deploy the scripts inside, or you can run it from inside a separate script to use your modules without having to install them in a specific path. Oh, and with the PAR self extracting executable, you can even bundle Perl core files and run it in a machine that doesn’t have Perl installed! Sweet, eh? pp is part of the PAR family – it’s thescript that does the creation of the par packages. One downside of PAR is that it creates potentially HUGE packages. A simple hello world can be over 2Mb large. But that’s not a concern in this tutorial, since we will be getting rid of having to keep Oracle up to date in every single machine, changing environment variables and the such.
When running a PAR package, it calculates the MD5SUM of the file and creates a temporary directory under /var/tmp/par-userId/cache-md5sum (replace userId with the ID of the person running the file, and md5sum with the actual md5sum) . It then unpacks the contents of the PAR into that directory and sets some environment variables – some of which will tell Perl to check in there for modules. However, it seems to not automatically extract the libs, so we have to modify MyDB.pm to have it behave appropriately.
Making MyDB.pm work when called in a PAR context
Create a BEGIN block checking for $ENV{PAR_TEMP}. This is the value of the PAR temp directory where the files should be extracted. Since PAR creates zipped files, it offers a method to return an Archive::Zip handler:
my $zip_handler = PAR::par_handle(‘MyDB.par’);
Now you can use Archive::Zip methods to manipulate your .par file. I used the following code for my BEGIN block:
BEGIN { if ($ENV{PAR_TEMP}) { # Get $par_cache (easier to type than $ENV{PAR_TEMP}) my $par_cache = $ENV{PAR_TEMP}; # Get Zip handler my $par_file = PAR::handle("MyDB.par"); # Set files to look for my %membersToFind = (’libclntsh.so.10.1’ =>1, ’libnnz10.so’ => 1 ); # Unpack files for my $m ($par_file->members()) { # Get path/name of the zipped file my $fileName = $m->fileName(); # Prepare to check if the libs # have already been extracted use Find::File; # Iterate through %membersToFind keys for my $k (keys %membersToFind) { # If zipped file is one of the keys... if ($fileName =~ /$k/) { # do nothing if the file has already been extracted next if find(sub { /$k/ or return 0 },$par_cache); # ...then remove from hash (we’ll check the hash later)... delete $membersToFind{$k}; # ... and unpack into $par_cache directory $par_file->extractMember($fileName,"$par_cache") || warn ("Failed to extract $fileName n"); } } } # Check to see if any of the files wasn’t unpacked because it wasn’t found if (scalar(keys %membersToFind) > 0) { warn("The following file(s) could not be found in PAR file: " . join(’,’,@{ keys %membersToFind }) ."n"); } } }
So now MyDB.pm will know what to do if $ENV{PAR_TEMP} is set. The next step is to generate the PAR file.
Creating the PAR package
You’ll need a dummy perl script to pass to pp:
$ echo "use MyDB;" > dummy.pl
and then create the PAR file. Make sure MyDB.pm and your oracle libs are in places where Perl can find it. I placed MyDB.pm in the same directory as dummy.pl, and set LD_LIBRARY_PATH to my $ORACLE_HOME/lib32 (if you’re using Instant Client, set it to $ORACLE_HOME/instantclient or wherever your instant client keeps the libs). The pp command is this (on a shell prompt):
$ pp -z 9 -v 3 -p -d -o MyDB.par -X DBD::Proxy -X DBD::DBM -X DBD::File -X DBD::NullP -X DBD::Gofer -X DBD::Sponge -X DBD::ExampleP -X DBD::mysql -X DBD::Gofer::Policy::rush -X DBD::Gofer::Policy::classic -X DBD::Gofer::Policy::Base -X DBD::Gofer::Policy::pedantic -X DBD::Gofer::Transport::pipeone -X DBD::Gofer::Transport::Base -X DBD::Gofer::Transport::stream -X DBD::Gofer::Transport::null -X DBD::mysql::GetInfo -X DBD::Chart -X DBD::Chart::Plot -M MyDB -M PerlIO -l libclntsh.so.10.1 -l libnnz10.so -l libociei.so # for Oracle instant client users only! Thanks to Jonathon Robinson for the heads up. (see comments for details) dummy.pl
pp command explained
pp: the PAR packer script
-z 9: maximum compression
-v 3: maximum verbosity
-p: create par file
-d: dependent. This means it will not add the Perl interpreter to the bundle. This reduces its size, but makes it rely on either perl being installed in the target machine, or your script (the one that’s going to use MyDB) being PAR’d without the -d option.
-0 MyDB.par: the filename to be used on output
-X module: removes un-necessary modules. Read more about it here.
-M module: the modules you want to add (PAR sometimes misses a module during its dependency scan). PerlIO is required.
-l lib: adds the additional libs
dummy.pl: last item of the command, tells pp what the main script is.
There – after lots of output on the screen, you should have a pretty large MyDB.par (at least 7Mb). Now we have to alter our main script (the one that uses MyDB in the first place) to have it handle the PAR file. Add the following lines right after your use strict; line (you DO use strict, RIGHT?!).
use PAR './MyDB.par'; use MyDB; # the rest is business as usual
Once altered, you can choose to pp your script to have it run without a perl interpreter on another machine. This might take several attempts, since you will have to test the executable a few times to make sure you got all your modules bundled. The basic pp command though is this:
$ pp -z 9 -v 3 -o yourscript.exe yourscript.pl # .exe not required
Here the absense of -d makes it assume -B which bundles Perl inside the executable. As mentioned before, you might need to add some -M module parameters before yourscript.pl to catch any missing modules.
Running the scripts
To run the scripts, just send them to the target machine and execute yourscript.exe. It will fetch MyDB.par in the same directory, extract it to $ENV{PAR_TEMP} the first time (so it might be a little slow at first) and carry on with its logic.
Caveat
I strongly advise that you delete your par_cache directory between rebuilds (while testing). The reason for this is that the cache will be created by the execution of yourscript.exe and NOT by MyDB.par. When this happens and you alter MyDB.par, the changes will not be reflected when you run yourscript.exe.
If you liked this post, please leave a comment. If you didn’t, leave a comment too! 🙂 I’d like to know what people think of my blog and ways to improve it.
3 Responses
Hey Vinny,
I’ve been struggling with this issue for days. How did you get around setting the ORACLE_HOME system variable on the client machine?
With your instructions above I receive the following error on a client machine (Oracle not installed):
DBI connect(‘host=;port=1521;sid=test’,’test’,…) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at DbConnect.pm line 103
As you might guess, line 103 contains the DBI->connect call.
Now, I’ve verified that the files are indeed extracted to $ENV{‘PAR_TEMP’}. When ORACLE_HOME is unset I get the above error message. I’ve attempted to set ORACLE_HOME to $ENV{‘PAR_TEMP’} but this doesn’t do the trick. I’ve even gone as far as copying the files from $ENV{‘PAR_TEMP’} to $ENV{‘PAR_TEMP’}/lib (which is where Oracle expects to find the .so files). This also hasn’t worked.
So to reiterate my question. How have you solved the problem with setting the ORACLE_HOME environment variable?
Thanks,
~ Jon
In articulating my problems in my above post, I realised what the real issue was (trying to get an Oracle DB connection without requiring ORACLE_HOME to be set). I found the following article:
http://download.oracle.com/docs/cd/B12037_01/appdev.101/b10779/oci01int.htm#423364
Note, the solution posted here is missing a critical so file. I got mine to work by setting my LD_LIBRARY_PATH to my $ORACLE_HOME/instantclient and changing the pp to include an additional module:
pp -z 9 -v 3 -p -d -o MyDB.par
-X DBD::Proxy
-X DBD::DBM
-X DBD::File
-X DBD::NullP
-X DBD::Gofer
-X DBD::Sponge
-X DBD::ExampleP
-X DBD::mysql
-X DBD::Gofer::Policy::rush
-X DBD::Gofer::Policy::classic
-X DBD::Gofer::Policy::Base
-X DBD::Gofer::Policy::pedantic
-X DBD::Gofer::Transport::pipeone
-X DBD::Gofer::Transport::Base
-X DBD::Gofer::Transport::stream
-X DBD::Gofer::Transport::null
-X DBD::mysql::GetInfo
-X DBD::Chart
-X DBD::Chart::Plot
-M MyDB
-M PerlIO
-l libclntsh.so.10.1
-l libnnz10.so
-l libociei.so
dummy.pl
Cheers,
~ Jon
Hi Jon,
I checked all my systems and only the instantclient has libociei.so. Thanks for bringing that up, since I did all my testing on regular clients (non-instant). I’ll add the note to the main post.
Vinny