RE: Bug in Execute Immediate clause???

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 02 Jul 2003 09:51:20 -0700
Message-ID: <F001.005BF073.20030702093548@fatcity.com>

<FONT face="Courier New" color=#0000ff
size=2>Are we forgetting that your current schema is SCOTT ??
<FONT face="Courier New" color=#0000ff
size=2> 
<FONT face="Courier New" color=#0000ff
size=2>comment that line, and then try running the procedure ...
<FONT face="Courier New" color=#0000ff
size=2> 
<FONT face="Courier New" color=#0000ff
size=2>Raj
<FONT face="Courier New"

size=2>-------------------------------------------------------------------------------- 

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email
are strictly personal. QOTD: Any clod
can have facts, having an opinion is an art !

<FONT face=Tahoma

  size=2>-----Original Message-----From: Ranganath K   [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 02, 2003   11:31 AMTo: Multiple recipients of list ORACLE-LSubject:   Bug in Execute Immediate clause???   

<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Hi <SPAN   class=SpellE>Listers,
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">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?    

<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">CREATE OR REPLACE procedure test   authid current_user
  is
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">c<FONT   face=Arial size=2>
  number;
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">n<FONT   face=Arial size=2>
  number;
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">a<FONT   face=Arial size=2>
  varchar2(1000);
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Begin
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Execute Immediate 'Alter Session   Set Current_Schema =
  SCOTT';
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Dbms_output.put_<SPAN   class=GramE>line<FONT face=Arial
  size=2><SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">(<SPAN 
  class=SpellE><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">sys_context<FONT 

  face=Arial size=2>('<SPAN
  class=SpellE>userenv', '<SPAN
  class=SpellE>Current_schema'));
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Select <SPAN   class=GramE>count(*) into c From temp1;
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Dbms_output.put_line<FONT   face=Arial size=2><SPAN
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">(c);
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">End;
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ERROR at line   1:
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORA-00942: table or view does not   exist
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORA-06512: at "TEST.TEST", line   9
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">ORA-06512: at line   1
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Can anybody help me out?<SPAN   style="mso-spacerun: yes">  Any help in this regard is very much   appreciated.
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Thanks and   Regards,
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"> 
<SPAN

  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Ranganath

********************************************************************This e-mail 

message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank

you.*********************************************************************2

Received on Wed Jul 02 2003 - 11:51:20 CDT