Re: No Data found error

[EMAIL PROTECTED] (Naren) wrote in message news:<[EMAIL PROTECTED]>...
> 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