CREATE OR REPLACE PROCEDURE purge_sample
AS
       count_x  NUMBER  := 0;
       total  NUMBER  := 0;
       

      CURSOR del_record_cur IS 
          SELECT request_id FROM RQ_ADVICES_IN_PROGRESS_DUP 
         WHERE REQUEST_CREATION_TIME<SYSDATE-7 AND STATUS IN('S') 
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
         AND ROWNUM<200;
         
         delrow del_record_cur%ROWTYPE;

     BEGIN
     
            
       FOR delrow IN del_record_cur LOOP
       
      
       
         DELETE /*+ parallel(req,8)*/ FROM RQ_ADVICES_IN_PROGRESS_DUP req
           WHERE REQUEST_ID = delrow.REQUEST_ID;

         total := total + 1;
         count_x := count_x + 1;

         IF (count_x >= 100) THEN
           COMMIT;
            
           count_x := 0;
         END IF;

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

DELETE FROM RQ_ADVICES_IN_PROGRESS_DUP WHERE REQUEST_CREATION_TIME<SYSDATE-7 AND STATUS IN('S') 
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_SERVICE_ADVICE_DETAILS_DUP)
         AND REQUEST_ID IN(SELECT REQUEST_ID FROM RQ_ACCOUNT_ADVICE_DETAILS_DUP)
         AND ROWNUM<2000;

create table parent_table
(
 parent_table_id number not null
,somedata varchar2(4000)
)
/

alter table parent_table add primary key (parent_table_id)
/

create table child_table
(
 child_table_id number not null
,parent_table_id number not null
,somedata varchar2(4000)
)
/

alter table child_table add primary key (child_table_id)
/
alter table child_table add foreign key (parent_table_id) references parent_table
/


insert into parent_table values (1,'abc');
insert into parent_table values (2,'xyz');

insert into child_table values (101,2,'pdq');
--
-- insert 10 million more rows into child_table
--

commit
/

delete
from parent_table
where parent_table_id = 1
/

SELECT request_id FROM REQUESTS_DUP 
         WHERE STATUS in('C') AND MONTHS_BETWEEN(SYSDATE,REQUEST_DATE)>6 
         AND (REQUEST_SOURCE_ID in(2,4)) AND (REQUEST_TYPE_ID in('L','S'));