How to Setup Sqlplus for Most Efficient Use on Windows

Sqlplus command line or console version is actually more powerful and efficient than its Windows or GUI version.^note1 The feature that brings the greatest convenience is probably being able to press UP key to recall previous commands. With Oracle11g, the Windows version is no longer available, making the console version more than just appealing, but essential. This article summarizes some tips you may find useful in using the console version. I assume your desktop OS is XP unless otherwise noted.

1. Create a shortcut in Start menu

In Windows Explorer, at C:\Documents and Settings\user\Start Menu (where user is you or "All Users"), create a shortcut named for instance "Sqlplus" pointing to %oracle_home%\bin\sqlplus.exe, and prepend cmd.exe /k. (The path is different in Windows 7; see bottom of this page.) So the shortcut target looks like this:

Because of cmd /k, if you exit sqlplus, sometimes accidentally by ^C, the console window remains open instead of closing itself. So you can still see the result on screen.

If for any reason you need to set oracle_home and oracle_sid just for running this sqlplus, i.e. not wanting to set it in Control Panel, the command will be:cmd /k set oracle_home=d:\oracle\product\10.2.0\db_2& set oracle_sid=orcl& d:\oracle\product\10.2.0\db_2\bin\sqlplus (adjust paths as needed). If you think too many environment variables should be set, put them in a batch file and call the file on this command instead. For instance, c:\10g.bat & cmd /k d:\...

I normally set "Start in" folder to somewhere other than the default %oracle_home%\bin, such as d:\temp, so a spooled file goes there by default.

Once this shortcut is there, you can quickly launch Sqlplus by going to Start -> Sqlplus. If you want to launch it even faster, set a shortcut key for it.

2. Adjust shortcut properties

Screen colors are probably the most important, ergonomically speaking. In Properties window for this shortcut, go to Colors tab, I strongly suggest you choose black for Screen Text and very light color or white for Screen Background. Trust me on this advice! Do an experiment with two console windows, one with white text in black background and the other with black text in white or light color background (see below). Make sure text font is exactly the same in size and face. Stand some distance away. You'll find that the text in the second window looks much clearer and you can read faster. I think the reason is that white text lines have more visible tiny black gaps than black text lines have white gaps.

Eye strain test

D:\>od -x D:\oracle\product\10.2.0\db_1\bin\sqlplus.exe|head -5
0000000 5a4d 0090 0003 0000 0004 0000 ffff 0000
0000020 00b8 0000 0000 0000 0040 0000 0000 0000
0000040 0000 0000 0000 0000 0000 0000 0000 0000
0000060 0000 0000 0000 0000 0000 0000 00f8 0000
0000100 1f0e 0eba b400 cd09 b821 4c01 21cd 6854

D:\>od -x D:\oracle\product\10.2.0\db_1\bin\sqlplus.exe|head -5
0000000 5a4d 0090 0003 0000 0004 0000 ffff 0000
0000020 00b8 0000 0000 0000 0040 0000 0000 0000
0000040 0000 0000 0000 0000 0000 0000 0000 0000
0000060 0000 0000 0000 0000 0000 0000 00f8 0000
0000100 1f0e 0eba b400 cd09 b821 4c01 21cd 6854

Don't be discouraged by only 16 color choices. You can adjust Red, Green and Blue to fairly high numbers to get your favorite light color, such as 200-255-200 for light green, 255-255-200 for beige, etc.

Now go to Options tab. Check QuickEdit mode and Insert mode checkboxes. It makes copy and paste of text easier. (If you see somebody send an image of Sqlplus text, you know he/she is very computer un-savvy!)

Under Font, choose a small font but not too small to your vision. I like Lucida Console, and not Bold version of it.

Under Layout, set Width for both Screen buffer and Window sizes to the same number, I would say at least 100, up to the full width of your screen, but make sure the vertical scroll bar is still visible near the right edge and no horizontal scroll bar is shown at the bottom. On my 19-inch monitor using 10-point Lucida Console font, I can get 188 in screen width. Set Height for Screen Buffer to 2000, or at least 1000. The reason you want the Sqlplus screen as wide as you feel comfortable is so that you'll have less cluster in the query output.

3. Sqlplus setting

Default pagesize 14 is absolutely inadequate. I prefer 100 (except in case of very wide tables such as some Oracle Applications AOL tables, where select * repeats headers too often even with pagesize 100). Since we widened our sqlplus console window in the last step to, say, 188, we should make full use of it. But due to a problem with console window,^note2 you should set linesize to 1 less than window size, e.g., 187. The only time you want a smaller linesize is when you DESCribe a table or view, so that column names and types are not too far apart in DESC output. (Interestingly, this DESCribe feature is not for DESCribing a PL/SQL package or procedure.)

But typing these set commands every time you start Sqlplus would be tedious, even with abbreviations such as set pages 100 lin 187. So add them to%oracle_home%\sqlplus\admin\glogin.sql, or current directory or %sqlpath%\login.sql. If one or both of them are already in there, change or delete them. Other useful settings are:^note3

set long some_big_number serveroutput on arraysize 100 trimspool on tab off ...
column plan_plus_exp format a100
...

With the above window width and Sqlplus linesize setting, query column values for the same row need to be vertically aligned with the column headers (and dash lines), as in the case of select * from a_wide_table... Because Sqlplus uses tabs for fast display, some column headings or even values may not align very well with dash lines under the headings. Set tab off to correct the problem. If you really prefer to see one data row shown on one row, the preferred tool is Toad or any one under Toad Alternatives. But Sqlplus is not completely hopeless; you can set both window width and Sqlplus linesize to a very big number like 1000 to achieve the same, except that you may still need to manually set the width for varchar2(a big number) columns to a smaller size (e.g., col mycol for a80).

4. Environment variables

The following are commonly used environment variables that affect the behavior of Sqlplus, which can also be set in Windows registry. But if they're set in both places, environment variables take precedence. If you need to find the current value of a variable regardless where it's set, you can type@[%the_variable_name%] at SQL> prompt to see it outputs the literal string you typed (which means it's not set) or its value (when it's set); try @[%oracle_home%]and @[%nosuchvar%] to see what I mean. For a fairly complete list of the variables, see Radoslav Rusinov's article (see pp.12-7; translate with Google Translate).

LOCAL: Windows equivalent of UNIX TWO_TASK environment variable. With this set, sqlplus user/pass is translated to sqlplus user/pass@value_of_%LOCAL%.

NLS_DATE_FORMAT: I like to set it to yyyymmdd hh24:mi:ss so DATE type columns are displayed in my favorite format. The console command is: set nls_date_format=yyyymmdd hh24:mi:ss. (Yes, there is ONE space between dd and hh24 and it's NOT interpreted by set as two arguments!) Setting this environment variable is preferred over adding alter session set nls_date_format='yyyymmdd hh24:mi:ss' to [g]login.sql because you won't get the annoying "Session altered" feedback upon login, and as a bonus, if you use RMAN from this console window, timestamp in RMAN shows in this format too. So you may consider setting it in Control Panel as a system-wide variable. See also NLS_LANG below.

NLS_LANG: The primary purpose of this variable is as the name suggests. If your PC uses a code page other than 437 (check by chcp console command), you may wish to set this variable to english or american, unless you want feedback for the Sqlplus commands to be shown in your own language. Sometimes this variable is also changed in order to display foreign language text stored in the database, before running imp or exp (but not expdp or impdp) to avoid character set conversion; if the database you export from and the one you import into have different character sets, Oracle recommends you set NLS_LANG for your exp and imp environment to the same as the source, not target, database. Not widely known about this variable or registry key is the fact that if for some reason the registry is missing this key, setting NLS_DATE_FORMAT environment parameter alone won't let you see the desired DATE format when you select sysdate from dual; but since NLS_LANG is automatically set in registry during Oracle software installation, you're probably not aware of this issue. Equally obscure is the fact that if your Sqlplus starts in an NLS_LANG setting that differs in character set from that of the database you run queries in, sometimes Sqlplus widens the column in the output for string type columns, either for a query or for the Sqlplus print command to print the bind variable value. To see the difference, run select username from user_users in databases that use different charactersets after you set NLS_LANG to these two values,.al32utf8 and .we8iso8859p1 (note the leading dot). But using the setting .we8mswin1252 seems to avoid this problem.

SQLPATH: If somescript is in this directory, you can type @somescript at SQL> prompt to run it without specifying full path.

5. Tips

Many people are used to typing edit (or ed for short) at SQL> prompt to launch an editor. The problem is that it litters the file afiedt.buf wherever you launch Sqlplus. You can limit this litter to one single place with set editfile some_fixed_path\somefile.^note4 Unless your SQL or PL/SQL commands are multi-line, you may enjoy better pressing UP arrow key to retrieve previous commands. This is one of the best features of the console version Sqlplus. The command line buffer size is only 50 by default and can be changed under Options tab in Properties window. Also, don't just use LEFT and RIGHT keys; press Home key (with NumLock off) to go to line beginning, Control-LEFT/RIGHT to move one word instead of one character at a time, and Esc to clean current line immediately. F7 is a little known trick in console; it shows all saved commands in your history buffer up to the history size (see below). Once you press F7, you can move UP/DOWN to the one you want, LEFT or RIGHT to select it on your command prompt (SQL>), or Enter to directly select and execute it, or Esc to cancel.

The beautiful window from F7

Since we enabled Quick Edit, you can double click on a word or select text to highlight it, right click or press Enter to copy (no message telling you it's copied), and right click again to paste (or in most applications such as Notepad, ^V to paste). Do not press ^C or ^V in this console window. ^V does nothing and ^C either exits Sqlplus or, if the query is still running inside the database, stops the query. It may take you a while to get used to the console window's copy and paste, and it's very confusing to have wrong text copied when you accidentally highlight some text (you can tell by the changed window title that begins with "Select"). This accidental highlight also "freezes" the screen; if you know your query should return rows now but the screen "hangs", make sure there's no text highlighted. (If there is, press any key such as Space to un-highlight.)

Another good feature is that you can set console window title with command such as title yong@testdb. At SQL> prompt, prepend with $ or host (or hos for short): host title yong@testdb. With a title for the console window, you can quickly spot it when it's minimized on the taskbar. If you don't set the title, our Sqlplus window opened by this Start-button shortcut will be "sqlplus - full_path_to_sqlplus". If you have to frequently set title because you often change connections (dropping a table in Prod while you thought you were in Dev is not pretty!), or because you often press ^C ending up at console command prompt, you can save a little effort by creating a short named sqlplus.exe, such as s.exe by copying sqlplus.exe to s.exe (in Windows Vista, a symbolic link will do). You may as well rename the Start-button shortcut to single letter "s". Then you can type s yong@testdb. The Sqlplus window will automatically bear the title "s - s yong@testdb". Doing that saves some typing and hopefully the title is short enough to show in the minimized tab on taskbar. Some people prefer to customize SQL> prompt to show DB name and/or username. You can follow this instruction (section "My Personal SQL Prompt"). Beginning with 10g, sqlprompt adjusts _connect_identifier and _user dynamically. Obviously the console Window title or the label on the minimized tab won't automagically show the same string.

It's unfortunate that Oracle 11g no longer provides Windows Sqlplus (sqlplusw.exe). Although I don't recommend it for general use, it has some interesting features compared with the console version Sqlplus. Both Windows and console Sqlplus allow you to vertically highlight or copy text; vertically highlighting indented text makes reading an execution plan easier. And both Sqlplus output lines on terminal strictly based on time or input order, unlike in a Linux/UNIX terminal, where pasting multi-lines of SQL to it shows the SQLs mixed with query result in the middle instead of at the end.^note5 On the downside, the command console doesn't support here document, so you can't pipe SQL or Sqlplus commands to a SQL session, as you would be able to do on Linux/UNIX.^note6

Both Windows and console Sqlplus allow for string search (in console Sqlplus, go to Edit | Find). But the console Sqlplus can find strings from the entire buffer, as much as you can scroll back to, while the Windows Sqlplus only searches the visible screen.

Having said all these, the Windows version Sqlplus does have some unique features missing in the console version:

• Windows Sqlplus never exits out of Sqlplus when you press Control-C (UNIX/Linux sqlplus doesn't either), while the console Sqlplus may. This is a very annoying problem and may be the only good reason sometimes I still use the Windows Sqlplus. Hopefully Bug 8890996, an enhancement request filed by me in 2009, will eventually get Oracle's attention.
• The Windows Sqlplus has much more powerful special character display than the console version. Try select chr(49833) copyright from dual; in both to see what I mean. The weak capability of the console version could be dangerous in some cases; if the text column has two Carriage Return's, the Windows Sqlplus shows a rectangular dot, which serves as a warning something is here, but the console version silently "erases" the first part of the text. Try select 'x'||chr(13)||'y'||chr(13)||'z' from dual;. The behavior doesn't change with different NLS_LANG settings.^note7

6. Useful link

William Robertson's Setting up SQL*Plus on Windows is very user-friendly.

_________________

[note1] Some people call the command console a DOS window. But the correct way to say is that sqlplus.exe is a Win32 Console application, and sqlplusw.exe a Win32 GUI app. (Thanks to Dennis Yurichev's correction)

[note2] See details here. The problem does not exist on a UNIX/Linux terminal.

[note3] Set long big_number so you can see the definition of a complicated trigger or view, or text in any long or clob column. Serveroutput on is for dbms_output.put_line (but do NOT set it to on if you need to call dbms_xplan.display_cursor in 10g to show the plan for the last executed SQL), or if you simply don't like the little overhead of that one extra consistent get. A bigger than default 15 arraysize (or array for short) is to save on consistent gets; the default 15 is rarely adequate (Perhaps it increases a little sqlplus.exe process memory and server side PGA). Trimspool is to remove trailing space characters in the spool file padded to linesize so the file is smaller and lines don't "mysteriously" wrap.
Before 10g, column plan_plus_exp needs to be larger so your explain plan output doesn't wrap lines too early. It's interesting that plan_plus_exp can only be set to linesize-19 at maximum unless you reduce object_node_plus_exp; beyond that, explain plan output lines wrap. So if linesize is say 145, you need col plan_plus_exp for a126, not a127. Beginning with 10g client, you don't need to worry about this column setting.

[note4] If you set editfile nul:, you get "Incorrect function" message when you edit and the default editor is Notepad, as if you typed notepad nul: at command prompt. So this is not an option. (On Linux/UNIX, if you set editfile /dev/null, you see an empty vi editor and you get "/dev/null" is not a filemessage when you edit, as if you typed vi /dev/null at shell level.)

[note5] Private email with Don Libes, the author of the Expect programming language, confirms that there's no workaround for this feature of UNIX terminals, unless the program (sqlplus here) has custom code to avoid this "premature" echoing. But some shells behave better than others. For instance, in bash you would paste a lot of lines at once on a fairly fast connection to the terminal to see jumbled lines.

[note6] Somebody wrote a very clever script to mimic a here document in the command console (originally posted here):

@echo off
FIND "/*%none% some_label" <%0 |sqlplus username/password
GOTO end
: ----------- embedded SQL-------------------------
/* some_label */ select * from dba_free_space where rownum = 1;
/* some_label */ rem select tablespace_name from dba_tablespaces;
: ----------- end of embedded SQL-------------------------
:end

[note7] This is actually a problem with the Windows console, not Sqlplus itself. UNIX/Linux terminal suffers from the same problem. See my posting to Oracle-L.

[2011-08 Update]
In Windows 7, the path is
C:\Users\user\AppData\Roaming\Microsoft\Windows\Start Menu\Programs
You need to manually type AppData in the path since it's hidden.