Theoretically saying %NOTFOUND is a cursor attribute which returns TRUE if the last FETCH statement retrieved no record and FALSE if the last FETCH statement retrieved a record, while NO_DATA_FOUND is a predefined exception (an error condition), which is raised when a SELECT … INTO … statement fetches no record.

Difference between the two is that, if a FETCH statement doesn’t retrieved any record, NO_DATA_FOUND is not raised, or in other words, while using cursors NO_DATA_FOUND exception is not raised, rather the %NOTFOUND attribute value is set to TRUE. While in case of a SELECT … INTO … statement, NO_DATA_FOUND exception is raised and %NOTFOUND attribute of the implicit cursor is also set to TRUE.

Here is an example:

SQL> DECLARE

l_ename VARCHAR2(100);

BEGIN

SELECT ename INTO l_ename FROM emp

WHERE empno = ‘1515′;

DBMS_OUTPUT.PUT_LINE(l_ename);

EXCEPTION

WHEN NO_DATA_FOUND THEN

IF ( SQL%NOTFOUND ) THEN

DBMS_OUTPUT.PUT_LINE(’NOTFOUND’);

ELSE

DBMS_OUTPUT.PUT_LINE(’FOUND’);

END IF;

END;

/

NOTFOUND

SQL> DECLARE

CURSOR c1 IS SELECT ename FROM emp WHERE empno = 1515;

l_ename VARCHAR2(100);

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO l_ename;

IF ( c1%NOTFOUND ) THEN

DBMS_OUTPUT.PUT_LINE(’NOTFOUND’);

ELSE

DBMS_OUTPUT.PUT_LINE(l_ename);

END IF;

EXIT WHEN c1%NOTFOUND;

END LOOP;

CLOSE c1;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(’NO_DATA_FOUND’);

END;

/

NOTFOUND

Hence the two are different while sometimes taken to be the same.