SYS_CONTEXT in a Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SYS_CONTEXT in a Procedure

  1. #1
    Join Date
    Apr 2001
    Posts
    54

    SYS_CONTEXT in a Procedure

    I can use the following SQL statement to get the OS_USER from Oracle.


    select sys_context('USERENV','OS_USER') from dual;


    However when I try to use this in a procedure and assign the value into a variable, no value is assigned.

    select sys_context('USERENV','OS_USER') into v_osuser from dual;

    My procedure fails as instead of assigning the OS_USER to v_osuer... os_user is NULL.

    Any ideas on why this isn't working? Is there a limitation on the INTO clause or since this is in a procedure?

    Thanks.
    -mcslain

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: SYS_CONTEXT in a Procedure

    Originally posted by mcslain
    My procedure fails as instead of assigning the OS_USER to v_osuer... os_user is NULL.
    What do you mean "it fails"? It fails with ORA error? It works just as expected for me, here is a simple example:
    Code:
    SQL> select sys_context('USERENV','OS_USER') from dual; 
    
    SYS_CONTEXT('USERENV','OS_USER')
    --------------------------------------------------------------------
    Administrator
    
    SQL> create or replace procedure foo
      2  as
      3    dummy varchar2(50);
      4  begin
      5    select sys_context('USERENV','OS_USER') into dummy from dual;
      6    dbms_output.put_line('OS user returned by proc: ' || dummy);
      7  end;
      8  /
    
    Procedure created.
    
    SQL> set serveroutput on
    SQL> begin
      2    foo;
      3  end; 
      4  /
    
    OS user returned by proc: Administrator
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Posts
    54
    yep, your right... it works. thesys_context was returning os_name with domain and username for the machine my server was on (where i wrote the procedure) so i wrote this with substr to grab the username. however, all the machines other than the server were returning only os username... so my substr was killing them off essentially.

    anyhow... thanks for helping out. it got me back on the right path.

    -mcslain

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