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 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+.
Two versions of SQL*Plus are provided on the Windows platform. The deprecated GUI version can seem initially more attractive as it has a cheerful white screen, an icon, more buttons, "File > Open", "File > Spool" and "Options > Environment" options, but it is generally less useful. The directional keys simply scroll up or across the screen, rather than retrieving previous commands or navigating in the current record. You can't change the font or the colours. There is no option to paste copied text with a right-mouseclick (though you can highlight-right-click to re-run a command). Its one neat trick is the ability to change the working directory from the "File > Open" menu option. This article focusses on the character mode version.
Much as I love Unix (and my Mac), it has to be said that the Windows version of SQL*Plus is better than the Unix one because it is more configurable and comes with command-line history.
One strength of the character mode sqlplus.exe is that it simply runs in the Windows command window (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. It also uses a fraction of the system resources (sqlplusw.exe hogs around 12MB as soon as you start it up, while sqlplus.exe is initially listed in Task Manager as Zero KB, only rising to a few kilobytes as you fill the scroll buffer). The following steps essentially describe how to customise cmd windows.
Location for scripts
First, set up a folder for files. 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
The important thing is to have a dedicated folder for SQL*Plus stuff.
Define a desktop shorcut
Find the sqlplus.exe executable (not sqlplusw.exe!) in the Oracle bin folder. In earlier releases this was something like
c:\Oracle\bin, while in 12.1 it's
Using the right mouse button, drag it to the desktop and select "Create shortcut here". 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.
Update - 2016These days I use a custom toolbar option in PL/SQL Developer to launch a SQL*Plus window using the current database login credentials, and I use another one to run the current script in SQL*Plus. See my PL/SQL Developer Setup guide for how to do that. (TOAD and SQL Developer have similar options.)
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. "c:\oracle\sql".
- (If you have a 10g or earlier client available) Change the icon: give the generic shortcut a proper 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 only have 11g on the machine I'm using to write this article, and 11g does not include sqlplusw.exe. Which is a good thing.)
Now work through the other property tabs (you will notice that there are more options available than sqlplusw.exe gave you).
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.
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.
You can probably go smaller than the default size and get more on the screen without losing legibility. I like Consolas or Lucida Console. (I can't stand Courier, New or otherwise, but maybe that's just me.)
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.
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
By default this will invoke Notepad.exe and edit a new file named "login.sql". Notice that we are now working in the "SQL" folder we created earlier.
- 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.
- 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
TITLEcommand to set the title property of the current window. (See the example above.)
TABsetting 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.
TRIMSPOOLsetting 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!