We are trying to insert around 1.5 million records using PL/Sql in loop in chunk of 50,000 records. We also want to write output on console as soon as 1 iteration of loop is completed. As we are loading in chunk of 50000 records, I am hoping it would write 30 times on the console. But, in my case, it just giving me status after all cycle of loop is completed, just not sure why it's not printing the result as soon as one cycle of loop is completed. Logging is very crucial for our process. Please suggest.
Query :
--- business logic--
LOOP
DBMS_OUTPUT.PUT_LINE ('Starting Loop - '|| Counter );
FETCH PRSQ_CURSOR BULK COLLECT INTO PRSQ_EXT LIMIT 50000;
DBMS_OUTPUT.PUT_LINE ('Total Number of Records to be Inserted ' || PRSQ_CURSOR%ROWCOUNT);
BEGIN
FORALL i IN 1 .. PRSQ_EXT.COUNT
INSERT INTO FSG_WRK.CMC_PRSQ_SITE_QA_BKP0915
VALUES (
PRSQ_EXT(i).PR_ID,
PRSQ_EXT(i).AD_ID,
PRSQ_EXT(i).AD_TYPE,
PRSQ_EXT(i).AD_DT,
PRSQ_EXT(i).SQ_VER_DT,
PRSQ_EXT(i).SQ_ITEM,
PRSQ_EXT(i).SQ_VER_DT,
PRSQ_EXT(i).SQ_VER_DT,
PRSQ_EXT(i).SQ_VER_DT,
PRSQ_EXT(i).SQ_VSRC,
PRSQ_EXT(i).SQ_VER_IND,
PRSQ_EXT(i).SQ_VRSL,
PRSQ_EXT(i).SQ_VER_NAME,
PRSQ_EXT(i).SQ_VMTH,
PRSQ_EXT(i).SQ_COMMENT,
PRSQ_EXT(i).SQ_LOCK_TOKEN,
PRSQ_EXT(i).XR_SOURCE_ID
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error Inserting Records into table- '|| SUBSTR (SQLERRM, 1, 100) || SQLCODE );
END;
DBMS_OUTPUT.PUT_LINE ('Ending Loop - '|| Counter );
Counter := Counter+1 ;
EXIT WHEN PRSQ_CURSOR%NOTFOUND;
END LOOP;
COMMIT;
CLOSE PRSQ_CURSOR;
DBMS_OUTPUT.PUT_LINE ('Script Execution Finished- '|| SYSTIMESTAMP);
I am getting expected output but when whole process got completed :
Starting Script Execution - 15-SEP-20 03.23.04.822577000 AM -05:00
Starting Loop - 1
Total Number of Records to be Inserted 50000
Ending Loop - 1
Starting Loop - 2
Total Number of Records to be Inserted 100000
Ending Loop - 2
Starting Loop - 3
Total Number of Records to be Inserted 150000
Ending Loop - 3
Starting Loop - 4
Total Number of Records to be Inserted 200000
Ending Loop - 4
Starting Loop - 5
Total Number of Records to be Inserted 250000
Ending Loop - 5
Starting Loop - 6
Total Number of Records to be Inserted 300000
Ending Loop - 6
Starting Loop - 7
Total Number of Records to be Inserted 350000
Ending Loop - 7
Starting Loop - 8
Total Number of Records to be Inserted 400000
Ending Loop - 8
Starting Loop - 9
Total Number of Records to be Inserted 450000
Ending Loop - 9
Starting Loop - 10
Total Number of Records to be Inserted 500000
Ending Loop - 10
Starting Loop - 11
Total Number of Records to be Inserted 550000
Ending Loop - 11
Starting Loop - 12
Total Number of Records to be Inserted 600000
Ending Loop - 12
Starting Loop - 13
Total Number of Records to be Inserted 650000
Ending Loop - 13
Starting Loop - 14
Total Number of Records to be Inserted 700000
Ending Loop - 14
Starting Loop - 15
Total Number of Records to be Inserted 750000
Ending Loop - 15
Starting Loop - 16
Total Number of Records to be Inserted 800000
Ending Loop - 16
Starting Loop - 17
Total Number of Records to be Inserted 850000
Ending Loop - 17
Starting Loop - 18
Total Number of Records to be Inserted 900000
Ending Loop - 18
Starting Loop - 19
Total Number of Records to be Inserted 950000
Ending Loop - 19
Starting Loop - 20
Total Number of Records to be Inserted 1000000
Ending Loop - 20
Starting Loop - 21
Total Number of Records to be Inserted 1050000
Ending Loop - 21
Starting Loop - 22
Total Number of Records to be Inserted 1100000
Ending Loop - 22
Starting Loop - 23
Total Number of Records to be Inserted 1150000
Ending Loop - 23
Starting Loop - 24
Total Number of Records to be Inserted 1200000
Ending Loop - 24
Starting Loop - 25
Total Number of Records to be Inserted 1250000
Ending Loop - 25
Starting Loop - 26
Total Number of Records to be Inserted 1300000
Ending Loop - 26
Starting Loop - 27
Total Number of Records to be Inserted 1350000
Ending Loop - 27
Starting Loop - 28
Total Number of Records to be Inserted 1400000
Ending Loop - 28
Starting Loop - 29
Total Number of Records to be Inserted 1450000
Ending Loop - 29
Starting Loop - 30
Total Number of Records to be Inserted 1500000
Ending Loop - 30
Starting Loop - 31
Total Number of Records to be Inserted 1550000
Ending Loop - 31
Starting Loop - 32
Total Number of Records to be Inserted 1600000
Ending Loop - 32
Starting Loop - 33
Total Number of Records to be Inserted 1650000
Ending Loop - 33
Starting Loop - 34
Total Number of Records to be Inserted 1700000
Ending Loop - 34
Starting Loop - 35
Total Number of Records to be Inserted 1750000
Ending Loop - 35
Starting Loop - 36
Total Number of Records to be Inserted 1800000
Ending Loop - 36
Starting Loop - 37
Total Number of Records to be Inserted 1850000
Ending Loop - 37
Starting Loop - 38
Total Number of Records to be Inserted 1866217
Ending Loop - 38
Script Execution Finished- 15-SEP-20 03.49.19.842459000 AM -05:00
PL/SQL procedure successfully completed.
Total Record- 1866217
Start Time - 15-SEP-20 03.23.04.822577000 AM -05:00
End Time - 15-SEP-20 03.49.19.842459000 AM -05:00
