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

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Tue, 26 Feb 2002 16:57:05 GMT
Message-ID: <l.1014742625.1072265625@[64.94.198.252]>

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.  

-- 
Sent by dbadba62 from  hotmail  subpart from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

Received on Tue Feb 26 2002 - 10:57:05 CST