G-7910

Error

Never use DML within a SQL macro.

Rule Details

Severity Level: Blocker
Quality Area: Reliability
Fixing times: 5 mins
Local-medium
Database Support: OracleDB (v19.7.0 +)

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

Non-Compliant Example

create or replace package body my_package is
   function row_generator(in_num_rows in number) -- dbLinter ignore(G-7460) non-deterministic function
      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;
end my_package;
/

Issues

Line	Column	Message
5	7	Insert used in SQL macro row_generator.
7	7	Commit used in SQL macro row_generator.

Compliant Solution - ★★★★★

create or replace package body my_package is
   function row_generator(in_num_rows in number) -- dbLinter ignore(G-7460) non-deterministic function
      return varchar2 sql_macro as
   begin
      return 'select level as row_sequence
             from dual
          connect by level <= in_num_rows';
   end row_generator;
end my_package;
/

References

same as Trivadis G-7910