-
Expert Member
Report Error
The following cursor was written to find sum of issues for a given date. It is working if it returns more than one record. The value should be zero if no record was found. But it is not working for zero records. What is wrong with the following code?
OPEN C2;
SRW.MESSAGE (1,'CURSOR 2 AFTER OPEN');
FETCH C2 INTO V_ISS;
SRW.MESSAGE (2,'CURSOR 2 AFTER FETCH');
IF C2%NOTFOUND THEN
V_ISS := 0;
SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
END IF;
CLOSE C2;
I tried using C2%ROWCOUNT = 0 also. Could anyone suggest where is the mistake in the above code?
-
Expert Member
Re: Report Error
If no data found(other exception) occur control goes to exception block so ur code
(IF C2%NOTFOUND THEN
V_ISS := 0;
SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
END IF; )
is not executed u need to include the above code in
Exception block i.e...
OPEN C2;
SRW.MESSAGE (1,'CURSOR 2 AFTER OPEN');
FETCH C2 INTO V_ISS;
SRW.MESSAGE (2,'CURSOR 2 AFTER FETCH');
.
.
.
Exception
When others then --(or when no_data_found then)
V_ISS := 0;
.....
SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
END IF;
.......
CLOSE C2;
-
Expert Member
Re: Report Error
Thank you for your reply.
I tried to rectify the above problem using exception no_data_found. But it does not work.
I think in cursors, if no data found then it will not raise exception. It will return NULL value. I rectified above problem in the following way
IF V_ISS IS NULL THEN
V_ISS := 0;
END IF;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules