G-7910: Never use DML within a SQL macro.
Critical
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 | create or replace function row_generator (
num_rows_in in number(32,0)
)
return varchar2 sql_macro as
begin
insert into function_calls(name, called_at, parameter_value)
values ($$PLSQL_UNIT, current_timestamp, num_rows_in);
commit;
return 'select level as row_sequence from dual connect by level <= num_rows_in';
end row_generator;
/
|
Example (good)
| create or replace function row_generator (
num_rows_in in number(32,0)
)
return varchar2 sql_macro as
begin
return 'select level as row_sequence from dual connect by level <= num_rows_in';
end row_generator;
/
|