Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SELECT in "EXECUTE IMMEDIATE" returning nothing

SELECT in "EXECUTE IMMEDIATE" returning nothing

From: Ramon F Herrera <ramon_at_conexus.net>
Date: 5 Jul 2002 21:15:47 -0700
Message-ID: <c9bc36ff.0207052015.2235e79c@posting.google.com>


In a stored procedure, inside a loop, I have this:

      EXECUTE IMMEDIATE v_SQLString INTO v_Result;
      DBMS_OUTPUT.PUT_LINE(v_Result);

The v_SQLString is recreated in every iteration, with the only change from one iteration to the next being the name of the table being queried. Each SELECT will return zero or one rows.

The problem is that when the SELECT on a table returns no rows, I get the follwowing error message:

ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "DATABASE.RECORDOF", line 18
ORA-06512: at line 2

What is Oracle trying to tell me here? I am running the EXEC IMMEDIATE precisely to find out whether there is a hit or not. Why is it complaining? Is there any way to find if the table will have zero or one line without a SELECT? Is there something like:

      if table such contains id=x then EXECUTE IMMEDIATE ...;

It looks like I cannot do an EXECUTE IMMEDIATE unless I know in advance that the SELECT will return something.

Your insight in this will be very much appreciated.

-Ramon F. Herrera Received on Fri Jul 05 2002 - 23:15:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US