I am newbie to Oracle and there is a list partitioned table based on a date column in the database. I am trying to create a table with one static partition and using automatic keyword to create next partitions automatically using the column name. Below is the syntax of my table DDL

CREATE TABLE INSURANCE_PAYMENT 
   (CLAIM_NUMBER VARCHAR2(26 CHAR), 
    CHECK_NUMBER VARCHAR2(20 CHAR), 
    CHECK_DATE TIMESTAMP, 
    PAY_TYPE VARCHAR2(10 CHAR), 
    CHECK_AMOUNT NUMBER(38,2) 
    D_CYCLE_DATE_R DATE
   )
   PARTITION BY LIST (D_CYCLE_DATE_R) AUTOMATIC (
   PARTITION P1 VALUES (TO_DATE('2024-05-29', 'YYYY-MM-DD'))); 

If query the ALL_TAB_PARTITIONS table to check the partition columns of the table it is giving below result

P1 is static partition and SYS_P6533 is a dynamic partition and I am trying to fetch partition name using partition value for example the P1 partition created for date ‘2024-05-29’ using below query

SELECT PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = 'INSURANCE_PAYMENT' AND HIGH_VALUE = 
TO_NUMBER(TO_CHAR(TO_DATE('2024-05-29', 'YYYY-MM-DD'), 'YYYYMMDDHH24MISS'))

I am getting below error

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 11 Column: 51

I am confused it says HIGH_VALUE is long type, but value inserted in the table for it looks weird “TO_DATE(‘ 2024-05-29 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)”.

Please help me how can i get the partition name based on the partition value. Please let me know if you need any more information.