Oracle insert into Loop

Oracle insert through Loop




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR del_record_cur IS
SELECT rowid
FROM .
WHERE

BEGIN
FOR rec IN del_record_cur LOOP
DELETE FROM .
WHERE rowid = rec.rowid;

total := total + 1;
count := count + 1;

IF (count >= 1000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from ..');
END;
/





DECLARE


cnt NUMBER := 0 ;
total NUMBER := 0 ;

CURSOR copy_record_cur IS
SELECT * from emp_anuj ;


BEGIN

FOR rec IN copy_record_cur LOOP
insert into scott.emp_temp
(EMPNO ,
ENAME ,
JOB ,
HIREDATE ,
SAL ,
COMM ,
DEPTNO )
values (
rec.EMPNO ,
rec.ENAME ,
rec.JOB ,
rec.HIREDATE ,
rec.SAL ,
rec.COMM ,
rec.DEPTNO
) ;



total := total + 1;
cnt := cnt + 1;

IF (cnt >= 5000) THEN
COMMIT;

cnt := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from scott.emp_temp');
END;
/




DECLARE

count NUMBER := 0;
total NUMBER := 0;

CURSOR copy_record_cur IS
SELECT *
FROM cccprod1.DM_AUDITTRAIL_S_backup
WHERE TIME_STAMP >'07-DEC-08';
-- rec.


BEGIN

FOR rec IN copy_record_cur LOOP
insert into cccprod1.DM_AUDITTRAIL_S (
R_OBJECT_ID ,
EVENT_NAME ,
EVENT_SOURCE ,
R_GEN_SOURCE ,
USER_NAME ,
AUDITED_OBJ_ID ,
TIME_STAMP ,
STRING_1 ,
STRING_2 ,
STRING_3 ,
STRING_4 ,
STRING_5 ,
ID_1 ,
ID_2 ,
ID_3 ,
ID_4 ,
ID_5 ,
I_IS_REPLICA ,
I_VSTAMP ,
CHRONICLE_ID ,
OBJECT_NAME ,
VERSION_LABEL ,
OBJECT_TYPE ,
EVENT_DESCRIPTION ,
POLICY_ID ,
CURRENT_STATE ,
WORKFLOW_ID ,
SESSION_ID ,
USER_ID ,
OWNER_NAME ,
ACL_NAME ,
ACL_DOMAIN ,
APPLICATION_CODE ,
CONTROLLING_APP ,
ATTRIBUTE_LIST ,
ATTRIBUTE_LIST_ID ,
AUDIT_SIGNATURE ,
AUDIT_VERSION ,
HOST_NAME ,
TIME_STAMP_UTC ,
I_AUDITED_OBJ_CLASS ,
REGISTRY_ID ,
I_IS_ARCHIVED ,
AUDITED_OBJ_VSTAMP )
values
(
rec.R_OBJECT_ID ,
rec.EVENT_NAME ,
rec.EVENT_SOURCE ,
rec.R_GEN_SOURCE ,
rec.USER_NAME ,
rec.AUDITED_OBJ_ID ,
rec.TIME_STAMP ,
rec.STRING_1 ,
rec.STRING_2 ,
rec.STRING_3 ,
rec.STRING_4 ,
rec.STRING_5 ,
rec.ID_1 ,
rec.ID_2 ,
rec.ID_3 ,
rec.ID_4 ,
rec.ID_5 ,
rec.I_IS_REPLICA ,
rec.I_VSTAMP ,
rec.CHRONICLE_ID ,
rec.OBJECT_NAME ,
rec.VERSION_LABEL ,
rec.OBJECT_TYPE ,
rec.EVENT_DESCRIPTION ,
rec.POLICY_ID ,
rec.CURRENT_STATE ,
rec.WORKFLOW_ID ,
rec.SESSION_ID ,
rec.USER_ID ,
rec.OWNER_NAME ,
rec.ACL_NAME ,
rec.ACL_DOMAIN ,
rec.APPLICATION_CODE ,
rec.CONTROLLING_APP ,
rec.ATTRIBUTE_LIST ,
rec.ATTRIBUTE_LIST_ID ,
rec.AUDIT_SIGNATURE ,
rec.AUDIT_VERSION ,
rec.HOST_NAME ,
rec.TIME_STAMP_UTC ,
rec.I_AUDITED_OBJ_CLASS ,
rec.REGISTRY_ID ,
rec.I_IS_ARCHIVED ,
rec.AUDITED_OBJ_VSTAMP
) ;

total := total + 1;
count := count + 1;

IF (count >= 5000) THEN
COMMIT;
count := 0;
END IF;

END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('INSERTED ' || total || ' records from CCCPROD1.DM_AUDITTRAIL_S');
END;
/