Convert the Highvalue of user_partition in varchar2

Question

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.