I'm using Oracle 12. I have a table which is partitioned by column dt which is DATE type.
For partitioning I've used the following syntax:
PARTITION BY RANGE (dt) (
partition part_01 values less than (DATE '2023-08-01')
)
So other partitions are created automatically for each dt.
The task is: I need to check if the partition for a particular date exists and if so, truncate the partition.
I can't directly use partition name because it doesn't contain any information about date (naming pattern is default and is like SYS_PXXXXXXX).
So I try to use user_tab_partition view. Column high_value of that view contain the highest dt for rows stored in that partition.
As high_value column data type is long first I have to write the code using dbms_xmlgen.getxmltype function to get XML-representation of available high_values values, then parse it and get the date that I need.
That looks too complicated for such a simple task.
What's the best way for designing such a routine?
Maybe a better approach is to manually control the partitions names when they are created (and hence be able to access them in an easy way)?
