Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problem with explicit cursor and NO_DATA_FOUND

rajasekhar_nMar 5 2008 — edited Mar 5 2008
Hi all,
I have a problem with explicit cursor and no_data_found exception.The problem is if i use explicit cursor , when the query returs zero rows the output statement message(' data is not available') in the exception block is not displaying,if the query returns any rows it is displaying.If I am not using cursor the exception message is displaying.what's the problem.

without cursor:
o/p is : 'DATA IS NOT AVAILABLE'
DECLARE
N VARCHAR2(30);
M NUMBER;
BEGIN
SELECT LAST_NAME,COUNT(*) into N,M FROM ACCESS_REQUEST_REM_LOG GROUP BY LAST_NAME
HAVING COUNT(*) > 1;
DBMS_OUTPUT.PUT_LINE(N||' '||M);
/*OPEN C;
LOOP
FETCH C INTO N,M;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(N||' '||M);
END LOOP;
CLOSE C;*/
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DATA IS NOT AVAILABLE');
END;
/


with explicit cursor:(it is not displaying the exception message when query returs zero rows).

DECLARE
N VARCHAR2(30);
M NUMBER;
CURSOR C SELECT LAST_NAME,COUNT(*) into N,M
FROM ACCESS_REQUEST_REM_LOG
GROUP BY LAST_NAME
HAVING COUNT(*) > 1;
BEGIN
OPEN C;
LOOP
FETCH C INTO N,M;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(N||' '||M);
END LOOP;
CLOSE C;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('DATA IS NOT AVAILABLE');
END;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 2 2008
Added on Mar 5 2008
2 comments
739 views