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;

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

Add a counter

Code:

---

counter := 0

insert ...
counter := counter + 1
if counter = 1000 then
  commit;
  counter := 0;
end if;

---

Why do you want to commit every 100 records?

Because this procedure will insert over 1 mil. records and my temp tablespace is very limited. I let the procedure to run and after 5 hours I got the following error(Error): ORA-30036: unable to extend segment by 1024 in undo tablespace 'UNDOTBS1'.

sorry, 1000 records

why aren't you doing ...

Code:

---

insert into TRN_NORM_30DAY
select ... from bblg.CDR_SUMMARY@prldw ...

---

This line-by-line stuff sucks for performance.

Quote:

---

Originally posted by rkiss
Because this procedure will insert over 1 mil. records and my temp tablespace is very limited. I let the procedure to run and after 5 hours I got the following error(Error): ORA-30036: unable to extend segment by 1024 in undo tablespace 'UNDOTBS1'.

---

frequent commits increase the chance of ora-1555 so you can commit frequently if you like but risk running into snapshot too old

why dont oyu just increase the size of the undo tbs?

steve

Since it tries to do the whole thing in one commit, it will fail with rollback problems (again).

Thank you for you help

Have you tried it and measured the amount of rollback?

Have you also tried insert /*+ append */ with the table in nologging mode?

Read this also.

slimdave,

thanks for all the help. I really appreciate it.