Re: help:alter session set current_schema does not work in pl/sql

Mike F wrote:

> We are using 817.3 on solaris
>
>
> 1 create or replace PROCEDURE test
> 2 ( user_IN IN VARCHAR2 )
> 3 AS
> 4 dummy1 varchar2(20);
> 5 BEGIN
> 6 execute immediate 'alter session set current_schema = ' || user_in;
> 7 select table_name
> 8 into dummy1
> 9 from user_tables where rownum < 2;
> 10 sys.dbms_output.put_line(dummy1);
> 11* END;
>
> SQL> /
> Procedure created.
>
> SQL> exec system.test('TEST');
> AQ$_QUEUES
>
> PL/SQL procedure successfully completed.
>
> The expected result should be TEST as well.
>
> What is wrong here?
> Thanks for your help.
>

Mike, this won't work, try using

  create or replace PROCEDURE test

       ( user_IN IN VARCHAR2 )
authid current_user (standard|default is: authid definer (ie. owner)) ....
That will execute in the current user schema and enviroment. /svend jensen Received on Wed Feb 27 2002 - 01:24:25 CST