PROCEDURE TEST_CALL is

bob varchar2(1000);
BEGIN

execute immediate 'alter session set current_schema=nc';

select sys_context('USERENV','CURRENT_SCHEMA') into bob from dual;

dbms_output.put_line(mike);

-- execute immediate 'select 1 from nc_parm';
  
execute immediate 'alter session set current_schema=dash';

END;

there are two types of procedures

a) definers rights procedure - the default and the correct one to use 99.9999% of the time (or more)

Here, when you compile the procedure, the references to external code AND objects referenced in SQL are fixed at compile time - at COMPILE TIME - without regards to the 'current schema' - the only 'current schema' in a definers rights routine is....

the compiling schema. always. The compiling schema

and - this is important - no matter WHO runs the procedure, no matter WHAT their environment is, no matter what - the SQL will access the same objects because the current schema in a definers rights routine is ALWAYS the compiling schema.

This is the very nature of their design, this is intended, this is 100% good (this is a huge security feature)


b) invokers rights

Here, when you compile the procedure, the references to external code is fixed at compile time BUT objects referenced in SQL are resolved at runtime. The code runs the sql with the privileges of the invoker - not the definer.

These are useful in a small set of cases - a tiny set of cases - utilities only typically. Not in production code run hundreds or hundreds of millions of times per day.




Consider:

ops$tkyte%ORA10GR2> create table scott.t( x varchar2(40) );

Table created.

ops$tkyte%ORA10GR2> insert into scott.t values ( 'this is scott' );

1 row created.

ops$tkyte%ORA10GR2> create table t ( x varchar2(40) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 'this is ' || user );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure dr( p_schema in varchar2 )
  2  as
  3  begin
  4          execute immediate 'alter session set current_schema = ' || p_schema;
  5          for x in (select x, sys_context( 'userenv', 'current_schema' ) cs from t)
  6          loop
  7                  dbms_output.put_line( x.x );
  8                  dbms_output.put_line( x.cs );
  9                  dbms_output.put_line( rpad( '-', 40, '-' ));
 10          end loop;
 11  end;
 12  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure ir( p_schema in varchar2 )
<b>  2  authid current_user
</b>  3  as
  4  begin
  5          execute immediate 'alter session set current_schema = ' || p_schema;
  6          for x in (select x, sys_context( 'userenv', 'current_schema' ) cs from t)
  7          loop
  8                  dbms_output.put_line( x.x );
  9                  dbms_output.put_line( x.cs );
 10                  dbms_output.put_line( rpad( '-', 40, '-' ));
 11          end loop;
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec ops$tkyte.dr( user )
this is OPS$TKYTE
OPS$TKYTE
----------------------------------------

PL/SQL procedure successfully completed.

<b>no matter what, that is what will happen here - we are in a definer right routine, it (current schema) is FIXED at compile time - regardless of what the session setting was.

else - think about it, if SCOTT ran procedure XXX.PROC and TKYTE ran XXX.PROC - unqualified references in XXX.PROC would be resolved using SCOTT and then TKYTE wouldn't they - because by default SCOTT And TKYTE always have different current schemas - that would completely destroy the usefulness of procedures</b>

ops$tkyte%ORA10GR2> select x, sys_context( 'userenv', 'current_schema' ) cs from t;

X                    CS
-------------------- --------------------
this is OPS$TKYTE    OPS$TKYTE


ops$tkyte%ORA10GR2> exec ops$tkyte.dr( 'scott' )
this is OPS$TKYTE
OPS$TKYTE
----------------------------------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select x, sys_context( 'userenv', 'current_schema' ) cs from t;

X                    CS
-------------------- --------------------
this is scott        SCOTT

<b>so the procedure changed the current session - we can see that by the differing sqlplus output - however, it cannot, will not, should not change the operating environment of a DEFINERS RIGHTS routine</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec ops$tkyte.ir( 'scott' )
this is scott
SCOTT
----------------------------------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select x, sys_context( 'userenv', 'current_schema' ) cs from t;

X                    CS
-------------------- --------------------
this is scott        SCOTT

<b>Now that is different - same code - except for the authid clause.  And since the current schema was scott, the sql was resolved to be scott.t...</b>

ops$tkyte%ORA10GR2> exec ops$tkyte.ir( user )
this is OPS$TKYTE
OPS$TKYTE
----------------------------------------

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select x, sys_context( 'userenv', 'current_schema' ) cs from t;

X                    CS
-------------------- --------------------
this is OPS$TKYTE    OPS$TKYTE

<b>and then ops$tkyte.t</b>

but in short, you should not be doing this in code that is not a utility (like a bit of code to dump a table to a file for example). This invokers rights is "special"