Today let me tell you about customization of SQL*Plus prompt. How we can use predefined variables to customize the SQL*Plus prompt so that we know where we are working. This information is important whether you are a DBA or a Developer.

Following are the two files which are available at $ORACLE_HOME/SQLPLUS/admin through which we can configure and change the SQL*Plus prompt:

1. GLOGIN.SQL
2. LOGIN.SQL

When SQL*Plus starts after completion of connection to database it first look for GLOGIN.SQL and then LOGIN.SQL to execute their content.

Prior to Oracle 10g, GLOGIN.SQL used to execute only once while opening the SQL*Plus and does not execute if you disconnect and reconnect to another database or user on same instance of SQL*Plus. But with Oracle 10g, it is executed every time a successful connection to a database is completed.

For example, the command below could be embedded in GLOGIN.SQL or LOGIN.SQL to display the account used to connect to the database:SET SQLPROMPT “_USER > ”

The below shows the results:

SYSTEM >

Currently there are following eight pre-defined variables which can be used to customize a user’s prompt:

_CONNECT_IDENTIFIER
Connection identifier used to make connection, where available.
_DATE
Current date, or a user defined fixed string.
_EDITOR
Specifies the editor used by the EDIT command.
_O_VERSION
Current version of the installed Oracle Database.
_O_RELEASE
Full release number of the installed Oracle Database.
_PRIVILEGE
Privilege level of the current connection.
_SQLPLUS_RELEASE
Full release number of installed SQL*Plus component.
_USER
User name used to make connection.