Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use REST Enabled SQL to Execute Code on Target
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment