Re: SELECT in "EXECUTE IMMEDIATE" returning nothing

begin

      EXECUTE IMMEDIATE v_SQLString INTO v_Result;

• if you come here, there is one exception when no_data_found then --if you come here, there is none when others then raise; end,

Ramon F Herrera a écrit :

> 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 Sun Jul 07 2002 - 07:46:38 CDT