Fixing a little problem in the login.sql script found in Tom Kyte's book

From: FC <flavio_at_tin.it>
Date: Tue, 14 Jan 2003 14:19:41 GMT
Message-ID: <10VU9.28651$0v.828646@news1.tin.it>

Hello folks,
while playing around with the login.sql script found in the excellent book of Tom Kyte, "Oracle one-on-one".
 I managed to fix its behaviour if you happen to start SQL*Plus with the /NOLOG switch.

I don't know if anyone else ever had a similar problem, I searched Tom's site but I couldn't find anything, there are only versions of the script modified to display various prompts.

Tested on Oracle EE 8.1.7.0.0 is running on Win2000.

The original script is as follows (or with a slightly different decode clause):

---

...
column global_name new_value gname
set termout off
select lower(user) || '@' || decode(global_name, 'ORACLE8.WORLD', '8.0',

'ORA8I.WORLD', '8i',

'ORA8IDEV.WORLD', 'dev8i',

global_name) global_name
  from global_name;
set termout on
set sqlprompt '&gname> '
...

---

The "improved" version is:

...
set termout off
# begin workaround
define gname = disconnected
column global_name new_value gname
whenever sqlerror continue none
# end workaround
select lower(user) || '@' || decode(global_name, 'ORACLE8.WORLD', '8.0',
'ORA8I.WORLD', '8i',
'ORA8IDEV.WORLD', 'dev8i',

global_name) global_name

   from global_name;
set termout on
set sqlprompt '&gname> '
...

When you start SQL*Plus with /NOLOG, the original script hangs until you press CTRL-C because there is no connection to the DB and the SELECT statement fails. By adding the three lines above, the failure of the SQL statement is trapped and the value "disconnected" is retained by the gname substitution variable, so that you get a valid prompt informing you that you are not (yet) connected.
Of course the script still works as before for a normal connection.

Further on the login.sql subject, the setting of SQLPATH environment variable in windows environments would probably need some more detailed explanation.
SQLPATH is a registry key created upon Oracle installation and its default value is

%ORACLE_HOME%\dbs

this setting applies to *any* user working on the same machine.

In order to allow more users to customise the login.sql script, each user can create a SQLPATH environment variable containing something like:

%USERPROFILE%\My Documents\Scripts

or any other arbitrary path and place his/her login.sql right there.

Note also that the registry entry has not been touched, the setting can be done from the advanced setting tab of "My Computer".

It is also interesting to know that if you want to pick up a special version of login.sql, you can do as follows:
1. open a command prompt window
2. SET SQLPATH=<some-other-path>

this statement overrides the current value of SQLPATH (both the registry entry and the environment variable) for the remainder of the session.

Bye,
Flavio Received on Tue Jan 14 2003 - 08:19:41 CST