8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Script: part_hv_to_date.sql
( Download Script )
CREATE OR REPLACE FUNCTION part_hv_to_date (p_table_owner IN VARCHAR2, p_table_name IN VARCHAR2, p_partition_name IN VARCHAR2) RETURN DATE -- ----------------------------------------------------------------------------------- -- File Name : https://oracle-base.com/dba/miscellaneous/part_hv_to_date.sql -- Author : Tim Hall -- Description : Create a function to turn partition HIGH_VALUE column to a date. -- Call Syntax : @part_hv_to_date -- Last Modified: 19/01/2012 -- Notes : Has to re-select the value from the view as LONG cannot be passed as a parameter. -- Example call: -- -- SELECT a.partition_name, -- part_hv_to_date(a.table_owner, a.table_name, a.partition_name) as high_value -- FROM all_tab_partitions a; -- -- Does no error handling. -- ----------------------------------------------------------------------------------- AS l_high_value VARCHAR2(32767); l_date DATE; BEGIN SELECT high_value INTO l_high_value FROM all_tab_partitions WHERE table_owner = p_table_owner AND table_name = p_table_name AND partition_name = p_partition_name; EXECUTE IMMEDIATE 'SELECT ' || l_high_value || ' FROM dual' INTO l_date; RETURN l_date; END; /