CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
    AS
    V_PART_NM  VARCHAR2(20);
    V_PART_CNT NUMBER;
    V_DATE DATE;
    V_SCHEMA_NAME VARCHAR(15);
    V_TABLE_NAME VARCHAR2(30);
    V_PRT_NM    VARCHAR2(30);
    V_PRT_PNT    VARCHAR2(10);
    V_SQL VARCHAR2(200);
    V_PART_DATE_TABLE VARCHAR2(30);
    CURSOR C_DATE
    IS
    SELECT TRUNC(PART_DATE) FROM ERS_SRC.RIO_PART_DATE;
BEGIN
     V_TABLE_NAME :=TABLE_NAME;
       V_SCHEMA_NAME := SCHEMA_NAME;
       V_PART_DATE_TABLE := PART_DATE_TABLE;

    --EXECUTE IMMEDIATE 'SELECT MAX(TRUNC(TRANSACTION_END_TIME))  FROM ' || V_SCHEMA_NAME ||'.' || V_TABLE_NAME INTO V_DATE;
    --EXECUTE IMMEDIATE 'SELECT MAX(TRUNC(DATE_LOADED))  FROM ' || V_SCHEMA_NAME ||'.' || V_TABLE_NAME INTO V_DATE;
 OPEN C_DATE;
 LOOP
  FETCH C_DATE INTO V_DATE;
     IF C_DATE%NOTFOUND THEN
          EXIT;
     END IF;
  
    IF V_TABLE_NAME LIKE '%RIO%'  THEN 
       IF V_SCHEMA_NAME = 'ERS_SRC' THEN
         V_PART_NM := 'RIO_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_31123000';
    
    ELSIF V_SCHEMA_NAME = 'ERS_STAGE' THEN
      V_PART_NM := 'RIO_STG_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_STG_31123000';
    ELSIF V_SCHEMA_NAME = 'ERS_TARGET' THEN
       /*IF V_TABLE_NAME = 'ERS_RIO_HR_AGG' THEN
      V_PART_NM := 'RIO_TGT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_31123000';
    ELSIF V_TABLE_NAME = 'ERS_RIO_DAILY_EXT' THEN
    V_PART_NM := 'RIO_TGT_EXT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_EXT_31123000';
    ELSIF V_TABLE_NAME = 'ERS_RIO_FACT' THEN
      V_PART_NM := 'RIO_TGT_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    V_PRT_NM := 'RIO_TGT_31123000';
    END IF;*/
    select SUBSTR(PARTITION_NAME,1,LENGTH(PARTITION_NAME) - 8)|| TO_CHAR(V_DATE ,'DDMMYYYY') into V_PART_NM   from all_tab_partitions
    where table_name = V_TABLE_NAME AND ROWNUM< 2;
    
    select SUBSTR(PARTITION_NAME,1,LENGTH(PARTITION_NAME) - 8)|| '31123000' into V_PRT_NM  from all_tab_partitions
    where table_name = V_TABLE_NAME AND ROWNUM< 2;
    
    END IF;
    ELSIF V_TABLE_NAME LIKE '%CER%' THEN
      V_PART_NM := 'CER_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    ELSIF V_TABLE_NAME LIKE '%PAC%' THEN
      V_PART_NM := 'CER_' || TO_CHAR(V_DATE ,'DDMMYYYY') ;
    END IF;
    DBMS_OUTPUT.PUT_LINE(V_PART_NM);
    
    SELECT COUNT(*) INTO V_PART_CNT FROM ALL_TABLES A,ALL_TAB_PARTITIONS B where A.OWNER NOT IN ('SYS','SYSTEM') AND
    A.TABLE_NAME = B.TABLE_NAME
    AND A.PARTITIONED = 'YES'
    AND B.TABLE_NAME = V_TABLE_NAME 
    AND B.TABLE_OWNER = V_SCHEMA_NAME 
    AND B.PARTITION_NAME = V_PART_NM;
    
    DBMS_OUTPUT.PUT_LINE('.....SPLITIING PARTITION..........');
    IF V_PART_CNT = 0 THEN
       V_SQL := 'ALTER TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' SPLIT PARTITION ' || V_PRT_NM ||' AT (TO_DATE(' || '''' || TO_CHAR(V_DATE +1  ,'DD/MM/YYYY') ||''''   
     || ',''DD/MM/YYYY''))' || ' INTO ( PARTITION '|| V_PART_NM || ' ,PARTITION ' ||V_PRT_NM ||') PARALLEL 3' ;
 
      DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL;

     --EXECUTE IMMEDIATE 'ANALYZE TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' COMPUTE STATISTICS';
     --DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME,V_TABLE_NAME, DEGREE => 3);
   
     --DBMS_OUTPUT.PUT_LINE('ANALYZE TABLE ' || SCHEMA_NAME ||'.' ||V_TABLE_NAME ||' COMPUTE STATISTICS');
        -- DBMS_OUTPUT.PUT_LINE('.....ANALYZING TABLE..........');
    ELSE
    DBMS_OUTPUT.PUT_LINE('.....PARTITION ALREAY EXIST..........');
    --DBMS_STATS.GATHER_TABLE_STATS (SCHEMA_NAME,V_TABLE_NAME, DEGREE => 3);
    
    END IF;
END LOOP;   
   EXCEPTION
   WHEN OTHERS THEN
   NULL;

   END PARTITION_TEST;
/

    CURSOR C_DATE
    IS
    EXECUTE IMMEDIATE 'SELECT TRUNC(PART_DATE) FROM ' ||PARAMETER_SCHEMA||'.'||PARAMETER_TABLE||

END LOOP;   
   EXCEPTION
   WHEN OTHERS THEN
   NULL;