Setting sql prompt with service name???

[ronnie]

Hi,

How can i set my sql prompt to show me the service I am looged into.

Thanks

[marist89]

Put this in your login.sql

set termout off
col user_name new_value user_name
col db_name new_value db_name
select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
set sqlprompt "&user_name@&db_name> "
set termout on
set trimspool on
set trimout on
set serveroutput on size 1000000
set timing on

[ronnie]

Originally posted by marist89
Put this in your login.sql

set termout off
col user_name new_value user_name
col db_name new_value db_name
select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
set sqlprompt "&user_name@&db_name> "
set termout on
set trimspool on
set trimout on
set serveroutput on size 1000000
set timing on

This shows me the sid I am connected to.

Is there a way i can see the service i am conected to.

I have exact same databases on 2 machines. One is for testing/Development and the other one is production.

Test/Development is called louise_company and the production one is called company.

Here is what it shows me

company@sid7.herold.com> connect company/company@louise_company
Connected.
company@sid7.herold.com> connect company/company@company
Connected.
company@sid7.herold.com>

As you can see it shows me the same promt for both the connections.

[thanigai]

How about this...
P1=`/usr/xpg4/bin/id -u -n`
P2=`hostname`
PERM=$P1@$P2
PS1='$PERM@$PWD
$'; export PS1

Thanks.
Thanigaivasan.

[ronnie]

Originally posted by thanigai
How about this...
P1=`/usr/xpg4/bin/id -u -n`
P2=`hostname`
PERM=$P1@$P2
PS1='$PERM@$PWD
$'; export PS1

Thanks.
Thanigaivasan.

Isnt that for unix....

I am on windows 2000

[thanigai]

I am sorry..
...I did not get a chance to work on windows platform...
Thanks.
Thanigaivasan.

[ocp2b]

ronnie, this is what I did to get the username and machine name I connected to.

Add this to the glogin.sql file located in sqlplus/admin:

set termout off
spool set_prompt.sql
select distinct 'set sqlprompt '||user ||'-'|| b.terminal ||'> '
from v$process b, dual
where b.terminal is not null;
spool off
start set_prompt.sql
set termout on


I also created this file that goes in you Bin directory:
(I named this file CONNECT.SQL)

set termout off
CONNECT &1
start c:\glogin.sql


What happens is then you select sqlplus, your prompt will look like this:

SYS-ICEMAN> (I logged in as internal on the a machine named Iceman)

Now if I want to connect to a different machine, I type
@connect username/password@machine_name

and I will get a new sql prompt showing the username and new machine name I just connected to.

example:

SYS-ICEMAN>@connect scott/tiger@maverick
SCOTT-MAVERICK>

[oravijay]

Hi,

Try to create your network service name based on your instance name/database name.
That will solve your purpose.

Vijay.

[ronnie]

Originally posted by ocp2b
ronnie, this is what I did to get the username and machine name I connected to.

Add this to the glogin.sql file located in sqlplus/admin:

set termout off
spool set_prompt.sql
select distinct 'set sqlprompt '||user ||'-'|| b.terminal ||'> '
from v$process b, dual
where b.terminal is not null;
spool off
start set_prompt.sql
set termout on


I also created this file that goes in you Bin directory:
(I named this file CONNECT.SQL)

set termout off
CONNECT &1
start c:\glogin.sql


What happens is then you select sqlplus, your prompt will look like this:

SYS-ICEMAN> (I logged in as internal on the a machine named Iceman)

Now if I want to connect to a different machine, I type
@connect username/password@machine_name

and I will get a new sql prompt showing the username and new machine name I just connected to.

example:

SYS-ICEMAN>@connect scott/tiger@maverick
SCOTT-MAVERICK>

Hi,

This is what I wanted , but theres a problem here

If from inside a sqlplus session i try to change my connection the prompt does not change.

I created the connect.sql also.

This is whats happenning

COMPANY-THELMA>connect company/company@louise_company
Connected.
COMPANY-THELMA>

It should show

COMPANY_LOUISE> as the promt now

[ronnie]

Originally posted by marist89
Put this in your login.sql

set termout off
col user_name new_value user_name
col db_name new_value db_name
select lower(user) user_name, ltrim(rtrim(lower(global_name))) db_name from global_name;
set sqlprompt "&user_name@&db_name> "
set termout on
set trimspool on
set trimout on
set serveroutput on size 1000000
set timing on

In this also the sid does not change if i try to connect to another database from withing a sqlplus session

company@sid7.herold.com> connect www_dba/biteme2@brutweb
Connected.
company@sid7.herold.com>