Tom,

We have an application on Oracle Standard (so no Partition Features) and we did a virtual partition management of some tables.

The question is, if possible, how to get a forall statement (such as an insert) fired from a view bassed trigger instead of.

As I see in the trace, the forall statement is correctly fired with one execute with multiple rows, but on the real table, I see one execute per row.

How can I code the trigger in order to get on the real table one execute with multiple rows?
Are there any alternative solution for this scenario?

Here are my test tables and view, and the trace result:

Tables:

create table AVDM_TEST_BULK_01 AS SELECT * FROM user_objects WHERE ROWNUM <= 1000;
create table AVDM_TEST_BULK_02 AS SELECT * FROM user_objects WHERE ROWNUM <= 1000;

VIEW (pseudo partitioned table)

CREATE OR REPLACE VIEW AVDM_TEST_BULK AS
SELECT * FROM AVDM_TEST_BULK_01
UNION ALL 
SELECT * FROM AVDM_TEST_BULK_02
/

Trigger Instead Of
(The real one have more logic in it. This is only a sample trigger)

CREATE OR REPLACE TRIGGER AVDM_TEST_BULK_IOF
 INSTEAD OF
  INSERT OR DELETE OR UPDATE
 ON AVDM_TEST_BULK
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
IF INSERTING THEN 
    INSERT INTO AVDM_TEST_BULK_01
    (
    object_name                    ,
    subobject_name                 ,
    object_id                      ,
    data_object_id                 ,
    object_type                    ,
    created                        ,
    last_ddl_time                  ,
    timestamp                      ,
    status                         ,
    temporary                      ,
    generated                      ,
    secondary                      )
    VALUES
    (
    :new.object_name                    ,
    :new.subobject_name                 ,
    :new.object_id                      ,
    :new.data_object_id                 ,
    :new.object_type                    ,
    :new.created                        ,
    :new.last_ddl_time                  ,
    :new.timestamp                      ,
    :new.status                         ,
    :new.temporary                      ,
    :new.generated                      ,
    :new.secondary                      );
END IF ;
END ;
/

Finally, the unit test code:

DECLARE
   TYPE avdm_test_bulk_aat IS TABLE OF avdm_test_bulk%ROWTYPE
      INDEX BY BINARY_INTEGER;

   l_avdm_test_bulk   avdm_test_bulk_aat;
BEGIN
   SELECT *
   BULK COLLECT INTO l_avdm_test_bulk
     FROM avdm_test_bulk;

   FORALL indx IN l_avdm_test_bulk.FIRST .. l_avdm_test_bulk.LAST
      INSERT INTO avdm_test_bulk
          values l_avdm_test_bulk (indx);

END;
/

Trace

INSERT INTO AVDM_TEST_BULK 
VALUES
 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ) 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.10       0.08          0          0          0        2000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.10       0.08          0          0          0        2000


INSERT INTO AVDM_TEST_BULK_01 ( OBJECT_NAME , SUBOBJECT_NAME , OBJECT_ID , 
  DATA_OBJECT_ID , OBJECT_TYPE , CREATED , LAST_DDL_TIME , TIMESTAMP , STATUS 
  , TEMPORARY , GENERATED , SECONDARY ) 
VALUES
 ( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   2000      0.02       0.04          0         29       2208        2000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.02       0.04          0         29       2208        2000