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 runs Windows 7 unless otherwise noted.
1. Create a shortcut in Start menu
In Windows Explorer, at C:\Users\user\AppData\Roaming\Microsoft\Windows\Start Menu\Programs (where user is you or "All Users"; you need to manually type AppData in the path since it's hidden), create a shortcut named for instance "Sqlplus" pointing to %oracle_home%\bin\sqlplus.exe, and prepend cmd.exe /k. (I use %oracle_home% notation to refer to your oracle home folder. Also note Windows XP uses a different path, C:\Documents and Settings\user\Start Menu.) So the shortcut target may look like this:
cmd /k d:\oracle\product\11.2.0\Db_2\bin\sqlplus.exeBecause 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. (On Windows 10, pin the shortcut to Start menu.)
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\11.2.0\db_2& set oracle_sid=orcl& d:\oracle\product\11.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 in terms of ergonomics. 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
| c:\ C:\WINDOWS\System32\cmd.exe | c:\ C:\WINDOWS\System32\cmd.exe |
D:\>od -x D:\oracle\product\11.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\11.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 prior to Windows 10,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 (but for 12cR2, see Doc 2241021.1). 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 ... --columns you commonly access col object_name for a30 col db_link for a40 col host for a40 ...
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).
[Update 2018-03] Sqlplus 12.2 has the new option -f or -fast, which is equivalent to changing the following settings: arraysize from 15 to 100, lobprefetch from 0 to 16384, rowprefetch from 1 to 2, statementcache from 0 to 20, and pagesize 14 to 50000.
[Update 2018-08] Sqlplus 18.1 has the option set linesize window, which automatically sets linesize to the same as the console window width, but gratuitously, also sets pagesize to that of the window height. This means the output of a query will repeat the header once every screen full of lines. I hope Oracle can fix this problem. Even if you set linesize to window, as said above or in note2, linesize should be 1 less than the console width unless you run Windows 10. Check the difference by select username, external_name from user_users;
4. Environment variables
Environment variables on Windows are case-insensitive and their values generally are so too. 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. (Here the percent sign is literal; type it as is.) 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 a 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: If your PC uses code page 437 (check with chcp console command), you should set the character set part of this environment variable to US8PC437, e.g. set it to .US8PC437 (note the leading dot) or american_america.us8pc437, according to Oracle Character Sets for Console Mode (OEM) Code Pages. If this is not set here, its value may be AMERICAN_AMERICA.WE8MSWIN1252 set in registry by the default installation, which is only good for GUI not console applications. (You may temporarily set NLS_LANG to a value not recommended by Oracle in case you wish to read command feedback or error message in a language you know, e.g., english or american. But the easier alter session set nls_language=english would at least get the error message in English.) Before running imp or exp (but not expdp or impdp), this variable is also better set to the same as the source database character set to avoid character set conversion, even if the source and target databases use different character sets (Ref). 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 variable 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.
SQLPATH: If somescript is in this directory, you can type @somescript at SQL> prompt to run it without specifying full path.
5. Tips and Cautions
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 like the fact that you can press 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, End to the end, 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
| c:\ yong@testdb | ||
|
SQL> SQL> SQL>
|
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 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). The best place to set that automatically on launching Sqlplus may be glogin.sql, with this line in it: host title &&_user@&&_connect_identifier, or host title &&_connect_identifier:&&_user if you care about the name of the DB more than the user, especially when you only see the icons on the 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. Note that in Windows 7 and above, if your window title always begins with Administrator and cannot be erased with the title command, prepend runas /trustlevel:0x20000 to the command console target cmd /k..., or modify cmd.exe.mui. See this for more info.
It's unfortunate that Oracle 11g no longer provides Windows Sqlplus (sqlplusw.exe).note5 Both Windows and console Sqlplus allow you to vertically highlight or copy text (Windows 10 vertical selection requires pressing Alt or unchecking "Enable line wrapping selection" in console Options); vertically highlighting indented text makes reading an execution plan easier. And both flavors of Sqlplus output lines on terminal strictly based on time or input order, unlike in a Linux/UNIX terminal, where pasting multiple lines of SQL to it shows the SQLs mixed with query result in the middle instead of at the end.note6 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.note7
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:
We all know the benefit of using bind variables. Unfortunately, prior to 12cR2, Sqlplus has a serious flaw in its implementation. Consider
var n number exec :n := 123 select * from emp where empid=:n;While the query happily takes the runtime value 123 for the bind variable, the bind or assignment line right above it actually creates a cursor in the shared pool with SQL text BEGIN :n := 123; END;, taking about 24 kB memory (depending on Oracle version). Since it's PL/SQL, the literal value 123 is immune from the setting of cursor_sharing. Imagine you set :n to thousands of different values, thinking you saved memory and parse time on the query using the bind variable. Before they fix it on my enhancement request 20802412, use a tool other than Sqlplus such as Perl, Java, Python, etc., where bind is a purely client side action.
var n number=123 var s varchar2(10)=Smith var s varchar2(10)='Smith' var name varchar2(10) var name=SmithThe value assignment is truly client-side so there is no longer shared pool "contamination". Also, Sqlplus 11.2.0.4 with certain patches may already support this.
6. Useful link
William Robertson's Setting up SQL*Plus on Windows is very user-friendly.
7. Undocumented
command line switches:
-n: bypasses glogin.sql
-prelim: allows sysdba to login to do limited debugging (Doc 986640.1)
-dynamic and -static: 12c only. Not sure what they are (they're NOT for you to force connection to the listener of dynamic or static registration when both exist.)
-restrict: same as -r
-silent: same as -s
-compatibility: same as -c
-logon: same as -l
set system variables:
_copylowvalue: to use copy command on data embedded with chars of low ASCII value (Doc 197614.1)
_copynumbertofloat: (Bug 29213)
_prelim: see command line switch
_restrict: see command line switch
_showrowship: After you set it to on, a query selecting more than 80% (_row_shipping_threshold) columns will show "_ROWSHIP TRUE". (Wide Table Select (Row Shipping), Virtual Columns and Row Shipping)
xmlformat: If your XML output with multiple layers of tags does not show multiple lines with indentation, set xmlformat on. Try select xmlelement("outertag", xmlelement("innertag",username)) from user_users;
misc:
show pdbdetails: 19c; provides PDB info (nothing new compared to v$pdbs)
_________________
[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 occur in a UNIX/Linux terminal, nor on Windows 10.
[note6] Private email with Don Libes, the author of the Expect programming language, kind of confirms that there is 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.
[note7] 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
[note8] This particular problem can be avoided by chcp 1252 and set nls_lang=.we8mswin1252 at Windows Console before launching sqlplus. (Ref: 945448.1)
[note9] 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. On the other hand, you may also argue that the text should not have two carriage returns on one line because a carriage return, by definition, simply moves the current input position to the line head as on a typewriter.