In the question “Call external REST API from ORDS endpoint” I asked for the best way to query an external web service.
You advised me to use APEX_WEB_SERVICE, which I did.
A little background info, in ORDS I need to use the PL/SQL source type, because there is some extra checking / processing.
This make that I can’t follow the advice of Jeff Smith to use the Media resource source type, see
https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/ This brought me in the same place as this question on the Oracle communities:
https://community.oracle.com/tech/developers/discussion/4193354/expose-json-with-ords The issue is that the JSON contains all kinds of escape sequences for the string.
Question: what is the best way for a PL/SQL source type ORDS to expose the received JSON from the web service.
I did my homework and this is what I came up with the code below.
The code below is working and giving me the right results. Hoewever I was wondering if I'm on the right track her.
Also calling an external web service from ORDS and giving back the JSON seems to me a common use-case for which there is very little information if you need to do it from an endpoint of the PL/SQL media type.
DECLARE
l_json_result CLOB;
j apex_json.t_values;
BEGIN
/* Business logic resulting in true or false */
l_result := Business_Check();
IF l_result = 'success'
THEN
l_json_result :=
APEX_WEB_SERVICE.make_rest_request (p_url => 'http://localhost:8081/myendpoint',
p_http_method => 'GET');
APEX_JSON.open_object;
APEX_JSON.parse (j, l_json_result);
APEX_JSON.write ('result', 'success');
APEX_JSON.write ('data', j);
APEX_JSON.close_object;
END;
Thanks for your patience. I passed this around some of the ORDS folks.
The media resource type is definitely generally as the way to go, thus for example, if you had a procedure or anonymous block like yours above that has a JSON output, then a recommended technique would to wrap that within a PLSQL function that returns the JSON. Because once you have a function that does it, your ORDS interface can be:
select 'application/json' mime_type, my_function_returning_json() from dual
and then Jeff's post
https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/ becomes the way forward.
Other options can be to just use HTP.P to push out the JSON youself, but generally better to keep it call within the ORDS constructs.