Update a table with dynamic query using bulk collect

Question

I want update a table using a dynamic query, cursor and bulk collect. But I don't know the syntax:

declare
    my_cursor   ?;
    -- other objects;
begin
    execute immediate
        "select s.col1, s.col2, rowid, d.rowid 
        from source_table s, destination_table d
        where s.id = d.id "
    BULK COLLECT INTO my_cursor;

    FORALL i IN my_cursor.FIRST..my_cursor.LAST
        UPDATE destination_table set col_a=my_cursor(i).col1 , col_b=my_cursor(i).col2 
        WHERE rowid = my_cursor(i).rowid;

    commit;
end;

what would be the correct syntax and oracle objects please help.

Answer 1

You can use something like this:

declare
  type REC_TYPE is record (
    ID SOURCE_TABLE.ID%type,
    COL1 SOURCE_TABLE.COL1%type,
    COL2 SOURCE_TABLE.COL2%type
  );
  type REC_CURSOR is ref cursor;
  ref_cursor REC_CURSOR;
  rec REC_TYPE;
  sql_query VARCHAR2(4000);
begin
  sql_query := 'select s.ID, COL1, COL2 from SOURCE_TABLE s, DESTINATION_TABLE d where s.ID = d.ID';

  open ref_cursor for sql_query;

  loop
    fetch ref_cursor into rec;
    exit when ref_cursor%NOTFOUND;

    update DESTINATION_TABLE
    set COL_A = rec.COL1, COL_B = rec.COL2 
    WHERE ID = rec.ID;
  end loop;

  close ref_cursor;
  commit;
end;
/

or with bulk collect:

declare
  type REC_TYPE is record (
    ID SOURCE_TABLE.ID%type,
    COL1 SOURCE_TABLE.COL1%type,
    COL2 SOURCE_TABLE.COL2%type
  );
  type REC_TYPES is table of REC_TYPE;
  type REC_CURSOR is ref cursor;
  ref_cursor REC_CURSOR;
  recs REC_TYPES;

  sql_query VARCHAR2(4000);
begin
  sql_query := 'select s.ID, COL1, COL2 from SOURCE_TABLE s, DESTINATION_TABLE d where s.ID = d.ID';

  open ref_cursor for sql_query;
  fetch ref_cursor bulk collect into recs;
  close ref_cursor;

  FOR ind IN recs.FIRST .. recs.LAST
  loop
    update DESTINATION_TABLE
    set COL_A = recs(ind).COL1, COL_B = recs(ind).COL2 
    WHERE ID = recs(ind).ID;
  end loop;

  commit;
end;
/