Monday, February 25, 2008

Changing SQL prompt

Add the following to end of $ORACLE_HOME/sqlplus/admin/glogin.sql

set sqlprompt "_USER'@'_CONNECT_IDENTIFIER SQL>"

below shown code can also be added to get the same output and even run custom query at sqlplus login. As they rely on database tables cannot be used when database is down.

define _editor=vi
set termout off
column prompter new_value m_prompt

select sys_context('USERENV','CURRENT_USER') '@' sys_context('USERENV','DB_NAME')' SQL>' prompter from dual;

set sqlprompt '&m_prompt'
undefine m_prompt

set termout on