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)”