Setting up SQL*Plus on Windows

Despite living in the era of GUI tools such as PL/SQL Developer, there are times when a simple text browser is all you want. Sometimes you may not have access to an IDE, or or you want to run a script, or you just want to mail a simple example to a colleague or web forum, or you may not trust your fancy IDE to give you the actual results without manipulating them in some way, and sometimes you may prefer the text-based Explain Plan results you get from DBMS_XPLAN. Whatever the reason, it is worth knowing your way around SQL*Plus.1 The product is called SQL*Plus, not SQL*PLUS or SQL+.

SQL*Plus is part of the Oracle Client (both Full and Instant versions). This guide assumes you have an Oracle client installed. The current Oracle Client installation guide is here.

The Windows Command Shell

One strength of sqlplus.exe is that it simply runs in the Windows command shell (cmd.exe). If you have never used sqlplus.exe before but you have configured your cmd window the way you like it, you will find that sqlplus.exe inherits these settings. The following steps essentially describe how to customise the command shell.

Set up your path variable

To be able to call sqlplus.exe without specifying its full path - that is, being able to type sqlplus.exe instead of c:\wherever\oracle\client\bin\sqlplus.exe (the location will vary depending on how you installed it) - you'll need to add its location to the path if it's not already included. From the Windows search bar, start typing environment and it will list some options including "Edit environment variables for your account". Make sure that the folder containing sqlplus.exe is included in the Path variable.

Location for scripts

First, set up a folder for scripts. By default in Windows, the working folder is the one that contains the executable, in this case the "bin" directory of the Oracle client installation. This is not a good place for your SQL*Plus scripts. Using Windows Explorer, create a folder called something like "SQL" in a convenient location. These days corporate desktops generally give you a private network drive as F:\ or similar, which makes a great place to put it, or you might use your Windows user's roaming folder (if you're not sure where that is, open a cmd window and type echo %appdata%, which will give something like C:\Users\yourusername\AppData\Roaming). The important thing is to have a dedicated folder for your SQL*Plus stuff that is easy to find and not a system folder.

Define a desktop shortcut

In recent Windows versions, you can just right-click on the desktop and choose 'New > Shortcut', then enter sqlplus as the location. Assuming sqlplus.exe is in your path, Windows will find it for you and expand the path in the shortcut properties. Click 'next' to continue and enter a name for it.

Alternatively, find the sqlplus.exe executable in the Oracle bin folder. Using the right mouse button, drag it to the desktop (or some other convenient location) and select "Create shortcut here".

To avoid the situation where pressing Control-C to interrupt a long-running command causes sqlplus.exe to exit and the window to vanish, you can set the Target property to powershell.exe -NoExit sqlplus, or if you don't have Powershell, cmd /k sqlplus.exe. This means the shortcut starts a Powershell or command window that immediately calls SQL*Plus, so if SQL*Plus terminates it will leave you back on the command line.

Change the name of the new shortcut to [the username you will use]@[the instance name you will connect to], for example "william@dev". You may want to leave a space before the "@" to allow the label to wrap. You will now have a blank-looking icon with a helpful label under it.

Set the shortcut properties

Right-click the shortcut and choose Properties.

• Set the username and instance to connect as: in the "Target" field containing the executable path, append a space followed by [username]@[TNS label], for example "william@dev". (You can add more SQL*Plus shortcuts later for other user and instance combinations. We will get one how we like it first, then copy it.) You can also enter a password here, depending on how security-conscious you feel; for example "william/secret@dev".
• Set the default working folder: in the "Start in" field, enter the path to the "SQL" folder you created above, e.g. "f:\sql".
• (If you have a 10g or earlier client available, perhaps unlikely these days) - give it the classic SQL*Plus icon by using the "Browse" option to navigate to the Oracle bin folder and selecting sqlplusw.exe. (You'll notice this is is an older screenshot than the others on this page. This is because I no longer have a 10g client on the machine I'm using to write this article, so I don't have sqlplusw.exe. Good riddance.)
  
• If it's the 21st century, there's no icon, but you can browse online at sites like icons08.com oriconfinder.com. You can often change the colour and size. The downloaded image will be in .png format but you can convert it to .ico at sites like zamzar.com. I came up with this: [download]
  
  A little fuzzy perhaps, but it looks better if you pin it to the taskbar instead of having it as a desktop shortcut:
  
  The icon you give it also appears as part of the application when you launch it (and the shortcut properties), making it easier to distinguish from other command windows.
  

Choose some colours

Experiment with text and background colours, so that they have reasonable contrast (not grey on grey) and don't glare (pink on orange), and so that the cursor is visible (again, avoid a mid-grey background as it produces a mid-grey cursor). I often like to use a neutral blue or green for development and test environments, and red for production. You can pick preset colours from the list and then adjust their RGB field values as you prefer. Higher numbers are paler.

Options:

Hit the "F1" key for additional online help for each option.

• Choose a medium or large setting for "Cursor size" to help it stand out. If you chose subtle colours, a "blinking underscore" cursor can be hard to spot.
• "Buffer size" is the number of commands to keep in history. Each line entered counts as one line.
• Check "Discard old duplicates" so that only distinct command lines will be stored. As well as saving resources and keeping recent commands in the scrollback history, this reduces the number of "Up arrow" key presses required to recover a previous command.
• Under "Editing options", check both "QuickEdit mode" and "Insert mode". QuickEdit mode lets you copy and paste using only the right-mouse button, instead of having to select "Mark", "Copy" and "Paste" from the Edit menu. (From Windows 10, this is the default setting.)

Font

Change the font to Consolas. (General rule - this is the first thing to do in any developer tool.) You can also probably go smaller than the default size without losing legibility.

Layout

The default is 80 characters wide by 24 high. Again, you can make it bigger without taking up too much of your desktop.

• Screen buffer size: make this really high, for example 400+ lines. This is the amount you will be able see using the scroll bars.
• Window size: this is the actual size of the window.

Click "OK".

Now double-click that shortcut to fire up SQL*Plus, and log in.

Set up the SQL*Plus environment:

Set environment variables

In secure office desktops you may not be able to change system settings including the registry, but you can often change the environment variables for your own account. From the Windows Start menu, start typing environment and look for "Edit the environment variables for your account" in the results (the exact wording varies according to your Windows version). The SQL*Plus Reference lists some useful settings (though notice that not all apply to Windows).

Create a login.sql

About login.sql

SQL*Plus uses two settings scripts, login.sql and glogin.sql, referred to in the documentation as the User Profile and Site Profile, respectively.

• glogin.sql is the global script that is automatically run for all users of that Oracle Home. It resides in [ORACLE_HOME]/sql/sqlplus/admin.
• login.sql is just for you. You can specify its location in the SQLPATH variable or registry entry.

Prior to Oracle 12.2, SQL*Plus automatically invoked a login.sql if it found one in the current directory. In the steps above, we create a directory for SQL scripts such as z:\sql, place a login.sql in it, set the shortcut to start there, and it gets called automatically.

Starting in 12.2, SQL*Plus no longer automatically runs a login.sql script in the current directory. This is for sound security reasons, but it means we must set SQLPATH in our environment. (In Linux, the corresponding environment variable is named ORACLE_PATH, just to confuse you.)

SQLPATH can be either an environment variable, or a registry entry in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, or both. A registry entry is set up on installation, with the value of [ORACLE_HOME]\DBS, which may not be what you want. It's probably best to override it with a user-level environment variable, with a value like z:\sql. It's a path variable, so you can add multiple locations separated by ';'

Finally, depending on your version and patch set, you may hit Bug 25804573, SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL, and SQL*Plus won't call your login.sql even after all that.

Create a login.sql file in your SQLPATH directory, and edit it.

• If you have a preferred editor, define it as the SQL*Plus default using, for example
  
  def _editor = gvim
  
  (note the leading underscore for "_editor"). Ultraedit, Textpad and Notepad++ are also highly rated. It's generally best to ensure that the executable is set up in your user PATH variable so that you can invoke it without needing to specify a path. If you do include a path and it contains spaces, enclose it in double quotes.
• SQL*Plus "set" commands can usually be abbreviated, and can be combined onto one line, so for example
  
  set pagesize 999
set linesize 111
  
  can also be written as
  
  set pages 999 lines 111
  
  (note that the default page size is rather unhelpfully 12)
• Some people like to replace the default SQL> prompt with their username and the name of the instance they are connected to. I don't use this myself as I find that a prompt more than four characters long messes up my query layout, since the first line always starts at a different position than the other lines.

  From the 10g Oracle client onwards, SQL*Plus re-executes the login.sql script after each successful "connect" command. A sample login.sql is supplied, which prints out some session information when you connect to the database by querying the V$ tables. This uses the Windows TITLE command to set the title property of the current window. (See the example above.)

• The TAB setting is a frankly odd option to replace arbitrary multiples of blank spaces with tab characters which will be displayed with unpredictable sizes. Set it to OFF.
• The TRIMSPOOL setting trims blank spaces from the ends of lines when spooling to files. Set it to ON.
• For Explain Plan in SQL*Plus, try xplan.sql.
• For a handy database object name resolver, try which.sql.
• All the SET commands are listed in the online SQL*Plus reference manual. Bookmark it!

Sample login.sql for Windows:

set linesize 132 pagesize 999 feedback off tab off trimout on trimspool on verify off termout off

def _editor = gvim

col windowtitle new_value windowtitle
select '&_user@&_connect_identifier [session '||sys_context('USERENV','SID')||' serial# '||dbms_debug_jdwp.current_session_serial||']' as windowtitle
from dual;

host title &windowtitle
undef windowtitle

set termout on feedback on

Additional resources

• Oracle Client installation guide (19c)
• Oracle SQL*Plus Reference (19c)