Rails on Oracle: Part 1 - Installing the oracle instant client on Mac OS X
Anton Jenkins | March 25, 2009
These instructions were performed using version 10.5.6 of OS X and the 10.1.03 Oracle instant client.
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, trymyserver.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 statuson 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 |
Great Tutorial, thank you so much. Just to let you know that link to Part 2 "http://www.pixellatedvisions.com/2010/07/02/rails-on-oracle-part-2-setting-up-rails-to-connect-to-an-oracle-database" is broken. Probably you want it to point to "http://www.pixellatedvisions.com/2009/03/26/rails-on-oracle-part-2-setting-up-rails-to-connect-to-an-oracle-database"
Thanks,
Jaime
Ah, thanks for that Jaime! I'd set the publish date to 2010 whilst I was working on the post and forgot to change the link. My bad ;o)
Great writeup!
Shouldn't the symbolic link in step 5 be:
Nice spot Ben! I'm always putting those the wrong way around! :oD
All fixed now.
Quick question...I'm hoping you can help! I'm attempting to create a ruby on rails/oracle project for my database design class to develop my project on. I'm just confused about the whole creating an actual database to link up to. If I have the instant client installed, how do I go about creating a database to link my rails project to?
Hi for step 5, you mention :
ln -s /opt/oracle/bin/sqlplus /usr/local/bin/sqlplus
But bin isnt located in the oracle folder if we follow your previous steps. within oracle, the only other folder is instantclient, right? am I doing something wrong?
You're quite right - I've made (yet another!) mistake with that symbolic link. If you've followed the instructions then the sqlplus binary should have been unzipped into the instantclient directory. I've amended the link above (3rd time lucky!) to be...
So where I had previously put bin, it should have been instantclient. My bad, apologies!
@ash : Creating an Oracle database? Hmmm..... we could spend a whole blog post covering that topic! ;)
If you are doing this for a class is there a definite requirement to use Oracle? Usually Oracle is a bit overkill for rails work but if you are using rails in an enterprise environment then it's quite possible you will have your hand forced and will have to use Oracle as that's what they are using. But if you are developing a stand alone application from scratch on rails I would be inclined to use MySQL as it's capable enough and much easier for the novice DBA to work with.
So what I'm getting at is, if you are inexperienced with Oracle, only use it as a last resort. Oracle is a complicated beast and in most instances MySQL will suffice.
I'm taking a wild stab in the dark here but I'm guessing your class is probably focussed on SQL, rather than Oracle in-particular? They probably just use Oracle because of it's use in the enterprise and thus giving you relevant experience with a popular database. In which case you might be OK with using MySQL as the SQL you will use will be almost identical to what you'd run on Oracle for most operations (SELECTS, INSERTS etc etc). Even SQLite would do the trick for a coursework project and that couldn't really be any easier to work with.
However, if you absolutely have to use Oracle then creating a database is quite a task and I hate to sound like I'm dodging the question here but there are so many variables involved that it would be impossible for me to answer without knowing a lot more detail.
A good place to start is Oracle's installation documentation :
Oracle installation instructions
If I were you I'd try having a chat with the DBA at your university (or wherever you are studying) and ask them nicely if they can set you up with a schema on their database to store the data for your rails app. I suspect you don't really need your own Oracle database at all.
This didn't work for me. Error:
dyld: Library not loaded: /b/227/sqlplus/lib/libsqlplus.dylib
Referenced from: /usr/local/bin/sqlplus
Reason: image not found
Trace/BPT trap
Any ideas?
@Mitch, my guess would be your DYLD_LIBRARY_PATH is not set properly. Is DYLD_LIBRARY_PATH set to the location of your instant client directory?
@ash if you're still interested in setting up an Oracle DB instance (or if someone else is) you could take a look at http://blog.rayapps.com/2009/04/12/how-to-install-oracle-database-10g-on-mac-os-x-intel/
(he's the guy who wrote the oracle-enhanced ActiveRecord adapter).
@anton Thanks for the write-up! This helped me a lot. I'm currently trying to evaluate if it's worth the hassle to create our web app in RoR, when we're forced to access a legacy Oracle Db.
@Martin, as long as you are comfortable with Oracle I think rails is a good option. I've done another post on the subject of marrying rails to a legacy database which you might be interested in...
Getting rails to play with a legacy Oracle database
Thanks! I was having an issue with UTF8 char and setting the NLS_LANG var fixed it. You have no idea how happy I am right now! Thx again
guide works! i was having trouble installing ruby-oci8 before. this guide's very much up to date with the latest osx oracle files available for download and required for instantclient@macosx
@Mitch
I have the same problem, and clearing the Apple quarantine attribute on all the files in the instantclient folder solved the problem for me.
sudo xattr -d com.apple.quarantine /opt/oracle/instantclient/*
Thx for this hints, made my day!!!