Dynamically assigned SQL*Plus command prompt

From: XU <xux_at_informa.bio.caltech.edu>
Date: 10 Mar 1999 18:53:55 GMT
Message-ID: <7c6f43$7k1@gap.cco.caltech.edu>

[ Article crossposted from comp.databases.oracle.tools ]
[ Author was XU ]
[ Posted on 10 Mar 1999 18:52:02 GMT ]

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 Received on Wed Mar 10 1999 - 12:53:55 CST