Simple SQL to load data from one table to other table with the same structure

Simple SQL to load data from one table to other table. It has below features

1. It has exception section to avoid aborting the SQL for errors.
2. It will tract the start and end time of loading.
3. First one: First Section is record by record processing section. It will dynamically take the record names from a table. Source record is the same structure as destination but in different instance which is connected to Destination DB with a DBLink.
4. Second Section: Second Section is row by row processing.

Please note that the text enclosed in Square Brace should be replaced with appropriate Record name/Field Name.

DECLARE

  ERRORCODE NUMBER(10);

  ERRORMESG VARCHAR2(1000);

  TIMESTMP TIMESTAMP;

  RECNAME12 VARCHAR2(20);

  STMT_STR VARCHAR2(500);

  RECNM2 VARCHAR2(500);

  RECNM1 VARCHAR2(500);

  SINGLESPACE VARCHAR2(500);

  SINGLESPACE1 VARCHAR2(500);

  ROW_COUNT1 NUMBER(10);

  ROW_COUNT2 NUMBER(10);

BEGIN

SELECT CURRENT_TIMESTAMP INTO TIMESTMP FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Sample DC start '||TIMESTMP);

FOR J IN(SELECT RECNAME RECNAME1 FROM [RECORD WITH TABLE NAMES] WHERE FLAG_FIELD = 'REC')

LOOP

   BEGIN

    STMT_STR := 'INSERT INTO [Log TAble1](RECNAME,BEGINDTTM,ENDDTTM,ROW_COUNT,TOTAL_ROW_COUNT,ERRCOLORBG,DESCR200) VALUES(:TEST1,:TEST2,:TEST3,:TEST4,:TEST5,:TEST6,:TEST7)';

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM PS_'||J.RECNAME1||'@[DB Link NAme]' INTO ROW_COUNT1 ;

    EXECUTE IMMEDIATE STMT_STR USING J.RECNAME1,CURRENT_TIMESTAMP,SYSDATE,ROW_COUNT1,0,' ',' ';

   

    EXECUTE IMMEDIATE 'INSERT INTO PS_'||J.RECNAME1||' SELECT * FROM PS_'||J.RECNAME1||'@[DB Link NAme]';

   

    STMT_STR := 'INSERT INTO [Log TAble1](RECNAME,BEGINDTTM,ENDDTTM,ROW_COUNT,TOTAL_ROW_COUNT,ERRCOLORBG,DESCR200) VALUES(:TEST1,:TEST2,:TEST3,:TEST4,:TEST5,:TEST6,:TEST7)';

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM PS_'||J.RECNAME1 INTO ROW_COUNT2 ;

    EXECUTE IMMEDIATE STMT_STR USING J.RECNAME1,CURRENT_TIMESTAMP,SYSDATE,ROW_COUNT1,ROW_COUNT2,' ',' ';

   

    EXCEPTION

    WHEN OTHERS THEN

      errorcode := SQLCODE;

      ERRORMESG := SQLERRM;

      ERRORMESG := ERRORMESG || '->(' || J.RECNAME1  || ')' ;

      EXECUTE IMMEDIATE 'INSERT INTO SYSADM.[Log TAble1](RECNAME,BEGINDTTM,ENDDTTM,ROW_COUNT,TOTAL_ROW_COUNT,ERRCOLORBG,DESCR200) VALUES(:TEST1,:TEST2,:TEST3,:TEST4,:TEST5,:TEST6,:TEST7)' USING J.RECNAME1,CURRENT_TIMESTAMP,SYSDATE,ROW_COUNT1,0,' ',ERRORMESG;

      COMMIT;

      DBMS_OUTPUT.PUT_LINE(TO_CHAR(ERRORCODE) || '-' || ERRORMESG);

   

  END;

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Sample DC End '||TIMESTMP);

---------- Second Section - Start ---------

-- Table1

    SELECT CURRENT_TIMESTAMP INTO TIMESTMP FROM DUAL;

    dbms_output.put_line('Table1 start '||timestmp);

--    SELECT 'Table1' INTO RECNAME1 FROM DUAL;

--Insert total count in log table before running script

    INSERT INTO [Log TAble1](RECNAME,BEGINDTTM,ENDDTTM,ROW_COUNT,TOTAL_ROW_COUNT,ERRCOLORBG,DESCR200) VALUES('[Table1]',CURRENT_TIMESTAMP,NULL,(SELECT COUNT(*) FROM [Table1]@[DB Link Name]),0,' ',' ');

--Insert 8.3 table data into 9.1 table

      FOR J IN (SELECT [Field1] Field1,

      [FIELD2] FIELD2,

      [FIELD3] FIELD3,

      [FIELD4] FIELD4,

      [FIELD5] FIELD5,

      [Field6] Field6 FROM [Table1]@[DB Link Name])

   

LOOP

                BEGIN

                                INSERT INTO [TABLE1]

                                ([Field1],

      [Field2],

      [FIELD3],

      [Field4],

      [Field5],

      [FIELD6])

    VALUES

    (J.field1,

    J.field2,

    J.FIELD3,

    J.field4,

    J.FIELD5,

    J.FIELD6);

   

                EXCEPTION

                                                WHEN OTHERS THEN

                                                                errorcode := SQLCODE;

                                                                ERRORMESG := SQLERRM;

                                                                errormesg := errormesg || '->(' || J.field1 || ',' || J.field2 || ',' || J.field3 || ')' ;

       

                                                INSERT INTO [DETAILED LOG TABLE2](RECNAME,BEGINDTTM,ENDDTTM,FIELDNAME0,FIELDNAME1,FIELDNAME2,DESCR200) VALUES ('[Table1]',CURRENT_TIMESTAMP,NULL,'[field1]','[field2]','[field3]',ERRORMESG);

   

                                                COMMIT;

                               

    dbms_output.put_line(TO_CHAR(errorcode) || '-' || errormesg);

                END;

END LOOP;

DBMS_OUTPUT.PUT_LINE('table1 completed '||TIMESTMP);

INSERT INTO [Log TAble1](RECNAME,BEGINDTTM,ENDDTTM,ROW_COUNT,TOTAL_ROW_COUNT,ERRCOLORBG,DESCR200) VALUES('[Table1]',CURRENT_TIMESTAMP,NULL,(SELECT COUNT(*) FROM [Table1]@[db link name]),(SELECT COUNT(*) FROM [Table1]),' ',' ');

COMMIT;

END;