Re: No Data found error

naren_on_net_at_hotmail.com (Naren) wrote in message news:<825c72b.0311280533.772e8448_at_posting.google.com>...
> Hi,
>
> I am a newbie to Oracle and stored procedures. Hence this question
> comes up. The complete scenario is like this. We have a multithreaded
> application using pthreads on HP-UX machine. We are connecting to
> Oracle database 8.1.7. The application calls stored procedures on
> database using OCI calls. It is also ensured that the our application
> is thread safe.
>
> When a particular procedure is called, a SQL select statment returns
> an exception NO DATA FOUND. However when we connect though SQL and try
> executing the same SQL select query , it is found that the data is
> present. Also other SQL select statements in the same procedure prior
> to this one work fine. However one difference is there. The flow of
> the SQL in the proc is as follows
> BEGIN
> SELECT
> a,
> b,
> INTO
> d_a,
> d_b,
> FROM X
> WHERE condition1 = 10
> AND condition2= 3;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> raise_application_error (-20033, '99991, no data found --
> ');
> END;
>
> BEGIN
> SELECT m INTO d_m
> FROM Y
> WHERE condition3= d_a
> AND condition4 = d_b;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> raise_application_error (-20033, '99992, no data found ');
> END; --> problem occurs here
>
> Could anyone please tell me what can be cause of this error.
>
> Thanks in advance
> Naren

The second block will be executed regardless of the outcome in the first block. If you have no_data_found in the first block, you handle this situation (which is good), but you'll need to set up a boolean in the block and conditionally execute the second block. Otherwise if there is nothing sensible in d_a and d_b you'll end up with no_data_found in the second block.

Sybrand Bakker
Senior Oracle DBA Received on Mon Dec 01 2003 - 09:53:03 CET