from SonarSource:

"FORALL" statements should use the "SAVE EXCEPTIONS" clause

When the FORALL statement is used without the SAVE EXCEPTIONS clause and an exception is raised by a DML query, the whole operation is rolled back and the exception goes unhandled. Instead of relying on this default behavior, it is better to always use the SAVE EXCEPTIONS clause and explicitly handle exceptions in a ORA-24381 handler.

Noncompliant Code Example

CREATE TABLE my_table(
  id NUMBER(10) NOT NULL
);

DECLARE
  TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
  my_table_ids my_table_id_type := my_table_id_type();
BEGIN
  FOR i IN 1 .. 10 LOOP
    my_table_ids.EXTEND;
    my_table_ids(my_table_ids.LAST) := i;
  END LOOP;

  -- Cause the failure
  my_table_ids(10) := NULL;

  FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST  -- Noncompliant
    INSERT INTO my_table
    VALUES (my_table_ids(i));
END;
/

SELECT COUNT(*) FROM my_table;

DROP TABLE my_table;

Compliant Solution

-- ...

DECLARE
  TYPE my_table_id_type IS TABLE OF my_table.id%TYPE;
  my_table_ids my_table_id_type := my_table_id_type();

  bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
BEGIN
  FOR i IN 1 .. 10 LOOP
    my_table_ids.EXTEND;
    my_table_ids(my_table_ids.LAST) := i;
  END LOOP;

  -- Cause the failure
  my_table_ids(10) := NULL;

  FORALL i IN my_table_ids.FIRST .. my_table_ids.LAST SAVE EXCEPTIONS
    INSERT INTO my_table
    VALUES (my_table_ids(i));
EXCEPTION
  WHEN bulk_errors THEN
    -- Explicitly rollback the whole transaction,
    -- or handle each exception individually by looping over SQL%BULK_EXCEPTIONS
    ROLLBACK;
END;
/

New Rule: use SAVE EXCEPTIONS in FORALL statement #23

Description

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions