Use REST Enabled SQL to Execute Code on Target

REST_ENABLED_SQL.sql

DECLARE
  l_sql_parameters apex_exec.t_parameters;
  l_json_clob      CLOB;
BEGIN
  -- Attach to an APEX Session.
  apex_session.attach (p_app_id => 100, p_page_id => 1, p_session_id => 106852946147127);
  -- Add a Parameter for the returned CLOB.
  apex_exec.add_parameter (l_sql_parameters, 'JSON_CLOB', l_json_clob);
  -- Execute the code on the Target Server.
  apex_exec.execute_remote_plsql(
      p_server_static_id => 'DEV_CNDEMO',
      p_plsql_code       => q'#declare l_json_clob CLOB; begin l_json_clob := cndemo_ords_pk.fetch_items(); :JSON_CLOB := l_json_clob; end;#',
      p_auto_bind_items  => false,
      p_sql_parameters   => l_sql_parameters );
  -- Return the the JSON CLOB.
  l_json_clob := apex_exec.get_parameter_clob( 
      p_parameters  => l_sql_parameters,
      p_name        => 'JSON_CLOB');

  -- Truncate the table in the Traget.  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE rest_transfer_test';
  
  -- Parse the JSON CLOB and populate the table with the JSON from the Source.
  INSERT INTO rest_transfer_test
   (item_id, item_number, description, country_of_mfg, channel, item_uom, date_introduced, status, orderable)
        SELECT jt.*
    FROM   JSON_TABLE(l_json_clob, '$.ITEMS[*]'
             COLUMNS (item_id         NUMBER         PATH '$.ITEM_ID',
                      item_number     VARCHAR2(50)   PATH '$.ITEM_NUMBER',
                      description     VARCHAR2(100)  PATH '$.DESCRIPTION',
                      country_of_mfg  VARCHAR2(100)  PATH '$.COUNTRY_OF_MFG',
                      channel         VARCHAR2(20)   PATH '$.CHANNEL',
                      item_uom        VARCHAR2(10)   PATH '$.ITEM_UOM',
                      date_introduced VARCHAR2(12)   PATH '$.DATE_INTRODUCED',
                      status          VARCHAR2(10)   PATH '$.STATUS',
                      orderable       VARCHAR2(1)    PATH '$.ORDERABLE')) jt;
  COMMIT;
  
END;