Skip to Main Content
  • Questions
  • Inappropriate (?) ORA-06503 when the exception block contains raise_application_error but no return or raise

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Christian.

Asked: November 15, 2024 - 4:36 pm UTC

Last updated: November 28, 2024 - 3:05 pm UTC

Version: 19

Viewed 100+ times

You Asked

Hi,

We stumbled upon ORA-06503 and wanted to use
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL, ERROR:5005';
to prevent developers from writing functions without a return statement.
Works fine, but now we get a PLS-05005 error when the exception block doesn't contain a return or raise statement.
We use raise_application_error very often in our exception blocks and a return statement afterwards would be dead code.
Do we have to accept this dead code or is there a more elegant way?


ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL, ERROR:5005';
CREATE OR REPLACE FUNCTION foo RETURN NUMBER AUTHID DEFINER IS
BEGIN
    RAISE NO_DATA_FOUND;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(-20001, 'some dynamic message that is handled by the caller');
--        RETURN NULL; -- Isn't this just dead code?
END foo;

and Chris said...

You're right - it is dead code.

The elegant way is to upgrade! From 21c this no longer raises a warning:

select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL, ERROR:5005';

Session altered.

CREATE OR REPLACE FUNCTION foo RETURN NUMBER AUTHID DEFINER IS
BEGIN
    RAISE NO_DATA_FOUND;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       raise_application_error(-20001, 'some dynamic message that is handled by the caller');
END foo;
/
Function created.

sho err
No errors.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library