pl sql %NOTFOUND
Nicholas's answer is what you want if you want to use SELECT INTO
. However, if it is more important that you are able to use %FOUND
or %NOTFOUND
, consider FETCH
ing from a cursor instead:
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;
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.
Nick's answer is correct.
In oracle documentation however it is stated that SQL%NOTFOUND
works with SELECT INTO
but before one could check SQL%NOTFOUND
to be TRUE an error is generated called as no_data_found
.
so to use SQL%NOTFOUND
one first needs to hande no_data_found
error.
DECLARE
VAR SUPP_NM VARCHAR(100);
VAR_SUPP_ID NUMBER := 1;
WHILE_VAR CHAR := 'Y';
BEGIN
BEGIN
SELECT SUPP_NM
INTO VAR_SUPP_NM
FROM TEST.SUPPLIER
WHERE SUPP_ID = VAR_SUPP_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null; -- or write something here if u want.
END;
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;
So what I have done is added a inner BEGIN-END
block enclosing the SELECT
statement that generates no_data_found
exception. After that you can check for the value of SQL%NOTFOUND
.
You can read more about this in oracle docs. Start from this active link in mytime : https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00703