How to set prompt in SQL*PLUS?

From time to time, I always want to have the command prompt of SQL*Plus
behave like that in a UNIX login shell - dynamically customized to display
the current login username. It is useful to constantly remind a user who
he/she is (or poses as), especially when a person logs-in as many
different users at the same time (opens many SQL*Plus sessions).

I am proposing the following handy solution:

In the global SQL*Plus login script "glogin.sql", add these lines:

set echo off
set feedback off
set heading off
spool /tmp/userp.sql
select 'set sqlp "'||decode(user, 'SYS', 'SYS# "', 'SYSTEM', 'SYSTEM# "',
user||'> "') from dual;
spool off
start /tmp/userp.sql
set heading on
set feedback on
set echo on

Save the "glogin.sql" file and start SQL*Plus, and you will get the
command prompt "SYS# " when login as SYS, or the prompt "SCOTT> " when
login as SCOTT, for example. The "#" is there to remind you of the extra
power (and danger) when you login as SYS or SYSTEM (again, mimicking the
UNIX login shell prompt). To make it work on NT, just change the file/dir
naming convention (i.e. /tmp/userp.sql -> C:\Temp\userp.sql).

Hope the above is useful.

"Robert" Xuequn XU
Caltech

This looks great, but with the following remark. Once logged on as SCOTT and
within SQL*PLUS you type CONNECT <another user>, after logged on, the prompt
will still be SCOTT. Very dangerous.

Is there a solution for that ???

Hosted by www.Geocities.ws