Blog_REST_Large_Volumes_fetch_items_zip.sql

DECLARE
  l_zip_blob    BLOB;
  l_json_blob   BLOB;
  l_json_clob   CLOB;
  l_files       apex_zip.t_files;
BEGIN  
  -- Call the REST Service on the Source Instance to get the Zipped JSON.
  l_zip_blob := apex_web_service.make_rest_request_b
   (p_url         => 'https://www.example.com/ords/dev/cndemo/blogs/rest_transfer_test_zip',
    p_http_method => 'GET');
  
  -- Unzip the Response
  l_files := apex_zip.get_files (p_zipped_blob => l_zip_blob );
  l_json_blob := apex_zip.get_file_content (p_zipped_blob => l_zip_blob, p_file_name => l_files(1) );
  -- Convert the JSON file to a CLOB.
  -- Note: blob_to_clob is a custom function to convert the CLOB to a BLOB
  l_json_clob := blob_to_clob (p_data => l_json_blob);

  -- 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;