Wednesday, June 19, 2013

Quick Tip: Oracle Sqlplus - changing prompt

Oracle SQL*Plus has several settings that can be customized using SET command. When you run SQL*Plus, typically the following (boring?) prompt greets you.

SQL>

If you ever want to change this to something more interesting, there is a SET option for that.

SET sqlprompt 'sqlplus:&_user@&_connect_identifier > '

This will be shown as,

sqlplus:<user>@<SID>>

For e.g.,

sqlplus:scott@hr>

Where scott is a user id logged into hr database.

References

http://docs.oracle.com/cd/B28359_01/server.111/b31189/ch12040.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm#i2675128