Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Blog Large Volumes over REST All PL/SQL Method
DECLARE
CURSOR cr_json_info (cp_json_clob IN CLOB) IS
SELECT jt.*
FROM JSON_TABLE(cp_json_clob, '$'
COLUMNS (has_more VARCHAR2(10) PATH '$.hasMore',
count NUMBER PATH '$.count',
next_url VARCHAR2(200) PATH '$.links[3].href')) jt;
l_json_clob CLOB;
lr_json_info cr_json_info%ROWTYPE;
l_next_url VARCHAR2(200);
BEGIN
-- Truncate the table in the Target.
EXECUTE IMMEDIATE 'TRUNCATE TABLE rest_transfer_test';
l_next_url := 'https://www.example.com/ords/dev/cndemo/blogs/rest_transfer_test';
-- Start loop through batches.
LOOP
-- Call the REST Service on the Source Instance to get the Zipped JSON.
l_json_clob := apex_web_service.make_rest_request
(p_url => l_next_url,
p_http_method => 'GET');
OPEN cr_json_info (cp_json_clob => l_json_clob);
FETCH cr_json_info INTO lr_json_info;
CLOSE cr_json_info;
-- Make sure there are some rows to process.
IF lr_json_info.count > 0 THEN
-- Parse the JSON CLOB for the current batch, and populate the table with data 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 IF;
-- Check to see if there are more rows to fetch.
IF lr_json_info.has_more = 'true' THEN
-- If there are more rows, Get URL of Next Batch
l_next_url := lr_json_info.next_url;
ELSE
-- We have reached the end, Stop the Loop.
EXIT;
END IF;
END LOOP;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment