Changing the prompt dynamically in SQL*Plus Oracle Headquarters Redwood Shores1

Changing the prompt dynamically in SQL*Plus

This is just a simple trick which is not well-known but makes life easier when using the good ol’ SQL*Plus commandprompt (and many people still do!).

If you set the SQLPROMPT environment variabele (either manually or in the login.sql)  the standard prompt is replaced by the name of the instance:

SQL> set sqlprompt "_CONNECT_IDENTIFIER>"

I use this in combination with the login.sql. I’m not sure if this was a feature before Oracle 10g, but recently I noticed that the login.sql is executed after every login, and not only when you start a new prompt. And that means that everytime you change your connection, the sqlprompt also changes according to the value of _CONNECT_IDENTIFIER. This probably will prevent many mistakes because you’re typing update-statements in the "wrong" environment!

These substitution variables are available from SQL*Plus 9.2 onwards. Other values you can use for sqlprompt are _USER, _DATE, _O_VERSION and _O_RELEASE. They speak for themselves I guess!

mydb> set sqlprompt "_USER _CONNECT_IDENTIFIER> "
SCOTT mydb>



  1. Marco Gralike November 30, 2006
  2. Marco Gralike November 30, 2006
  3. Marco Gralike November 30, 2006
  4. Nigel Thomas November 29, 2006
  5. Patrick Sinke November 28, 2006
  6. Arnoud Roth November 28, 2006