Recently, I tried to install and connect to Oracle databases directly from my new Mac Book using perl  (Yes, perl is still one of the most friendly programming language for DBAs). What first seemed to be easy was in reality quite difficult and long. Alone, the huge quantity of  hits on the WorldWideWeb was impressive. However none of the hits were providing the entire solution.

Follows, all the steps to prepare and install the Oracle instant client 11.2.0.4 … instant client 12c not yet available  🙁 for Mac OS X (Intel x86) (32-bit and 64-bit)

Oracle instant client installation

Following archives are required to run the client on your Mac

  •  instantclient-basic-macos.x64-11.2.0.4.0.zip or instantclient-basiclite-macos.x64-11.2.0.4.0.zip :
    contains all files required to run OCI, OCCI, and JDBC-OCI applications
  • instantclient-sdk-macos.x32-11.2.0.4.0.zip :
    Software Development Kit (SDK) contains all header files required to develop (compile) programs

and optionally the additional executable to run SQL*Plus

  • instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

Once downloaded, extract the client to a desired destination. Personally, I  like to install commercial add-on applications under the “/opt” folder:

X-Biederthal:~ jew$ ls -l /opt/ora_instantclient_112024_x64
total 386296
-rw-rw-rw-@ 1 root  wheel        484 Apr 10  2014 BASIC_README
-rw-rw-rw-@ 1 root  wheel        488 Apr 10  2014 SQLPLUS_README
-rwxrwxrwx@ 1 root  wheel      14348 Apr 10  2014 adrci
-rwxrwxrwx@ 1 root  wheel      40768 Apr 10  2014 genezi
-r--r--r--@ 1 root  wheel        368 Apr 11  2011 glogin.sql
-r-xr-xr-x@ 1 root  wheel   66167420 Feb  7  2014 libclntsh.dylib.11.1
-r-xr-xr-x@ 1 root  wheel    2817872 Jan 29  2014 libnnz11.dylib
-r-xr-xr-x@ 1 root  wheel    1897664 Feb  7  2014 libocci.dylib.11.1
-rwxrwxrwx@ 1 root  wheel  118707148 Apr 10  2014 libociei.dylib
-r-xr-xr-x@ 1 root  wheel     159004 Jan  7  2014 libocijdbc11.dylib
-r-xr-xr-x@ 1 root  wheel    1365444 Jan 27  2014 libsqlplus.dylib
-r-xr-xr-x@ 1 root  wheel    1504252 Jan  7  2014 libsqlplusic.dylib
-r--r--r--@ 1 root  wheel    2091135 Jan 28  2014 ojdbc5.jar
-r--r--r--@ 1 root  wheel    2739616 Jan 28  2014 ojdbc6.jar
drwxrwxrwx@ 7 root  wheel        238 Apr 10  2014 sdk
-r-xr-xr-x@ 1 root  wheel       8744 Jan 27  2014 sqlplus
-rwxrwxrwx@ 1 root  wheel     162380 Apr 10  2014 uidrvci
-r--r--r--@ 1 root  wheel      66779 Jan  7  2014 xstreams.jar

As I’m quite lazy, I added the Oracle instant client to my .bash_profile to automatically extend the PATH variable and set the ORACLE_HOME:

X-Biederthal:~ jew$ cat ~/.bash_profile 
# required for the Oracle Client
export ORACLE_HOME=/opt/ora_instantclient_112024_x64/
export TNS_ADMIN=${ORACLE_HOME}/network/admin
export PATH=${ORACLE_HOME}:/usr/local/ActivePerl-5.20/bin:${PATH}

Does SQL*Plus works?

X-Biederthal:~ jew$ sqlplus -v
dyld: Library not loaded: /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib
  Referenced from: /opt/ora_instantclient_112024_x64/sqlplus
  Reason: image not found
Trace/BPT trap: 5

Outch! why does the sqlplus binary not load the dynamic library “libsqlplus.dylib”? Indeed it’s yet available in the ORACLE_HOME. The solution consist either of relinking all dynamic libraries or adding the ORACLE_HOME to the Mac OS X dynamic linker (variable DYLD_LIBRARY_PATH). Please refer to Casey Lucas’s Blog post for details

Verify SQL*Plus works once one of the Cays Luca’s fix applied:

X-Biederthal:~ jew$ sqlplus -v

SQL*Plus: Release 11.2.0.4.0 Production

Install perl module DBD::Oracle

The below module installation has been tested with ActivePerl and the supplied system “/usr/bin/perl”

Before, we can start with the module installation (compilation) we have to install the entire Apple software development tools and libraries Xcode… 😕

 

To install the latest module DBD::Oracle, we can simply use Comprehensive Perl Archive Network (CPAN) command line tool, in conjunction with SHELL variables to enable successful compilation:

X-Biederthal:~ jew$ sudo su - 
X-Biederthal:~ root# export ORACLE_HOME=/opt/ora_instantclient_112024_x64
X-Biederthal:~ root# export DYLD_LIBRARY_PATH=${ORACLE_HOME}
X-Biederthal:~ root# /usr/local/ActivePerl-5.20/bin/cpan -i "DBD::Oracle"

CPAN: Storable loaded ok (v2.49_01)
Reading '/var/root/.cpan/Metadata'

  Database was generated on Tue, 08 Sep 2015 03:53:22 GMT

Running install for module 'DBD::Oracle'

CPAN: Digest::SHA loaded ok (v5.95)
CPAN: Compress::Zlib loaded ok (v2.068)
Checksum for /var/root/.cpan/sources/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz ok
CPAN: File::Temp loaded ok (v0.2304)
CPAN: YAML loaded ok (v1.15)
CPAN: Parse::CPAN::Meta loaded ok (v1.4414)
CPAN: CPAN::Meta loaded ok (v2.140640)
CPAN: Module::CoreList loaded ok (v5.20150520)
Configuring P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz with Makefile.PL

Multiple copies of Driver.xst found in: /usr/local/ActivePerl-5.20/site/lib/auto/DBI/ /usr/local/ActivePerl-5.20/lib/auto/DBI/ at Makefile.PL line 39.
Using DBI 1.634 (for perl 5.020002 on darwin-thread-multi-2level) installed in /usr/local/ActivePerl-5.20/site/lib/auto/DBI/

Configuring DBD::Oracle for perl 5.020002 on darwin (darwin-thread-multi-2level)
Remember to actually *READ* the README file! Especially if you have any problems.

Installing on a darwin, Ver#10.8
Using Oracle in /opt/ora_instantclient_112024_x64
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
Oracle version 11.2.0.4 (11.2)
Looks like an Instant Client installation, okay

Your DYLD_LIBRARY_PATH env var is set to '/opt/ora_instantclient_112024_x64'
Oracle sysliblist: 
Found header files in /opt/ora_instantclient_112024_x64/sdk/include.
client_version=11.2
< OUTPUT TRUNCATED >
< OUTPUT TRUNCATED >
< OUTPUT TRUNCATED >

All tests successful.
Files=38, Tests=9,  6 wallclock secs ( 0.11 usr  0.05 sys +  5.99 cusr  0.41 csys =  6.56 CPU)
Result: PASS
  PYTHIAN/DBD-Oracle-1.74.tar.gz
  /usr/bin/make test -- OK
Running make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/ActivePerl-5.20/site/lib/auto/DBD/Oracle/mk.pm
Installing /usr/local/ActivePerl-5.20/site/lib/DBD/Oracle.pm
Appending installation info to /usr/local/ActivePerl-5.20/lib/perllocal.pod
  PYTHIAN/DBD-Oracle-1.74.tar.gz
  /usr/bin/make install  -- OK

The module has been installed successfully. However, it’s still not working when you try import the module in your perl program:

Can't load '/usr/local/ActivePerl-5.20/site/lib/auto/DBD/Oracle/Oracle.bundle' for module DBD::Oracle: dlopen(/usr/local/ActivePerl-5.20/site/lib/auto/DBD/Oracle/Oracle.bundle, 1): Library not loaded: libclntsh.dylib.11.1   
Referenced from: /usr/local/ActivePerl-5.20/site/lib/auto/DBD/Oracle/Oracle.bundle   
Reason: image not found at /usr/local/ActivePerl-5.20/lib/DynaLoader.pm line 198.

This is caused because the dynamic library loader can’t find the Oracle instant client library “libclntsh.dylib.11.1”.

To solve this issue extend the Mac dynamic linker  path with the Oracle instant client folder to SHELL.

export DYLD_LIBRARY_PATH=${ORACLE_HOME}

To make it permanent, add the above command to your ~/.bash_profile.

Et voilà 🙂 you are now ready to connect  and query an Oracle Database from perl.

Nota bene

Currently, the Oracle 12c database client has not yet been released on Mac….  The Oracle client 11.2.0.X is fully compatible (certified) to connect to an Oracle 12.1.0.X database. Please refer to the My Oracle Support Matrix for details about “Client / Server Interoperability (Doc ID 207303.1)”

 

Post Views: 1,034
Thumbnail [60x60]
by
Jérôme Witt