SQLPLUS Logon Question Please

[dps]

I currenty have a script saved in my sql directory

prompt Note - you are logged on as DPS
set linesize 1000
set pagesize 40
set sqlp 'DPSdatabase> '
set serveroutput on
set trims on
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

In the icon I have setup in desktop I have the command line pointing to this sql script.

The issue is that whilst in my sqlplus session with my prompt as
DPSdatabase>

if I choose to connect to another database, my prompt remains as DPSdatabase>

How can I get around this???

 

[irvina]

Try getting the user and host name (or instance_name) from the db and make a prompt with the information. Therefore, same script, different prompt.

column uname new_value un

select User || '@'|| upper(host_name) uname
from v$instance;

set sqlprompt "&un.> "

 

[dps]

Thansk for the response Irvina.

I think I may have confused you a bit?

when I connect to a different instance ( say for example there are two instances - DPS database_cc (employee system) and DPSdatabase_nms (departments system). These can be either in Test database or the Live database - ie. dpsTest or dpsLive) ....

I logon - via Icon sitting on desktop - which points to the script which logs me in to the DPS database_cc, so the prompt in sqlplus session will be say DPSdatabase_cc@Test>

Now I connect as DPSdatabase_nms/passwd@dpsTest..

.....I want the sql prompt to show this and perhaps even throw in a message saying : "you are now connected to DPSdatabase_nms"

I actually am using the following script - so disregard the above

column global_name new_value gname
set termout off


select lower(user) || '@' ||
decode(global_name, 'dpsTest.WORLD', 'DPSdatabase_cc', 'dpsLive.WORLD',
'DPSdatabase_cc', global_name) global_name
from global_name;
set sqlprompt '&gname> '
set termout on

set underline off

What can you or anyone suggest please