Extracting Oracle high_value from user_tab_partitions

As always with Oracle its pretty ‘easy‘ and ‘straightforward‘ – you have to convert the high_value to XML and then extract it using XPATH (omg!). Let me guess, it would cost a fortune to provide native function/view that could do that…

SELECT partition_name,
extractValue(dbms_xmlgen.getxmltype('SELECT high_value
            FROM user_tab_partitions up2
            WHERE up2.partition_position = ' || up1.partition_position),
    '//ROW/HIGH_VALUE') AS high_value
FROM user_tab_partitions up1
ORDER BY high_value;

Enjoy (that simplicity) !
This is the simplest solution I’ve found for extracting TIMESTAMP/DATE from high_value, without using stored procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *

*