행위

오라클 테이블 복사 프로시져

DB CAFE

1 오라클 테이블 간 데이터 복사 프로시져

1.1 sp_dbm_copy

CREATE OR REPLACE PROCEDURE sp_dbm_copy 
    (  p_tname    in varchar2 default null
     , p_dblink   in varchar2 default null 
     , p_option   in varchar2 default 'I' -- I:insert, R:replace(truncate), A:append
     , p_exec     in number default 0 
     , p_owner    in varchar2 default ''
     , p_srcowner in varchar2 default null 
     , p_srctname in varchar2 default null 
    ) authid current_user
is 
/* =============================================================================
XXX_DBA 계정에 select any table 권한 필요: 없을 경우 다계정에 대해 NO_COLUMN_LIST 발생
create table XXX_DBA.tb_dbm_copy_log 
(
    logdt      date not null,             -- 작업일자
    owner      varchar2 (100) not null,   -- 테이블 소유자
    tname      varchar2 (100) not null,   -- 테이블명
    dblink     varchar2 (100),            -- DB LINK
    sowner     varchar2 (100),            -- 원본 테이블 소유자
    stname     varchar2 (100),            -- 원본 테이블
    coption    varchar2 (10),             -- 옵션(I:Insert, A:Append, R:Truncate and Insert)
    result     varchar2 (5),              -- 결과(S:Sucess, F:Fail)
    rowcount   number,                    -- 건수
    errmsg     varchar2 (300),            -- 에러메시지
    constraint pk_dbm_copy_log primary key (logdt, owner, tname)
);
============================================================================= */
    NO_COLUMN_LIST EXCEPTION; 
    NO_PRIMARYKEY  EXCEPTION; 
    v_tgt_tname varchar2(50) default null; 
    v_tgt_owner varchar2(30) default null; 
    v_src_owner varchar2(30) default null; 
    v_src_tname varchar2(50) default null; 
    v_option    varchar2(2);
     
    type typ_rec_cols is record 
        (colid user_tab_columns.column_id%TYPE, colname all_tab_columns.column_name%TYPE); 
    type typ_tab_cols is table of typ_rec_cols; 
    tab_cols  typ_tab_cols; 
 
    v_dblink    varchar2(100) default ' '; 
    v_sqltxt    long; 
    v_sqlwhere  varchar2(1000);
    v_colist    long; 
    v_msg       long; 
    v_rowcnt    number(10) default 0; 
    V_NL        char(1) default chr(10); 
    V_DELIM       varchar(10) default ','; 
begin 
    if p_tname is null then 
        dbms_output.enable(100); 
        dbms_output.put_line('usage: sp_dbm_copy(          ');
        dbms_output.put_line('          p_tname    => null ');
        dbms_output.put_line('        , p_dblink   => null ');
        dbms_output.put_line('        , p_option   => ''i'' -- I:insert, R:replace(truncate), A:append');
        dbms_output.put_line('        , p_exec     => 0    ');
        dbms_output.put_line('        , p_owner    => null ');
        dbms_output.put_line('        , p_srcowner => null ');
        dbms_output.put_line('        , p_srctname => null)');        
        dbms_output.put_line(' p_option=I:Insert, R:Replace(truncate and insert), A:Append'); 
        return; 
    end if; 

    v_tgt_owner := upper(p_owner); 
    v_tgt_tname := upper(p_tname); 
    v_option    := upper(p_option);
    if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if; 
    if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if; 
    if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if; 
    ------------------------------------------------------------------
    v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('; 
    if p_exec <> 1 then  
        dbms_output.enable(1000);  
        if v_option = 'R' then dbms_output.put_line('truncate table '||v_tgt_owner||'.'||v_tgt_tname||';'); end if; 
        dbms_output.put_line(v_sqltxt);  
    end if; 
    -- column list
    execute immediate  
        'select t.column_id, t.column_name 
         from all_tab_columns t, all_tab_columns'||v_dblink|| ' s '|| 
        ' where t.owner = :1 
            and t.table_name = :2 
            and s.owner = :3 
            and s.table_name = :4 
            and t.column_name=s.column_name 
           order by t.column_id'  
        bulk collect into tab_cols  
        using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname; 
    if SQL%ROWCOUNT<1 then raise NO_COLUMN_LIST; end if; 
     
    for i in tab_cols.first..tab_cols.last 
    loop 
        if p_exec=1 then  
            if i=tab_cols.first then 
                v_colist := trim(tab_cols(i).colname); 
            else 
                v_colist := v_colist ||V_NL||V_DELIM|| trim(tab_cols(i).colname); 
            end if; 
        else 
            if i=tab_cols.first then 
                dbms_output.enable(1000); dbms_output.put_line('     '||tab_cols(i).colname);  
            else 
                dbms_output.enable(1000); dbms_output.put_line('    ,'||tab_cols(i).colname);  
            end if; 
        end if; 
    end loop; 
    
    if v_option = 'A' then
        for icur in (select l.column_name, l.position from dba_cons_columns l, dba_constraints c 
                    where l.owner=c.owner and l.constraint_name=c.constraint_name
                      and c.constraint_type='P' 
                      and c.owner=v_tgt_owner and c.table_name=v_tgt_tname
                    order by position)
        loop
            if icur.position = 1 then
                -- v_sqlwhere := icur.column_name;
                v_sqlwhere := 'B.'||icur.column_name||' = A.'||icur.column_name;
            else
                --v_sqlwhere := v_sqlwhere||', '||icur.column_name;
                v_sqlwhere := v_sqlwhere||' AND B.'||icur.column_name||' = A.'||icur.column_name;
            end if;
        end loop;
        if v_sqlwhere is null then raise NO_PRIMARYKEY; end if;
        -- v_sqlwhere := 'WHERE ('||v_sqlwhere||') NOT IN ( SELECT '||v_sqlwhere||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')';
        v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' B WHERE '||v_sqlwhere||')';
    end if;
 
if p_exec=1 then 
       if v_option = 'R' then execute immediate 'truncate table '||v_tgt_owner||'.'||v_tgt_tname; end if; 
 
        v_sqltxt := 'INSERT INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('|| v_colist ||')'||V_NL 
                    ||'SELECT '||v_colist||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A '; 
        if v_option = 'A' then  v_sqltxt := v_sqltxt ||chr(10)||v_sqlwhere; end if;

        begin
            execute immediate v_sqltxt; 
            v_rowcnt := to_char(SQL%ROWCOUNT); 
            v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(v_rowcnt,10,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink;
            commit;
            --(--------------------------------------------------------------------------------------------------------
            execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, rowcount)'
                      ||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)'
                      using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'S', to_number(v_rowcnt);
            commit;                      
            --)--------------------------------------------------------------------------------------------------------
        exception
            when others then
            rollback;
            --(--------------------------------------------------------------------------------------------------------
            execute immediate 'insert into XXX_DBA.tb_dbm_copy_log(logdt, owner, tname, dblink, sowner, stname, coption, result, errmsg)'
                      ||' values( sysdate, :1, :2, :3, :4, :5, :6, :7, :8)'
                      using v_tgt_owner, v_tgt_tname, v_dblink, v_src_owner, v_src_tname, v_option, 'F', substr(SQLERRM,1,250) ;
            commit;
            --)--------------------------------------------------------------------------------------------------------
            v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
            dbms_output.enable(100); 
            dbms_output.put_line(v_msg); 
        end;
        dbms_output.enable(2000); 
        dbms_output.put_line(v_msg); 
    else 
        dbms_output.enable(1000); 
        dbms_output.put_line('    )'); 
        dbms_output.put_line('  SELECT '); 
        for i in tab_cols.first..tab_cols.last 
        loop 
            if i=tab_cols.first then 
                dbms_output.enable(1000); dbms_output.put_line('     '||tab_cols(i).colname);  
            else 
                dbms_output.enable(1000); dbms_output.put_line('    ,'||tab_cols(i).colname);  
            end if; 
        end loop; 
        dbms_output.put_line('  FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' A '); 
        if v_option = 'A' then dbms_output.put_line(v_sqlwhere); end if;
        dbms_output.put_line(';'); 
        
    end if; 
     
exception 
    when NO_COLUMN_LIST then 
        v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
    when NO_PRIMARYKEY then 
        v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
    when others then 
        v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
end;

1.2 sp_dbw_copy

CREATE OR REPLACE PROCEDURE sp_dbw_copy
    (  p_tname    in varchar2 default null
     , p_dblink   in varchar2 default null 
     , p_option   in varchar2 default 'I' -- I,R,DI,M,AI,AX
     , p_exec     in number default 0 
     , p_owner    in varchar2 default null
     , p_srcowner in varchar2 default null 
     , p_srctname in varchar2 default null 
    ) 
is 
    NO_COLUMN_LIST EXCEPTION; 
    NO_PRIMARYKEY  EXCEPTION; 
    v_tgt_tname varchar2(50) default null; 
    v_tgt_owner varchar2(30) default null; 
    v_src_owner varchar2(30) default null; 
    v_src_tname varchar2(50) default null; 
    v_option    varchar2(2);
     
    type typ_rec_cols is record 
        (colid user_tab_columns.column_id%TYPE, colname all_tab_cols.column_name%TYPE, pno number); 
    type typ_tab_cols is table of typ_rec_cols; 
    
    tab_cols          typ_tab_cols; 
    v_dblink          varchar2(100)  default ' ';
    v_sql_select      varchar2(1000) default ' ';
    v_sql_dataRemove  varchar2(1000) default ' ';
    v_sqlwhere        varchar2(4000);
    v_sql_copyData    long; 
    v_colist          long; 
    v_keycols         varchar2(4000);
    v_merge_join      varchar2(4000);
    v_merge_update    varchar2(4000);
    v_merge_insert    varchar2(4000);
    v_msg             varchar(4000); 
    v_rowcnt          number(15)  default 0; 
    v_start_dtm       date; 
    v_secs            varchar2(10);
    V_NL              char(1)     default chr(10); /* new line */
    V_TAB             char(4)     default '    ';  /* tab      */
    V_DELIM           varchar2(10) default ',';     /* delimter */
begin 
    v_tgt_owner := upper(p_owner); 
    v_tgt_tname := upper(p_tname); 
    v_option    := upper(p_option);

    if p_tname is null or v_option not in ('I','R','DI','M','AI','AX')
    then 
        v_msg := 
             V_NL||'usage: '
           ||V_NL||' exec sp_dbw_copy('
           ||V_NL||'          p_tname    => ''table_name'' '
           ||V_NL||'        , p_dblink   => null '
           ||V_NL||'        , p_option   => ''i'' -- I/R/DI/M/AI/AX '
           ||V_NL||'        , p_exec     => 0 '
           ||V_NL||'        , p_owner    => '''' '
           ||V_NL||'        , p_srcowner => null '
           ||V_NL||'        , p_srctname => null); '
           ||V_NL||'   p_option=> I:Insert, R:Replace(truncate and insert), DI:Delete Insert '
           ||V_NL||'      , M:Merge, AI:Append(not In), AX:Append(not eXists) '
        ;

        dbms_output.enable(500); 
        dbms_output.put_line(v_msg);
        return; 
    end if; 

    if (p_srcowner is null) then v_src_owner := v_tgt_owner; else v_src_owner := upper(p_srcowner); end if; 
    if (p_srctname is null) then v_src_tname := v_tgt_tname; else v_src_tname := upper(p_srctname); end if; 
    if (p_dblink is not null) then v_dblink := '@'||p_dblink; end if; 
    if v_option = 'R'  then
        v_sql_dataRemove := 'truncate table '||v_tgt_owner||'.'||v_tgt_tname ;
    elsif v_option = 'DI' then 
        v_sql_dataRemove := 'delete from '||v_tgt_owner||'.'||v_tgt_tname; 
    end if; 
    -- column list
    v_sql_select := 'select tgt.column_id, tgt.column_name, p.position pno '
          ||V_NL||'  from all_tab_cols tgt '
          ||V_NL||'       join all_tab_cols'||v_dblink||' src '
          ||V_NL||'         on (src.column_name = tgt.column_name) '
          ||V_NL||'       left join all_constraints c  '
          ||V_NL||'         on (c.owner = tgt.owner and c.table_name = tgt.table_name and c.constraint_type = ''P'') '
          ||V_NL||'       left join all_cons_columns p  '
          ||V_NL||'         on (p.owner = c.owner and p.table_name = c.table_name and p.constraint_name = c.constraint_name '
          ||V_NL||'        and p.owner = tgt.owner and p.table_name = tgt.table_name and p.column_name = tgt.column_name) '
          ||V_NL||' where tgt.owner = :1 '
          ||V_NL||'   and tgt.table_name = :2 '
          ||V_NL||'   and src.owner = :3 '
          ||V_NL||'   and src.table_name = :4 '
          ||V_NL||'   and tgt.virtual_column <> ''YES'' '
          ||V_NL||' order by tgt.column_id  '
    ;

    execute immediate v_sql_select         
      bulk collect into tab_cols  
      using v_tgt_owner, v_tgt_tname, v_src_owner, v_src_tname; 
    if SQL%ROWCOUNT < 1 then 
      dbms_output.enable(10000);
      dbms_output.put_line(v_sql_select);
      raise NO_COLUMN_LIST; 
    end if; 
     
    for i in tab_cols.first..tab_cols.last 
    loop 
        /* insert column list */
        if i=tab_cols.first then 
            v_colist := V_TAB||'  SRC.'||trim(tab_cols(i).colname);
        else 
            v_colist := v_colist ||V_NL||V_TAB||V_DELIM||' SRC.'|| trim(tab_cols(i).colname); 
        end if; 
        
        /* merge on clause, update set clause */
        if tab_cols(i).pno is not null then
            v_merge_join := v_merge_join||' AND SRC.'||tab_cols(i).colname||' = TGT.'||tab_cols(i).colname;
            v_keycols := v_keycols ||', '||tab_cols(i).colname;
        else 
            v_merge_update := v_merge_update||', TGT.'||tab_cols(i).colname||' = SRC.'||tab_cols(i).colname||V_NL||V_TAB;
        end if;
    end loop;

    v_merge_join := substr(v_merge_join, 5, 10000);
    v_keycols := substr(v_keycols, 2,10000);
    v_merge_update := substr(v_merge_update, 2, 10000);
    
    if v_option in ('M','AI', 'AX') then
        if v_merge_join is null then raise NO_PRIMARYKEY; end if;
        if v_option = 'AI' then 
            v_sqlwhere := 'WHERE ('||v_keycols||') NOT IN ( SELECT '||v_keycols||' FROM '||v_tgt_owner||'.'||v_tgt_tname||')';
        elsif v_option = 'AX' then
            v_sqlwhere := 'WHERE NOT EXISTS ( SELECT 1 FROM '||v_tgt_owner||'.'||v_tgt_tname||' TGT WHERE '||v_merge_join||')';
        end if;
        
    end if;

    if v_option = 'M' then
        v_sql_copyData := 'MERGE INTO '||v_tgt_owner||'.'||v_tgt_tname||' TGT '
                  ||V_NL||'USING '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC '
                  ||V_NL||'   ON ('||v_merge_join||' ) '
                  ||V_NL||'WHEN MATCHED THEN UPDATE '
                  ||V_NL||'     SET '
                  ||V_NL||'     '||v_merge_update
                  ||V_NL||'WHEN NOT MATCHED THEN '
                  ||V_NL||'     INSERT ('
                  ||V_NL||      replace(replace(v_colist, ' SRC.',' '), V_TAB, V_TAB||V_TAB)||') '
                  ||V_NL||'     VALUES ('
                  ||V_NL||replace(v_colist, V_TAB, V_TAB||V_TAB)||')'
        ;
    else
        v_sql_copyData := 'INSERT '|| case when v_option in ('R','AI','AX') then '/*+ APPEND */' end 
                  ||V_NL||'  INTO '||v_tgt_owner||'.'||v_tgt_tname ||'('
                  ||V_NL|| replace(v_colist , ' SRC.', ' ')
                  ||V_NL||')'
                  ||V_NL||'SELECT /*+ PARALLEL(SRC 1) */'
                  ||V_NL||v_colist
                  ||V_NL||'FROM '||v_src_owner||'.'||v_src_tname||v_dblink||' SRC '; 
        if v_option IN ('AI', 'AX') then  v_sql_copyData := v_sql_copyData ||chr(10)||v_sqlwhere; end if;
    end if;
 

    if p_exec=1 then 
        begin
            select sysdate into v_start_dtm from dual;
            if v_option in ('R','DI') then execute immediate v_sql_dataRemove; end if; 
            execute immediate v_sql_copyData; 
            v_rowcnt := SQL%ROWCOUNT; 
            v_msg := v_tgt_owner||'.'||v_tgt_tname||':(option='||v_option||'):'||lpad(to_char(v_rowcnt, 'fm999,999,999,999'),15,'.')||' rows from '||v_src_owner||'.'||v_src_tname||v_dblink;
            commit;
            --(--------------------------------------------------------------------------------------------------------
            select to_char(trunc(tm /3600), 'fm00')
                   ||':'||to_char(mod(trunc(tm/60),60),'fm00')
                   ||':'||to_char(mod(tm,60), 'fm00')
            into v_secs 
            from (select (sysdate - v_start_dtm) * 24 * 60 * 60 tm from dual)
            ;

            insert into XXX_DBA.tb_dbw_copy_log
                   (logid, owner, table_name, result, rowcount
                  , src_owner, src_table_name, dblink, copyopt, start_time, end_time, errmsg)
             values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', to_number(v_rowcnt)
                  , v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate, v_secs);
            commit;                      
            --)--------------------------------------------------------------------------------------------------------
        exception when others then
            v_msg := SQLERRM;
            rollback;
            --(--------------------------------------------------------------------------------------------------------
            insert into XXX_DBA.tb_dbw_copy_log
                   (logid, owner, table_name, result, errmsg
                  , src_owner, src_table_name, dblink, copyopt, start_time, end_time)
             values(sq_dbw_copy_log.nextval, v_tgt_owner, v_tgt_tname, 'S', substr(v_msg, 1, 250)
                  , v_src_owner, v_src_tname, v_dblink, v_option, v_start_dtm, sysdate);
            commit;   
            --)--------------------------------------------------------------------------------------------------------
            v_msg := 'ERROR : '|| substr(v_msg,1,500); 
            dbms_output.enable(1000); 
            dbms_output.put_line(v_msg); 
        end;
        dbms_output.enable(1000); 
        dbms_output.put_line(v_msg); 
    else 
        dbms_output.enable(10000); 
        dbms_output.put_line(''); 
        if v_option in ('R','DI') then dbms_output.put_line(v_sql_dataRemove||';'); end if; 
        dbms_output.put_line(v_sql_copyData||';');
    end if; 
     
exception 
    when NO_COLUMN_LIST then 
        v_msg := 'ERROR : [ NO_COLUMN_LIST ] '||v_tgt_owner||'.'||v_tgt_tname; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
    when NO_PRIMARYKEY then 
        v_msg := 'ERROR : [ NO_PRIMARYKEY ] '||v_tgt_owner||'.'||v_tgt_tname; 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
    when others then 
        v_msg := 'ERROR : ['|| to_char(SQLCODE) ||']'|| substr(SQLERRM,1,500); 
        dbms_output.enable(100); 
        dbms_output.put_line(v_msg); 
end;
/