create a proc with dynamic sql and merge

[pando]

I modified a bit to get the insert part dynamic

declare
  string varchar2(1000);
  l_owner varchar2(36);
  l_table_name varchar2(36);
  function process_string(p_owner varchar2, p_table_name varchar2)
  return varchar2
  as
    l_string varchar2(1000);
  begin
    l_string := 'insert values('||chr(10);
    for i in (select * from dba_tab_columns where table_name=p_table_name and owner=p_owner)
    loop
      l_string := l_string||'b.'||i.column_name||',';
    end loop;
    l_string := substr(l_string, 1, instr(l_string, ',', -1)-1)||')';
    return l_string;
  end;
begin
  for x in (select * from user_tables where table_name='EMP2')
  loop
    select owner, table_name
      into l_owner, l_table_name
      from dba_tables
     where table_name=x.table_name
       and owner != USER;
    string := process_string(l_owner, l_table_name);
    string := 'merge into '||x.table_name||' a'||
              ' using '||l_owner||'.'||l_table_name||' b'||
              ' on (a.empno = b.empno) when matched then update set a.job = b.job when not matched then '||string;
    dbms_output.put_line(string);
    execute immediate string;
  end loop;
end;
/