create a proc with dynamic sql and merge

[pando]

hi

I cant test with remote database now, I tried with this

begin
for i in (select * from user_tables where table_name='EMP2')
loop
  execute immediate 
  'merge into '||i.table_name||
  ' using emp
   on (emp.empno = '||i.table_name||'.empno)
   when matched then update set '||i.table_name||'.job = emp.job
   when not matched then insert values(emp.empno, emp.ename, emp.job, emp.mgr, 
                                       emp.hiredate, emp.sal, emp.comm, emp.deptno)';
end loop;
end;
/

It works but of course it is not as dynamic as you want (ok I am too lazy here but because also I dont have two 9i databse to test). I guess you want all column names and table name being dynamic, I am sure it works but probably with tons of concat() functions....

Also my cursor is not what you want, yours would probably have two for loops one to obtain table names and the other to obtain column names. And you probably want to join local user_tables and remote user_tables (and tab_columns)

What I dont know how to do is make the INSERT part dynamic since one can have 3 columns, one 4 or 5 and so on.... I think if you can try ask this on asktom.oracle.com he must have some nice solutions for the INSERT part