I have a requirement where I have to download the log/output file of an ESS job from Oracle Cloud in Oracle APEX. For this, I'm using an API mentioned in the Oracle Docs to get Execution details of an ESS Job which is from the below link.
REST API for Oracle Financials Cloud - Get job details
As the Document content in the response of the API is base64 encoded, I'm converting it into BLOB.
In APEX, I have used the following block of code in a process defined in my APEX page to call the API using make_rest_request function.
DECLARE
l_resp_body CLOB;
l_request_id VARCHAR2(4000);
l_content VARCHAR2(4000);
l_numcols NUMBER;
l_blob BLOB;
fname Varchar2(100):='doc1';
BEGIN
apex_web_service.g_request_headers(1).name := 'Content-Type'; apex_web_service.g_request_headers(1).value := 'application/json';
l_resp_body := apex_web_service.make_rest_request(
p_url => 'https://<server>/fscmRestApi/resources/11.13.18.05/erpintegrations',
p_http_method => 'GET',
p_username => <username>,
p_password => <password>,
p_parm_name => APEX_UTIL.string_to_table('finder'),
p_parm_value => apex_util.string_to_table('ESSJobExecutionDetailsRF;requestId=1442848,fileType=ALL')
);
APEX_JSON.parse (l_resp_body); /*Parsing the response to get the document content*/
l_numcols := APEX_JSON.get_count (p_path => 'items');
l_content:=apex_json.get_varchar2(p_path=>'items[%d].DocumentContent',p0=>l_numcols);
l_blob:= utl_raw.cast_to_raw(l_content); /*converting the varchar to BLOB*/
sys.HTP.init;
sys.OWA_UTIL.mime_header('application/pdf', FALSE);
sys.HTP.p('Content-Length: ' || DBMS_LOB.GETLENGTH(l_blob));
sys.HTP.p('Content-Disposition: filename="' || fname || '"');
sys.OWA_UTIL.http_header_close;
sys.WPG_DOCLOAD.download_file(l_blob);
apex_application.stop_apex_engine;
EXCEPTION
WHEN apex_application.e_stop_apex_engine THEN
NULL;
END;
I have created a button in my page and assigned the button to the above process. When I run the page and click on the button I'm getting an error: "Unexpected token U in JSON at position 0". Please suggest if there is anything wrong in this approach followed or any modifications required in the code.
Thanks in Advance.