CODEWITHSUNDEEP
plsql
Jonnny
Jonnny

Reputation: 5039

PL/SQL raise_application_error with SQL%NOTFOUND

I am trying write a procedure that is checking that people in some of my tables are assigned properly. This is the first procedure in a package. If everybody is assigned properly the SQL will return no records. If people are not assigned the SQL will return those people not assigned.

...

BEGIN

    FOR R_PEOPLE IN C_PEOPLE

        LOOP

        IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20002, 'ALL PEOPLE ASSIGNED, PROCEED');
        END IF;

       -- When data is found, should raise exception and stop billing process

        IF SQL%FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'MISSING PEOPLE');
        END IF;

        END LOOP;

END;

What I'm trying to do is raise two different messages out to the end user who will be running this in TOAD. If no results are returned, everyone is assigned, I want to output the first IF statement "ALL PEOPLE ASSIGNED, PROCEED". If the SQL returns records then ideally I want the second IF to execute "MISSING People".

Currently, when people are missing, I get the "MISSING PEOPLE" output to the user. But if everyone is already assigned, I just get "Procedure Complete".

Upvotes: 0

Views: 854

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

FOR R_PEOPLE IN C_PEOPLE

    LOOP

    IF SQL%NOTFOUND THEN
    -- ^^^^^^^^^^^^
    -- this can never be true since if there was no row,
    -- you won't enter the loop
        RAISE_APPLICATION_ERROR(-20002, 'ALL PEOPLE ASSIGNED, PROCEED');
    END IF;

    ...

This can't work as, if there was no match, you won't execute the loop body even once.

I don't know how is your query, but maybe you could rewrite that using a COUNT(*) an use the result (either 0 or non-zero) to raise the appropriate exception ?

Something like that, maybe:

SELECT COUNT(*) INTO RESULT FROM .... WHERE ..... ;
IF RESULT = 0
THEN
    RAISE_APPLICATION_ERROR(-20002, 'ALL PEOPLE ASSIGNED, PROCEED');
ELSE
    RAISE_APPLICATION_ERROR(-20001, 'MISSING PEOPLE');
END IF;

Upvotes: 1

Related Questions