Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question.
Asked: June 28, 2016 - 10:28 am UTC
Last updated: June 29, 2016 - 7:00 am UTC
Version: 11
Viewed 1000+ times
SQL> create or replace 2 function par_name(p_table_name varchar2, p_seq date) return varchar2 is 3 l_high_value varchar2(2000); 4 l_date date; 5 begin 6 for i in ( 7 select partition_name, high_value 8 from user_tab_partitions 9 where table_name = p_table_name 10 order by partition_position ) 11 loop 12 l_high_value := i.high_value; 13 execute immediate 'select '||l_high_value||' from dual' into l_date; 14 if l_date = p_seq then 15 return i.partition_name; 16 end if; 17 end loop; 18 19 return null; 20 end; 21 / Function created. SQL> SQL> select par_name('PARENT',date '2015-07-01') from dual; PAR_NAME('PARENT',DATE'2015-07-01') ---------------------------------------------------------------------------------------------------------------------------------- P_201506 1 row selected. SQL> SQL> set timing on SQL> declare 2 x varchar2(30); 3 begin 4 for i in 1 .. 1000 loop 5 x := par_name('PARENT',date '2015-07-01'); 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.19 SQL> SQL>
A reader, June 29, 2016 - 6:09 am UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library