Simple SQL to load data from one table to other table. It
has below features
- It has exception section to avoid aborting the SQL for errors.
- It will tract the start and end time of loading.
- 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.
- 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
FOR J IN(SELECT
RECNAME RECNAME1 FROM [RECORD WITH TABLE NAMES] WHERE FLAG_FIELD = 'REC')
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
---------- 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])
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;
