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;

--------------------------------------------------------