CODEWITHSUNDEEP
oracle-databasedatexmltable
Alexander Ginzburg
Alexander Ginzburg

Reputation: 1

Using VALIDATE_CONVERSION function with results of XMLTABLE operator produces ORA-43909 error

The issue showed up when trying to fix the conversion error which had been raised while parsing XML text containing dates. Since input XML was rather large, it was decided to create a query displaying erroneous pieces of XML elements (just to check whether they could be processed other way). Using the VALIDATE_CONVERSION function in a WHERE clause seemed a good choice for that purpose. However, the query failed as soon as that function was involved. Because the original query looked too bulky, I tried to strip it for clarification. The result is below

--------- This query works fine
with q_test as (
  select 1 as id, '2023-08-26' as dt from dual
  union all select 2, 'Invalid date' from dual
)
select q.*, validate_conversion(q.dt as date, 'yyyy-mm-dd')
from q_test q;
--------- This one fails
with q_test1 as (
  select XMLTYPE('<?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>') xml
  from dual
),
q_test2 as (
  select cast(t1.id as number) as id, t1.dt
  from q_test1 q1,
    XMLTABLE('//TEST/RECORD' passing q1.xml
    columns
      ID    path 'ID'
      , DT  path 'DT'
    ) t1
)
select q2.*, validate_conversion(q2.dt as date, 'yyyy-mm-dd') from q_test2 q2;

Here the dubious function was placed in SELECT clause but the effect was the same. Although the queries were expected to produce the same results, they don't. The first one returns two rows like this

1   2023-08-26      1
2   Invalid date    0

The second query issues the error "ORA-43909: invalid input data type". The description of the error states that for the DATE output type the input data type can be CHAR, NCHAR, VARCHAR2, or NVARCHAR2 which seems to be true in the case, but the error still occurs. If the VALIDATE_CONVERSION function call is removed from both queries they return just the same. DB: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Assuming that the XMLTABLE operator might produce a data type other than allowed, I tried to cast the value of the dt column to the varchar2(20) type, but the result was the same. Now that I believe XMLTABLE and VALIDATE_CONVERSION are contradictory, there is probably a workaround to store the results of the query cleared of the function in some temporary table and then apply that function to its contents, but this way is inconvenient for the real query and would possibly lead to performance issue

Upvotes: 0

Views: 362

Answers (1)

MT0
MT0

Reputation: 168470

You can simplify the code to:

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;

Which works in Oracle 21 and Oracle 23 but fails in Oracle 18 (and I'm assuming Oracle 12, but I don't have an instance of that version to test) with the error ORA-43909: invalid input data type.

If you comment out the VALIDATE_CONVERSION then the query works (and using DUMP on the DT column shows that the data type is type 1, which is a VARCHAR2, so the correct type is being output by the XMLTABLE.)

Either:

  1. Upgrade your database to a version where the query works.
  2. Contact Oracle support (under your support contract, assuming you have one for such an old version of the database) and ask them if they have a patch for this bug.

Upvotes: 1

Related Questions