create a proc with dynamic sql and merge
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: create a proc with dynamic sql and merge

Hybrid View

  1. #1
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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;
    /

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    tks pando, did a little teaking and it works like a charm
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hey mind to share the code? who knows probably need something like this some day

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
  •  


Click Here to Expand Forum to Full Width