Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT in "EXECUTE IMMEDIATE" returning nothing
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