Hi All,
I have written block for deleting records batch wise.
after running the below the entire tables gets deleted.
Objective behind the query:
To Delete records beyond 180 days from the table temp_audit_events having the schema ppm.
The below block deletes all the records from the table temp_audit_events
I am unable to find what mistake i am doing.
Oracle version: 11g
Please assist me on this.
declare
V_MONTH VARCHAR2(10):=180;
LINE_COUNT NUMBER:= 0;
V_Count NUMBER;
CURSOR C1
IS
SELECT *
FROM ppm.temp_audit_events iae
WHERE TRUNC(EVENT_DATE) <= trunc(SYSDATE-V_MONTH)
AND TRUNC(EVENT_DATE) > trunc(SYSDATE-(V_MONTH+10));
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM ppm.temp_audit_events
WHERE TRUNC(EVENT_DATE)<= trunc(SYSDATE-V_MONTH);
DBMS_OUTPUT.PUT_LINE ('Deletion Started');
WHILE LINE_COUNT < V_COUNT
LOOP
FOR REC IN C1
LOOP
IF C1%NOTFOUND THEN
LINE_COUNT:= 999999999999999;
ELSE
DELETE
FROM ppm.temp_audit_events
WHERE REC.AUDIT_EVENT_ID IN
(SELECT DIA.AUDIT_EVENT_ID FROM ppm.temp_audit_backup DIA);
LINE_COUNT := LINE_COUNT +1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Total Record Deleted');
DBMS_OUTPUT.PUT_LINE (LINE_COUNT);
V_MONTH := V_MONTH + 10;
COMMIT;
END LOOP;
Dbms_Output.Put_Line ('Deletion Completed');
Dbms_Output.Put_Line ('Number of Records Deleted'||' '|| Line_Count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE);
ROLLBACK;
END;
Thanks & Regards,
Sameet Kumar Patro