Duplicate error message when error handling-CodePudding

I have this procedure:

CREATE OR REPLACE PROCEDURE procA (paramA VARCHAR2, paramB VARCHAR2, output_value OUT VARCHAR2)
IS
BEGIN
    IF paramA IS NOT NULL THEN
        BEGIN --1
            SELECT columA
              INTO output_value
              FROM tableA
             WHERE columnB = paramA;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 2
                SELECT columB
                  INTO output_value
                  FROM tableB
                 WHERE columnC = paramB;
        END;
    ELSE
        SELECT columB
          INTO output_value
          FROM tableB
         WHERE columnC = paramB;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20008, 'Error: ' || SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 200));
END procA;

I run this call:

DECLARE
    output   VARCHAR2 (10);
BEGIN
    procA ('valueA', 'valueB', output);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE (SQLERRM); --3
END;

With these parameters the first SELECT statement gets a NO_DATA_FOUND (1). And handled with the that EXCEPTION. Then, I run the second SELECT statement (2) and I get again, a NO_DATA_FOUND exception. Why do I get a duplicate NO_DATA_FOUND message in (3), like this:

ORA-20008: Error: 100 ORA-01403: no data found
ORA-01403: no data found

The first exception is handled, I expected that the last error handling will return just one message.

CodePudding user response：

You aren't seeing a single error being duplicated; you're seeing two errors, which happen to be the same.

If you add a format_error_stack call to your final exception hander you can see that both of the thrown exceptions are in that stack:

...
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
        RAISE_APPLICATION_ERROR (-20008, 'Error: ' || SQLCODE || ' ' || SUBSTR (SQLERRM, 1, 200));
END procA;
/

The call from your anonymous block then shows:

ORA-01403: no data found
ORA-06512: at "<schema>.PROCA", line 13
ORA-01403: no data found
ORA-06512: at "<schema>.PROCA", line 6

ORA-20008: Error: 100 ORA-01403: no data found
ORA-01403: no data found

db<>fiddle

It might be a bit clearer if you vary the errors, e.g.:

declare
  l_dummy varchar2(1);
begin
  begin
    select 'x' into l_dummy from all_users;
  exception when too_many_rows then
    select 'x' into l_dummy from all_users where 1=0;
  end;
exception when others then
  dbms_output.put_line(dbms_utility.format_error_stack);
  dbms_output.put_line('caught <' || sqlerrm || '>');
end;
/

which produces:

ORA-01403: no data found
ORA-06512: at line 7
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

caught <ORA-01403: no data found
ORA-01422: exact fetch returns more than requested number of rows>

db<>fiddle

The SQLERRM call combines both error messages, though the documentation doesn't seem to say that will happen. Oracle support note 2438311.1 seems to suggest it shouldn't, but it does all of the db<>fiddle versions and in 12.2.0.1 and 19.11 at least.

But, what if I just want the last one?

If you're on Oracle 12c or later, you can use UTL_CALL_STACK.ERROR_MSG(1) to get the first error from the stack, instead of using SQLERRM.

...
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20008,
            'Error: ' || SQLCODE || ' ' || SUBSTR (UTL_CALL_STACK.ERROR_MSG(1), 1, 200));
END procA;
/

which will now just show:

ORA-20008: Error: 100 no data found

db<>fiddle (and another one for my earlier example.)

You can read more about that function in the documentation.

CodePudding user response：

There is a difference between handling and catching an exception. You're using RAISE_APPLICATION_ERROR, which throws the exception to the caller although you've caught it once and since you catch it two times - you have two errors.