If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SQLPLUS Login..

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"

You can place your code in glogin.sql (in $ORACLE_HOME/plusXX), but this will only take care of your initial SQL*Plus login. It won't work with 'connect user/passwd'
I have another file 'db.sql' in SQLPATH so I can always run it as

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.

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.