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;
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.