APEX 20.2
Oracle 18cXE
Hi,
I am using APEX to interact via REST with a PostgreSQL database on a separate server. Using the built-in CRUD operations on the REST Data Source objects via declarative Report and Form pages works perfectly.
However when I try to do a more complex piece of work using APEX_EXEC.EXECUTE_REST_SOURCE (but still accessing a pre-defined REST Data Source within APEX) I have run into a small issue. I can POST, PATCH and DELETE records successfully using APEX_EXEC.ADD_PARAMETER and a call to the REST Data Source but I am not able to set a JSON null value for a numeric item in the JSON request body of the REST Data Source.
Testing the same PATCH request in Postman I set the JSON body as:
"value_id":null
and this works correctly. The value is updated to a null value in the PostgreSQL db.
The same is true for a string value.
If I pass an Oracle NULL using APEX_EXEC.ADD_PARAMETER to a string value in the "Request Body Template" then this works and the null value is updated in PostgreSQL.
However if I pass a NULL value to an integer in the Request Body of the REST Data Source in the same way:
A) If the Request Body Template is enquoted, e,g, "value_id":"#NAME_VALUE_ID#" then this error is raised - ,"code":"22P02","message":"invalid input syntax for type integer:\"\""
B) If the Request Body Template is not enquoted, e,g, "value_id":#NAME_VALUE_ID# then the following error is raised - Failed reading: not a valid json value. Expecting object value at ','
Does anyone know how to deal with this?