Wednesday, October 15, 2008

Customising SQL Prompt in pre-10g Oracle Homes

I always say that the risk of running a script in an instance wherein it is not meant to be run, is always high, if the SQL prompt in SQL*PLUS is unchanged i.e., if it is retained as "SQL>". This risk can be mitigated, to a great extent, if not fully, by customising the SQL prompt to something like 'USER@SID>'.

This can be achieved in Oracle 10g quite easily
(refer my earlier post).

Releases prior to Oracle 10g did not have the special pre-defined variable "_USER", which can be set in glogin.sql. However, there exist a lot of workarounds for customising the SQL Prompt and retaining it!

I have detailed below one such workaround.

1. Modify the .profile of each user and add the environment variable SQLPATH to it so that our scripts can be accessed from anywhere.

e.g: export SQLPATH=/u019/app/oracle/govtt/govttora/8.0.6/sqlplus/admin;/u018/app/oracle/govtt/govttdb/9.2.0/sqlplus/admin

2. Create two files login.sql and connect.sql under $ORACLE_HOME/sqlplus/admin or under any directory that is defined in the SQLPATH, with the following contents:



col prom new_value prom
set termout off
define prom='NOT CONNECTED'
select upper(sys_context('USERENV','SESSION_USER'))|| '@'|| upper(sys_context('USERENV','DB_NAME')) prom

from dual;
set sqlprompt "&prom> "
set termout on


connect &1


3. Whenever one wants to switch between users, use the command @connect instead of connect. If we do not use @connect, then the new user would not be displayed in the sqlprompt. This is as illustrated below:


$ sqlplus apps

SQL*Plus: Release - Production on Tue Oct 14 07:35:38 2008

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

APPS@ORCL> @connect system

Enter password:

SYSTEM@ORCL> sho user


SYSTEM@ORCL> conn apps

Enter password:

SYSTEM@ORCL> sho user



No comments: