Blog REST Large Volumes Function to fetch JSON, ZIP it and return it

Blog_REST_Large_Volumes_fetch_items_zip.sql

FUNCTION fetch_items_zip RETURN BLOB IS
  l_json_clob  CLOB;
  l_json_blob  BLOB;
  l_zip_blob   BLOB;
BEGIN
  -- Fetch all 100,000 Rows into a JSON Array.
  SELECT JSON_OBJECT (KEY 'ITEMS' VALUE (
    SELECT JSON_ARRAYAGG(JSON_OBJECT (*) RETURNING CLOB)
    FROM   REST_TRANSFER_TEST) RETURNING CLOB
  )  INTO l_json_clob
  FROM DUAL;

  -- Convert the JSON CLOB into a BLOB.
  -- Note: clob_to_blob is a custom function to convert the CLOB to a BLOB
  l_json_blob := clob_to_blob (p_data => l_json_clob);

  -- ZIP the JSON BLOB.  
  apex_zip.add_file (
            p_zipped_blob => l_zip_blob,
            p_file_name   => 'data.json',
            p_content     => l_json_blob);
  apex_zip.finish (p_zipped_blob => l_zip_blob );
  
  -- Return the ZIP file.      
  RETURN l_zip_blob;
END fetch_items_zip;