Prevent Oracle error ORA-18043 in select statement

Natty 0
Viewed 28

I'm storing in a table the logs of an API in my application. In one of the columns, I store the raw Json sent in the HTTP request.

I've been tasked to create a page in my application dedicated to easily explore every entered logs, with filters, sorting, etc.

One of the sorting needed is on the date that was indicated in the Json body of the HTTP call. I've managed to do so using the Oracle's Json API :

SELECT * 
FROM FUNDING_REQUEST f
ORDER BY TO_TIMESTAMP_TZ(JSON_VALUE(
  f.REQUEST_CONTENTS,
  '$.leasing_information.consumer_request_date_time'
), 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM') ASC

This works either if $.leasing_information.consumer_request_date_time is defined or not but I have an issue when the value is wrongly formatted. In one of my test, I sent this to my API :

{
  [...],
  "leasing_information": {
    "consumer_request_date_time": "2021-25-09T12:30:00.000+02:00",
    [...]
  }
}

There is no 25th month, and my SQL query now returns the following error : ORA-01843: not a valid month.

I would like to handle this value as NULL rather than returning an error, but it seems like the TO_TIMESTAMP_TZ DEFAULT clause does not really work the way I want it to. Doing this also returns an error :

SELECT * 
FROM FUNDING_REQUEST f
ORDER BY TO_TIMESTAMP_TZ(JSON_VALUE(
  f.REQUEST_CONTENTS,
  '$.leasing_information.consumer_request_date_time'
) DEFAULT NULL ON CONVERSION ERROR, 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM') ASC

ORA-00932:inconsistent datatypes ; expected : - ; got : TIMESTAMP WITH TIME ZONE

I would also like to avoid using a PL/SQL function if possible, how can I prevent this request from returning an error ?

oracle sql json
1 Answers

You don't need to extract a string and then convert that to a timestamp; you can do that within the json_value(), and return null if that errors - at least from version 12c Release 2 onwards:

SELECT * 
FROM FUNDING_REQUEST f
ORDER BY JSON_VALUE(
  f.REQUEST_CONTENTS,
  '$.leasing_information.consumer_request_date_time'
  RETURNING TIMESTAMP WITH TIME ZONE
  NULL ON ERROR
) ASC

db<>fiddle, including showing what the string and timestamp extractions evaluate too (i.e. a real timestamp value, or null).

Alex Poole0
Related