conversion of high_value to date datatype

Question

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what like this.

declare
       dt date;
   time_to_stay number:=1; --CONFIGURE AS PER THE REQUIREMENT
    begin
    for x in (select partition_name , high_value, partition_position
          from user_tab_partitions
          where table_name = 'DEMO')
loop
   execute immediate 'select '||x.high_value||' from dual' into dt;      
     if to_date(dt) < sysdate-time_to_stay AND x.partition_position <>'1'     then
execute immediate 'ALTER TABLE DEMO DROP PARTITION '|| x.partition_name|| ' UPDATE GLOBAL INDEXES';

This query executes but does not deletes the partitions. However I see that main issue is converison of LONG datatype of high_value to DATE to make the comparision between high_value and sys_date.

How can I fix this?

Answer 1

There's a lot of restrictions on using long columns which make them tricky to work with. One way around this problem is to convert the data into an XML structure. Once you've done this you can use XPATH expressions on the XML compare the field values.

The conversion to XML can be done using dbms_xmlgen.getxmltype passing in your query as a string, like so:

select dbms_xmlgen.getxmltype('
  select p.table_owner, 
         p.table_name, 
         p.high_value
  from   all_part_key_columns k, 
         all_tab_cols c, 
         all_tab_partitions p
  where  k.owner = c.owner
  and    k.column_name = c.column_name
  and    k.name = c.table_name
  and    k.owner = p.table_owner
  and    k.name = p.table_name
  and    (c.data_type = ''DATE'' or 
          c.data_type like ''TIMESTAMP%'')') as xml
from   dual;

This will give you data like:

<ROWSET>
 <ROW>
  <TABLE_OWNER>TABLE_OWNER</TABLE_OWNER>
  <TABLE_NAME>TABLE</TABLE_NAME>
  <HIGH_VALUE>TIMESTAMP' 2013-02-06 00:00:00'</HIGH_VALUE>
 </ROW>
</ROWSET>

You can then parse the high_value date/timestamp into an actual date. Having done this you can use it for comparison against an actual date to return you a list of the partitions affected:

with date_partitions as
  (select dbms_xmlgen.getxmltype('
select p.table_owner, 
       p.table_name, 
       p.high_value
from   all_part_key_columns k, 
       all_tab_cols c, 
       all_tab_partitions p
where  k.owner = c.owner
and    k.column_name = c.column_name
and    k.name = c.table_name
and    k.owner = p.table_owner
and    k.name = p.table_name
and    (c.data_type = ''DATE'' or 
        c.data_type like ''TIMESTAMP%'')') 
          as xml
   from   dual)
SELECT  x.*
FROM    date_partitions p, 
        xmltable('/ROWSET/ROW'
          passing p.xml
          columns table_owner varchar2(30) 
                    path '/ROW/TABLE_OWNER',
                  table_name varchar2(30) 
                    path '/ROW/TABLE_NAME',
                  high_value varchar2(30) 
                    path '/ROW/HIGH_VALUE'
         ) x
where   to_date(substr(x.high_value,
                     instr(high_value, '''')+2,
                     19),
              'yyyy-mm-dd hh24:mi:ss') <= sysdate-90

Answer 2

I think there is another solution to this.

DECLARE

   CURSOR c1 IS
   SELECT
      HIGH_VALUE
   FROM
      USER_TAB_PARTITIONS
   WHERE
      TABLE_NAME = 'MYTAB';

   ls_sql_statement   VARCHAR2(1000);
   ld_date_val        DATE;

BEGIN

   FOR c1rec IN c1 LOOP
      ls_sql_statement := 'SELECT '||c1rec.HIGH_VALUE||' FROM DUAL';
      EXECUTE IMMEDIATE ls_sql_statement INTO ld_date_val;
      --Do more stuff here with ld_date_val
   END LOOP;

END;
/

Answer 3

You can use guls answer to rename all partitions. Afterwards you can work with the partition name instead of the high_value:

DECLARE
        current_row           user_tab_partitions%ROWTYPE;
        CURSOR partition_cursor IS SELECT * FROM user_tab_partitions WHERE interval = 'YES';
        high_value_string   VARCHAR2(1000);
        high_value_date      DATE;
        new_name   VARCHAR2(1000);
BEGIN
    FOR current_row IN partition_cursor
    LOOP
        high_value_string := 'SELECT ' || current_row.HIGH_VALUE || ' FROM DUAL';
        EXECUTE IMMEDIATE high_value_string INTO high_value_date;
        new_name := 'P' || to_char(high_value_date, 'YYYY_MM_DD');
        
        IF current_row.partition_name != new_name THEN
            DBMS_OUTPUT.PUT_LINE('RENAME ' || current_row.table_name || ' ' || current_row.partition_name || ' TO ' || new_name);
            EXECUTE IMMEDIATE 'ALTER TABLE ' || current_row.table_name || ' RENAME PARTITION ' || current_row.partition_name || ' TO ' || new_name;
        END IF;
    END LOOP;
END;