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.
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:
___________________________________________________________
login.sql
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.sql
connect &1
@login.sql
___________________________________________________________
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 8.0.6.0.0 - 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 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
APPS@ORCL> @connect system
Enter password:
Connected.
SYSTEM@ORCL> sho user
USER is "SYSTEM"
SYSTEM@ORCL> conn apps
Enter password:
Connected.
SYSTEM@ORCL> sho user
USER is "APPS"
___________________________________________________________
Comments