Re: Bug in Execute Immediate clause???

[Manoj Kumar Jha]

First session: -------------------- SQL> CREATE OR REPLACE procedure testing authid current_user is   2  sql_stmt VARCHAR2(4000);   3  c number;   4  n number;   5  a varchar2(1000);   6  Begin   7  sql_stmt := 'Alter Session Set Current_Schema = scott';   8  Execute Immediate sql_stmt;   9  Dbms_output.put_line(sys_context('userenv', 'Current_schema'));  10  sql_stmt := 'Select count(*) From temp';  11  Execute Immediate sql_stmt into c;  12  Dbms_output.put_line(c);  13  END;  14  /   Procedure created.   Second Session: -------------------------   SQL> conn test1 Enter password: ***** Connected. SQL> exec test1.testing;   PL/SQL procedure successfully completed.   SQL> set serveroutput on SQL>  exec test1.testing; SCOTT 0   PL/SQL procedure successfully completed.   SQL>       in second session when you execute the procedure the schema changed and not find the procedure in that schema....     try this one.....       With Regards, Manoj Kumar Jha   ----- Original Message ----- From: Ranganath K To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 02, 2003 9:00 PM Subject: Bug in Execute Immediate clause??? Hi Listers,   The below procedure gets created successfully in TEST Schema.  But when I execute the procedure by starting a fresh session connecting as TEST schema I get the below error and when I execute the procedure for the second time it executes successfully.  I have granted the dba privileges and explicit granted select on TEMP1 to TEST Schema.  Inspite of that I am getting the below errors.  I tried this 8.1.7 and 9.2.1.0.  Is it a bug in the code or the database?    CREATE OR REPLACE procedure test authid current_user is c number; n number; a varchar2(1000); Begin Execute Immediate 'Alter Session Set Current_Schema = SCOTT'; Dbms_output.put_line(sys_context('userenv', 'Current_schema')); Select count(*) into c From temp1; Dbms_output.put_line(c); End;   ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "TEST.TEST", line 9 ORA-06512: at line 1   Can anybody help me out?  Any help in this regard is very much appreciated.   Thanks and Regards,   Ranganath