oracle-databasebulkupdatedynamic-queries

Update a table with dynamic query using bulk collect


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.


Solution

  • 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;
    /