Customize your SQL*Plus prompt

Sunday May 18, 2003 | | Comments (1)
Guilduss prompted (heh) me to look into customizing my login prompt. Here's how. In $ORACLE_HOME/sqlplus/admin, edit/create a file called login.sql. Add this to it (hacking to fit your needs). It'll run when sqlplus starts, additionally, you can run it from the SQL> prompt with '@login.sql'.


define gname = 'not connected'
column global_name new_value gname
set termout off
SELECT
lower(user) || '@' || v$instance.HOST_NAME || ':' || v$instance.instance_name global_name
FROM
v$instance, global_name;
set termout on
set sqlprompt '&&gname> '
This creates a prompt something like 'jim@dbhost:practice>'. It gives user@host:instance . Very much like my bash prompt 'oracle@styx:/oracle/product/8.1.7/sqlplus/admin$' which is PS1=u@h:w$.

Categories:

1 Comments

By jim on July 28, 2003 2:54 PM

The above only works if you grant the user in question select privileges to v$instance which is disabled by default. I granted the select privelege to my 'connect' role to that all connecting users can tweak their sqlprompt like I showed. Here's how I'm doing it now.

define gname = 'not connected'
column global_name new_value gname
set termout off
SELECT
v$instance.HOST_NAME || '.' || v$instance.instance_name || '.' || lower(user) global_name 
FROM
v$instance, global_name;
set termout on
set sqlprompt '&&gname> '