Extract date from partition high_value In the USER_TAB_PARITIONS, there is the attribute HIGH_VALUE, formatted TIMESTAMP' 2017-01-01 00:00:00'.

My problem:
I want to convert HIGH_VALUE into a date with a simple query (without procedure, and HIGH_VALUE is as long stored).

So far I could transform it with that procedure:

set serveroutput on size 30000;
DECLARE
   CURSOR c1 IS
   SELECT HIGH_VALUE, PARTITION_NAME
   FROM USER_TAB_PARTITIONS
   WHERE TABLE_NAME = 'MY_TABLE';

   partition_date   VARCHAR2(100);
   partition_name   VARCHAR2(1000);
   date_val         DATE;
   name_val         VARCHAR2(100);
BEGIN
   FOR c1rec IN c1 LOOP
      partition_date := 'SELECT ' || c1rec.HIGH_VALUE || ' FROM DUAL';
      partition_name := 'SELECT ' || c1rec.PARTITION_NAME || ' FROM DUAL';
      EXECUTE IMMEDIATE partition_date INTO date_val;
      DBMS_OUTPUT.PUT_LINE(date_val);
      DBMS_OUTPUT.PUT_LINE(c1rec.PARTITION_NAME);
   END LOOP;
END;
/


My question:
Is there any possibility to do it without a procedure? For example like:

SELECT HIGH_VALUE, PARTITION_NAME
FROM USER_TAB_PARTITIONS
WHERE 1=1
  AND TABLE_NAME = 'MY_TABLE'
  AND HIGH_VALUE &lt; sysdate-1;
Something like this?  This will help you extract the LONG into text without a procedure, but I'm not sure about what SYSDATE logic you want to limit your result set to.  You would have to play with that final WHERE clause to get the right values.

SELECT * from (
  SELECT PARTITION_NAME,
    extractvalue
      ( dbms_xmlgen.getxmltype
        ( 'select high_value
           from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
        '//text()' ) as high_value 
  FROM DBA_TAB_PARTITIONS t
  WHERE TABLE_NAME = 'MY_TABLE')
WHERE to_char(add_months(sysdate,-1),'SYYYYMMDD') &lt; high_value;