'Invalid Json from Oracle Apex
I have a problem with returning a json object from a REST method in Oracle Apex. Apex puts an escape character in front of every quotation mark and hence the json object is returned as a string.
The database procedure is defined as taking three parameters. One in and two out where the first out is a Json returned as a clob and the other as varchar2.
dbProcedure(paymentid in varchar2, paymentKeys out clob, message out varchar2)
If I call the procedure, using a pl/sql script in SQL Developer, I get a correctly defined json. However if I call the procedure from Apex I get the json with escape characters for the quotation marks.
The PL/SQL procedure call (in SQL Developer) returns:
{"PaymentKeys":[{"PaymentKey":"00000084","InvoiceId":5000119,"InvoiceNumber":"94500502","A_P-t ...
and, using Postman, the Apex Rest call returns:
{"keyvalues":"{\"PaymentKeys\":[{\"PaymentKey\":\"00000084\",\"InvoiceId\":\"\\\"5000119\\\"\",\"InvoiceNumber\":\"94500502\",\"A_P- ...
The REST method is defined in APEX as:
Method: GET
Source Type: PL/SQL
Format: JSON
Parameters: first IN as HTTP HEADER and the two OUT as type RESPONSE.
begin
dbProcedure(:paymentid, :paymentKeys, :message);
end;
I can see that if I change the Source Type in the Rest method to be of type HTTP HEADER I get the correct result from the procedure, that is a valid Json. However by setting the Sourse Type to RESPONSE I get the invalid Json. So this seems to be some Apex feature. (The consumer of the rest method asked to get a RESPONSE rather than a HTTP HEADER.)
I have now tried to define it as a single parameter REST. Defined a function in the database returning a clob with the REST parameter being a IN/OUT, Access Method, and the Source Type as HTTP HEADER. This also produces the correct result.
How do I get Apex to return the correct result, a valid json?
Product versions:
Apex 19.1.0.00.15
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Postman 9.15.0
SQL Developer 21.4.2.018
Solution 1:[1]
Jeff Smith (ThatJeffSmith) has a post on this here and it seems that htp.p is the way out.
The solution that I chose was to use htp.p.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | jacobm |