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 have a requirement where I would like to use the HIGH_VALUE field of user_partition table as a Varchar2 or TimeStamp value and I would like to get it used as a Function in SQL Query.

Something like stated below, but that does not seems to be working currently.

create or replace
function SomeFunction(high_val LONG raw) 
return varchar2
is
   lHigh_value     LONG;
   strDate_clause  VARCHAR2(100);
begin
   lHigh_value := high_val;
   strDate_clause := lHigh_value;
   return strDate_clause;
end;

Here are the details of the requirement:

I have an application where I have 1000’s of table and all the table are divided in modules , so for example web module has 100 tables , all having partitioning and there name start with a prefix WEB_USER_5MIN for example.

For my application I would like to delete old partition tables (for example if the partition is 2 days old or 2 months old) based on checking of the HIGH_VALUE field since I can’t take the partition names as they are autogenrated by the system.

I have written the following code which is a procedure which works fine.

FOR key_val,max_no_tbl IN select key,value from tbldataconfig where key like 'MaxNoTables5min%' 


FOR x IN (SELECT table_name, partition_name, high_value
FROM user_tab_partitions WHERE table_name =key_val)
LOOP
          IF (TO_DATE (SUBSTR (x.high_value, 11, 10), 'YYYY-MM-DD') < max_no_tbl)
          THEN
          BEGIN
                   lv_drop_part :=
                             'ALTER TABLE '|| x.table_name|| ' DROP PARTITION '||                     x.partition_name|| ' UPDATE GLOBAL INDEXES';
                   DBMS_OUTPUT.put_line ( ' Dropping Partition:'|| x.partition_name|| CHR (10));
END LOOP;

LOOP;

But the problem with the above code is that it is having multiple loops which can be avoided if I can write the code in the following way.

FOR key_val,max_no_tbl IN select key,value from tbldataconfig where key like 'MaxNoTables5min%' 

insert into tbl_marked_for_delete SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE  table_name like ''%' || key_val || '%5min || ''' and somefunction(HIGH_VALUE) < (SYSTIMESTAMP-numtodsinterval(max_no_tbl, 'DAY'))

LOOP

Please suggest how can I write the code in order to get HIGH_VALUE Returned as a TIMESTAMP of VARCHAR2 value inside a function.

There are many number of such procedures.

share|improve this question
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.