pl sql %NOTFOUND

oracle, plsql

Solution

To catch the `NO_DATA_FOUND` exception rewrite your code as follows by adding `exception` section:

DECLARE
    VAR_SUPP_NM VARCHAR2(100);
    VAR_SUPP_ID  NUMBER := 1;
    WHILE_VAR CHAR := 'Y';
BEGIN
  SELECT SUPP_NM
    INTO VAR_SUPP_NM
    FROM TEST.SUPPLIER
   WHERE SUPP_ID = VAR_SUPP_ID;

 DBMS_OUTPUT.PUT_LINE('DATA FOUND');

exception
  when no_data_found 
  then DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');

END;

Checking `SQL%FOUND` or `SQL%NOTFOUND` have no meaning in the case of `select into` statement, because if the select statement returns no rows it will always raise `no_data_found` exception, except, if that select statement invokes aggregate function, it will always return data or null if no rows has been selected.

Do not use `varchar` datatype, use `varchar2` datatype instead.

Problem

I'm just wondering why this piece of code is not working. I don't have any supplier `id=1` in my table. ``` DECLARE VAR SUPP_NM VARCHAR(100); VAR_SUPP_ID NUMBER := 1; WHILE_VAR CHAR := 'Y'; BEGIN SELECT SUPP_NM INTO VAR_SUPP_NM FROM TEST.SUPPLIER WHERE SUPP_ID = VAR_SUPP_ID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND'); ELSIF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('DATA FOUND'); END IF; END; ``` I get a 01403 error in Toad but not handled as `sql%notfound`. Why isn't the `sql%notfound` working?

Original source