Extract date from partition high_value

by KeyMaker00   Last Updated June 19, 2018 10:06 AM

In the USER_TAB_PARITIONS, there is the attribute PARITION_DATE, formatted TIMESTAMP' 2017-01-01 00:00:00'.

My problem: I want to convert PARITION_DATE into a date with a simple query (without procedure).

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;

Note: HIGH_VALUE is saved as long (in the schema).

Tags : sql oracle


Related Questions



Altering the location of Oracle-Suggested Backup

Updated July 03, 2016 08:02 AM

Ways to Schedule Rman backups

Updated February 24, 2017 12:06 PM

EMCA insists that it cannot connect to the service

Updated August 28, 2017 14:06 PM

Password for Oracle Database 18c user “system”

Updated August 02, 2018 06:06 AM