Pl sql% NOTFOUND

Question

I'm just wondering why this piece of code is not working. I have no provider id 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; 

Answer 1

To catch the NO_DATA_FOUND exception, NO_DATA_FOUND your code as follows by adding the 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 does not make sense in the case of the select into statement, because if the select statement does not return any rows, it will always throw a NO_DATA_FOUND exception, in addition, if this select statement calls the aggregate function, it will always return data or null if no row is selected.

Do not use the varchar data type, use the varchar2 data type varchar2 .

Answer 2

Nicholas's answer is what you want if you want to use SELECT INTO . However, if it is more important that you can use %FOUND or %NOTFOUND , consider FETCH ing from the cursor:

 DECLARE VAR SUPP_NM VARCHAR2(100); VAR_SUPP_ID NUMBER := 1; WHILE_VAR CHAR := 'Y'; CURSOR c1 IS SELECT SUPP_NM FROM TEST.SUPPLIER WHERE SUPP_ID = VAR_SUPP_ID; BEGIN OPEN c1; FETCH c1 INTO VAR_SUPP_NM; IF c1%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND'); ELSIF c1%FOUND THEN DBMS_OUTPUT.PUT_LINE('DATA FOUND'); END IF; CLOSE c1; END;