Re: Dynamically assigned SQL*Plus command prompt

From: Kaboel Karso <karso_at_kpn.com>
Date: 12 Mar 1999 12:58:24 GMT
Message-ID: <01be6c87$d7483150$ce1e1dac@ut9811252159>

Hi,

Could be complexer than that. Suppose you have different databases and you don't even know where you logged on. Or better yet, you started different SQL*Plus sessions on the same db and you have to identify what the actual session is. There is a sample below. Of course you can make it simpler.

Cheers
KKO

---

set termout off;
set echo off;
column c2 noprint new_value PID;
set verify off;
set head off;
set feedback off;
set termout off;
set pagesize 0;

select NVL(sid||'.'||serial#||'.'||audsid,'0') c2 from v$session where audsid=userenv('sessionid');

spool /tmp/sqlstr.sql;

SELECT 'SET SQLPROMPT '||chr(39)|| USER ||'~'||'&&PID@'|| lower(name) ||' \> '||chr(39)
FROM v$database;
/
spool off;

@/tmp/sqlstr.sql
/
undef 1
undef 2

set head on;
set feedback on;
set termout on;
set echo off;
set pagesize 24;
!rm /tmp/sqlstr.sql
!clear
define _editor=vi

---

XU <xux_at_informa.bio.caltech.edu> schreef in artikel <7c6f5q$7k1_at_gap.cco.caltech.edu>...
> [ Article crossposted from comp.databases.oracle.misc ]
> [ Author was XU ]
> [ Posted on 10 Mar 1999 18:53:55 GMT ]

> 

> [ 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 Fri Mar 12 1999 - 06:58:24 CST