2

This question already has an answer here:

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;

marked as duplicate by JSapkota, mustaccio, dezso, Marco, LowlyDBA Jun 20 '18 at 22:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • @JSapkota: It's not a duplicate since the question is doing it without a procedure. – KeyMaker00 Jun 19 '18 at 10:16
3

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
 /
  • In my console, it's written ORA-01843: "not a valid month". Any idea? I have tried 'YYYY-MM-DD' but then '"day of month must be between 1 and last day of month"' – KeyMaker00 Jun 19 '18 at 13:31
  • Strange What is database version? – Sam Jun 19 '18 at 13:33
  • Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production – KeyMaker00 Jun 19 '18 at 13:38
  • I have set it as answer since it has helped me the most, with the answer from @sandman. – KeyMaker00 Jun 19 '18 at 13:55
  • I gave credit to Sandman :-) – Sam Jun 19 '18 at 13:56
3

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;
  • I have no right on DBA_TAB_PARTIONS but with USER_TAB_PARTITIONS. And it rocks. Thanks. – KeyMaker00 Jun 19 '18 at 12:57
  • I cannot filter with this where clause. E.g. The timestamp TIMESTAMP' 2017-01-01 00:00:00' is not filtered. Any idea? – KeyMaker00 Jun 19 '18 at 13:17
0

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...

Not the answer you're looking for? Browse other questions tagged or ask your own question.