problem with explicit cursor and NO_DATA_FOUND
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;
/