Extract date from partition high_value [duplicate]

Question

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 < sysdate-1;

Answer 1

Slightly tweaked Sandman's code

WITH date_partition AS (
SELECT
    partition_name,
    extractvalue(dbms_xmlgen.getxmltype('select high_value
       FROM   USER_TAB_PARTITIONS WHERE table_name = '''
        || t.table_name
        || ''' and PARTITION_NAME = '''
        || t.partition_name
        || ''''),'//text()') AS high_value
FROM
    user_tab_partitions t
   WHERE TABLE_NAME = 'MY_TABLE'
 ) 
 ,final_result
  (SELECT
  partition_name,
  TO_DATE(substr(high_value,11,10),'YYYY-DD-MM') high_value
FROM
date_partition
)
 SELECT partition_name,high_value FROM final_result
 WHERE HIGH_VALUE < SYSDATE -1
 /

Answer 2

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') < high_value;

Answer 3

After an epic fight, we got it:

WITH date_partition AS (
    SELECT  partition_name,
            TO_DATE(
                substr(
                    extractvalue(dbms_xmlgen.getxmltype(
                        'select high_value FROM USER_TAB_PARTITIONS WHERE table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.partition_name || ''''
                    ), '//text()')
                , 12, 10),
            'YYYY-MM-DD') AS high_value_t
    FROM user_tab_partitions t
    WHERE TABLE_NAME = 'MY_TABLE'
) 
SELECT high_value_t
FROM date_partition
WHERE high_value_t > sysdate -1;

The problem is the Europe VS US format, and some complications...