PL/SQL (MOSC)

MOSC Banner

sys_context inside a packaged procedure?

edited Nov 16, 2011 12:07AM in PL/SQL (MOSC) 5 commentsAnswered ✓
I'm trying to retrieve a user's current schema from within a procedure but all I get back is the owner of the procedure. 
>: create or replace procedure user1.procedure2 as
   schema_name  varchar2( 30 ) := sys_context( 'USERENV', 'CURRENT_SCHEMA' ) ;
begin
    dbms_output.put_line( 'UserEnv.CurrentSchema = (' || schema_name || ')' );
    . . .
end;
/

Procedure created.

>: alter session set current_schema = user2 ;

Session altered

>: select sys_context( 'userenv', 'current_schema' ) schema from dual ;

SCHEMA
------
USER2

>: exec user1.procedure2 ;
UserEnv.CurrentSchema = (USER1)
. . .

No matter what I set my current schema to, the procedure always sees the "current" schema as the owner of the procedure.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center