Find us on Google+ Google+

August 9, 2010

Setting SQL prompt in Oracle

The default prompt in SQL*Plus is SQL>, does not provide any information like who the user is and what the user is connected as. Prior to Oracle9i, we have to do elaborate coding to get the information as the SQL prompt, from Oracle 9.2.0 we can use SET SQLPROMPT along with SQL*Plus predefined variables.

Whenever SQL*PLUS starts up, it looks for a file named glogin.sql under the directory $ORACLE_HOME/sqlplus/admin. If the file is found, it is read and the containing statements executed. This allows to store settings across SQL*PLUS sessions. From Oracle 10g, after reading glogin.sql, SQL*PLUS also looks for a file named login.sql, in the directory from where SQL*PLUS was and in the directory that the environment variable SQLPATH points to, and reads it and executes it. Settings from the login.sql take precedence over settings from glogin.sql.

In Oracle9i, whenever a user connects through SQL*PLUS, Oracle will execute only glogin.sql, from 10g Oracle will execute login.sql as well. From Oracle 10g, the login.sql file is not only executed at SQL*Plus startup time, but also at connect time as well. So SQL prompt will be changed after connect command.

To set SQL prompt permanently, update $ORACLE_HOME/sqlplus/admin/glogin.sql or login.sql


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

_connect_identifierwill display connection identifier.
_datewill display date.
_editorwill display editor name used by the EDIT command.
_o_versionwill display Oracle version.
_o_releasewill display Oracle release.
_privilegewill display privilege such as SYSDBA, SYSOPER, SYSASM
_sqlplus_releasewill display SQL*PLUS release.
_userwill 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.


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


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


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>"
SQL> set sqlprompt "_user'@'_connect_identifier>"
The SQL*Plus prompt will shows

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>"

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>

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

The SQL*Plus prompt will shows

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 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options>

The variable _O_RELEASE contains a string representation of the Oracle database version number. If Oracle database version is 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


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

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

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

Source: Internet