CODEWITHSUNDEEP
oracle-databaseplsql
user1050619
user1050619

Reputation: 20856

pl sql %NOTFOUND

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?

Upvotes: 12

Views: 109367

Answers (3)

Rishabh Kumar
Rishabh Kumar

Reputation: 2430

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

Upvotes: 4

Aurifier
Aurifier

Reputation: 197

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;

Upvotes: 7

Nick Krasnov
Nick Krasnov

Reputation: 27251

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.

Upvotes: 22

Related Questions