Difference in FORALL statement behaviour in 11G (Doc ID 851982.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
PL/SQL - Version 11.1.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 12-Sep-2012***

Symptoms

Difference in behaviour of FORALL in 11g.

FORALL inserts only first row in the loop.

The below example shows the behaviour of FORALL in both 10g and 11g

When the sample is tested on Oracle version 10G
===================================

SQL> set serveroutput on size unlimited
create table t1 (id int);
create table t2 (id int);

DECLARE
   TYPE idList IS VARRAY(3) OF NUMBER;
   ids idList := idList(0,1,2);
BEGIN
-- Batch Insert
   FORALL i IN 1..3
         insert into t1 select ids(i) from dual where ids(i) = (select ids(i) from dual);
    -- Loop Insert
   for i in 1..3 loop
   begin
      insert into t2 select ids(i) from dual where ids(i) = (select ids(i) from dual);
      DBMS_Output.Put_Line(ids(i));
   end;
    end loop;
END;
/

SQL>
Table created.

SQL>
Table created.

SQL>

0
1
2

PL/SQL procedure successfully completed.

SQL> select * from t1;

        ID
----------
         0
         1
         2

SQL> select * from t2;

        ID
----------
         0
         1
         2

Oracle version 11.1
==============

SQL> set serveroutput on size unlimited
create table t1 (id int);
create table t2 (id int);

SQL>
Table created.

SQL>
Table created.

SQL> DECLARE
  2     TYPE idList IS VARRAY(3) OF NUMBER;
  3     ids idList := idList(0,1,2);
  4  BEGIN
  5  -- Batch Insert
  6     FORALL i IN 1..3
  7           insert into t1 select ids(i) from dual where ids(i) = (select ids(i) from dual);
  8      -- Loop Insert
  9     for i in 1..3 loop
10     begin
11        insert into t2 select ids(i) from dual where ids(i) = (select ids(i) from dual);
12        DBMS_Output.Put_Line(ids(i));
13     end;
14      end loop;
15  END;
16  /

SQL>
0
1
2

PL/SQL procedure successfully completed.

SQL> select * from t1;

        ID
----------
         0

SQL> select * from t2;

        ID
----------
         0
         1
         2



As you can see above , when the same sample code is run on 11.1.0, only one row is inserted in table t1.

But ideally three rows should be inserted.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms