Sandy's scratchpad

Public Scratchpad

Installing DBD::Oracle on Mac (El Capitaine).

I had a few issues when installing DBD::Oracle on El Capitaine, but eventually succeeded. I thought I would share my process, which may help someone attempting the same process.

PS: I didn't figure this out all by myself, I would like to give credit to the blogs/tutorials listed in the reference section below

Preparation

Get Oracle client

download from: http://www.oracle.com/technetwork/topics/intel-macsoft-096467.html

instantclient-basic-macos.x64-11.2.0.4.0.zip
instantclient-sdk-macos.x64-11.2.0.4.0.zip
instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

Install Oracle client

Create directory /Library/Oracle
copy previously installed zip files to this directory

unzip files

cd /Library
sudo mkdir Oracle

sudo cp ~/Downloads/instantclient*.zip .

sudo unzip instantclient-basic-macos.x64-11.2.0.4.0.zip
sudo unzip instantclient-sdk-macos.x64-11.2.0.4.0.zip
sudo unzip instantclient-sqlplus-macos.x64-11.2.0.4.0.zip
[download]

Configure Oracle client

Create a /network/admin folder in instantclient_11_2

cd instantclient_11_2
sudo mkdir network
sudo mkdir network/admin
[download]

Using a text editor, create tnsnames.ora in the network/admin folder
Be sure to replace hostname and SID with the appropriate data for your Oracle instance. network/admin folder. It should contain:

  ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SID)
    )
  )
[download]

set environment variables. Edit ~/.bash_profile and add the following

ORACLE_HOME=/Library/Oracle/instantclient_11_2
DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:/Library/Oracle/instantclient_11_
+2
TNS_ADMIN=/Library/Oracle/instantclient_11_2/network/admin
PATH=$PATH:/Library/Oracle/instantclient_11_2
CLASSPATH=$CLASSPATH:$ORACLE_HOME
 
export ORACLE_HOME
export DYLD_LIBRARY_PATH
export TNS_ADMIN
export PATH
export CLASSPATH
[download]

or edit ~/.tcshrc (I don't use bash... :( )

# required for the Oracle Client

setenv ORACLE_HOME /Library/Oracle/instantclient_11_2
if ($?DYLD_LIBRARY_PATH) then       
    setenv DYLD_LIBRARY_PATH ${DYLD_LIBRARY_PATH}:$ORACLE_HOME
else
    setenv DYLD_LIBRARY_PATH $ORACLE_HOME
endif

setenv TNS_ADMIN $ORACLE_HOME/network/admin

setenv PATH ${PATH}:$ORACLE_HOME

if ($?CLASSPATH) then
    setenv CLASSPATH ${CLASSPATH}:$ORACLE_HOME
else
    setenv CLASSPATH $ORACLE_HOME
endif
[download]

source your .bash_profile or .tcshrc file, or log off and then back on.

Test Oracle Client

If this works, great!

[~] sqlplus -v

SQL*Plus: Release 11.2.0.4.0 Production

[~]
[download]

If you get this error message,...

dyld: Library not loaded: /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib
+/libsqlplus.dylib
  Referenced from: /Users/sandy/Downloads/instantclient_11_2/sqlplus
  Reason: image not found
[download]

then the DYLD_LIBRARY_PATH has not been set properly.

Install DBD::DBI

[~] sudo cpan
Password:
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v2.00)
Enter 'h' for help.

cpan[1]> install DBI
[download]

Installing DBD::Oracle

I had issues with this, so I did not use CPAN.

Prepare:
- download the tarball for DBD::Oracle
  http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm
- unzip and untar
```
gunzip DBD-Oracle-1.75_2.tar.gz
tar -xvf DBD-Oracle-1.75_2.tar
cd DBD-Oracle-1.75_2
[download]
```

prepare the make file:

[~/Downloads/DBD-Oracle-1.75_2] perl Makefile.PL

[... deleted stuff ...]

Using DBI 1.634 (for perl 5.018002 on darwin-thread-multi-2level) inst
+alled in /Library/Perl/5.18/darwin-thread-multi-2level/auto/DBI/
Configuring DBD::Oracle for perl 5.018002 on darwin (darwin-thread-mul
+ti-2level)

[... deleted stuff...]

Installing on a darwin, Ver#15.0
Using Oracle in /Library/Oracle/instantclient_11_2
dyld: Library not loaded: /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib
+/libsqlplus.dylib
  Referenced from: /Library/Oracle/instantclient_11_2/sqlplus
  Reason: image not found
        
[... more deleted errors ...]
[download]

Fixing dyld: Library not loaded: problem

For some reason, the DYLD_LIBRARY_PATH environment variable is not being used/set correctly? (even though 'sqlplus' works)

create a bash script with this content named "fix_dyld.sh". (see reference 4 below for link to article which explains what this is doing)

Note that this is modified from what was shown in the reference 4 because "el capitaine" does not like "@executable_path". I changed to code to hardcode the paths to "/Library/Oracle/instant_client_11_2"

#!/bin/sh
# script to change the dynamic lib paths and ids for oracle instant cl
+ient
# exes and libs

# proces all the executable files in this directory
find . -maxdepth 1 -type f \( -perm -1 -o \( -perm -10 -o -perm -100 \
+) \) -print | while read exe
do
    echo
    echo adjusting executable $exe
    baseexe=`basename $exe`
    otool -L $exe | awk '/oracle/ {print $1}' | while read lib
    do
        echo adjusting lib $lib
        baselib=`basename $lib`
        if [ "$baseexe" = "$baselib" ]
        then
            echo changing id to $baselib for $exe
            install_name_tool -id $baselib $exe
        else
            echo changing path id for $lib in $exe 
            install_name_tool -change $lib /Library/Oracle/instantclie
+nt_11_2/$baselib $exe
        fi
    done
done
[download]

Go to /Library/Oracle/instant_client_11_2, and run this script

[/Library/Oracle/instantclient_11_2] sudo bash ~/myScrips//fix_dyld.sh
+ 
Password:
adjusting executable ./adrci
adjusting lib /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dylib.11.1
changing path id for /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dyli
+b.11.1 in ./adrci
adjusting lib /ade/dosulliv_ldapmac/oracle/ldap/lib/libnnz11.dylib

[... deleted stuff...]
[download]

Prepare makefile (again). You must run as administrator because the process will want to create a soft link in your /Library/Oracle/instant_client_11_2 directory.

[~/Downloads/DBD-Oracle-1.75_2] sudo perl Makefile.PL
Password:

[... deleted stuff ...]

Using DBI 1.634 (for perl 5.018002 on darwin-thread-multi-2level) inst
+alled in /Library/Perl/5.18/darwin-thread-multi-2level/auto/DBI/
Configuring DBD::Oracle for perl 5.018002 on darwin (darwin-thread-mul
+ti-2level)

[... deleted stuff...]

Looks like an Instant Client installation, okay
You don't have a libclntsh.dylib file, only /Library/Oracle/instantcli
+ent_11_2/libclntsh.dylib.11.1
So I'm going to create a /Library/Oracle/instantclient_11_2/libclntsh.
+dylib symlink to /Library/Oracle/instantclient_11_2/libclntsh.dylib.1
+1.1

[...deleted stuff...]

Writing Makefile for DBD::Oracle
Writing MYMETA.yml and MYMETA.json
[download]

make

[~/Downloads/DBD-Oracle-1.75_2] make
cp lib/DBD/Oracle/Object.pm blib/lib/DBD/Oracle/Object.pm
cp lib/DBD/Oracle/GetInfo.pm blib/lib/DBD/Oracle/GetInfo.pm
cp lib/DBD/Oracle/Troubleshooting/Aix.pod blib/lib/DBD/Oracle/Troubles
+hooting/Aix.pod
cp lib/DBD/Oracle/Troubleshooting/Sun.pod blib/lib/DBD/Oracle/Troubles
+hooting/Sun.pod

[... deleted stuff ...]

Manifying blib/man3/DBD::Oracle::Troubleshooting.3pm
Manifying blib/man3/DBD::Oracle::Troubleshooting::Win64.3pm
Manifying blib/man3/DBD::Oracle::Troubleshooting::Vms.3pm
Manifying blib/man3/DBD::Oracle::Troubleshooting::Linux.3pm
Manifying blib/man3/DBD::Oracle::Troubleshooting::Macos.3pm
Manifying blib/man3/DBD::Oracle::Object.3pm
Manifying blib/man3/DBD::Oracle::Troubleshooting::Win32.3pm
[download]

test (this fails on el capitaine, probably doesn't on earlier version of Mac ??)

[~/Downloads/DBD-Oracle-1.75_2] make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_h
+arness(0, 'blib/lib', 'blib/arch')" t/*.t
t/000-report-versions-tiny.t .. # 
[... deleted stuff ... mostly version numbers of required modules ...]
t/000-report-versions-tiny.t .. ok   
t/00versions.t ................ 
Can't load '/Users/sandy/Downloads/DBD-Oracle-1.75_2/blib/arch/auto/DB
+D/Oracle/Oracle.bundle' 
for module DBD::Oracle: dlopen(/Users/sandy/Downloads/DBD-Oracle-1.75_
+2/blib/arch/auto/DBD/Oracle/Oracle.bundle, 2): 
Library not loaded: libclntsh.dylib.11.1
Referenced from: /Users/sandy/Downloads/DBD-Oracle-1.75_2/blib/arch/au
+to/DBD/Oracle/Oracle.bundle

Reason: unsafe use of relative rpath libclntsh.dylib.11.1 in 
/Users/sandy/Downloads/DBD-Oracle-1.75_2/blib/arch/auto/DBD/Oracle/Ora
+cle.bundle 
with restricted binary at /System/Library/Perl/5.18/darwin-thread-mult
+i-2level/DynaLoader.pm line 194.
 at t/00versions.t line 10.

[... more errors ...]
[download]

fix "unsafe use of relative rpath"
- Note that the path name of the Oracle.bundle would need to be changed to reflect your situation (check the error message for the exact location of the perl file it is complaining about)
```
sudo install_name_tool -change libclntsh.dylib.11.1 /Library/Oracle/in
+stantclient_11_2/libclntsh.dylib.11.1 
/Users/sandy/Downloads/DBD-Oracle-1.75_2/blib/arch/auto/DBD/Oracle/Ora
+cle.bundle
[download]
```

test again

[~/Downloads/DBD-Oracle-1.75_2] make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_h
+arness(0, 'blib/lib', 'blib/arch')" t/*.t
t/000-report-versions-tiny.t .. # 

[... deleted stuff ...]

t/000-report-versions-tiny.t .. ok   
t/00versions.t ................ # OCI client library version: 11.2.0.4
t/00versions.t ................ ok   
t/01base.t .................... ok   
t/10general.t ................. skipped: Unable to connect to Oracle
t/12impdata.t ................. skipped: Unable to connect to Oracle

[... deleted stuff ...]

All tests successful.
Files=38, Tests=9,  8 wallclock secs ( 0.11 usr  0.06 sys +  6.33 cusr
+  0.96 csys =  7.46 CPU)
Result: PASS
[download]

install
```
sudo make install
[download]
```

My final test

[~/Downloads] cat test_connection.pl 
#!/usr/bin/perl
use strict;
use warnings;

use DBD::Oracle;
use DBI;

my $user= "user";
my $passwd = "password";

my $dbh = DBI->connect("dbi:Oracle:ORCL", $user, $passwd);

[~/Downloads] perl test_connection.pl 
[~/Downloads]
[download]

References

http://blog.dbi-services.com/install-oracle-instant-client-perl-dbdoracle-mac-os-x-yosemite/
http://www.toadworld.com/products/toad-mac-edition/b/weblog/archive/2013/07/02/setting-up-a-connection-to-oracle-on-your-mac
http://stackoverflow.com/questions/33275605/el-capitan-perl-dbd-unsafe-use-of-relative-path
http://blog.caseylucas.com/2013/03/03/oracle-sqlplus-and-instant-client-on-mac-osx-without-dyld_library_path/