SQL*Plus command history on Mac OS X

Anyone who has tried and use SQL*Plus on Unix would have seen that its quite frustrating since it does not have a command history function under Linux and Unix. When you try to use the ‘up’ key, you get something like this:
However there is a utility called rlwrap that can help us fix this problem. rlwrap is a readline wrapper for shell commands which uses input from the controlling terminal. It adds a persistent input history for each command and supports user-defined completion.

Here are the steps to install rlwrap on Mac OS X:

1. Download Macports from http://darwinports.com/ (If you don't have it already)
2. Install macports.
3. Update macports. Go to /opt/local/bin and type the following:

sudo ./port -d selfupdate

4. Install rlwrap with the following command (You will see it will install some dependencies too):

sudo ./port install rlwrap

5. Add the following line to your .bash_profile:

alias sqlplus='/opt/local/bin/rlwrap sqlplus'

6. Launch SQL*plus as usual and use rlwrap to access your sql history!

Sqlplus on a Mac with Oracle Instant Client

One of the things any Oracle developer uses is sqlplus, but getting it to work on Mac OS-X is not quite as easy as on Windows. Follow these steps to install sqlplus on your Mac.

1. Download the Oracle Instant Client Zip files:

• Instant Client Package - Basic
• Instant Client Package - SQL*Plus
• Instant Client Package - JDBC Supplement (Optional)
• Instant Client Package - SDK (Optional)

2. Launch the Terminal and unzip the files to instantclient10_2.

unzip instantclient-basic-macosx-10.2.0.4.0.zip unzip instantclient-sqlplus-macosx-10.2.0.4.0.zip unzip instantclient-jdbc-macosx-10.2.0.4.0 #Optional unzip instantclient-sdk-macosx-10.2.0.4.0 #Optional

3. Move the directory to where you want it. I put mine in /usr/local/oracle/instantclient_10_2.

sudo mkdir /usr/local/oracle/ sudo mv instantclient_10_2 /usr/local/oracle/

4. Then go to this directory and make symbolic links for dynamic libraries.

sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib sudo ln -s libocci.dylib.10.1 libocci.dylib

5. I reccomend creating a tnsnames.ora file where you add all your connection details. I placed mine in /usr/local/oracle/network/admin.

Example of a tnsnames.ora file:

IDENTIFIER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = userid.myhosteddb.net)(PORT = 1521)) ) (CONNECT_DATA = (SID = odb)) )

6. Then you need to set up necessary environment variables. I added mine to my .bash_profile script so they are alway's set when I run the terminal.

In your home folder you can type:

vi .bash_profile

And then add the following lines at the end of the file (Make sure you set the PATH correctly otherwise sqlplus won't run):

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2" export SQLPATH="/usr/local/oracle/instantclient_10_2" export TNS_ADMIN="/usr/local/oracle/network/admin" export NLS_LANG="AMERICAN_AMERICA.UTF8" export PATH=$PATH:$DYLD_LIBRARY_PATH

7. You should now be able to run sqlplus. Close the terminal and re-lunch it. Now run:

sqlplus [username]/[password]@[service]

8. Sqlplus should now run and look something like this: