Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!opentransit.net!wanadoo.fr!not-for-mail
From: Hugues Parvillers <Hugues.Parvillers@AgdF.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: SELECT in "EXECUTE IMMEDIATE" returning nothing
Date: Sun, 07 Jul 2002 14:46:38 +0200
Organization: AgdF - from Data to Information
Lines: 47
Message-ID: <3D28382E.25FC18ED@AgdF.com>
References: <c9bc36ff.0207052015.2235e79c@posting.google.com>
NNTP-Posting-Host: arouen-102-1-5-215.abo.wanadoo.fr
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: wanadoo.fr 1026045992 11800 80.15.68.215 (7 Jul 2002 12:46:32 GMT)
X-Complaints-To: abuse@wanadoo.fr
NNTP-Posting-Date: 7 Jul 2002 12:46:32 GMT
To: Ramon F Herrera <ramon@conexus.net>
X-Mailer: Mozilla 4.7 [fr]C-NSCPCD  (WinNT; U)
X-Accept-Language: fr
Xref: easynews comp.databases.oracle.server:153304
X-Received-Date: Sun, 07 Jul 2002 05:43:46 MST (news.easynews.com)


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