16f6c0aecf
Inline adoc files when they are included exactly once. Also fix language tags because this inlining gives us better information on what language the code is written in.
85 lines
1.9 KiB
Plaintext
85 lines
1.9 KiB
Plaintext
== Why is this an issue?
|
|
|
|
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
|
|
|
|
[source,sql]
|
|
----
|
|
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
|
|
|
|
[source,sql]
|
|
----
|
|
-- ...
|
|
|
|
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;
|
|
/
|
|
|
|
-- ...
|
|
----
|
|
|
|
ifdef::env-github,rspecator-view[]
|
|
|
|
'''
|
|
== Implementation Specification
|
|
(visible only on this page)
|
|
|
|
=== Message
|
|
|
|
Add the SAVE EXCEPTIONS clause to this FORALL statement and an explicit ORA-24381 exception handler.
|
|
|
|
|
|
endif::env-github,rspecator-view[]
|