How to resolve error ORA-01861: literal does not match format string during insertion of JSON data in Oracle table?

Question

I have a table

CREATE TABLE MATERIAL_JSON_DECODE ( ID NUMBER(5), CREATED_DATE DATE );

During insertion , I am getting error ORA-01861: literal does not match format string :

INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
WITH CODES AS 
(
 SELECT ID,
    CAST(TO_TIMESTAMP_TZ(createdDate, 'FXYYYY-MM-DD"T"HH24:MI:SS.FXFF3"Z"') AT LOCAL AS DATE)createdDate
    FROM 
    (
    SELECT DISTINCT 
     ID,createdDate
    FROM
    MATERIAL_T D,
    JSON_TABLE
    (
       D.MESSAGE_VALUE, '$' COLUMNS
       (
         ID VARCHAR2(6) PATH '$._id',
         createdDate VARCHAR2(100) PATH '$.createdDate'
        
     )   
  )
  )
  )
  SELECT ID,TO_DATE(CREATEDDATE ,'DD-MON-YYYY HH24:MI:SS') AS CREATED_DATE FROM CODES ;

MESSAGE_VALUE Column in MATERIAL_T has JSON data :

{"_id":"58093","createdDate":"2023-12-08T12:25:36.686Z"}

Please guide how to resolve this error ?

Answer 1

As well as doing unnecessary conversions, you are relying on implicit conversions, which will sometimes work - and will sometimes appear to work but not give you the expected result. For example with the 'default' NLS settings your code doesn't error, but gives a final result in the year 0023, not 2023, and also loses the time. (See example in the fiddle below.)

You can avoid almost all the conversion though, by telling JSON_TABLE that the value is a timestamp rather than a string - as it's in ISO format it will then do the conversion for you:

    createdDate TIMESTAMP WITH TIME ZONE PATH '$.createdDate'

You then just need to do your time zone adjustment from UTC, and cast to the right data type. You can also simplify your code to remove the extra CTE and inline view along the way. (And move away from the old join syntax using commas...)

INSERT INTO MATERIAL_JSON_DECODE(ID , CREATED_DATE)
SELECT DISTINCT ID, CAST(createdDate AT LOCAL AS DATE)
FROM MATERIAL_T D
CROSS APPLY JSON_TABLE
(
  D.MESSAGE_VALUE, '$'
  COLUMNS
  (
    ID VARCHAR2(6) PATH '$._id',
    createdDate TIMESTAMP WITH TIME ZONE PATH '$.createdDate'
  )
)

The result depends on your actual local time zone of course, but if you were in New York that would give you:

ID	CREATED_DATE
58093	2023-12-08 07:25:36

fiddle