A.0 Audit Pruning Procedure

--start script--

CREATE OR REPLACE PROCEDURE Z_AUDIT_PRUNING (
    TableName   IN VARCHAR2,
    ColumnName  IN VARCHAR2,
  BATCHCOUNT IN NUMBER DEFAULT 100000,
   OUTCOUNT OUT NUMBER)  

as 
CHGS_TABLE varchar2 (1024);  
CHILDSQL varchar2 (1024) ; 
CLOB_TABLE varchar2 (1024) ;
LOOPCOUNT NUMBER(20) := 1;
QUERYSQL  varchar2 (1024);  
ErrorMsg varchar2 (1024); 
CHANGE_COLUMN VARCHAR2(1024);
STARTDATE  DATE := SYSDATE;
UTCDATE  TIMESTAMP := systimestamp at time zone 'UTC';
  
 BEGIN
  OUTCOUNT := 0 ;
  WHILE LOOPCOUNT > 0
  LOOP
    -- insert the eventid in 
     QUERYSQL := 'INSERT INTO EventTableTemp(eventID) SELECT EVENTID  FROM '||TableName||' where  '||ColumnName ||' <= :1 AND  rownum <= '||BATCHCOUNT||' ' ;
    
     EXECUTE IMMEDIATE QUERYSQL using in UTCDATE ; 
      COMMIT;
    for i in  (SELECT  table_name   FROM user_constraints  WHERE r_constraint_name in (SELECT constraint_name FROM user_constraints WHERE constraint_type in ('P','U')  AND table_name = TableName  ) )
    LOOP
        -- get the cachgs table (child1 )
      CHGS_TABLE := i.table_name;
      
      
      IF CHGS_TABLE IS NOT NULL
      THEN
      
        -- get the primary key column for chgs table
        
        SELECT cols.column_name INTO CHANGE_COLUMN  FROM user_constraints cons, user_cons_columns cols WHERE cols.table_name = CHGS_TABLE AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner  ;
          
      --  EXECUTE IMMEDIATE QUERYSQL  ; 
        
      
        -- insert datachangeid in temp2 table
        
         QUERYSQL := 'INSERT INTO ChangeTableTemp(changeID) SELECT '||CHANGE_COLUMN||'  FROM '||CHGS_TABLE||' where EVENTID In ( SELECT eventID FROM  EventTableTemp ) ';
    
        EXECUTE IMMEDIATE QUERYSQL ; 
        
        for a in  (SELECT  table_name as t  FROM user_constraints  WHERE r_constraint_name in (SELECT constraint_name FROM user_constraints WHERE constraint_type in ('P','U')  AND table_name = CHGS_TABLE  ) )
        LOOP
        CLOB_TABLE := a.t;
        
        IF CLOB_TABLE IS NOT NULL
        THEN
      
          -- delete from clob (child2) tables
          
          QUERYSQL := '
          DECLARE
            TYPE myarray
            IS
            TABLE OF RAW(16);
            l_data myarray;
            BATCHCOUNT NUMBER ;
            CURSOR r
            IS
            SELECT changeID FROM  ChangeTableTemp FOR UPDATE SKIP LOCKED;
            BEGIN
            OPEN r;
            
            BATCHCOUNT := :1;
            FETCH r BULK COLLECT INTO l_data LIMIT BATCHCOUNT;
            While true
            loop
            FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
            DELETE  FROM '||CLOB_TABLE||' where '||CHANGE_COLUMN||'  =l_data(i);  
            FETCH r BULK COLLECT INTO l_data LIMIT BATCHCOUNT;
            exit when r%notfound;
            end loop;            
            CLOSE r;
            EXCEPTION
              WHEN OTHERS THEN 
            NULL;
            END;';
          
          --QUERYSQL := 'DELETE  FROM '||CLOB_TABLE||' where '||CHANGE_COLUMN||'  In ( SELECT changeID FROM  ChangeTableTemp )' ;
          EXECUTE IMMEDIATE QUERYSQL  using  IN BATCHCOUNT; 
          
          
          COMMIT;
          
          
        
        END IF ; -- end if clob is not null
        
        END LOOP ; -- end for clob loop
        
        
        
        
        -- delete from chgs table 
        QUERYSQL := 'DECLARE
        TYPE myarray
            IS
            TABLE OF RAW(16);
            BATCHCOUNT NUMBER ;
        l_data myarray;
        CURSOR r
        IS
        SELECT eventID FROM  EventTableTemp FOR UPDATE SKIP LOCKED;
      BEGIN
        OPEN r;
        BATCHCOUNT := :1;
        FETCH r BULK COLLECT INTO l_data LIMIT BATCHCOUNT;
        While true
            loop
        FORALL i IN 1..l_data.COUNT SAVE EXCEPTIONS
        DELETE  FROM '||CHGS_TABLE||'  where EVENTID =l_data(i);
        COMMIT ;        
        FETCH r BULK COLLECT INTO l_data LIMIT BATCHCOUNT;
            exit when r%notfound;
            end loop;          
        CLOSE r;
      EXCEPTION
        WHEN OTHERS THEN 
          NULL;
      END;' ;
        --QUERYSQL := 'DELETE  FROM '||CHGS_TABLE||'  where EVENTID In ( SELECT eventID FROM  EventTableTemp )' ;
        EXECUTE IMMEDIATE QUERYSQL  using  IN BATCHCOUNT;
        
        COMMIT;
        
        
        
      END IF; -- end if chgs is not null
  
     
    END LOOP ;  -- end chgs for loop
    
    
    -- delete from main table 
    QUERYSQL :='DECLARE
    TYPE myarray
            IS
            TABLE OF ROWID;
    TBROWS myarray;
    
    LOOPCNT NUMBER :=0;
        CURSOR DELETE_TABLE
        IS
         SELECT ROWID ID  FROM '||TableName||' where  EVENTID In ( SELECT eventID FROM  EventTableTemp  ) ORDER BY ROWID;
      BEGIN
        OPEN DELETE_TABLE;
        LOOP
        FETCH DELETE_TABLE BULK COLLECT INTO TBROWS LIMIT :1;         
        FORALL ROW IN 1 .. TBROWS.COUNT
        DELETE FROM '||TableName||' WHERE ROWID = TBROWS(ROW);
        LOOPCNT  := LOOPCNT + TBROWS.COUNT;
        COMMIT;
        EXIT
        WHEN DELETE_TABLE%NOTFOUND;
        END LOOP;
        CLOSE DELETE_TABLE;
        :2 := LOOPCNT;
        END;' ;
    --QUERYSQL := 'DELETE  FROM '||TableName||' where  EVENTID In ( SELECT eventID FROM  EventTableTemp  )' ;
    EXECUTE IMMEDIATE QUERYSQL  using  IN BATCHCOUNT , OUT LOOPCOUNT; --INTO  LOOPCOUNT;
    --LOOPCOUNT := ROWCOUNT;
    
     OUTCOUNT := OUTCOUNT+ LOOPCOUNT  ; 
    COMMIT;
    
    EXECUTE IMMEDIATE 'TRUNCATE TABLE EventTableTemp' ;
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ChangeTableTemp' ;
    
    IF CEIL((SYSDATE-STARTDATE)*24*60) >= 60 THEN
        EXIT;
    END IF;
  END LOOP;
  
  

  DELETE FROM zAuditPruningLog WHERE CreateDate <=SYSDATE-400;
  INSERT INTO zAuditPruningLog values(SYSDATE,TableName,OUTCOUNT,'Success',NULL);
  COMMIT;  
  
   EXCEPTION WHEN OTHERS THEN
  OUTCOUNT := SQLCODE;
  ErrorMsg := substr(SQLERRM, 1, 4000);
  ROLLBACK;
  DELETE FROM zAuditPruningLog WHERE CreateDate <= SYSDATE-400;
  INSERT INTO zAuditPruningLog values(SYSDATE,TableName,OUTCOUNT,'Fail',ErrorMsg);
  COMMIT; 

END z_Audit_Pruning;
/


--end script--