Update multiple columns based on values of another table in stored procedure

Question

I am trying to write a dynamic merge procedure that is able to update multiple columns based on values of another table. I try to store query result as a string which I can then use to build my dynamic query.

create or replace PROCEDURE SC2_MERGE(
    source_table_name IN VARCHAR2,
    destination_table_name IN VARCHAR2,
    bk_column_name IN VARCHAR2 DEFAULT 'bk_hash_key',
)

AS
 merge_cmd varchar(32767);
 update_set_cmd varchar(32767);

 
  
CURSOR update_record IS
    SELECT  listagg('t.'|| column_name ||' = '|| 's.'|| column_name, ',')
  FROM all_tab_cols
 WHERE table_name = destination_table_name
    and column_name not in ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');
    v_update_record  update_record%ROWTYPE;

BEGIN  

    
     FOR v_update_record IN update_record LOOP
              update_set_cmd:= v_update_record;
     END LOOP;
      
    
   merge_cmd:= 'MERGE INTO' || destination_table_name || ' t
                         USING ' || source_table_name || ' s
                         ON (t.' || bk_column_name || '= s.'  ||  bk_column_name || ')
                         WHEN MATCHED THEN
                                          UPDATE SET ' || update_set_cmd;


 execute IMMEDIATE merge_cmd;

END;

However, when I try to compile the procedure I'll receive:

32/15 PL/SQL: Statement ignored
32/33 PLS-00382: expression is of wrong type Errors: check compiler log

I also tried all_tab_cols.column_name%TYPE instead of update_record%ROWTYPE, but receive the same error.

The output of the cursor query looks like this:

Any help or pointing in the right direction is highly appreciated!

Answer 1

You don't need to bother defining a cursor record if you're using a cursor for loop - that's the beauty of a cursor for loop!

Your code could be rewritten to be something like (N.B. untested):

CREATE OR REPLACE PROCEDURE sc2_merge(source_table_name      IN VARCHAR2,
                                      destination_table_name IN VARCHAR2,
                                      bk_column_name         IN VARCHAR2 DEFAULT 'bk_hash_key',)

 AS
  merge_cmd      VARCHAR(32767);

  CURSOR update_record IS
    SELECT listagg('t.' || column_name || ' = ' || 's.' || column_name, ',') col_list
    FROM   all_tab_cols
    WHERE  table_name = destination_table_name
    AND    column_name NOT IN ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');

BEGIN

  FOR update_set_cmd IN update_record
  LOOP
    merge_cmd := 'MERGE INTO ' || destination_table_name || ' t
                         USING ' || source_table_name || ' s
                         ON (t.' || bk_column_name || '= s.' || bk_column_name || ')
                         WHEN MATCHED THEN
                                          UPDATE SET ' || update_set_cmd.col_list;

    EXECUTE IMMEDIATE merge_cmd;
  END LOOP;

END;
/

Note that in order to be able to reference the field in the record, I gave the calculated column in the cursor an alias (col_list). Then to access the field, you use the format <record_name>.<field_name>, i.e. update_set_cmd.col_list.

Also, I moved the merge statement inside the loop, since you're only going to be looping over a single row.

Because that's the case, you don't actually need to loop at all, you can simply select your column into a variable:

CREATE OR REPLACE PROCEDURE sc2_merge(source_table_name      IN VARCHAR2,
                                      destination_table_name IN VARCHAR2,
                                      bk_column_name         IN VARCHAR2 DEFAULT 'bk_hash_key',)

 AS
  merge_cmd  VARCHAR2(32767);
  v_col_list VARCHAR2(32767);

BEGIN

  SELECT listagg('t.' || column_name || ' = ' || 's.' || column_name, ',') col_list
  INTO   v_col_list
  FROM   all_tab_cols
  WHERE  table_name = destination_table_name
  AND    column_name NOT IN ('PK_HASH_KEY', 'BK_HASH_KEY', 'VALID_FROM', 'VALID_UNTIL');

  merge_cmd := 'MERGE INTO ' || destination_table_name || ' t
                         USING ' || source_table_name || ' s
                         ON (t.' || bk_column_name || '= s.' || bk_column_name || ')
                         WHEN MATCHED THEN
                                          UPDATE SET ' || v_col_list;

  EXECUTE IMMEDIATE merge_cmd;

END;
/

You won't need to worry about error handling the case when no rows or too many rows are returned for this implicit cursor because you are using an aggregate function on the whole set of data (i.e. you don't have a group by clause) - this will always return a single row.