Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
Thanks Cris for the reply. This is working fine for me. Please modify the accordingly for me so that I give my own "table_owner" and my own "table_name" at run time(I will be getting the value of table_owner at run time, however I already have the table name).. Thanks in advance. –  Rajesh Feb 10 '13 at 13:05
    
This query will return all the partitions (you can see) in the database older than 90 days. You just need to update the where clause to set the exact table_owner and table_name. –  Chris Saxon Feb 10 '13 at 15:44
    
yes, it worked. However I have a small query. I wanted to write this as stored procedure with declare and begin section. when I include begin and declare section in the code , I get the following error. Declare section is required to make that 90 days configurable.Th error is RA-06550: line 5, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. –  Rajesh Feb 11 '13 at 8:53
    
If you're having problems getting this working in a PL/SQL block then please open a new question. –  Chris Saxon Feb 11 '13 at 9:36
    
fdfdfdfdasfd erffe –  Rajesh Feb 14 '13 at 14:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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