Blog Large Volumes over REST All PL/SQL Method

Blog_REST_Large_All_PLSQL.sql

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;