DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: sql prompt message

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    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.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx for the posting. Yeah, I have something similar to this one.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2003
    Location
    Pakistan
    Posts
    3
    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
    Last edited by KashifDBA; 12-19-2003 at 08:26 AM.

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    For SID u can use global variable (9i)
    set sqlprompt '&_connect_identifier'

  6. #6
    Join Date
    Dec 2003
    Location
    Pakistan
    Posts
    3
    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)
    Last edited by KashifDBA; 12-20-2003 at 04:46 AM.

  7. #7
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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

  8. #8
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    DUH! Use distinct clause in sub query

    select distinct sid from v$session_connect_info

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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)

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width