Thanks for responding Sameer.

Yes you are absolutly right - 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) ....

Say 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 DPS database@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