SQLPLUS Logon Question

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

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

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

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.

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

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

Code:

---


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>

---

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.

Code:

---


cat conn.sql

connect &1
undefine &1
@login.sql


sys@SIMP> @conn.sql system

system@SIMP>

---

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

Quote:

---

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]

Quote:

---

Originally posted by 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

---

read the post, I put @conn USERNAME@TNS not username/password@tns

look conn.sql

have you noticed I have set a SQLPATH variable... do you have that?

Thanks Sameer.

Ok so it seems i'll have to admit defeat.

I'll just have to accept that having so many instances can be handled by above script(s) but there is a limit to how much a requirement such as mine can be met.

Thanks

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.