Merge statement array as source table

Question

Is it possible to use array as source for merge statement

E.g. I have the below merge statement and when I compile, I am getting error. How can I use merge with array as source table?

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND 
table of records 

Procedure code

CREATE or REPLACE PROCEDURE my_proc (varray IN my_array)
     AS
     BEGIN
          FORALL i IN varray.first..varray.last
          MERGE INTO tab t
          USING dual
          ON ( t.proj_id = varray(i).proj_id)
          WHEN MATCHED THEN
          UPDATE  set
          proj_title = varray (i).proj_title
                      WHERE   proj_id = varray (i).proj_id
            WHEN NOT MATCHED THEN
            insert (proj_id,proj_title)
            values (varray (i).proj_id,varray (i).proj_title);
         values (varray (i).proj_id,varray (i).proj_title);

Answer 1

There is a restriction in Oracle 10g - you can't access individual fields of records in FORALL statement. You could do it if you were using Oracle 11g.

There are workarounds, however, and I recommend the following article that proposes a few of them: PLS-00436 in 10g - Workaround.

Answer 2

The problem here is that you are referring to the same collection in your SET clause and WHERE clause. See Oracle Documentation for Forall statement, go to the Restrictions section, second bullet point.

I would suggest you rename your varray collection as something different, as it is a keyword. I would also suggest you separate this collection into multiple scalar collections (varrays or nested tables having only one column) for each column and then use these collections in your forall statement.