G-7910: Never use DML within a SQL macro.
Blocker
Reliability, Testability
Restriction
Oracle Database 21c (19c from version 19.7 for table macros alone)
Reason
Doing DML (except for select
) within a SQL macro can lead to disastrous side effects from calling the macro in a SQL query.
Logging macro calls via a call to a procedure that does DML in an autonomous transaction can be an exception to the rule.
Example (bad)
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | create or replace package body my_package is
function row_generator(in_num_rows in number) -- NOSONAR: non-deterministic
return varchar2 sql_macro as
begin
insert into function_calls(name,called_at,parameter_value)
values ($$plsql_unit,current_timestamp,in_num_rows);
commit;
return 'select level as row_sequence -- NOSONAR: G-1050
from dual
connect by level <= in_num_rows';
end row_generator;
end my_package;
/
|
Example (good)
| create or replace package body my_package is
function row_generator(in_num_rows in number) -- NOSONAR: non-deterministic
return varchar2 sql_macro as
begin
return 'select level as row_sequence -- NOSONAR: G-1050
from dual
connect by level <= in_num_rows';
end row_generator;
end my_package;
/
|