SQL Plus prompt

Add the following lines to your login.sql file (which must be present on the ORACLE_PATH variable). (Thanks to suggestion from a reader Hans, this has been updated to support the nologin clause)


define gname = 'not connected'
column global_name new_value gname
set termout off
select lower(user) || '@' ||
replace(global_name,'.WORLD',null) global_name
from global_name;
set termout on
set sqlprompt 'SQL &&gname> '

This will only work when you login, so if you're the kind of person that issues "conn" or "connect" within your SQL Plus session, create two files


conn.sql
-----------
connect &1
@login


connect.sql
-----------
connect &1
@login

Then use

SQL> @conn newuser/newpass