64-bit Perl with 32-bit Oracle issue on Windows 7(64-bit)

If 64-bit Perl is installed with DBD::Oracle, and it uses 32-bit Oracle client then connection to Oracle will fail. For 64-bit Perl it expects 64-bit Oracle client installed, same for 32-bit Perl - it will expect 32-bit Oracle client. Perl will not connect to Oracle if there is a mismatch in library version.

My simple program below to test connection to Oracle with DBD::Oracle module:

########## start program: testOracDB.pl ###################

use DBI;

my $dbh = DBI->connect( 'dbi:Oracle:ora10g', 'system', 'system');

my $sql = "SELECT systimestamp FROM DUAL";

my $sth = $dbh->prepare( $sql );

$sth->execute();

print $sth->fetchrow_array;

$sth->finish;

########### end program #############################

The program above fails with the following error:

$ perl testOracDB.pl

install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:%1 is not a valid Win32 application at C:/Perl/lib/DynaLoader.pm line 191. at (eval 3) line 3.

Compilation failed in require at (eval 3) line 3. Perhaps a required shared library or dll isn't installed where expected at testOracDB.pl line 3.

Issue:
On my Windows 7(64-bit), perl 5v14(64-bit) was installed.

$ perl -v

This is perl 5, version 14, subversion 4 (v5.14.4) built for MSWin32-x64-multi-thread.

(truncated ouput)

My Oracle client version was 32-bit. How to check version of sqlplus is below.

Resolution:

• Install the 64-bit Oracle client on the box.
• After successful installation of Oracle 11g(64-bit) client, update Windows "Environment Variables" for ORACLE_HOME, LD_LIBRARY_PATH, and Path variables to point to correct Oracle.
• Check if the sqlplus is actually 64-bit:
The method I found googling that "If the two directories exists: $ORACLE_HOME/lib32 and $ORACLE_HOME/lib, then it is 64 bit Oracle client version" does not help. My Oracle 11g(64-bit) client doesn't show 2 LIB folders.


In Windows 7(64-bit) the best way to confirm would be to check the Task Manager when sqlplus is running. From Windows command prompt run SQLPLUS. Check the Task Manager, it will correctly show "sqlplus.exe *32" if Oracle client is 32-bit. For 64-bit client it shows "sqlplus.exe".

Running the example program will give proper result now:

$ perl testOracDB.pl

12-JAN-14 10.41.06.635645 AM +05:30

We will face the same issue if 32-bit Perl is installed and if it tries to use 64-bit Oracle. 32-bit Perl will have a DBD::Oracle with 32-bit client, so it will expect to find a 32-bit Oracle client. It will not be able to use a 64-bit Oracle client.

Note that, we can have both 32 and 64-bit Oracle client installed on the same box. Update the "Environment Variables" settings accordingly as per the need.