How to Set SQL Prompt in Oracle? (SCOTT@ORCL>)

How to Set SQL Prompt in Oracle? (SCOTT@ORCL>)

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • Blog
  • 2 mins read

In this tutorial, I am giving an example to how to set SQL prompt in the USER@DATABASE> format in Oracle.

Set SQL Prompt in Oracle Example

After logging into the SQL*PLUS, give the following command to set the SQL prompt as user@database>:

set sqlprompt '&_user.@&_connect_identifier.> '

Suppose you logged in as user SCOTT and Oracle database name is ORCL then the prompt would be SCOTT@orcl> as shown in the below image:

SQL Prompt Example

But I understand that it is hard to remember the syntax of the command and to give this command every time you log in to SQL*Plus. So here is how you can set this setting permanently for SQL*Plus.

Whenever SQL*Plus starts, it looks for the glogin.sql file for the custom settings. The location of the glogin.sql file is ORACLE_HOME/sqlplus/admin, for example: F:\app\Administrator\product\11.2.0\dbhome_1\sqlplus\admin. To set the SQL prompt, open the glogin.sql file from this folder and paste this command (SET SQLPROMPT '&_user.@&_connect_identifier.> ') into the file and save it.

Now if you open the SQL*Plus and after logging in you will see the custom prompt.

Note: Maybe you have installed multiple Oracle homes and when you will run the SQL*Plus from the command line by typing SQLPLUS in CMD then maybe it will run from the location set in the PATH variable in the Windows. So make sure that you are running the SQL*Plus from the Oracle home where you configured its prompt setting.

See also: