Apologies if this is a known bug: in function f_plw_5005_6009 (see test code below), the warning PLW-05005 is expected, but missing.
Test script for SQL*Plus:
set echo on
alter session set plsql_warnings = "ENABLE:ALL, DISABLE:5018";
create or replace function f_plw_5005 return varchar2
is begin
if dbms_random.value > 0.5 then
return 'OK';
end if; -- ooops, missing else
end f_plw_5005;
/
show errors
create or replace function f_plw_6009 return number
is begin
if dbms_random.value > 0.5 then
raise no_data_found;
else
return 0;
end if;
exception
when others then
return 1;
end f_plw_6009;
/
show errors
create or replace function f_plw_5005_6009 return number
is begin
if dbms_random.value > 0.5 then
raise no_data_found;
else
return 0;
end if;
exception
when others then
dbms_output.put_line('Let''s pretend to handle this...');
end f_plw_5005_6009;
/
show errors
----------------------------------------------------------------
column name format a15
column line format 999
column text format a70 word_wrapped
select name, attribute, line, text
from user_errors
where type = 'FUNCTION'
and name like 'F\_PLW%' escape '\'
order by name, sequence;
Results:
1/ Oracle 23c FREE
SQL> alter session set plsql_warnings = "ENABLE:ALL, DISABLE:5018";
Session altered.
SQL> create or replace function f_plw_5005 return varchar2
...
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION F_PLW_5005:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05005: subprogram F_PLW_5005 returns without value at line 6
SQL> create or replace function f_plw_6009 return number
...
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION F_PLW_6009:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9 PLW-06009: procedure "F_PLW_6009" OTHERS handler does not end in
RAISE
SQL> create or replace function f_plw_5005_6009 return number
...
Function created.
SQL> show errors
No errors.
SQL> select name, attribute, line, text
2 from user_errors
3 where type = 'FUNCTION'
4 and name like 'F\_PLW%' escape '\'
5 order by name, sequence;
NAME ATTRIBUTE LINE TEXT
--------------- --------- ---- ----------------------------------------------------------------------
F_PLW_5005 WARNING 1 PLW-05005: subprogram F_PLW_5005 returns without value at line 6
F_PLW_6009 WARNING 9 PLW-06009: procedure "F_PLW_6009" OTHERS handler does not end in RAISE
2 rows selected.
2/ Oracle 19.9
SQL> alter session set plsql_warnings = "ENABLE:ALL, DISABLE:5018";
Session altered.
SQL> create or replace function f_plw_5005 return varchar2
...
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION F_PLW_5005:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05005: subprogram F_PLW_5005 returns without value at line 6
SQL> create or replace function f_plw_6009 return number
...
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION F_PLW_6009:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9 PLW-06009: procedure "F_PLW_6009" OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
SQL> create or replace function f_plw_5005_6009 return number
...
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION F_PLW_5005_6009:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05005: subprogram F_PLW_5005_6009 returns without value at
line 11
SQL> select name, attribute, line, text
2 from user_errors
3 where type = 'FUNCTION'
4 and name like 'F\_PLW%' escape '\'
5 order by name, sequence;
NAME ATTRIBUTE LINE TEXT
--------------- --------- ---- ----------------------------------------------------------------------
F_PLW_5005 WARNING 1 PLW-05005: subprogram F_PLW_5005 returns without value at line 6
F_PLW_5005_6009 WARNING 1 PLW-05005: subprogram F_PLW_5005_6009 returns without value at line 11
F_PLW_6009 WARNING 9 PLW-06009: procedure "F_PLW_6009" OTHERS handler does not end in RAISE
or RAISE_APPLICATION_ERROR
3 rows selected.
Regards,