Sunday, 31 December 2017

Windows Instant Client Setup

On my systems, I always opt for the instant client so I can get going with SQL*Plus as quickly as possible - the full client is just beyond my needs. If you are a regular consumer of my posts, you may have noticed I'm not primarily a Windows user, but of late I've had a need to be using Windows. So this particular post will be guiding the set up of the instant client, setting up your tnsnames and getting SQL Developer to use the OCI/thick driver.

So first, head over to the download page for Instant Client: http://www.oracle.com/technetwork/topics/winx64soft-089540.html. I'm just grabbing the latest version as at the time of this article (12.2.0.1)

The packages I usually grab are:


  1. Instant Client Package - Basic Light
  2. Instant Client Package - SQL*Plus
  3. Instant Client Package - SDK


The basic and basic light packages make mention that one pre-requisite is the Microsoft Visual Studio 2013 Redistributable. So go ahead and install that before hand if necessary.

From the three files downloaded, you should end up with 3 zip files. Now, you will want to extract the contents of each into the same folder - usually I end up with a single folder named instantclient_12_2 containing all the files.


Each zip file Oracle provides contains a single folder (instantclient_12_2), so if you apply the same path during extraction, all files will go to the same location. In my example, I end up with all my instant client files at the following path:

C:\Users\trent\Downloads\instantclient_12_2\instantclient_12_2\

So, I then cut the folder in the last segment of the path and put it in my C drive, such that I end up with C:\instantclient_12_2.

Now that we have all the files, we need to a couple of extra steps so we can start using SQL*Plus.

Our folder contains a binary for sqlplus, so we should add this path to our Path environment variable, so that we can type sqlplus within command prompt without needing to specify the full path. So open the Environment Variables configuration tool within Windows. On Windows 10, this is easily accessible by searching for it in the Start menu.



There are two options - User or System variables. Since I'm the only one using the system, I'll just opt for User variables. So focus on the Path line, click edit, and add the location where you stored your instant client files.


It's also not a bad idea to set an environment variable ORACLE_HOME to point to the same location.

With all that done, you should now be able to open up command prompt and type: sqlplus /nolog, to get an SQLPlus command prompt. If you have a database server you can connect to, try connecting too to make sure all is good.

Ok, the next part is how do we connect with our tnsnames entry?

I know my entry will look like this:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

So, option one is within ORACLE_HOME, make a folder in the path: $ORACLE_HOME\network\admin named tnsnames.ora with your TNS entry specifications (like above), and you don't need to do anything else - it will be automatically picked up. You can now connect to the server with the convenient shorthand of XE. E.g. sqlplus user@XE

If by chance you would prefer to keep it in another location, you just need an environment variable for TNS_ADMIN.

With the instant client now installed, I like to tell SQL Developer to use the instant client I have installed on my system, so navigate to Tools -- Preferences... -- Database -- Advanced, and set up the configuration based on where you store your instant client. 


Read more about the thick driver support within SQL Developer on Jeff Smith's blog: https://www.thatjeffsmith.com/archive/2014/01/oracle-sql-developer-4-and-the-oracle-client/