These instructions were performed using version 10.5.6 of OS X and the 10.1.03 Oracle instant client.

Update: I’ve just performed these steps with version 11.2.0.3.0 (64-bit) of the instant client and it worked without a hitch.

Step 1: Download the instant client from Oracle

We’re going to use the instant client because this is by far the easiest way to get up and running. Traditionally oracle clients have been quite heavy weight but the instant client is quick to download and easy to install.

Go to the appropriate download page for your CPU:

There are several download options. Download the following to your ~/Downloads directory:

  • Instant Client Package – Basic
  • Instant Client Package – SQL*Plus
  • Instant Client Package – SDK

Step 2: Install the files on your machine

I usually install these files in the /opt/oracle/instantclient directory. Assuming you’ve downloaded the zip files to your ~/Downloads directory we need to make a new home for our instant client and unzip the files there….

1
2
3
4
5
6
7
8
9
mkdir -p /opt/oracle/
cd /opt/oracle

mv ~/Downloads/instantclient*.zip .
unzip instantclient-basic-macosx-10.2.0.4.0.zip
unzip instantclient-sdk-macosx-10.2.0.4.0.zip
unzip instantclient-sqlplus-macosx-10.2.0.4.0.zip

mv instantclient_10_2 instantclient

Obviously if you have a slightly different version of the client you are going to need to amend those paths slightly.

Step 3: Create some symbolic links for the libraries

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

Step 4: Set your ORACLE_HOME, NLS_LANG and DYLD_LIBRARY_PATH variables

You could do this in a couple of ways. Because I’m the only user of my machine I like to do this in my personal ~/.bash_profile. If you share the machine with someone who also wants to use the client you may want to put this in /etc/profile or/etc/bashrc. The choice is yours. I find ~/.bash_profile is cleaner:

1
2
3
export NLS_LANG="AMERICAN_AMERICA.UTF8"
export ORACLE_HOME=/opt/oracle/instantclient
export DYLD_LIBRARY_PATH=$ORACLE_HOME

Now you could close your terminal session and start a new one to force a reload or try this instead:

1
source ~/.bash_profile

Do a quick echo on $ORACLE_HOME and $DYLD_LIBRARY_PATH to make sure these environment variables are set correctly.

Step 5: Put sqlplus in your $PATH

There are couple of ways to do this. You could add /opt/oracle/bin to your $PATH or you could add a sym link to the sqlplus binary in a directory which is already in your $PATH. I’m going to do the latter as I don’t want to make my $PATH variable longer just for one executable.

I’ve already got /usr/local/bin in my $PATH so I’m going to make the sym link there:

1
ln -s /opt/oracle/instantclient/sqlplus /usr/local/bin/sqlplus

Just give that a test to make sure it’s getting found :

1
which sqlplus

This should return /usr/local/bin/sqlplus. Check your $PATH variable if it’s not working.

Step 6: Try it out!

Lets assume we’ve got the following Oracle database:

  • Host : myserver
  • domain : mydomain.com
  • Port : 1521
  • SID : mydatabase
  • Service : mydatabase.mydomain.com

And a username and password of :

  • Username : myuser
  • Password : mypassword

We can connect to this database using the following connect string:

1
sqlplus myuser/mypassword@//myserver:1521/mydatabase.mydomain.com

It’s a bit of a mouthful, but if that worked then we’re all up and running. In this instance we’re connecting over the default port of 1521. Because of this we can actually leave the port out and Oracle will assume the default:

1
sqlplus myuser/mypassword@//myserver/mydatabase.mydomain.com

Possible errors

ORA-12154: TNS:could not resolve the connect identifier specified

It couldn’t find your host. Try the following:

  • Instead of just putting myserver, try myserver.mydomain.com
  • Are you sure you got the port right? Oracle defaults to listening on 1521. Is the listener running on your server for sure? Try running lsnrctl status on the server just to double check it’s running and which port it’s on.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

It’s found the host and the listener but it’s can’t find the database you’ve specified at that listener. The last part of the connect string where it says mydatabase.mydomain.com is where it’s falling over now. Again, the quickest way to check this is to ssh onto your oracle database server and query the listener with lsnrctl status which will also tell you which services the listener is listening on. Copy and paste the correct service into the sqlplus connect string and it should work.

ORA-01017: invalid username/password; logon denied

I think this one speaks for itself! If you’re sure of the username and password then maybe it contains characters which may need escaping? Try surrounding the username and password in double quotes:

1
sqlplus "myuser"/"mypassword"@//myserver:1521/mydatabase.mydomain.com

Coming in Part 2

Now that we can connect our machine to Oracle, in part 2 we’re going to get rails in on the fun as well. Check back in a week or so and I should have that post finished.

Update: Part 2 has now been published and shows you how to install the ruby-oci8 library, how to install the oracle_enhanced adapter and how to configure your database.yml.


Update 15/05/09: Added NLS_LANG to the environment variables
comments powered by Disqus