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
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.
We’re surprised by broken Raritan KVM console (Java application) on some of our workstations, mine included. We could login to the Raritan KVM (Dominion KX II), but could not open console to any server. We’re always greeted with the error:
Error while getting the list of open Targets, please try again in a few seconds.
First we upgraded Raritan firmware to the latest one (126.96.36.199.886), but the error was still there. The next suspect was Java itself. I noticed that coworkers working with older version of JRE does not have the problem…and to make long story short, we finally found explanation for new “security feature” introduced in Java 7 U51, a quote from Oracle release notes:
Since Dominion KVM server applet is not signed the JRE will block the app by default. The solution is to add url to Raritan KVM server to the exception list.
First, make sure that you open correct (the latest) Java Control Panel, this is especially important if you happen to have more than one JRE/JDK on your machine, because you won’t find the “Exception Site List” in older Java Control Panel. In my case (Windows7, x64) the best approach is to manually find the executable:
C:\Program Files (x86)\Java\jre7\bin\javacpl.exe
Then check the version:
Add url to exception list:
Here is a short note on how to (at least) partially fix ghastly default fonts that SQL Developer is cursed with it on Linux. After I installed the recently released SQL Developer 4.0 on my Fedora 19 workstation, I immediately increased the font size from 11 points to 14 by editing:
$ nano ~/.sqldeveloper/system188.8.131.52.80/o.sqldeveloper.184.108.40.206.80/ide.properties # To modify the font size for all look-and-feels in all locales, set # the Ide.FontSize property. For example: Ide.FontSize=14
…plus, I changed the way how Java is smoothing fonts by adding two variables in product.conf file:
$ nano ~/.sqldeveloper/4.0.0/product.conf AddVMOption -Dswing.aatext=true AddVMOption -Dawt.useSystemAAFontSettings=lcd
Make sure that SQL Developer is not running while editing product.conf file, because it’ll overwrite your changes at exit!
And here is, somehow prettier SQL Developer IDE:
In the final, part 3 of the article, I’ll show you how we’re pulling from ServersCheck sensor the current temperature and relative humidity in our data center with simple SQL statement, like this:
If you recall, in Part 1 of the article I published Go “script” that could be compiled on all supported Go platforms, including Linux.
We decided that we’ll be using one of our existing Oracle11g XE instance running on Oracle Linux 6.4 (x64) to host our “vpctemp” application written in go. On this Linux server we already had the latest Go compiler installed (go 1.1.2 at the time of this writing), but we could as easily compile the vpctemp.go on some other box and then simply copy executable to our production Oracle Linux server — not worrying about some potential missing library, thanks to “go” statically compiled nature.
We copied vpctemp binary to /oracle/dba/go directory and turning on execution bit to allow vpctemp to be executed by oracle user:
[alesk@oraxe go]$ ls -l /oracle/dba/go total 5056 -rwxr-xr-x 1 oracle dba 5177336 Jul 31 12:17 vpctemp
Then all that we did was to create “pseudo” external table. Pseudo in a sense that external table has no reference to external data file, instead all that it includes is a call to preprocessor directive. Preprocessor directive points to our golang program, which pulls data from ServersCheck sensor and prints the result to standard output, which is send to a client. Very simple and convenient way for ad hoc checks. (Oracle introduced preprocessor directive for external table in 11gR2 and later on backporting feature to 10.2.0.5 as well)
// SQL*Plus...connect alesk@oraxe create or replace directory goexec as '/oracle/dba/go'; grant execute on directory goexec to dba; create table vpctemp ( line varchar2(80) ) organization external ( type oracle_loader default directory goexec access parameters ( records delimited by newline preprocessor goexec:'vpctemp' ) location ( goexec:'vpctemp' ) ); create public synonym vpctemp for alesk.vpctemp; grant select on alesk.vpctemp to dbateam;
And that’s it. For a DBA team getting the current temperature and humidity in a data center becomes as easy as:
SQL> select * from vpctemp;
** UPDATE May 2019 **
During the migration of golang vpctemp application from 11g to 18c database we got an error when querying from vpctemp external table:
SQL> select * from vpctemp; select * from vpctemp * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached
We found out that Oracle external table “preprocessor” in 18c doesn’t like the first record (blank line) and since original external table (see DDL above) was created without an explicit reject limit, the Oracle created external table wit the default valu, “reject limit 0”. That’s why query failed immediately.
The workaround was newly created external table:
CREATE TABLE KAVSEK.VPCTEMP ( LINE VARCHAR2(80 CHAR) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY GOEXEC ACCESS PARAMETERS ( records delimited by newline preprocessor goexec:'vpctemp' nologfile nobadfile ) LOCATION (GOEXEC:'vpctemp') ) REJECT LIMIT 10 NOPARALLEL NOMONITORING;
SQL> select * from vpctemp; LINE ----------------------------------------------- VPCtemp v0.10 Location: VPC1 *********************************** Timestamp : 2019-5-31 13:19:09 Temp. internal: 13.88 Temp. external: ? Humidity : ? 6 rows selected.
After you install Toad on workstation with Slovenian keyboard you might find out that you can not enter pipe (|) as usual, with AltGr+W in Toad Editor. Entering AltGr+W will open “Watches” instead of displaying pipe. The workaround is simple, go to Toad menu and select View -> Toad Options… -> Toolbars/Menus -> Shortcuts.
In combo box sort the content on column Category. Scroll to the “Editor” entries and find the line for command “Watches”. Select the line and replace default Ctrl + Alt + W with something new, for example Shift + Ctrl + Alt + W, as shown on the picture.
This is apparently a bug in Toad where Ctrl + Alt + W somehow equals AltGr + W!?