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.
