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.