Skip to Main Content
  • Questions

  • Inappropriate (?) ORA-06503 when the exception block contains raise_application_error but no return or raise

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

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 1000+ 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.


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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