CREATE OR REPLACE package DEPLOY.pkg_dp_transfer as

    
    function show_version return varchar2;


    procedure run_table (v_owner in TAB_REPOSITORY.owner%type, 
                        v_table_name in TAB_REPOSITORY.table_name%type);
                        
    procedure run_group (v_group in TAB_REPOSITORY.product%type);
    
     procedure run_schema (v_owner in TAB_REPOSITORY.owner%type, schema_only boolean default true);
    
end;
/


CREATE OR REPLACE package body DEPLOY.pkg_dp_transfer as

    c_version constant varchar2(32) := '01.00 / 20121105';
    c_remote_link constant varchar2(32):= 'STRMADMIN@VRS';
    type  tbsp_type is table of varchar2(128);
    v_ar_remap_tbsp_rule tbsp_type  := tbsp_type();
    
    
   

    
    
function show_version return varchar2
is

begin

    return 'pkg_dp_transfer version is: '||c_version;
end show_version;

 --
 --
 -- internal used procedures
 --

procedure   p_check_table (v_owner in TAB_REPOSITORY.owner%type,v_table_name in TAB_REPOSITORY.table_name%type)
is
    v_chk varchar2(1);

begin

 select 'A'  into v_chk from TAB_REPOSITORY where owner = v_owner and table_name = v_table_name and rownum < 2;



end p_check_table;


procedure run_dpump (p_table_list in varchar2)



   is
   
     h       NUMBER;
     l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
     l_job_state       VARCHAR2(30) := 'UNDEFINED';
     l_sts             KU$_STATUS;
     scn_number number;
     p_jobname varchar2(32) := substr('RUN_DPUMP_'||dbms_random.string('u',15),1,30) ;
  
  

   begin
   
    pkg_common_logging.init_log (
            'pkg_dp_transfer.run_dpump',
           user,
            'start'
        );
        
   pkg_common_logging.write_log('p_table_list: '||p_table_list);
   

   
   
    --
    -- init dpump
    --  
    
    h := DBMS_DATAPUMP.open(
    operation   => 'IMPORT',
    job_mode    => 'TABLE',
    remote_link => c_remote_link,
    job_name    => p_jobname ,
    version     => 'LATEST');

    pkg_common_logging.write_log('job_name: '||p_jobname||'; handle:'||h);
   
    
     pkg_common_logging.write_log('set log  file');
  DBMS_DATAPUMP.ADD_FILE ( handle    => h,
                            filename  => p_jobname||'.log',
                            directory => 'DATA_PUMP_DIR',
                            filetype  => DBMS_DATAPUMP.ku$_file_type_log_file );
    --
    -- parallel nur bei größeren tabellen
    --
    
    DBMS_DATAPUMP.SET_PARALLEL(
       handle  => h,
       degree  => 5);
       
       
    --
    -- remap tablespace
    -- v_ar_remap_tbsp_rule OLD_TBSP:NEW_TBSP, 
    --   
    IF v_ar_remap_tbsp_rule.COUNT > 0
         THEN
            FOR i IN 1 .. v_ar_remap_tbsp_rule.COUNT
            LOOP
            pkg_common_logging.write_log('Remap Tablespace von '||substr(v_ar_remap_tbsp_rule(i),1,instr(v_ar_remap_tbsp_rule(i),':') -1 ) ||
                   ' auf '|| substr(v_ar_remap_tbsp_rule(i),instr(v_ar_remap_tbsp_rule(i),':') +1 ,length(v_ar_remap_tbsp_rule(i))  ));



                DBMS_DATAPUMP.METADATA_REMAP (
                   handle    => h,
                   name      => 'REMAP_TABLESPACE',
                   old_value  => substr(v_ar_remap_tbsp_rule(i),1,instr(v_ar_remap_tbsp_rule(i),':') -1 ),
                   value     => substr(v_ar_remap_tbsp_rule(i),instr(v_ar_remap_tbsp_rule(i),':') +1 ,length(v_ar_remap_tbsp_rule(i))  )
                   );
                

            END LOOP;
    END IF;    
       
    
    pkg_common_logging.write_log('set table name',pkg_common_logging.set_debug);
   
    DBMS_DATAPUMP.metadata_filter(
    handle => h,
    name   => 'NAME_EXPR',
    value  => 'in ( '||p_table_list||')' 
    );
    
   pkg_common_logging.write_log('set TABLE_EXISTS_ACTION',pkg_common_logging.set_debug);
   

    dbms_datapump.set_parameter (h, 'TABLE_EXISTS_ACTION', 'REPLACE'); 


pkg_common_logging.write_log('set KEEP_MASTER',pkg_common_logging.set_debug);

    DBMS_DATAPUMP.set_parameter (handle   => h,
                                   name     => 'KEEP_MASTER',
                                   VALUE    => 1);


    pkg_common_logging.write_log('start job',pkg_common_logging.set_debug);
    
    dbms_datapump.start_job (h);  

    pkg_common_logging.write_log('waiting for job to finish...');
    dbms_datapump.wait_for_job(h, l_job_state); 
    pkg_common_logging.write_log('job state:'||l_job_state);
   pkg_common_logging.reset_log;
   
  exception
  when others then
   PKG_COMMON_LOGGING.WRITE_LOG_ERROR;
   commit;
   raise;
                
 end   run_dpump;
 
 
 
 procedure run_schema (v_owner in TAB_REPOSITORY.owner%type,schema_only boolean default true)



   is
   
     h       NUMBER;
     l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
     l_job_state       VARCHAR2(30) := 'UNDEFINED';
     l_sts             KU$_STATUS;
     scn_number number;
     p_jobname varchar2(32) := substr('RUN_SCHEMA_'||dbms_random.string('u',15),1,30) ;
  
  

   begin
   
    pkg_common_logging.init_log (
            'pkg_dp_transfer.run_schema',
           user,
            'start'
        );
        
   pkg_common_logging.write_log('schema_name: '||v_owner);
   

   
   
    --
    -- init dpump
    --  
    
    h := DBMS_DATAPUMP.open(
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => c_remote_link,
    job_name    => p_jobname ,
    version     => 'LATEST');

    pkg_common_logging.write_log('job_name: '||p_jobname||'; handle:'||h);
   
    
     pkg_common_logging.write_log('set log  file');
  DBMS_DATAPUMP.ADD_FILE ( handle    => h,
                            filename  => p_jobname||'.log',
                            directory => 'DATA_PUMP_DIR',
                            filetype  => DBMS_DATAPUMP.ku$_file_type_log_file );
    --
    -- parallel nur bei größeren tabellen
    --
    
    DBMS_DATAPUMP.SET_PARALLEL(
       handle  => h,
       degree  => 5);
       
       
    --
    -- remap tablespace
    -- v_ar_remap_tbsp_rule OLD_TBSP:NEW_TBSP, 
    --   
    IF v_ar_remap_tbsp_rule.COUNT > 0
         THEN
            FOR i IN 1 .. v_ar_remap_tbsp_rule.COUNT
            LOOP
            pkg_common_logging.write_log('Remap Tablespace von '||substr(v_ar_remap_tbsp_rule(i),1,instr(v_ar_remap_tbsp_rule(i),':') -1 ) ||
                   ' auf '|| substr(v_ar_remap_tbsp_rule(i),instr(v_ar_remap_tbsp_rule(i),':') +1 ,length(v_ar_remap_tbsp_rule(i))  ));



                DBMS_DATAPUMP.METADATA_REMAP (
                   handle    => h,
                   name      => 'REMAP_TABLESPACE',
                   old_value  => substr(v_ar_remap_tbsp_rule(i),1,instr(v_ar_remap_tbsp_rule(i),':') -1 ),
                   value     => substr(v_ar_remap_tbsp_rule(i),instr(v_ar_remap_tbsp_rule(i),':') +1 ,length(v_ar_remap_tbsp_rule(i))  )
                   );
                

            END LOOP;
    END IF;    
       
    
    pkg_common_logging.write_log('set table name',pkg_common_logging.set_debug);
   
    DBMS_DATAPUMP.metadata_filter(
    handle => h,
    name   => 'SCHEMA_EXPR',
    value  => ' = '''||v_owner||'''' 
    );
    
   pkg_common_logging.write_log('set TABLE_EXISTS_ACTION',pkg_common_logging.set_debug);
   

    dbms_datapump.set_parameter (h, 'TABLE_EXISTS_ACTION', 'REPLACE'); 
    
    if schema_only  then
     pkg_common_logging.write_log('Strunctural export only');
    DBMS_DATAPUMP.data_filter(handle=> h,name=> 'INCLUDE_ROWS' ,value=>0);
    else
     pkg_common_logging.write_log('full schema export including data');
    
    end if;

pkg_common_logging.write_log('set KEEP_MASTER',pkg_common_logging.set_debug);

 


    pkg_common_logging.write_log('start job',pkg_common_logging.set_debug);
    
    dbms_datapump.start_job (h);  

    pkg_common_logging.write_log('waiting for job to finish...');
    dbms_datapump.wait_for_job(h, l_job_state); 
    pkg_common_logging.write_log('job state:'||l_job_state);
   pkg_common_logging.reset_log;
   
  exception
  when others then
   PKG_COMMON_LOGGING.WRITE_LOG_ERROR;
   commit;
   raise;
                
 end   run_schema;
 
 --
 --
 -- exposed procedures
 --
 
 
 procedure run_table
 (v_owner in TAB_REPOSITORY.owner%type, 
 v_table_name in TAB_REPOSITORY.table_name%type)
                        
 is
 
 p_table_list varchar2(1024) ;
 
 begin
                        
       pkg_common_logging.init_log (
            'pkg_dp_transfer.run_table',
            v_owner||'.'||v_table_name,
            'start'
        );
        
   pkg_common_logging.write_log('started: '||v_owner||'.'||v_table_name);
   
    pkg_common_logging.write_log('check table',pkg_common_logging.set_debug);
   p_check_table (v_owner,v_table_name);  
   
   p_table_list := ''''||v_table_name||'''';
    pkg_common_logging.write_log('p_table_list='||p_table_list,pkg_common_logging.set_debug);
    
   run_dpump(p_table_list ) ;
                      
     pkg_common_logging.reset_log;
   
     exception
  when others then
   PKG_COMMON_LOGGING.WRITE_LOG_ERROR;
   commit;
   raise;
                      
 end run_table;                       
                      
    procedure run_group (v_group in TAB_REPOSITORY.product%type)
       is
   
   h       NUMBER;
     l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
     l_job_state       VARCHAR2(30) := 'UNDEFINED';
     l_sts             KU$_STATUS;
     scn_number number;
     p_jobname varchar2(32) := substr(v_group||dbms_random.string('u',15),1,30) ;
     p_table_list varchar2(8000) ;
     i number := 0;
 
 begin
 
    pkg_common_logging.init_log (
            'pkg_dp_transfer.run_group',
            v_group,
            'start'
        );
   pkg_common_logging.write_log('started: '||v_group);
   
   
   
   -- build table list
   
   for r_tab in (select table_name from TAB_REPOSITORY where product = v_group) loop
   
   i := i +1;
   
    pkg_common_logging.write_log('table_name= '||r_Tab.table_name||'; count='||i,pkg_common_logging.set_debug );
    
 
    
    if i = 1 then
         p_table_list := ''''||r_tab.table_name||'''';
    else
         p_table_list := p_table_list||','''||r_tab.table_name||'''';
    end if;
    
   pkg_common_logging.write_log('p_table_list='||p_table_list,pkg_common_logging.set_debug);
  
  
  
    end loop;
   run_dpump(p_table_list ) ;
  
    
   pkg_common_logging.reset_log;


   
  exception
  when others then
   PKG_COMMON_LOGGING.WRITE_LOG_ERROR;
   commit;
   raise;
                
 end   run_group;
    
    
    
end;
/