Skip to content

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
13
14
create or replace function row_generator(
   in_num_rows in number
)
   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
             from dual 
          connect by level <= in_num_rows';
end row_generator;
/

Example (good)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create or replace function row_generator(
   in_num_rows in number
)
   return varchar2 sql_macro as
begin
   return 'select level as row_sequence
             from dual 
          connect by level <= in_num_rows';
end row_generator;
/