Systems Engineering and RDBMS

SQL *PLUS prompt in Oracle

Posted by decipherinfosys on March 6, 2008

Whenever SQL*Plus is invoked, the glogin.sql file gets executed from $ORACLE_HOME/sqlplus/admin folder. After reading the glogin.sql file, SQL*Plus also looks for login.sql in the directory from which SQL*Plus is invoked. This file contains the variable which sets the SQL*Plus environment like page size, line size, editor, prompt etc.

Prior to Oracle 10g, the file login.sql gets executed only when SQL*Plus is launched. This was a problem when SQL*Plus prompt is set with user name. Here is an example to set the prompt as user name. We can also set it into glogin.sql or login.sql file.

SQL> connect scott/tiger@orcl

SQL> set sqlprompt “_user >”;

Immediately, the prompt will change to the user you are connected to. In our case it is the user scott. Now in Oracle 9i and earlier, if we connect to a different user, the prompt still shows the original user which was set when SQL*Plus was launched as login.sql did not get executed at connect time. So even though we are connected to the new user, SQL*Plus still displays that it is connected to previous user. In 10g, this is not the case.

SCOTT> CONNECT decipher/decipher@orcl

As you see, prompt now changed to the user we are connected to. This is because in Oracle 10g, login.sql gets executed at connect time so prompt always shows the current user you are connected to. This is very useful, when we are working with multiple databases and multiple users.

We can even set prompt to display user and database both.

DECIPHER> DECIPHER >set sqlprompt “_user’@’_Connect_identifier>”

Sorry, the comment form is closed at this time.

%d bloggers like this: