Originally posted by dps

dps_cc@test_dbase>
Above the uname would be dps_cc and passwd dps_cc

then I do the following : -

dps_cc@test_dbase> connect dps_nms@live_dbase
Enter password: dps_nms
Connected.
dps_cc@test_dbase>
*****the above prompt should now be dps_nms@live_dbase> not "dps_cc@test_dbase>" ****
If you change the database connection in the same SQL*Plus session (WITHOUT CLOSING SQL*PLUS AND SWITCH TO NEW DATABASE USING connect) as you are doing above, then setting of dynamic prompt will be defficult..

execute your .SQL file again to set the prompt .. which will again execute sql statement to get the current instance name for the database ....

otherwise on new connection the prompt will remain same . i.e instance name of old database...

Don't hardcode username/password in login sql file..

Pass following sql file to SQL*Plus .. SQL*Plus will prompt you for username and password.. but after login it will set the prompt.

login.sql
-----------------------------------------------------
SET TERMOUT OFF
COLUMN X NEW_VALUE Y
SELECT global_name X FROM global_name;
SET SQLPROMPT '&Y SQL>'
SET TERMOUT ON
-----------------------------------------------------

To connect to new database close the SQL*Plus and open it again.. Now supply username and password for live database... and now it will run this sql file again and will set the promt for live database.


Sameer

[Edited by Sameer on 07-26-2002 at 09:23 AM]