Oracle Interval Partitioning - Find High Value

SET TIMING ON
set serveroutput on size 100000

DECLARE
   l_table_name      VARCHAR2 (30) := 'MY_INT_PART_TABLE';
   l_table_owner     VARCHAR2 (30) := 'EMP';
   l_highvalue       VARCHAR2 (8000);
   l_high_value_dt   DATE;

BEGIN

   FOR lrec
      IN (  SELECT partition_name
              FROM all_tab_partitions
             WHERE     table_name = l_table_name
                   AND table_owner = l_table_owner
                   AND partition_name  LIKE 'SYS%'
                   AND interval = 'YES'
          ORDER BY 1)
   LOOP
     -- high_value is a long column. Select into a variable and extract the date/number from it.
      SELECT high_value
        INTO l_highvalue
        FROM all_tab_partitions
       WHERE     table_name = l_table_name
             AND table_owner = l_table_owner
             AND partition_name = lrec.partition_name;

    EXECUTE IMMEDIATE 'select ' || l_highvalue || ' from dual' INTO l_high_value_dt;
    DBMS_OUTPUT.PUT_LINE ('Partition Name:' || lrec.partition_name ||', High Value DT: '||to_char(l_high_value_dt,'yyyymmdd-hh24:mi:ss'));

    -- Continue to do partition related tasks (drop/analyze etc)
    -- alter table mytable drop partition lrec.partition_name;

  END LOOP;

  COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error getting high value for partitions of ' || l_table_name);
      DBMS_OUTPUT.put_line ('ERROR MESG : ' || SQLERRM);
      RAISE;
END;
/      

DECLARE
   c_table_name   VARCHAR2 (30) := 'NUMBER_INT_PART_TABLE';
   c_table_owner  VARCHAR2 (30) := 'EMP';
   v_id           NUMBER := 0;

BEGIN

   FOR lrec
      IN (  SELECT partition_name
              FROM all_tab_partitions
             WHERE     table_name = c_table_name
                   AND table_owner = c_table_owner
                   AND partition_name  LIKE 'SYS%'
                   AND interval = 'YES'
          ORDER BY 1)
   LOOP
      -- Get the maximum id from partition based on the name.
      -- In the previous example, high value is selected into variable directly. Here we're using partition name to select the value.

      EXECUTE IMMEDIATE
            'select max(id) from '
         || c_table_name
         || '  partition ('
         || lrec.partition_name
         || ')'
         INTO v_id;

         DBMS_OUTPUT.put_line (
               'Partition Name: '
            || lrec.partition_name
            || ' with max  id <'
            || v_id
            || '>');

      -- Do any partition related operation (drop/analyze etc)

   END LOOP;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERR: Partition select : ' || SQLERRM);

END;
/