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 -> Re: SELECT in "EXECUTE IMMEDIATE" returning nothing

Re: SELECT in "EXECUTE IMMEDIATE" returning nothing

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 06 Jul 2002 07:29:21 +0200
Message-ID: <7ovciukhqd2k669trt5m2nvvo4j508k9d8@4ax.com>


On 5 Jul 2002 21:15:47 -0700, ramon_at_conexus.net (Ramon F Herrera) wrote:

>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

>It looks like I cannot do an EXECUTE IMMEDIATE unless I .

try to learn PL/SQL

SELECT INTOs always should be trapped for the NO_DATA_FOUND condition. BEGIN
SELECT INTO
EXCEPTION
WHEN NO_DATA_FOUND THEN
END; When you don't want that, you should use an explicit cursor open <cursor>
fetch <cursor> into <variable>
if <cursor>%NOTFOUND then

When you don't want that too (given your comment) you should switch to a different product.

You can't learn Oracle without RTFM

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Jul 06 2002 - 00:29:21 CDT

Original text of this message

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