How to handle different exceptions in PL/SQL differently?

Question

My stored-procedure is looping executing different statements. I want to handle the following situations:

1. When the statement returns nothing (no_data_found), I want to quietly skip the rest of the loop (continue).
2. When the statement causes an error of any type, I want to report it, and then skip the rest of the loop (continue);
3. When the statement finds rows, I want to report it.

The code looks like:

...
LOOP
    stmt := 'select * ......';
    BEGIN
        EXECUTE IMMEDIATE stmt;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN NULL;
        WHEN OTHERS THEN
            dbms_out.put_line(stmt || ': ' || SQLCODE);
        CONTINUE;
    END;
    dbms_out.put_line('Found! Use: ' || stmt);
END LOOP;

The above elicits no errors, but the Found-line is printed for every loop-iteration, including for statements, that yield no results...

Why is the CONTINUE-directive ignored -- am I wrong expecting the directive to be obeyed for any exception -- be it NO_DATA_FOUND or anything else?

Answer 1

In your exception block, the action for your NO_DATA_FOUND handler is NULL - so it executes the NULL statement (i.e. does nothing) and falls out of the BEGIN-END block, hitting the dbms_out.put_line('Found! Use: ' || stmt); statement. The only handler which will execute CONTINUE; is the WHEN OTHERS.

One way to get the behavior you describe is to do a SELECT COUNT(*)... into a numeric variable and then just check to see how many rows are returned:

DECLARE
  csr     SYS_REFCURSOR;
  nCount  NUMBER;
BEGIN
  LOOP
    stmt := 'SELECT COUNT(*) FROM (SELECT * from ... WHERE ...)';

    OPEN csr FOR stmt;

    FETCH csr INTO nCount;

    CLOSE csr;

    IF nCount > 0 THEN
      dbms_out.put_line('Found! Use: ' || stmt);
    ELSE
      dbms_out.put_line(stmt || ': ' || SQLCODE);
    END IF;
  END LOOP;
END;

Of course this is not really valid as there's no way for the value of stmt to change, but I suspect your "real" code handles that.