Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

with q_test1 (xml) as (
  SELECT XMLTYPE(
q'{<?xml version='1.0' encoding='UTF-8'?>
<TEST>
  <RECORD>
    <ID>1</ID>
    <DT>2023-08-26</DT>
  </RECORD>
  <RECORD>
    <ID>2</ID>
    <DT>Invalid date</DT>
  </RECORD>
</TEST>}'
         )
  FROM   dual
)
select x.*
       , validate_conversion(x.dt as date, 'yyyy-mm-dd')
from   q_test1 q1
       CROSS APPLY
       XMLTABLE(
         '/TEST/RECORD'
         passing q1.xml
         columns
           ID NUMBER        PATH 'ID'
         , DT VARCHAR2(100) PATH 'DT'
       ) x;
ORA-43909: invalid input data type

with q_test1 (xml) as (
  SELECT XMLTYPE(
q'{<?xml version='1.0' encoding='UTF-8'?>
<TEST>
  <RECORD>
    <ID>1</ID>
    <DT>2023-08-26</DT>
  </RECORD>
  <RECORD>
    <ID>2</ID>
    <DT>Invalid date</DT>
  </RECORD>
</TEST>}'
         )
  FROM   dual
)
select x.id,
       x.dt,
       DUMP(x.dt)
       --, validate_conversion(x.dt as date, 'yyyy-mm-dd')
from   q_test1 q1
       CROSS APPLY
       XMLTABLE(
         '/TEST/RECORD'
         passing q1.xml
         columns
           ID NUMBER        PATH 'ID'
         , DT VARCHAR2(100) PATH 'DT'
       ) x;


ID
DT
DUMP(X.DT)--,VALIDATE_CONVERSION(X.DTASDATE,'YYYY-MM-DD')


1
2023-08-26
Typ=1 Len=10: 50,48,50,51,45,48,56,45,50,54

2
Invalid date
Typ=1 Len=12: 73,110,118,97,108,105,100,32,100,97,116,101


CREATE TABLE test (value VARCHAR2(10));
INSERT INTO test (value) VALUES ('abcdef');
1 rows affected

-- Should also be Typ=1
SELECT DUMP(value) FROM test;


DUMP(VALUE)


Typ=1 Len=6: 97,98,99,100,101,102


-- Even simpler test.
SELECT dt, validate_conversion(x.dt as date, 'yyyy-mm-dd')
FROM   XMLTABLE(
         '/TEST'
         passing XMLTYPE('<TEST><DT>Invalid date</DT></TEST>')
         columns
           DT VARCHAR2(100) PATH 'DT'
       ) x;
ORA-43909: invalid input data type

SELECT dt,
       DUMP(dt) AS dump
       --, validate_conversion(x.dt as date, 'yyyy-mm-dd')
FROM   XMLTABLE(
         '/TEST'
         passing XMLTYPE('<TEST><DT>Invalid date</DT></TEST>')
         columns
           DT VARCHAR2(100) PATH 'DT'
       ) x;


DT
DUMP


Invalid date
Typ=1 Len=12: 73,110,118,97,108,105,100,32,100,97,116,101


SELECT validate_conversion(CAST('Invalid date' AS VARCHAR2(20)) as date, 'yyyy-mm-dd')
FROM   DUAL


VALIDATE_CONVERSION(CAST('INVALIDDATE'ASVARCHAR2(20))ASDATE,'YYYY-MM-DD')


0

ID	DT	DUMP(X.DT)--,VALIDATE_CONVERSION(X.DTASDATE,'YYYY-MM-DD')
1	2023-08-26	Typ=1 Len=10: 50,48,50,51,45,48,56,45,50,54
2	Invalid date	Typ=1 Len=12: 73,110,118,97,108,105,100,32,100,97,116,101