Digital Sanctum

While using Oracle’s SQL Loader utility the other day, I ran into a subtlety which wasn’t obvious. Most of the documentation out there describes the userid param as just taking user/pass. But it actually can take the user/pass@SID form as well. If you get an exception message something like the following:

SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
ORA-12541: TNS:no listener

try qualifying your sqlldr command with .world:

so

sqlldr userid=user/pass@yourSID control=test.ctl

becomes

sqlldr userid=user/pass@yourSID.world control=test.ctl

Oracle Mix is a joint effort between the folks at Thoughtworks and Oracle to provide a community for Oracle clients to exchange ideas and network with each other and Oracle.

Since there are several variations of docs on this (some of which are out of date) I figured I'd document this.

1. Download and install the Ruby interface from http://rubyforge.org/projects/ruby-oci8/
2. Then, in your config/database.yml file, you should have something like this:

If you're running Rails with JRuby you should have the Oracle JDBC drivers in the classpath and the entry above will be a little different:

Getting right down to business...

1. Download the Instant Client Packages (4 files)
2. Unzip the files into instantclient10_1
   PLAIN TEXT
   CODE:

   1. unzip instantclient-basic-macosx-10.1.0.3.zip
   2. unzip instantclient-sqlplus-macosx-10.1.0.3.zip
   3. unzip instantclient-sdk-macosx-10.1.0.3.zip
   4. unzip instantclient-jdbc-macosx-10.1.0.3.zip

3. Create two symbolic links for the files that have the version appended. This is so the Ruby OCI8 driver can find what it’s looking for.
   PLAIN TEXT
   CODE:

   1. cd instantclient10_1
   2. ln -s libclntsh.dylib.10.1 libclntsh.dylib
   3. ln -s libocci.dylib.10.1 libocci.dylib
   4. cd ..

4. Create directory /usr/local/oracle and then copy instantclient10_1 into it
   PLAIN TEXT
   CODE:

   1. sudo mkdir /usr/local/oracle
   2. sudo mv instantclient10_1 /usr/local/oracle/instantclient10_1

5. Open the system /etc/profile
sudo pico /etc/profile
6. Add these lines to /etc/profile
   PLAIN TEXT
   CODE:

   1. DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient10_1"
   2. export DYLD_LIBRARY_PATH
   3. SQLPATH="/usr/local/oracle/instantclient10_1"
   4. export SQLPATH

   Add /usr/local/oracle/instantclient10_1 to your PATH while you have /etc/profile open. Then save it.

7. Reload your profile
   
source /etc/profile

8. Test it out with the sqlplus command line app.
   PLAIN TEXT
   CODE:

   1. sqlplus [username][/password]@//[hostname][:port][/database]

If you can connect with that, then you’re good to go.