Oracle-L: Re: Bug in Execute Immediate clause???

From: Manoj Kumar Jha <jmanoj_at_delhi.tcs.co.in>
Date: Wed, 02 Jul 2003 23:42:37 -0800
Message-ID: <F001.005C3201.20030702234237@fatcity.com>

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Manoj Kumar Jha
INET: jmanoj_at_delhi.tcs.co.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jul 03 2003 - 02:42:37 CDT