Hi,
I had a case to drop partition from a interval partition table monthly basis dynamically, i have written the below code but the high value is coming as blank.
I had written below code to get the value
CREATE OR REPLACE FUNCTION GFL_COMMON_DATA.GET_HIGH_VALUE_AS_DATE (
p_TableName IN VARCHAR2,
p_PatitionName IN VARCHAR2
) RETURN DATE
IS
v_LongVal LONG;
BEGIN
SELECT HIGH_VALUE INTO v_LongVal
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = p_TableName
AND PARTITION_NAME = p_PatitionName;
RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/
When i run the below query i am getting blank value
select GET_HIGH_VALUE_AS_DATE('TESTA', 'SYS_P4410') a from dual;
a
-----
But when i checked from all_tab_partition table i can see the date parameter in the high value