sql prompt message

Move this to the appropriate forum if necessary.

Sambavan posted a question earlier about firing a message to the users screen when they login in. (http://www.dbasupport.com/forums/showthread.php?threadid=16324)

Here is a piece of code I added to my glogin.sql. I frequently logon to multiple databases on multiple machines. I was having a hard time keeping track of which username/instance/machine I was connected to. (Multiple windows all with the SQL>). This piece of code will automatically put the username followed by the machine I connected to as the sql prompt.

set termout off
spool set_prompt.sql
select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.sid = (select sid from v$session_connect_info);
spool off
start set_prompt.sql
set termout on

Hope you like it. Hope it helps.

Thanx for the posting. Yeah, I have something similar to this one.

Sam

Quote:

---

Originally posted by ocp2b

set termout off
spool set_prompt.sql
select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.sid = (select sid from v$session_connect_info);
spool off
start set_prompt.sql
set termout on

---

I have Just Joined this forum today and find for the GLOGIN.SQL
and find this thread
and when i issue these statments then it gives me error

and a.sid = (select sid from v$session_connect_info)
*
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row


Will be thank full for Helping

Jus issue the "select sid from v$session_connect_info" command and look at the result. The result should be 1 row. The error states there are more rows returned.

I guess, when you'll issue the statement, you'll understand the problem & the solution.

For SID u can use global variable (9i)
set sqlprompt '&_connect_identifier'

Quote:

---

Originally posted by efrijters
Jus issue the "select sid from v$session_connect_info" command and look at the result. The result should be 1 row. The error states there are more rows returned.

I guess, when you'll issue the statement, you'll understand the problem & the solution.

---

I have already issued this statment in SQL. then its return more then 80 rows. actually it is NETWORK Database so there are too many users are connected at one TIME. (i'm using Oracle 8i)

hi,

i am also getting similar error.

my "select sid from v$session_connect_info" gets me 790 rows.

what do i do?


with thanks


Raja

DUH! Use distinct clause in sub query

select distinct sid from v$session_connect_info

This thread is getting painful to watch . . . try this:

Code:

---

select 'set sqlprompt'||' "' || a.username ||'-'|| b.terminal ||'> "'
from v$session a, v$process b
where a.paddr = b.addr
and a.audsid = (SELECT USERENV('SESSIONID') FROM Sys.Dual)

---

Code:

---


set line 130
set serverout on
set pagesize 40
set termout off
col plan_plus_exp for a96

column gname new_value gname
variable server varchar2(64)

declare
  l_role varchar2(36);
  l_user varchar2(30) := user;
begin
  begin
  select role
    into l_role
    from session_roles
  where role='DBA';
  exception
  when no_data_found then null;
  end;
  if l_role = 'DBA' or l_user = 'SYS'
  then
    select lower(user) || '@' || instance_name || '-' || upper(host_name)
      into :server
      from v$instance;
  else
    select lower(user) || '@' || substr(global_name, 1, decode(instr(global_name, '.', -1, 1), 0, length(global_name) + 1,
          instr(global_name, '.', -1, 1) ) - 1)
      into :server 
      from global_name;
  end if;
end;
/

select :server gname
  from dual;

set sqlprompt '&gname>'


alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

set termout on

---