-
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);
I'm stmontgo and I approve of this message
-
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
I'm stmontgo and I approve of this message
-
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
Last edited by pando; 12-14-2002 at 07:47 PM.
-
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
I'm stmontgo and I approve of this message
-
hey mind to share the code? who knows probably need something like this some day
-
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.
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|