Using Oracle Instant Client and SQLPlus

Some time ago Oracle introduced their Instant Client as an alternative to needing a full install of the Oracle client to run your own apps or SQLPlus. I’ve been putting the Instant Client to good use these days and figured I would give a quick howto on getting it set up.

First you should download the correct binary for what you want to do. They have a build for a lot of different platforms and then they also split the libraries themselves out from SQLPlus. If all you need is the OCI library then all you have to do is download that part. I find it nice to get both the libraries and SQLPlus so I can verify that the setup works.

After you have downloaded that client you will need to unzip it. In the current case the directory it creates is “instantclient_10_2″ and in this example I’m going to assume you got both the libraries and SQLPlus. After unziping both packages change into the instantclient_10_2 directory.

Now that you are in the instantclient_10_2 directory you will need to set up some environment variables to be able to run SQLPlus. The following will do the trick (just remember that I’m assuming that everything you need is in your current directory, you would of course need to change these if you want to install in a different location):

[code lang=”text”]
export LD_LIBRARY_PATH=.
export TNS_ADMIN=.
export ORACLE_HOME=.
[/code]

Now you will need to create of copy a tnsnames.ora file into your current directory. Here is an example assuming you have Oracle running on a box at 192.168.1.100 with a listener on port 1521 and a SID of XE:

[code lang=”text”]
XE.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 192.168.1.100)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = XE)
)
)
[/code]

Now you are ready to go. Just run the following to connect to your Oracle database:

[code lang=”text”]
./sqlplus username/password@XE.WORLD
[/code]

Oracle’s Instant Client makes the world much easier now. In just a few steps you are able to connect to an Oracle database with SQLPlus and don’t have to go through a ton of installation mess.

As a side note, I also found that if you grab the header files from a full installation you can easily compile against the Instant Client libraries when using OCI calls. This can make developing an application that connects to Oracle very easy to set up.

[tags]oracle, sqlplus, instant client[/tags]

6 thoughts on “Using Oracle Instant Client and SQLPlus

  1. Patrick Holmes

    Thank you for posting this! I was ready to give up until I found your post with a sample tnsnames.ora entry that actually works. (Only Oracle can produce an “instant” product that takes hours to install and get working… ;-)

    thanks,
    pat holmes

  2. Aravind

    What can I say.

    Good documents like this exist through out the Internet but getting to it is nothing but luck. Definetely helped to solve my problem but I still trying to figure out LinkedSErver configuration from Sql Server to Oracle.

    Keep up the good work

  3. EsmCor

    … I have a question, should this configuration work for a Windows XP client or there’s something else to consider?

    I’ve been making some attempts, but can’t make it work!!

    Please give me some advise

  4. Matt Rogers

    Thanks for posting this. This is exactly what I was looking for. I’m connecting to an Oracle database on UNIX box from my windows laptop, and runninng SQL Plus commands from SQL Developer was not working. This works great! Thanks! (found this from a Google search)

    Matt

  5. Andy

    Please elaborate on these commands? Do I put them into a batch file somewhere or what?

    export LD_LIBRARY_PATH=.
    export TNS_ADMIN=.
    export ORACLE_HOME=.

    When I run SqlPlus.exe and, when prompted, enter my user-name and password, I get ERROR: ORA-12560: TNS: protocol adapter error. I have TNSNAMES.ORA file in the same installclient_10_2 folder.

    Thanks–

  6. DMcDConsult

    @Andy: I put instantclient in folder OracleInstantClient on my USB drive – so change your paths accordingly, but you could use a batch file with:

    SET PATH=E:\OracleInstantClient;%PATH%
    SET TNS_ADMIN=E:\OracleInstantClient
    SET LD_LIBRARY_PATH=E:\OracleInstantClient
    SET SQLPATH=E:\OracleInstantClient
    SET ORACLE_HOME=E:\OracleInstantClient

    Great post though.

Comments are closed.