SQLPLUS Logon Question

[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???

[Sameer]

Hi

"sqlp" is nothing but just to set the **prompt**.. Even though you change the database connection without closing SQL*Plus sesion it will remain same for the different database...

I think you are looking for dynamic prompt which will tell yu to which database you are connected? Is this what u want to do?

Sameer

[dps]

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

[dave777]

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

SQL> @db

to update my SQL prompt

regards.

[dps]

........Dave I do not have privleges to modify this sql file.

Any other suggestions how I can dynamically achieve my objective?????????

[pando]

oracle@simp:~> echo $SQLPATH
/opt/oracle/scripts/DBA
oracle@simp:~> cd $SQLPATH
oracle@simp:~/scripts/DBA> cat login.sql
set linesize 140
set pagesize 20
set termout off
set feed off
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

set termout on
set feed on


oracle@simp:~/scripts/DBA> sqlplus internal

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jul 26 09:31:03 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

sys@SIMP>

[dps]

Pando,

Thanks for the response, but........

you did not include in your output the bit where you then re-connect to another instance. In doing so this would be reflected by your sqlprompt.

[pando]

cat conn.sql

connect &1
undefine &1
@login.sql


sys@SIMP> @conn.sql system

system@SIMP>

[dps]

Unfortunately the login script when used by my desktop icon takes while to work and then starts up with the SQL>

your second peice prompts me for a value but then disconnects from oracle.....

The script which I have shown above - the one which is currently picked up by my sqlplus desktop icon - does the job I want so far as in that the prompt tells me which dbase eg.

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>" ****

Hope this clearifies it

Thanks in advance

[Sameer]

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]