How to Automate Max Partition Alert

http://www.dbaoncall.net/references/ht_max_partition_alert.html

One of my ETL scripts once failed with the following error:
ORA-14400: inserted partition key is beyond highest legal partition key

So I decided to write an alert which would check my date partitioned tables and pre-warn me of the ones which I have to add partitions to. The problem was that the information writes the maximum date condition of a partition is in column high_value in user_tab_partitions table. This column is still a long type, and the information is in such a format:
TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')

So the problem was to extract the date portion from a long column and evaluate the maximum date value per partitioned table.

For this I wrote the following function first:
--=====================================================================
--= Function : MaxPartValue
--= By : Potkin Nemat on 21-Jul-2003
--= Parameters :
--= IN : p_table (name o fthe table to evaluate max date partition for.
--= Return : max_date
--= Version :
--= 1.0 wrote the script
--=====================================================================
create or REPLACE FUNCTION MaxPartValue(p_table IN VARCHAR2)
RETURN DATE
IS

TYPE cv_type IS REF CURSOR;
cv cv_type;

p_query VARCHAR2(2000); -- used to retrieve the value of the long
column
p_text VARCHAR2(32000); -- used to store the long value in for
substr operation

CURSOR csub(p_text IN VARCHAR2) IS
SELECT to_date(substr(p_text,11,10),'YYYY-MM-DD') v_date
FROM dual;

max_date DATE;

BEGIN


p_query := 'SELECT high_value FROM user_tab_partitions WHERE
table_name = upper(';
p_query := p_query ||''''|| p_table||''''||')';

max_date := to_date('01-JAN-01'); -- initialise max_date with a date
in the past

OPEN cv FOR p_query;
LOOP
FETCH cv INTO p_text; -- the value of the long column is now
assigned to a varchar2 var
EXIT WHEN CV%NOTFOUND;
FOR csubrec IN csub(p_text) LOOP
IF max_date < csubrec.v_date THEN
max_date := csubrec.v_date; -- record the max partition
date per table
END IF;
END LOOP;
END LOOP;

RETURN max_date;

END MAXpartVALUE;

Once this function is created then it can be used in a simple query to list all the partitioned tables and their maximum date threshold partition:
SELECT p.name,to_char(maxpartvalue(p.name),'dd-MON-yyyy')
FROM user_part_key_columns p
,user_tab_columns t
WHERE p.object_type LIKE 'TABLE%' -- Oracle has spaces after
TABLE in this column!!
AND t.column_name = p.column_name
AND t.table_name = p.NAME
AND t.data_type = 'DATE'
ORDER BY 2 DESC