G-3320

🆓

Warning

Try to move transactions within a non-cursor loop into procedures.

Rule Details

Severity Level: Major
Quality Area: Maintainability
Fixing times: 10 mins
Local-high
Database Support: OracleDB (v7.3.4 +)

Reason

Commit inside a non-cursor loop (other loop types than loops over cursors - see also G-3310) is either a self-contained atomic transaction, or it is a chunk (with suitable restartability handling) of very large data manipulations. In either case encapsulating the transaction in a procedure is good modularity, enabling reuse and testing of a single call.

Example

Non-Compliant Example

declare
   co_upper_bound constant integer               := 5;
   co_max_level   constant integer               := 3;
   co_number      constant types_up.short_string := 'Number';
   co_line        constant types_up.short_string := 'Line';
   co_space       constant types_up.short_string := ' ';
   l_counter      integer                        := 0;
begin
   <<create_headers>>
   loop
      insert into headers (id,text)
      values (
         l_counter,co_number
         || co_space
         || l_counter
      );

      insert into lines (header_id,line_no,text)
      select l_counter
            ,rownum
            ,co_line
             || co_space
             || rownum
        from dual
     connect by level <= co_max_level;

      commit;
      l_counter := l_counter + 1;
      exit create_headers when l_counter > co_upper_bound;
   end loop create_headers;
end;
/

Issues

Line	Column	Message
27	7	Missing procedure for transaction in non-cursor loop.

Compliant Solution - ★★★★★

declare
   co_upper_bound constant integer               := 5;
   co_max_level   constant integer               := 3;
   co_number      constant types_up.short_string := 'Number';
   co_line        constant types_up.short_string := 'Line';
   co_space       constant types_up.short_string := ' ';
   procedure create_rows(
      in_header_id in headers.id%type
   ) is
      co_header_id constant headers.id%type := in_header_id;
   begin
      insert into headers (id,text)
      values (in_header_id,co_number
         || co_space
         || co_header_id);

      insert into lines (header_id,line_no,text)
      select co_header_id
            ,rownum
            ,co_line
             || co_space
             || rownum
        from dual
     connect by level <= co_max_level;

      commit;
   end create_rows;
begin
   <<create_headers>>
   for l_counter in 1..co_upper_bound
   loop
      create_rows(l_counter);
   end loop create_headers;
end;
/

References

same as Trivadis G-3320