Monthly Archives: July 2016

Connect from 32 bit environment to 64 Oracle instance

One may retrieve the version of Oracle with a simple command: “select * from v$version;”. One may then see that the Oracle instance is a 64 bit version. In my case, the full version name is “Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production”. Once this is known, one may ask whether it is possible to access this database from a 32 bit environment. Accessing an Oracle instance from a 32 bit environment is the subject of this small note.
We have multiple ways to access an Oracle instance.
The first method to access an Oracle instance is via SQL Developer. This works straightforward. Once installed, one may access from a 32 bit SQL Developer the 64 bit Oracle instance. No strings attached.
I then created a line in the tnsnames.ora. The entry is labeled as ORCL. I checked whether this worked with “tnsping ORCL”. This also worked without any issues. I then connected to the database with sqlplus. That also worked fine. I used a 11gR2 client. No issues were found to connect this to the Oracle version 12.

So the standard tools worked fine. They were all 32 bits whereas the Oracle instance was 64 bits.

Then I created a report. This can be done from the OBIEE environment. One connects to BI Publisher on an OBIEE server (something like http://van-maenen.com:7780/xmlpserver). One may then create a report. Let us assume that the report is stored as Tom\JaarAantal. The question is whether it is possible to access the report from a 32 bit tool. To this end, we start a 32 bit Word application that has a BI publisher add-in. It was straightforward to access the report. It was also possible to create a report from Word. See below the result from a 32 bit Word application using data from a 64 bit OBIEE server.
Naamloos

Then I tried to create a 32 bits ODBC connection to the 64 bits Oracle instance. I could directly use the Oracle client ODBC facilities to set up an ODBC connection. I could also use this ODBC connection to retrieve data into a 32 bits Excel sheet. I also used this 32 bit ODBC connection to connect from Qlikview to the 64 bit Oracle instance. No issue found there:
Untitled

Subsequently, I tried to access the 64 bits Oracle database with a 32 bit OLE DB connection. I used the connectstring; “Provider=OraOLEDB.Oracle.1;Password=bonvegni;Persist Security Info=True;User ID=hr;Data Source=ORCL;”. This worked nicely. I checked the OLE DB connection from Excel. This allows a very intuitive interface to connect to a database (such as Oracle). One may choose how to connect, be it ODBC or OLE DB.

I also tried to access the Oracle database from the squirrel client. This is a java client that uses java jar files to connect to an Oracle instance. Also there, no issue found when connecting to the Oracle database.
Untitled

It is also possible to connect from Perl on the 32 bit environment to the Oracle 64 bit environment. I used:

#!/usr/bin/perl 
use DBI;
use DBD::Oracle; 
print "Hello World.\n"; 
print $DBI::VERSION,"\n";
print $DBD::Oracle::VERSION,"\n";

my $db=DBI->connect("dbi:Oracle:orcl", "scott", "bunvegni");
my $SEL = "select * from scott.employees";
my $sth = $db->prepare($SEL);
$sth->execute();
while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n";
}END {
    $db->disconnect if defined($db);
    }

This also worked nicely.

All in all, I had absolutely no issue connecting from a 11G 32 bit client to the Oracle 12 64 server instance.