create a proc with dynamic sql and merge

Hello,
We have two databases, the main production and the remote
db that are not normall connected through network connection.
The remote and prod db are somewhat the same in that they each have 60 tables that are the same structure and name on each db

The remote db will be loaded up with data and once in awhile
we will want to dump the data from the remote db into the prod db.
This is surely easy enough to do with the 9i MERGE statement.

My question is how to dynamically build the merge statements at run time in a pl/sql procedure

I am guessing some sort of execute immediate statement that is going against user_tab_cols.

tks

steve

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

in 9i sql and plsql engine are integrated so I dont think you need to use dynamic sql to execute merge... also merge is an DML which should run even the engine were not integrated

sure ok but do i need to write 60 merge statements and what if the table structure changes or a table is added/removed


easy enough to select from user_tab_cols
to
MERGE into EVENT
USING (SELECT * from EVENT where EVENTID > 10000)
WHEN NOT MATCHED THE INSERT INTO event a SELECT * FROM event b
WHEN MATCHED THE UPDATE SET....

the UPDATE is the problem, how can I dynamically build the update at run time from a query on user_tab_cols

hi

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

Code:

---


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

I modified a bit to get the insert part dynamic

Code:

---


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

---

tks pando, did a little teaking and it works like a charm

hey mind to share the code? who knows probably need something like this some day :D

here it is

create or replace function build_update(p_table_name varchar2) return varchar2
AS
l_string varchar2(1000);
l_owner varchar2(36);
l_table_name varchar2(36);
begin
l_string := '(';
for i in (select * from user_tab_columns where table_name = p_table_name )
loop
l_string := l_string || i.column_name||',';
end loop;
l_string := substr(l_string, 1, instr(l_string, ',', -1)-1)||')';
return l_string;
end;
/


then in my main proc


var_update := build_update(p_table_name);
....
FOR x IN (SELECT var_col_1 FROM user_tab_cols where table_name=p_table_name)
LOOP
EXECUTE IMMEDIATE ' UPDATE ' || p_table_name || '@dblink SET ' || var_update || ' = ' || var_update || ' FROM ' ||
p_table_name || ' WHERE ' || var_col_1 || ' = ' || x.var_col_1 || ')';


so that is what I am working on at the moment. I will be adding the logic to selectively update the records I want but tks pando for getting me started in dynamically building the string.