Setting SQL prompt in Oracle

Setting SQL prompt in Oracle

TEXT can be predefined substitution variables which are prefixed with an underscore.

_connect_identifier	will display connection identifier.
_date	will display date.
_editor	will display editor name used by the EDIT command.
_o_version	will display Oracle version.
_o_release	will display Oracle release.
_privilege	will display privilege such as SYSDBA, SYSOPER, SYSASM
_sqlplus_release	will display SQL*PLUS release.
_user	will display current user name.

The variable _CONNECT_IDENTIFIER was introduced in SQL*Plus 9.2 and _DATE, _PRIVILEGE and _USER were introduced in SQL*Plus 10.1.

_USER
The variable _USER contains the current user name given by SHOW USER. If SQL*Plus is not connected, the variable is defined as an empty string.

SQL> set sqlprompt "_user>"

The SQL*Plus prompt will shows
SYSTEM>
SATYA>

_PRIVILEGE
When SQL*Plus is connected as a privileged user the variable _PRIVILEGE contains the connection privilege "AS SYSBDA" or "AS SYSOPER" or "AS SYSASM". If SQL*Plus is connected as a normal user the variable is defined as an empty string.
SQL> set sqlprompt "_user _privilege>"

The SQL*Plus prompt will shows
SYS AS SYSDBA>
SATYA AS SYSOPER>
ASMADM AS SYSASM>

_CONNECT_IDENTIFIER
The variable _CONNECT_IDENTIFIER contains the connection identifier used to start SQL*Plus. For example, if the SQL*Plus connection string is "hr/my_password@MYSID" then the variable contains MYSID. If you use a complete Oracle Net connection string like "hr/my_password@(DESCRIPTION=(ADDRESS_LIST=...(SERVICE_NAME=MYSID.MYDOMAIN)))" then _CONNECT_IDENTIFIER will be set to MYSID. If the connect identifier is not explicitly specified then _CONNECT_IDENTIFIER contains the default connect identifier Oracle uses for connection. For example, on UNIX it will contain the value in the environment variable ORACLE_SID or TWO_TASK. If SQL*Plus is not connected then the variable is defined as an empty string.

SQL> set sqlprompt "&_user@&_connect_identifier>"
or
SQL> set sqlprompt "_user'@'_connect_identifier>"
The SQL*Plus prompt will shows
SYS@PROD>
SYSTEM@DWH>

_DATE
The variable _DATE can be either dynamic, showing the current date or it can be set to a fixed string. The date is formatted using the value of NLS_DATE_FORMAT and will show time information. By default a DEFINE or dereference using &_DATE will give the date at the time of use. _DATE can be UNDEFINED, or set to a fixed string with an explicit DEFINE command. Dynamic date behavior is re-enabled by defining _DATE to an empty string.If we want to display current date:
SQL> set sqlprompt "_user _privilege 'on' _date>"
SYS AS SYSDBA on 9-AUG-10>

If we want to display the current date & time:
SATYA on 9-AUG-10 at DEVDB> alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';
SATYA on 08/09/2010 13:03:51 at DEVDB>

_EDITOR
The variable _EDITOR contains the external text editor executable name.
set sqlprompt _editor>

The SQL*Plus prompt will shows
vi>

_O_VERSION
The variable _O_VERSION contains a text string showing the database version and available options.
set sqlprompt _o_version>

The SQL*Plus prompt will shows
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options>

_O_RELEASE
The variable _O_RELEASE contains a string representation of the Oracle database version number. If Oracle database version is 11.1.0.7.0 then the variable contains "1101000700". The Oracle version may be different from the SQL*Plus version if you use Oracle Net to connect to a remote database.

set sqlprompt _o_release>

The SQL*Plus prompt will shows (for 10.2.0.4.0)
1002000400>

_SQLPLUS_RELEASE
The variable _SQLPLUS_RELEASE contains the SQL*Plus version number in a similar format to _O_RELEASE.

set sqlprompt _sqlplus_release>

The SQL*Plus prompt will shows (for 10.2.0.4.0)
1002000400>

SQL> set sqlprompt "_user'@'_connect_identifier:SQL> "
SCOTT@DEVDB:SQL> connect system/manager
SYSTEM@DEVDB:SQL> disconnect
@:SQL> connect OEM/OEM@oemdb
OEM@OEMDB:SQL>

To reset to the default SQL prompt,
SQL> set sqlprompt 'SQL>'

Source: Internet