Hands down, an old “GUI” version of Oracle SQL*Plus (sqlplusw.exe) which was available only on Windows, was always a bit awkward to use compared to a command line version of the same tool. Nevertheless, it was GUI version of the SQL*Plus that I was using most often for ad hoc access to database. Despite of obvious shortcomings in the area of command line history and editing, it felt familiar environment to many developers and DBA’s and now that it’s gone forever with the release of Oracle 11g, I thought I would prepare a “replacement” that will somehow visually resemble old tool but also bring some goodies not available in sqlplusw.exe.
First thing that we need to do, is to correctly setup environment. Since I’m using Windows 7 with regional settings for Slovenia and with Oracle NLS_LANG=SLOVENIAN_SLOVENIA.EE8MSWIN1250, it means that I can not simply create a shortcut to sqlplus.exe, because command line version of sqlplus.exe is not able to deal with code page CP1250. Before we run sqlplus.exe we need to change NLS settings to NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852 (or whatever MS DOS codepage variant you’re using in your country for Window command line). This “limitation” in particular was the #1 reason for sticking with sqlplusw.exe all those years and since “MS DOS” LATIN 2 codepage (CP852) is a subset of CP1250 it means that we must be aware that some characters can’t be displayed. For example euro symbol (€) is not supported by CP852, you won’t see properly € character and whenever you’ll want to insert € you must do it with Oracle function CHR(128) instead of typing € on keyboard — otherwise the character will be lost during client-server characterset translation. The simplest approach is to prepare a “special” launch script just for sqlpluse.exe, here is mine (saved in E:\Oracle):

@echo off
::
:: RunSQLPlus.cmd
:: 
title ORA11 x64
SET ORACLE_HOME=E:\ORACLE\ORA11P3
SET PATH=%ORACLE_HOME%\BIN;%PATH%
SET SQLPATH=E:\ORACLE\scripts
SET NLS_LANG=SLOVENIAN_SLOVENIA.EE8PC852
cmd /k sqlplus /nolog

Now we can create shortcut to this batch file, of course with legendary icon;-)

By default we have a boring sqlplus command prompt window. Let’s change some properties, open context menu of the window, then “Properties”. Make sure both “Quick Edit Mode” and “Insert mode” are checked, also increase “Buffer Size” size from 50 to something bigger, like 200.

On the next tab select font of your choice, my favorite font is “Lucida Console”, size 16.

On the “Layout” tab increase “Screen Buffer Size” and if you don’t like 80 x 25 character sized default window, change the proportion to something else.

On the “Colors” tab pick a color of your choice, since I want to achieve the look of old GUI sqlplusw I changed background to white and treated myself with a blue color for text, something I could not do in GUI variant.

Let’s close (and save changes) and again launch sqlplus to see the changes…

This is much better. The last tidbit of tweaking our new “king” is making SQL*PLus display customized sql prompt with user and database that we’re connected to. If you recall cmd batch script from above you’ll see SQLPATH pointing to E:\ORACLE\scripts. In this directory create your customized login.sql script, like this one:

-- login.sql
host title &_user@&_connect_identifier
set sqlprompt "&&_USER@&&_CONNECT_IDENTIFIER~SQL> "
set linesize 2000
set pagesize 500
set truncate on

This script will properly set both sqlplus prompt as well as title of the command line window to reflect user and db to which we’re connected. Even if command prompt is in minimized state on taskbar, we can easily determine to which database we’re connected by hovering mouse pointer over the icon.

And finally, I would again like to warn about properly setting up NLS_LANG environment variable (in my case to MS DOS Latin 2 codepage), otherwise you’ll end up with data corruption due to incorrect code page conversion between the client and server.

That’s pretty much all. One thing that you must be extra careful about is when you just want to use sqlplus to run some script written in Notepad/Notepad++ (written likely in cp1250 in CE region) in which case you should run sqlplus.exe with EE8MSWIN1250 settings. The instructions above are targeted to interactive ad-hoc querying.

Even if you’re a die-hard user of sqlplusw it’s just a question of time when you’ll appreciate the benefits of cmd.exe:

• Arrows Up and Down allow you to navigate between the commands in the buffer
• Arrows Left and Right allows you to navigate inside current command line
• F7 brings a menu with commands in the buffer. Select and press Enter to execute the command line selected, or Select and Right Arrow to put the selected line on the command prompt where you can edit it
• F8 allows you to search command line buffer. For example start writing your query, then press F8 and cmd will try to pull the line from the buffer.