Pando,

This is getting a bit confusing - maybe we 're on two wave-lengths.

Firstly, I operate in an NT env not UNIX.

Secondly, I 'cannot' modify the glogin (or login.sql) file as defined under the SQLPATH.

From what I gethered looking at your suggestion, you modified you login.sql to include -

column sqlprompt new_value prompt
column uname new_value username

select user||userenv('sessionid') uname
from dual;

select lower(user)||'@'||global_name||'>' sqlprompt
from global_name;

set editfile /tmp/&username.buf
set sqlprompt &prompt

What I have done is, I have a desktop Icon, in it's Target section I have I:\ORANT\BIN\PLUS33W.EXE DPSdatabase_cc/DPSdatabase_cc@dpstest @env.sql (the equivalent of the login.sql if you like)

This env.sql is in my C:\user\sql\dpstest directory as defined under Start section in for the icon

This env.sql contains the following -
.....
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

Then from your suggestion I understand that you have another sql file - conn.sql? In it you have defined

connect &1

undefine &1

@conn.sql

you call it at the prompt using @conn username?????


Now the sequence of events is as follows -

1) Launch desktop icon (one mentioned above)
RESULT: I get a prompt DPSdatabase_cc@dpstest>
i.e. the test database
2) At the prompt type the following -
DPSdata
conn DPSdatabase_cc/DPSdatabase_cc@dpslive
RESULT:
Enter password: ********
Connected.
DPSdatabase_cc@dpstest>

although global_name does come up as dpslive

3) At the 'DPSdatabase_cc@dpstest>' prompt for a new sqlplus session type conn DPSdatabase_nms@dpslive
RESULT: Enter password: ********
Connected.
DPSdatabase_cc@dpstest>
although global_name does come up as dpslive

4) If I change the contents in my env.sql to that of your suggestion the after launching the icon I get -
a blinkin' cursor, pressing enter gives me 'SQL>'
althought he global_name is in fact dpstest which is correct

5) Then at this prompt call the conn.sql
RESULT:

ERROR: ORA-03121: no interface driver connected - function not performed


Warning: You are no longer connected to ORACLE.

So how can I switch from test to live and vica-versa and get the sqlprompt to reflect the instance??

In my case for the test database I have the DPSdatabase_cc and DPSdatabase_nms and likewise on live.