Geek Answers Handbook

Join an array of statements as the source table

Is it possible to use an array as a source for a merge operator

eg. I have the following merge statement, and when I compile, I get an error. How to use merge with an array as a 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);
+4
source share
3 answers

There is a limitation in Oracle 10g — you cannot access individual record fields in an instruction FORALL. You can do this if using Oracle 11g.

, , : PLS-00436 10g - .

+5

, SET WHERE. . Oracle Documentation for Forall statement, , .

varray , . (varrays , ) , forall-.

+2

MERGE, DUAL:

SQL> set serveroutput on
SQL> 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 Two

2 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 merged

PL/SQL procedure successfully completed.

SQL> select *
  2    from projects;

   PROJ_ID PROJ_TITLE
---------- --------------------
         1 Project One
         2 New Project Two
         3 New Project Three

3 rows selected.
+2

More articles:


All Articles