ARCS Run Custom Report PL/SQL Procedure

arc_run_report_plsql.sql

PROCEDURE run_csv_report
  (p_group_name  IN VARCHAR2,
   p_report_name IN VARCHAR2,
   p_params_json IN VARCHAR2,
   p_module      IN VARCHAR2,
   p_run_async   IN BOOLEAN,
   x_file_url   OUT NOCOPY VARCHAR2,
   x_status_rec OUT NOCOPY status_rec) IS

  l_report_file_name     VARCHAR2(500);
  l_complete_url         VARCHAR2(500);
  l_payload              CLOB;
  l_response_clob        CLOB;
  l_response_obj         JSON_OBJECT_T;
  l_links_array          JSON_ARRAY_T;

BEGIN

  -- Build the complete file name of the CSV that will be generated on the ARCS 
  --  file system, and make sure it is unique.
  l_report_file_name := LOWER(REPLACE(p_report_name,' ','_')) || '_' || TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') || '.csv';

  -- Build the payload for the Run Custom Report REST API.
  l_payload := '{' ||
        '"groupName":'                || apex_json.stringify(p_group_name) ||
        ',"reportName":'              || apex_json.stringify(p_report_name) ||
        ',"generatedReportFileName":' || apex_json.stringify(l_report_file_name) ||
        ',"format":'                  || '"CSV"' ||
        ',"module":'                  || apex_json.stringify(p_module) ||
        ',"runAsync":'                || apex_json.stringify(p_run_async);

  -- Append the parameters JSON object if provided.
  IF p_params_json IS NOT NULL THEN
    l_payload := l_payload || ',"parameters":'||p_params_json;
  END IF;
  l_payload := l_payload || '}';
  
  -- Set the HTTP Headers for the REST API Call.
  apex_web_service.g_request_headers.DELETE;
  apex_web_service.g_request_headers(1).NAME := 'Content-Type';
  apex_web_service.g_request_headers(1).VALUE := 'application/json';
  
  -- Build complete URL to REST Service.  
  l_complete_url := GC_ARCS_INSTANCE_BASE_URL || 'arm/rest/fcmapi/v1/report';
  apex_debug.info('XX [%s] - URL [%s], Payload [%s]', utl_call_stack.subprogram(1)(2),l_complete_url,l_payload);

  -- Call REST Service to run the custom Report.
  l_response_clob := apex_web_service.make_rest_request
    (p_url                  => l_complete_url,
     p_http_method          => 'POST',
     p_transfer_timeout     => GC_REST_TIMEOUT_SECS,
     p_body                 => l_payload,
     p_credential_static_id => GC_APEX_WEB_CREDENTIAL_ARCS);
  apex_debug.info('XX [%s] - REST API Response Code [%s], Body [%s]', utl_call_stack.subprogram(1)(2),apex_web_service.g_status_code,l_response_clob);

  IF apex_web_service.g_status_code = 200 THEN

    -- Parse the JSON Response.
    l_response_obj := json_object_t.parse(l_response_clob);

    -- Get the status returned from the REST API.
    IF l_response_obj.get_number('status') <> 0 THEN
      apex_debug.error('XX Job Completed in Error: [%s]', l_response_obj.get_number('status'), l_response_obj.get_string('details'));
      raise_application_error(-20001, 'Job Completed in Error ['||l_response_obj.get_string('details')||']');
    END IF;

    -- Build a JSON array object from the 'links' array in the JSON response.
    l_links_array := l_response_obj.get_Array('links');
    -- Loop through the array to get the URL to the job status API.
    FOR i IN 0..l_links_array.get_size -1 LOOP
      IF JSON_OBJECT_T(l_links_array.get(i)).get_string('rel') = 'report-content' THEN
        x_file_url := JSON_OBJECT_T(l_links_array.get(i)).get_string('href');
      END IF;
    END LOOP;

    -- Make sure we found the URL for the report-content
    IF x_file_url IS NULL THEN
      x_status_rec.status_code := 'E';
      x_status_rec.status_msg  := 'REST Service Did not return report content link. Status ['||apex_web_service.g_status_code||']'||
                                  ', Response [' || l_response_clob ||']';
      apex_debug.error('XX [%s] - [%s]', utl_call_stack.subprogram(1)(2), x_status_rec.status_msg);
    END IF;
  ELSE
    -- We received a response other than 200 from the REST API
    x_status_rec.status_code := 'E';
    x_status_rec.status_msg  := 'REST Service Call Failed. Status ['||apex_web_service.g_status_code||']'||
                                ', Response [' || l_response_clob ||']';
    apex_debug.error('XX [%s] - [%s]', utl_call_stack.subprogram(1)(2), x_status_rec.status_msg);
  END IF;

  apex_debug.info('XX [%s] - Complete', utl_call_stack.subprogram(1)(2));
  
EXCEPTION WHEN OTHERS THEN
  x_status_rec.status_code := 'E';
  x_status_rec.status_msg  := 'Unhandled Error ['||SQLERRM||']';
  apex_debug.error('XX [%s] - Unhandled Error [%s]', utl_call_stack.subprogram(1)(2), SQLERRM);
  RAISE;
END run_csv_report;