Merge statement array as source table

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.

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.

You can generate the source dataset in the MERGE clause by selecting it from DUAL:

SQL> set serveroutput onSQL> create table projects (  2     proj_id integer not null primary key,  3     proj_title varchar2(20)  4  );Table created.SQL> insert into projects (proj_id, proj_title) values (1, 'Project One');1 row created.SQL> insert into projects (proj_id, proj_title) values (2, 'Project Two');1 row created.SQL> commit;Commit complete.SQL> select *  2    from projects;   PROJ_ID PROJ_TITLE---------- --------------------         1 Project One         2 Project Two2 rows selected.SQL> declare  2     type varray_t is varray(2) of projects%rowtype;  3     arr varray_t;  4  begin  5     with test_data as (select 2 as proj_id, 'New Project Two' as proj_title from dual  6                        union all select 3 as proj_id, 'New Project Three' as proj_title from dual)  7     select proj_id, proj_title  8       bulk collect into arr  9       from test_data; 10 11     forall i in arr.first .. arr.last 12        merge into projects 13        using (select arr(i).proj_id as proj_id, 14                      arr(i).proj_title as proj_title 15                 from dual) mrg 16           on (projects.proj_id = mrg.proj_id) 17         when matched then update set projects.proj_title = mrg.proj_title 18         when not matched then insert (proj_id, proj_title) values (mrg.proj_id, mrg.proj_title); 19 20     dbms_output.put_line(sql%rowcount || ' rows merged'); 21 22     commit; 23  end; 24  /2 rows mergedPL/SQL procedure successfully completed.SQL> select *  2    from projects;   PROJ_ID PROJ_TITLE---------- --------------------         1 Project One         2 New Project Two         3 New Project Three3 rows selected.