469,112 Members | 2,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

No Data found error

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
Jul 19 '05 #1
2 40418
Naren wrote:
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

what are the values of d_a and d_b?
I don't see a declarion like
d_a number := 12;
d_b number := 4;
begin
....

--
Regards, Frank van Bortel

Jul 19 '05 #2
na**********@hotmail.com (Naren) wrote in message news:<82*************************@posting.google.c om>...
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
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Andy | last post: by
32 posts views Thread by Neil Ginsberg | last post: by
6 posts views Thread by Nathan Kovac | last post: by
2 posts views Thread by Naren | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.