Skip to Main Content

Can APEX_EXEC.EXECUTE_REST_SOURCE read/use clob parameter?

ilmarskSep 22 2022

Hello!
When passing in CLOB variable via APEX_EXEC.ADD_PARAMETER to APEX_EXEC.EXECUTE_REST_SOURCE for the request body the variable is blank. Note that variable is added to APEX_EXEC.T_PARAMETERS type “table” properly and can be read back as CLOB. The issue is with APEX_EXEC.EXECUTE_REST_SOURCE not passing CLOB variable to the request body.
When performing the same test with VARCHAR(4000) the request is resolved and submitted properly. VARCHAR(32K) does not work as well.
Maybe there is some trick to the reference variable in the Request Body Template to force CLOB value?
Thank you for any help on the issue in advance.
Supporting info below:

Train of thoughts (assuming CLOB is no go):

  1. Write REST data source plugin and override body with clob from parameters type variable. Unfortunately, could not figure out how to pass in the parameters type variable to rest data source plugin. Another thought is to extract the info from apex_plugin_util.get_web_source_operation, but there is no documentation about any of the apex_plugin.t_web_source, t_web_source_fetch_params, t_web_source_operation – structure and example usage of the types would be very helpful;
  2. Use plain APEX_WEB_SERVICE.MAKE_REQUEST. This could work if there is a way to extract all necessary variables from the defined rest data source. Something like apex_plugin_util.get_web_source_operation would work if there is documentation on parsing it out to variables to feed into MAKE_REQUEST. i.e. apex_plugin_util.get_web_source_operation expects p_web_source in apex_plugin.t_web_source which is defined as REST data source plug-in meta data. How could I get in plsql plug-in meta data from the rest data source static id?

Setup: OCI cloud apex 22.1.4
Rest data source configure with DELTE, GET, & POST operations and global header, query string and response parameters. POST operation has an operational parameter for Request body – expecting json. See definition screen print:
image.pngCall:

DECLARE
L_MODULE_STATIC_ID VARCHAR2(200) := 'my_test';
L_JSON_BODY_CLOB CLOB;
L_PARAMETERS APEX_EXEC.T_PARAMETERS;
BEGIN
APEX_SESSION.CREATE_SESSION(P_APP_ID => 110, P_PAGE_ID => 1, P_USERNAME => 'MYUSER');
--L_JSON_BODY_CLOB := <--json generated from slq query
APEX_EXEC.ADD_PARAMETER(L_PARAMETERS, 'H_SYNC_KEYS_JSON', L_JSON_BODY_CLOB);
APEX_EXEC.EXECUTE_REST_SOURCE(P_STATIC_ID => L_MODULE_STATIC_ID, P_OPERATION => 'POST', P_PARAMETERS => L_PARAMETERS);
END;

This post has been answered by ilmarsk on Sep 23 2022
Jump to Answer
Comments
Post Details
Added on Sep 22 2022
1 comment
13 views