The Oracle (tm) Users' Co-Operative FAQ

How can I set the SQL*Plus prompt to something useful ?


Author's name: Connor McDonald

Author's Email: connor_mcdonald@yahoo.com

Date written: August 13, 2001

Update: Jan 23, 2006

Oracle version(s): 7.3 - 9.2, 10g

How can I set the SQL*Plus prompt to something useful ?

Back to index of questions


We've all been there - you've got 37 windows open on the screen, all of them running SQL Plus, all of them showing the innocuous "SQL>". It would be so much nicer if the prompt told us something meaningful.

Every time you start SQL Plus, Oracle searches for a file called "login.sql" anywhere along the path as defined by the ORACLE_PATH variable, and if not found, the glogin.sql file (the "global login file" which applies to all users by default).

The following entries in either of these files sets the prompt to "user@database>"

 
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 '&&gname> '

However, this is only run ONCE (when you login), so if you're the kind of person that issues "conn" or "connect" from within your SQL Plus session, you can then create two files

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

which will change your connection and then re-issue the prompt setting commands

 
scott@demo> @conn newuser/newpass
Connected.
newuser@demo>

Addendum (Jan 2006):

As from 10g, the login.sql and glogin.sql scripts are automatically executed by SQL*Plus every time you issue the connect command so the conn.sql and connect.sql scripts become redundant.


Further reading: N/A


Back to top

Back to index of questions