CREATE OR REPLACE PROCEDURE BI_COMPLIANCE_DEV2.PRO_AML_DM_COL_HIS_DEL(P_TABLE_ANME IN VARCHAR2) AS

  V_TABLE_NAME     VARCHAR2(100) := P_TABLE_ANME;
  V_PART_NAME      VARCHAR2(100);
  V_EXEC_SQL       VARCHAR2(500);

  CURSOR TABLE_PARTITION_LIST IS
    SELECT T.OBJECT_NAME    TABLE_NAME,
           T.SUBOBJECT_NAME PARTITION_NAME,
           T.CREATED        CREATE_DATE
      FROM USER_OBJECTS T
     WHERE  T.OBJECT_NAME = UPPER(V_TABLE_NAME)
       AND T.OBJECT_TYPE = 'TABLE PARTITION'
       AND T.GENERATED = 'Y'
       AND T.CREATED < SYSDATE - 30;

BEGIN

  FOR PART_NAME IN TABLE_PARTITION_LIST LOOP
    V_PART_NAME := PART_NAME.PARTITION_NAME;
    V_EXEC_SQL  := 'ALTER TABLE ' || V_TABLE_NAME || ' DROP PARTITION ' || V_PART_NAME;
  
    EXECUTE IMMEDIATE V_EXEC_SQL;
    COMMIT;
  END LOOP;
END;