How to Install and Configure SQL*Plus Instant Client on Windows

GUI tools like TOAD and SQL Developer are all fine, but those who have cut their teeth on SQL*Plus will not do without good old command-line interface. And so, a message like the one below makes one want to set things right immediately…

Here’s a step-by-step guide for doing away with this error, by installing SQL*Plus Instant Client on Windows.

First things first: What is SQL*Plus Instant Client?

From the Oracle documentation:

SQL*Plus Instant Client is a standalone product with all the functionality of SQL*Plus command-line. It connects to existing remote Oracle databases, but does not include its own database. It is easy to install and uses significantly less disk space than the full Oracle Database Client installation required to use SQL*Plus command-line.

If all you want is a simple command-line interface to connect to remote Oracle databases, Instant Client meets your purpose well.

On to the installation…

1. Download the installers from OTN: Basic OCI + SQL*Plus

SQL*Plus Instant Client package needs OCI Instant Client as a pre-requisite. For things to work, the two must be (i) of the same version, and (ii) installed in the same directory.

To download:

Go to OTN’s Instant Client Downloads for MS Windows.

Accept the license agreement.

Choose the packages you want to install — in this case:

• Basic Light Package – Smaller version of the Basic OCI package, with only English error messages
• SQL*Plus Package – The SQL*Plus command line tool for SQL and PL/SQL queries

Versions used: SQL*Plus 12.2.0.1.0, for a Windows 64-bit environment.

Click on the hyperlinks to start download — this will download the ZIP files to your local machine.

2. Extract the downloads

Extract both the ZIPs to the same local folder, say D:\Tools\sqlplus. A new subfolder (instantclient_12_2 in this case) will get created.

3. Edit the PATH variable to include the SQL*Plus Instant Client directory

Add the directory containing the Instant Client files to the PATH environment variable. Make sure this addition does not have a conflict with another existing Oracle path — if it does, either remove the existing value or move the new value to the front of the PATH variable.

We now have the SQL*Plus Instant Client ready to connect to remote databases.

4. Connect to a Database with SQL*Plus Instant Client via Oracle Net connection identifier

To connect to a database, first open a command prompt window.

Enter the Easy Connection identifier to connect to the database. For example, the Easy Connection identifier for the HR schema in an XE database, running on host sales-server, port 1521 is:

sqlplus hr/your_password@\"//sales-server:1521/XE\"

Once entered, SQL*Plus Instant Client will establish the database connection and you will see the SQL prompt.

You can now run your SQL commands through SQL*Plus Instant Client’s easy command-line interface.

Other than Easy Connection identifiers, Net Service Names can be used for making database connections via Instant Client. Net Service Names may come from sources such as LDAP or a tnsnames.ora file. For more details on connection options, see the user’s guide and reference.

Notes:

Remember that the error in the first screenshot (‘sqlplus’ is not recognized as an internal or external command) could be because of a misconfigured PATH — see step 3. Before you proceed to install SQL*Plus, verify that SQL*Plus is actually absent!

For more on SQL*Plus Instant Client, see the Oracle documentation: link. Also check out the advanced configuration tips in this neat tutorial at williamrobertson.net.