jon-dixon

DECLARE

  lc_json_file_url      CONSTANT VARCHAR2(500) := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/nueva/b/nueva-public/o/Blog%20Public%20Files%2Fjson_parsing%2Fsample.json';
  l_json                CLOB;
  l_orders_obj          JSON_OBJECT_T;
  l_orders_arr          JSON_ARRAY_T;
  l_order_obj           JSON_OBJECT_T;
  l_lines_arr           JSON_ARRAY_T;
  l_order_date          TIMESTAMP WITH TIME ZONE;
  l_ship_date           TIMESTAMP WITH TIME ZONE;

jon-dixon

SELECT jt.order_count
,      jt.order_number
,      jt.status
-- TO_UTC_TIMESTAMP_TZ converts an ISO 8601 Date and 
--  Time String to a TIMESTAMP WITH TIME ZONE data type.
,      TO_UTC_TIMESTAMP_TZ (jt.order_date)  order_date
,      TO_UTC_TIMESTAMP_TZ (jt.ship_date)   ship_date
,      jt.customer_number
,      jt.customer_name
,      jt.line_number

jon-dixon

DECLARE

  l_request_url         VARCHAR2(1000);
  l_request_json        CLOB;
  l_response_json       CLOB;
  l_response_obj        JSON_OBJECT_T;
  l_expires_ts_tz       TIMESTAMP WITH TIME ZONE;
  l_expires_str         VARCHAR2(100);
  l_par_name            VARCHAR2(100);
  l_access_type         VARCHAR2(25);

jon-dixon

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

jon-dixon

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

jon-dixon

DECLARE
  l_csv_clob     CLOB;
  l_response     CLOB;
  lt_parm_names  apex_application_global.VC_ARR2;
  lt_parm_values apex_application_global.VC_ARR2;
BEGIN
  -- Build the header line.
  l_csv_clob := TO_CLOB('"ITEM_ID","ITEM_NUMBER","DESCRIPTION","COUNTRY_OF_MFG","CHANNEL","ITEM_UOM","DATE_INTRODUCED","STATUS","ORDERABLE"'||chr(13));
  -- Loop through records building the CSV content.
  FOR csv_rec IN

jon-dixon

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(

jon-dixon

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;

jon-dixon

DECLARE
  -- Variable to store the PL/SQL block which will be sen to the Remote Server.
  l_plsql_code       CLOB := q'#
    DECLARE
      l_new_task_number  cndemo_tasks.task_number%TYPE;
    BEGIN
      cndemo_tasks_pk.create_task
       (p_task_name    => :TASK_NAME,
        p_task_detail  => :TASK_DETAIL,
        p_task_owner   => :TASK_OWNER,

jon-dixon

DECLARE
  -- Variable used to store the SQL Statement that will be sent to the Remote Server.
  l_sql_query        CLOB := q'#
    SELECT task_id
    ,      task_number
    ,      task_name
    ,      task_detail
    ,      due_date
    ,      task_status
    FROM   cndemo_tasks