select  
      partition_name
from
      user_tab_partitions
  where
       table_name = 'RB' and 
       HIGH_VALUE <= 1234

SQL> create or replace function high_value 
  2    (p_table in varchar2, p_part in varchar2)
  3  return varchar2
  4  is
  5    hv varchar2(32767);
  6  begin
  7    select high_value into hv 
  8    from user_tab_partitions 
  9    where table_name = p_table
 10      and partition_name = p_part;
 11    return hv;
 12  end;
 13  /

Function created.

SQL> col table_name format a18
SQL> col partition_name format a17
SQL> col high_value format a40 trunc
SQL> select table_name, partition_name, 
  2         high_value(table_name, partition_name) high_value
  3  from user_tab_partitions
  4  order by 1, 2
  5  /

TABLE_NAME         PARTITION_NAME    HIGH_VALUE
------------------ ----------------- ----------------------------------------
COMPOSITE_RNG_HASH SALES_2005        TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-M
COMPOSITE_RNG_HASH SALES_2006        TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-M
COMPOSITE_RNG_HASH SALES_2007        TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M
COMPOSITE_RNG_HASH SALES_2008        TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
COMPOSITE_RNG_HASH SALES_FUTURE      MAXVALUE
COMPOSITE_RNG_HASH SALES_PRE05       TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-M
SYSTEMLOG          HIGHTIME          MAXVALUE
SYSTEMLOG          OCT2007_07        20071007
SYSTEMLOG          OCT2007_14        20071014
SYSTEMLOG          OCT2007_21        20071021
SYSTEMLOG          OCT2007_28        20071027
T                  P1                100
T                  P2                MAXVALUE
TB_HXL_LIST_PART   P_1               1
TB_HXL_USER_SPLIT  HXL_USER_20111001 TO_DATE(' 2011-10-02 00:00:00', 'SYYYY-M

CREATE GLOBAL TEMPORARY TABLE GTT_COMMON (ATTR1 VARCHAR2(100), ATTR2 VARCHAR2(4000));

   FOR a in (  SELECT  partition_name, HIGH_VALUE from user_tab_partitions  where table_name = 'XYZ')
    LOOP
      insert into GTT_COMMON values(a.partition_name, A.HIGH_VALUE );
   END LOOP;
   END;
  /

ATTR1        ATTR2
------------ --------------
P_MAX        MAXVALUE
P_20120305   1330961409000
P_20120302   1330702209000
P_20120301   1330615811000
P_20120229   1330529409000
P_20120228   1330443009000
P_20120227   1330356610000
P_20120224   1330097411000
P_20120223   1330011010000
P_20120222   1329924609000
P_20120221   1329838210000
P_20110516   1305545402000
P_20110211   1297420202000
P_20100719   1279497600000


   SELECT ATTR1,TO_NUMBER(DECODE(RTRIM(ATTR2),'MAXVALUE' , NULL,RTRIM(ATTR2))) HIGH_VALUE 
FROM GTT_COMMON
 WHERE TO_NUMBER(DECODE(RTRIM(ATTR2),'MAXVALUE' , NULL,RTRIM(ATTR2))) <= [b]1330529409000[/b]

SQL> /

ATTR1                            HIGH_VALUE
-------------------------------------------
P_20120229                    1330529409000
P_20120228                    1330443009000
P_20120227                    1330356610000
P_20120224                    1330097411000
P_20120223                    1330011010000
P_20120222                    1329924609000
P_20120221                    1329838210000
P_20110516                    1305545402000
P_20110211                    1297420202000
P_20100719                    1279497600000

10 rows selected.

WITH gtt_common 
     AS (SELECT partition_name attr1, 
                high_value     attr2 
         FROM   user_tab_partitions 
         WHERE  table_name = 'XYZ') 
SELECT attr1, 
       To_number(Decode(Rtrim(attr2), 'MAXVALUE', NULL, 
                                      Rtrim(attr2))) high_value 
FROM   gtt_common 
WHERE  To_number(Decode(Rtrim(attr2), 'MAXVALUE', NULL, 
                                      Rtrim(attr2))) <= 1330529409000 

SQL> select table_name, partition_name
  2  from user_tab_partitions
  3  where high_value(table_name, partition_name) = 'MAXVALUE'
  4  order by 1, 2
  5  /
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
COMPOSITE_RNG_HASH             SALES_FUTURE
T                              P2

---------Code in procedure------


FOR curs in (
                         select
                           table_name ,
                           partition_name
                         from
                           user_tab_partitions
                         where
high_value(table_name, partition_name)  <= to_char(my_value) and table_name = UPPER(v_master_table)
                    )
LOOP
......................
......................
END LOOP;
--------------------------------



LINE/COL ERROR
-------- -----------------------------------------------------------------
182/26   PL/SQL: SQL Statement ignored
188/27   PL/SQL: ORA-00904: : invalid identifier
188/27   PLS-00231: function 'HIGH_VALUE' may not be used in SQL