CREATE OR REPLACE PACKAGE dbausr.COPYORGMAIN AUTHID CURRENT_USER
AS
    PROCEDURE Get_cols
    (
      powner      IN  VARCHAR2,
      ptable_name IN  VARCHAR2,
      pcols       OUT VARCHAR2
    );

    PROCEDURE Exec_ddl
    (
      pcmd        IN VARCHAR2
    );

    PROCEDURE Cp_table
    (
      powner           IN VARCHAR2,
      ptable_name      IN VARCHAR2,
      towner           IN VARCHAR2,
      ttable_name      IN VARCHAR2,
      pdblink          IN VARCHAR2,
      pstart_rowid     IN ROWID,
      pend_rowid       IN ROWID,
      scn              IN VARCHAR2,
      key_val          IN VARCHAR2,
      org_id           IN CHAR,
      org_list         IN BOOLEAN,
      src_tns          IN VARCHAR2,
      part_num         IN NUMBER
    );

    PROCEDURE Drop_temptable
    (
      powner           IN VARCHAR2,
      ptemp_table_name IN VARCHAR2
    );

    PROCEDURE RunCopyOrg
    (
      ModAction   IN VARCHAR2,
      ext_tab     IN VARCHAR2 DEFAULT 'EXP_MAP',
      tname       IN VARCHAR2,
      owner       IN VARCHAR2,
      bulk_size   IN NUMBER DEFAULT '100',
      scn         IN VARCHAR2 DEFAULT NULL,
      key_val     IN VARCHAR2 DEFAULT NULL,
      org_id      IN BOOLEAN DEFAULT FALSE,
      org_list    IN BOOLEAN DEFAULT FALSE,
      src_tns     IN VARCHAR2 DEFAULT NULL,
      part_num    IN NUMBER DEFAULT NULL
    );

END COPYORGMAIN;
/

CREATE OR REPLACE PACKAGE BODY dbausr.COPYORGMAIN
AS
  PROCEDURE Get_cols
      (
      powner      IN  VARCHAR2,
      ptable_name IN  VARCHAR2,
      pcols       OUT VARCHAR2
      )
  IS
      wcols VARCHAR2(30000);
      sep   VARCHAR2(1);
  BEGIN
    BEGIN
        FOR i IN
            (
                SELECT   column_name
                FROM     dba_tab_columns
                WHERE    owner = powner
                AND      table_name = ptable_name and column_name not in
                (select column_name from dba_tab_cols where virtual_column = 'YES' and owner = powner and table_name = ptable_name)
                ORDER BY column_id
            )
        LOOP
            wcols := wcols || sep || i.column_name;
            sep   := ',';
        END LOOP;
    END;
    pcols := wcols;
    RETURN;
  END Get_cols;

  PROCEDURE Exec_ddl
      (
      pcmd IN VARCHAR2
      )
  IS
  BEGIN
      dbms_output.Put_line(pcmd);
      EXECUTE IMMEDIATE pcmd;
      COMMIT;
  END Exec_ddl;

  PROCEDURE Cp_table
  (
      powner           IN VARCHAR2,
      ptable_name      IN VARCHAR2,
      towner           IN VARCHAR2,
      ttable_name      IN VARCHAR2,
      pdblink          IN VARCHAR2,
      pstart_rowid     IN ROWID,
      pend_rowid       IN ROWID,
      scn              IN VARCHAR2,
      key_val          IN VARCHAR2,
      org_id           IN CHAR,
      org_list         IN BOOLEAN,
      src_tns          IN VARCHAR2,
      part_num         IN NUMBER
  )
  AS PRAGMA autonomous_transaction;
      cols     VARCHAR2(30000);
      dml_stmt VARCHAR2(30000);
      sql_stmt VARCHAR2(30000);
  BEGIN
      IF key_val IS NOT NULL THEN
        IF org_id IS NULL THEN
          sql_stmt := ''||key_val||' IN (select trim(org_id) from dbausr.org_list where part_num = '||part_num||' and src_tns = ''||src_tns||'')';
        ELSE
          sql_stmt := ''||key_val||' = ''||org_id||''';
        END IF;
      ELSE
        sql_stmt := '1=1';
      END IF;

      Get_cols(powner,ptable_name,cols);
      dml_stmt := 'insert into '
                ||towner
                ||'.'
                ||ttable_name
                || ' nologging  ( '
                ||cols
                ||') ('
                || ' select /*+ rowid(p) */ '
                ||cols
                ||' from '
                ||powner
                ||'.'
                ||ptable_name
                ||pdblink
                ||' '||scn||' p where  rowid between '''
                ||pstart_rowid
                ||''' and '''
                ||pend_rowid
                ||''' and '
                || sql_stmt || ' )';
                dbms_output.Put_line(dml_stmt);
                EXECUTE IMMEDIATE dml_stmt;
                COMMIT;
                dbms_output.Put_line('Copy table '
                ||powner
                ||'.'
                ||ttable_name
                ||' - OK');
  EXCEPTION
      WHEN OTHERS THEN
        dbms_output.Put_line('Error message was: ' || SQLERRM);
        RAISE;
  END Cp_table;

  PROCEDURE Drop_temptable
  (
  powner           IN VARCHAR2,
  ptemp_table_name IN VARCHAR2
  )
  AS PRAGMA autonomous_transaction;
  BEGIN
      EXECUTE IMMEDIATE 'drop table ' || powner || '.' || ptemp_table_name ||' purge';
  END Drop_temptable;

  PROCEDURE RunCopyOrg
  (
      ModAction   IN VARCHAR2,
      ext_tab     IN VARCHAR2 DEFAULT 'EXTENT_MAP',
      tname       IN VARCHAR2,
      owner       IN VARCHAR2,
      bulk_size   IN NUMBER DEFAULT '100',
      scn         IN VARCHAR2 DEFAULT NULL,
      key_val     IN VARCHAR2 DEFAULT NULL,
      org_id      IN BOOLEAN DEFAULT FALSE,
      org_list    IN BOOLEAN DEFAULT FALSE,
      src_tns     IN VARCHAR2 DEFAULT NULL,
      part_num    IN NUMBER DEFAULT NULL
  )
  IS
        wcnt         NUMBER :=0;
        wrowcnt      NUMBER;
        foo          NUMBER;
        start_dt     DATE;
        errm         VARCHAR2(2000);
        sqlstmt      VARCHAR2(2000);
        CpyCur       SYS_REFCURSOR;
        CpyRowid     VARCHAR2(100);
        CpySrc_Owner VARCHAR2(100);
        CpySrcTbl    VARCHAR2(100);
        CpySrcdblink VARCHAR2(100);
        CpyStRowid   VARCHAR2(100);
        CpyEnRowid   VARCHAR2(100);
        CpyTmpTbl    VARCHAR2(100);
        CpyTgtOwn    VARCHAR2(100);
        CpyTgtTbl    VARCHAR2(100);
        CpyLobPre    VARCHAR2(100);
        CpyPartName  VARCHAR2(100);
        SlForUpdStmt VARCHAR2(200);
        InsSqlStmt   VARCHAR2(2000);
        DySqlStmt    VARCHAR2(2000);
        Pscn         VARCHAR2(100);
        sql_stmt     VARCHAR2(1000);
  BEGIN
          IF scn IS NOT NULL THEN
            Pscn := 'AS OF SCN '||scn;
          ELSE
            Pscn := '';
          END IF;

          IF key_val IS NOT NULL THEN
            IF org_id IS NULL THEN
              sql_stmt := ''||key_val||' IN (select trim(org_id) from dbausr.org_list where part_num = '||part_num||' and src_tns = ''||src_tns||'')';
            ELSE
              sql_stmt := ''||key_val||' = ''||org_id||''';
            END IF;
          ELSE
            sql_stmt := '1=1';
          END IF;

          dbms_output.put_line('Start of Copy');
          sqlstmt := 'SELECT    ROWID, src_owner, src_table_name , '
                      || '      CASE  '
                      || '          WHEN Regexp_like(dblink,''[db]'',''i'') THEN ''@''||dblink ELSE NULL  END dblink, '
                      || '      start_rowid, end_rowid, temp_table_name, tgt_owner, tgt_table_name, lob_present, partition_name '
                      || '      FROM     dbausr.' || ext_tab
                      || '      WHERE    status = ''READY'''
                      || '      AND      src_table_name = '''||tname||''''
                      || '      AND      src_owner = '''||owner||''''
                      || '      AND      ORDER BY extent_id, dbms_random.value ';
          OPEN CpyCur FOR sqlstmt;
          LOOP
              FETCH  CpyCur INTO CpyRowid, CpySrc_Owner, CpySrcTbl, CpySrcdblink, CpyStRowid, CpyEnRowid, CpyTmpTbl, CpyTgtOwn, CpyTgtTbl, CpyLobPre, CpyPartName;
              EXIT WHEN CpyCur%NOTFOUND;
              BEGIN
                      dbms_output.Put_line('Start of Copy_1');
                      dbms_output.Put_line(CpyLobPre
                      ||','
                      ||CpyRowid
                      ||','
                      ||CpyPartName
                      ||','
                      ||CpyTgtTbl);
                      SlForUpdStmt := 'SELECT 1 FROM dbausr.' || ext_tab || ' WHERE status=''READY'' AND ROWID=''' || CpyRowid || ''' FOR UPDATE NOWAIT ' ;
                      EXECUTE IMMEDIATE SlForUpdStmt INTO foo ;
              EXCEPTION
                  WHEN OTHERS THEN
                      GOTO continue1;
              END;
              IF (CpyLobPre = 'NO') THEN
                  BEGIN
                        dbms_application_info.set_module(CpySrc_Owner||'_'||CpySrcTbl,ModAction);
                        start_dt := SYSDATE;
                        Cp_table(CpySrc_Owner,CpySrcTbl, CpyTgtOwn, CpyTgtTbl, CpySrcdblink, CpyStRowid, CpyEnRowid, Pscn, key_val, org_id, org_list, src_tns, part_num);
                        DySqlStmt := 'UPDATE dbausr.'|| ext_tab ||' SET status=''COPIED'', last_update=SYSDATE, '
                                     ||' last_op_sec=(SYSDATE-:dr1)*24*3600 WHERE  ROWID=''' || CpyRowid || '''';
                        EXECUTE IMMEDIATE DySqlStmt using start_dt;
                  EXCEPTION
                      WHEN OTHERS THEN
                        errm :=  SQLERRM;
                        DySqlStmt := 'UPDATE dbausr.' || ext_tab   || ' SET status=''FAILCOPY'', message=''' || errm || ''', last_update=SYSDATE, '
                                     || ' last_op_sec=(SYSDATE-:1)*24*3600 WHERE  ROWID=''' || CpyRowid || '''';
                        EXECUTE IMMEDIATE DySqlStmt using start_dt;
                  END;
              ELSE
                  BEGIN
                        dbms_application_info.set_module(CpySrc_Owner||'_'||CpySrcTbl,ModAction);
                        start_dt := SYSDATE;
                        InsSqlStmt := 'insert into '
                                  ||CpyTgtOwn
                                  ||'.'
                                  ||CpyTgtTbl
                                  || ' nologging  '
                                  || '(select /*+ rowid(p) */ * '
                                  ||' from '
                                  ||CpySrc_Owner
                                  ||'.'
                                  ||CpySrcTbl
                                  ||CpySrcdblink
                                  ||' '||Pscn||' p where  rowid between :pr1 and :pr2  and '
                                  || sql_stmt || ' )';
                        EXECUTE IMMEDIATE InsSqlStmt using CpyStRowid, CpyEnRowid;
                        DySqlStmt := 'UPDATE dbausr.'||ext_tab||' SET status = ''LOBMERGED'', last_update = SYSDATE, last_op_sec = (SYSDATE - :dr1) * 24 * 3600
                               WHERE  ROWID = '''||CpyRowid||'''';
                        EXECUTE IMMEDIATE DySqlStmt using start_dt;
                  EXCEPTION
                      WHEN OTHERS THEN
                        errm      := SQLERRM;
                        DySqlStmt := 'UPDATE dbausr.' || ext_tab || ' SET status = ''FAILCOPY'', message = ''' || errm || ''', last_update = SYSDATE, '
                                     || ' last_op_sec = (SYSDATE - :dr1) * 24 * 3600 WHERE ROWID=''' || CpyRowid || '''';
                        EXECUTE IMMEDIATE DySqlStmt using start_dt;
                  END;
              END IF;
              COMMIT;
              wcnt := wcnt + 1;
              EXIT WHEN wcnt >= bulk_size;
              << continue1 >> NULL;
          END LOOP;
          CLOSE CpyCur;
  END RunCopyOrg;
END COPYORGMAIN;
/