The following insert statement is used to fill an interface table from a remote database, using a dblink (tblCalls_v1@sqlserverdb).
It commits every 500:
BEGIN
DECLARE
CURSOR C1 IS
SELECT
"ContactID" ContactID,
SYSDATE Created,
'0-1' Created_By,
SYSDATE Last_Upd,
'0-1' Last_Upd_By,
0 Modification_Num,
0 Conflict_ID,
1 If_Row_Batch_Num,
'FOR_IMPORT' If_Row_Stat,
"ContactID" Sr_Num,
0 If_Row_Stat_Num,
substr("ContactResult",1,1999) Gen_Act_Note,
substr("Notes",1,1999) Gen_Desc_Note,
'-' Gen_Out_Note,
substr("Category1ID",1,30) Sr_Type_CD,
"EmployeeID" Owner_Emp_Login,
"LastUpdate" Act_Open_DT
FROM tblCalls_v1@sqlserverdb
ORDER BY "ContactID";
C1_ROW C1%ROWTYPE;
C1_COUNT NUMBER;
BEGIN
C1_COUNT:=0;
FOR I IN C1 LOOP
INSERT INTO EIM_SRV_REQ (
ROW_ID, --DATA: "ContactID"
CREATED, --SYSDATE
CREATED_BY, --'0-1'
LAST_UPD, --SYSDATE
LAST_UPD_BY, --'0-1'
MODIFICATION_NUM, --0
CONFLICT_ID, --0
IF_ROW_BATCH_NUM, --1
IF_ROW_STAT, --'FOR_IMPORT'
SR_NUM, --DATA: ??? The same with ROW_ID???
IF_ROW_STAT_NUM, --0
GEN_ACT_NOTE, --DATA: substr("ContactResult",1,100)
GEN_DESC_NOTE, --DATA: "Notes"
GEN_OUT_NOTE, -- '-',
SR_TYPE_CD, --DATA: substr("Category1ID",1,30)
OWNER_EMP_LOGIN, --DATA: "EmployeeID"
ACT_OPEN_DT --DATA: "LastUpdate"
)
VALUES(
I.ContactID,
I.Created,
I.Created_By,
I.Last_Upd,
I.Last_Upd_By,
I.Modification_Num,
I.Conflict_ID,
I.If_Row_Batch_Num,
I.If_Row_Stat,
I.Sr_Num,
I.If_Row_Stat_Num,
I.Gen_Act_Note,
I.Gen_Desc_Note,
I.Gen_Out_Note,
I.Sr_Type_CD,
I.Owner_Emp_Login,
I.Act_Open_DT
);
IF C1_COUNT = 500 THEN
C1_COUNT := 0;
COMMIT;
END IF;
C1_COUNT:=C1_COUNT+1;
END LOOP;
END;
COMMIT;
END;