pl/sql cursor for loop delete statement

Question

So im currently working on a script which would basdically define a cursor that would loop around the tables and delete data that are older than 2 years with a row limit of 5000 and would exit when the rowid count reaches 0.

Now I have tried multiple approaches from selecting the multiple nested select after the forall statement as well as defining it as the select for the cursor, but I just cannot manage to put my finger on it and im unsure as to what am I doing wrong

The code in question

declare  
   cursor mycursor is SELECT ROWID FROM ( 
SELECT A.BYTEARRAY_ID_ FROM ACT_RU_VARIABLE A 
WHERE A.PROC_INST_ID_ IN (
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL)
SELECT FROM ACT_HI_ACTINST WHERE PROC_INST_ID_ IN 
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM ACT_RU_VARIABLE WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM ACT_RU_EVENT_SUBSCR WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);

SELECT FROM ACT_RU_EXECUTION WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM ACT_RU_TASK WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM ACT_HI_TASKINST WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM ACT_HI_PROCINST WHERE PROC_INST_ID_ IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
commit;
SELECT FROM BFM_PROCESS_BO WHERE PROCESS_INST_ID IN
(
SELECT PROC_INST_ID_ FROM ACT_HI_PROCINST  
WHERE END_TIME_ IS NOT NULL);
AND WHERE  t.created_date<sysdate-730  order by rowid; 
type rowid_table_type is  table  of rowid index by pls_integer;
   v_rowid   rowid_table_type;
BEGIN
   open mycursor;
   loop
     fetch   mycursor bulk collect into v_rowid  limit 5000; 
     exit when v_rowid.count=0;
     forall i in v_rowid.first..v_rowid.last
   delete from ACT_GE_BYTEARRAY WHERE rowid= v_rowid(i) 
   commit;
   end loop;
   close mycursor;
END;

I have scoured all over the web for something that would resemble this scenario (deleting a lengthy nested select) but most examples that i have tried to apply to my code failed and im running out of ideas on how can I cut this gordian knot so to say.

Initially in the code, the delete statement was defined in the nested select individually, however my goal is to have the deleting conditition defined on the top of the code that would be applied for all of the elements of the nested select (if it makes sense)

Answer 1

Code you posted is difficult to salvage; it is full of errors, e.g.

• you can't commit in declare section
• it is unclear what cursor contains, as there are plenty of select statements which are terminated (with a semi-colon), some of them being invalid as well (select without a from, select nothing (no column list, no asterisk), ...)
• rowid is related to a table; you can't expect that storing rowids from different tables would affect some other table
• "2 years" is not exactly 730 days; what about leap years?

Therefore, perhaps you should consider a different approach; this is a simplified example which presumes that tables you'd want to "clear" from old data contain the created_date column (you can additionally filter what cursor returns). Then, using dynamic SQL, delete rows from those tables.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     l_sql  VARCHAR2 (200);
  3  BEGIN
  4     FOR cur_r IN (SELECT table_name
  5                     FROM user_tab_columns
  6                    WHERE column_name = 'CREATED_DATE')
  7     LOOP
  8        l_sql :=
  9              'delete from '
 10           || cur_r.table_name
 11           || '  where created_date < add_months(sysdate, -2 * 12)';
 12
 13        EXECUTE IMMEDIATE l_sql;
 14
 15        DBMS_OUTPUT.put_line (
 16           cur_r.table_name || ': deleted ' || SQL%ROWCOUNT || ' row(s)');
 17
 18        COMMIT;
 19     END LOOP;
 20  END;
 21  /
AIRPLANES: deleted 1 row(s)
MY_TABLE1: deleted 3 row(s)
SCHEDULED: deleted 1 row(s)

PL/SQL procedure successfully completed.

SQL>