Search This Blog

Wednesday, May 5, 2010

Change SQL Prompt in oracle SQL*Plus (10g and up)

Below is an example to change the SQL*Plus prompt (10g and up), simple and yet very useful.
SET SQLPROMPT command is used to change the default SQL> prompt

1) Display username

set sqlprompt '_user>' 
2) Display database name
set sqlprompt '_connect_identifier_privilege>' 

Step 2 and 3 can be combined together to display username and database name together.
3) Display username and database name (e.g. apps@dbname> )
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
4) To set the time at sqlprompt
set time on 

Now the best part is to avoid typing this command everytime you open a new SQL*Plus session, edit glogin.sql file located at $ORACLE_HOME/sqlplus/admin directory as follows.
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER _PRIVILEGE>"
set time on

Related Post: For Pre 10g Releases

1 Comment:

sap upgrade ecc 6.0 said...

This post tells you how to change SQL prompt. In this post they have given steps to perform the given task. This is useful coding which you can implement in your application. The code is explained with screenshot. Thanks.

Copyright (c) All rights reserved. Presented by Suresh Vaishya