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

The Oracle (tm) Users' Co-Operative FAQ

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

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