Skip to content

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)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;
/