Author's name: Connor McDonald
Author's Email: email@example.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 ?
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
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
which will change your connection and then re-issue the prompt setting commands
scott@demo> @conn newuser/newpass
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