Spicing-up Oracle on Windows

July 25, 2007

This article covers the use of setting some extra string values in the registry, how to display what the values are set to, and how to work around the missing tool known as oerr.

Configuring SQL*Plus

Ever get tired of seeing the same old display in SQL*Plus? One relatively easy fix you can make is to add two string values to the registry. The string values set the font and size of the text you see in a SQL*Plus session. Note that this is only for the application, not the command line interface version. The values, which essentially become environment variables, are SQLPLUS_FONT and SQLPLUS_FONT_SIZE. Use regedit to add the strings under the path shown below.

Oracle 10g’s key under ORACLE changed from HOMEx (x=0, 1, etc.) to something similar to what is shown at the end of the path shown above. The end result (in the registry) of the addition should look similar to the example below where I’ve changed my font to Courier New and the size to 16.

Here is a comparison of the banner in the before and after images.

Before you go hog wild and implement your favorite font of Comic Sans MS, be advised that not very many fonts will actually work. You’re limited to fixed-pitch TrueType fonts, and if you try to use a variable-pitch or incorrectly specified (typo or just not present), you’ll revert to the default font and size of Fixedsys 16. Aside from Courier New and Fixedsys, other fixed pitch fonts you’re likely to have installed are Quest and Lucida Console.

This modification to the application won’t work if you’re using iSQL*Plus. I’m not mentioning the Web-based version just because it has “SQL*Plus” in its name. I’m mentioning iSQL*Plus because Oracle states the “Windows Graphical User Interface will be obsoleted in favor of the iSQL*Plus browser-based user interface in a future release of SQL*Plus.” It’ll be interesting to see if that comes to pass and how the user community will adapt or react to what can best be described as a paradigm shift in how millions of users interface with the Oracle RDBMS. Personally, I’d rather see the “Windows Graphical User Interface” become more like SQL Server’s Query Analyzer/Management Studio interface.

Environment Variables

In the prior section, I mentioned that the string values were like environment variables. If they are environment variables, then we should be able to detect or read them from the system. One package Oracle makes available for doing just that is the DBMS_SYSTEM package. This package is one of those under the hood, so to speak, but not really documented built-ins. The documentation (at least as far back as Oracle8i, for what’s posted at OTN) references it, but never specifies exactly what it does and how you’re supposed to use all of the procedures.

The package used to be created via the dbmsutil.sql script, but was moved to a wrapped package named prvtutil.plb (not prvtutil.sql mentioned in code comments). The comment about the package being moved is without doubt one of the most irony laden comments in all of Oracle. The stated reason for moving the code? It was “moved to prvtutil.sql for more obscurity.” That’s an excellent reason because as it was, the package wasn’t quite obscure enough. Let’s call that one “Mission accomplished” because it is definitely more obscure now.

The “looks like Oracle documentation” (the author did a good job) at http://orafaq.com/papers/dbms covers the GET_ENV procedure and points out the difference between what is returned when on Windows versus UNIX. A user or system setting for PATH, for example, won’t appear in GET_ENV. It’s not entirely true that only key settings are accessible. On my system, PERL5LIB is not set in the registry under ORACLE, but is a system environment variable. If I query for its value in the package, it will display. CLASSPATH and TEMP are other examples of system variables that display but are not in the registry key under ORACLE.

You may have to experiment with which system/user and ORACLE key variables GET_ENV will return for you, but once you do know what’s returned, bundle it up in your own procedure, and you’ll have a handy tool to output values that would otherwise take extra steps/clicks in several interfaces, tools, or procedure calls to obtain. Add in SYS_CONTEXT to get what DBMS_SYSTEM doesn’t or won’t cover.

SQL> create or replace procedure GET_ENV AS
  2   RetVal VARCHAR2(4000);
  3   RetNum number;
  4  BEGIN
  5    dbms_output.put_line(chr(10));
  6    dbms_system.get_env('ORACLE_SID', RetVal);
  7    dbms_output.put_line('ORACLE_SID is '||RetVal);
  8    dbms_system.get_env('ORACLE_HOME', RetVal);
  9    dbms_output.put_line('ORACLE_HOME is '||RetVal);
 10    dbms_system.get_env('NLS_LANG', RetVal);
 11    dbms_output.put_line('NLS_LANG is '||RetVal);
 12    dbms_system.get_env('CLASSPATH', RetVal);
 13    dbms_output.put_line('CLASSPATH is '||RetVal);
 14    dbms_system.get_env('ORACLE_HOME_KEY', RetVal);
 15    dbms_output.put_line('ORACLE_HOME_KEY is '||RetVal);
 16    dbms_system.get_env('TEMP', RetVal);
 17    dbms_output.put_line('TEMP is '||RetVal);
 18  END;
 19  /

Procedure created.

SQL> exec get_env;

ORACLE_SID is db10
ORACLE_HOME is C:\oracle\product\10.2.0\db_1
NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252
CLASSPATH is .;C:\Program Files\Java\jre1.5.0_11\lib\ext\QTJava.zip
ORACLE_HOME_KEY is SOFTWARE\ORACLE\KEY_OraDb10g_home1
TEMP is C:\WINDOWS\TEMP

PL/SQL procedure successfully completed.

What was that error message?

Oracle provides a command to output the cause and action for ORA (and many other) errors. The oerr command or utility is like DBMS_SYSTEM in some respects in that the documentation references it, but never outright describes what it does, and more importantly, what its limitations are.

user@db01-01.xyz.net:{23} oerr
Usage: oerr facility error

Facility is identified by the prefix string in the error message.
For example, if you get ORA-7300, "ora" is the facility and "7300"
is the error.  So you should type "oerr ora 7300".

If you get LCD-111, type "oerr lcd 111", and so on.
user@db01-01.xyz.net:{24} oerr ora 942
00942, 00000, "table or view does not exist"
// *Cause:
// *Action:

Okay, so not every error message has useful information associated with it, but that can be fixed on UNIX systems. Just edit the message library file and add in whatever you want (and keeping track of version changes in new releases so your comments aren’t lost, but that’s a separate issue).

How does oerr work on Windows? Answer: not at all because Oracle on Windows does not come with an equivalent counterpart to oerr on UNIX. If you’re clever enough, whip out some Java or Perl and make your own file parser/display utility (Google oerr and take your pick).

If you’re programming (or time) challenged, here’s a link to a freeware tool that is actually quite sharp: http://tham.eclub.lv/?My_Software:Oracle_oEMV:Download. It’s not 100% functional in that ORA-00942, as an example, does not appear when version 10.2 is selected, but does appear when 9.2 is the selected version.

The number of error prefixes in this version is 57, and there will be prefixes you’ve never seen before. You have to alter the version to get some of them to display, but overall, it’s a handy tool. Overall, it’s infinitely better than what Oracle provides on Windows for oerr.

In Closing

The tips for modifying SQL*Plus and outputting environment variable information are easy to implement. There’s always the general caution or prohibition regarding editing the registry to consider, but really, if editing it were that risky, I think Microsoft would have made it significantly harder to access - almost like the way Oracle makes something more obscure (“You can still use it, but we’ll just make it harder to find” type of mentality). If you’re using XP, you can always reboot in safe mode and go back to a restore point, or just export the key before starting. In any event, the examples shown here demonstrate that there are still lots of ways to make Oracle better and easier to use.

» See All Articles by Columnist Steve Callan