Partition Function

[lesstjm]

I need help. I have a range partition table. I need to have a function that I can pass in a date and table name and get back the partition name for that table that the date would fall in.

So if I had a table with three partitions with the following names. May2008, June2008, and Aug2008 and I passed in 13-JUN-08. I should get back the June2008 partition name back.

Can anyone help me with this? For Oracle 10.2.0.3

[PAVB]

I'm not doing your job but here is a hint... look at dba_tab_partitions.

[lesstjm]

That is alright if you couldn't figure it out. I did.

set serveroutput on
DECLARE
long_var LONG;
var_var VARCHAR2(2000);
v_part VARCHAR2(100);
v_date date;

cursor c1 is
SELECT partition_name,high_value
from user_tab_partitions
where table_name in ('WIRE_ACCT_STATIC_INFO')
order by partition_position;

BEGIN
v_date := to_date('01-JUL-2008','DD-MON-YYYY');
for v1 in c1 loop

var_var := substr(v1.high_value,10,11);

if (v_date < to_date(var_var,'YYYY-MM-DD') ) then
dbms_output.put_line(v1.partition_name||' '||var_var);
return;
end if;

end loop;

END;

[PAVB]

That is alright if you couldn't figure it out. I did.

Lucky you... you just bought your life-time pass to my black list.

Have a good life.