Re: SELECT in "EXECUTE IMMEDIATE" returning nothing

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
>

If you only are interested in a row hit or not, why not try select count and return the count value.

Your approch is very expensive, hard parsing of v_SQLString for every loop. Statement (cursor) reuse might be posible by creating the sql_string like select count(*) from :tab where... and EXECUTE IMMEDIATE v_SQLString INTO v_Result using <'table_name'> Try it, it might work. Received on Sat Jul 06 2002 - 06:12:25 CDT