-
commit after 1000 records
Hi,
I have the following Stored Procedure. I would like the stored procedure to commit after every 1000 records, but I do not how to do it.
Could someone help me out please
Thanks
-----------------------------------------------
PROCEDURE APPEND_TRN_NORM_30DAY as
DTE DATE;
CARRIER VARCHAR2(64);
SRP_ID VARCHAR2(32);
PIN NUMBER;
RELAY VARCHAR2(64);
DEVICE_TYPE VARCHAR2(32);
MESSAGE_TYPE VARCHAR2(32);
MFH_COUNT NUMBER;
MFH_SIZE NUMBER;
MTH_COUNT NUMBER;
MTH_SIZE NUMBER;
CURSOR C_TRN_NORM_30DAY IS
SELECT /* + INDEX(A CDR_SUM_DTE) +*/
distinct
a.DTE, b.CARRIER, c.SRPID, a.PIN, a.RELAY,
b.DEVICE_TYPE, a.MSGTYPE, a.MFH_COUNT,
a.MFH_SIZE, a.MTH_COUNT, a.MTH_SIZE
FROM bblg.CDR_SUMMARY@prldw a, bblg.DEVICE_INFO@prldw b, BBLG.SRP_INFO@prldw c
WHERE a.PIN = b.PIN
AND a.SRPID_ID = c.ID
AND a.DTE BETWEEN trunc(sysdate) - 32 AND trunc(sysdate) - 2;
cv_row C_TRN_NORM_30DAY%ROWTYPE;
BEGIN
OPEN C_TRN_NORM_30DAY;
LOOP
FETCH C_TRN_NORM_30DAY INTO cv_row;
EXIT WHEN C_TRN_NORM_30DAY%NOTFOUND;
INSERT INTO TRN_NORM_30DAY
(DTE, CARRIER, SRP_ID, PIN, RELAY, DEVICE_TYPE, MESSAGE_TYPE, MFH_COUNT, MFH_SIZE, MTH_COUNT, MTH_SIZE)
VALUES
(cv_row.DTE, cv_row.CARRIER, cv_row.SRPID, cv_row.PIN, cv_row.RELAY, cv_row.DEVICE_TYPE,
cv_row.MSGTYPE, cv_row.MFH_COUNT, cv_row.MFH_SIZE, cv_row.MTH_COUNT, cv_row.MFH_SIZE);
END loop;
COMMIT;
END;
--------------------------------------------------------
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|