pl sql %NOTFOUND

Question

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?

Answer 1

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 FETCHing 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;

Answer 2

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