ARCS Start Job Procedure

arcs_start_job_proc.sql

PROCEDURE start_job
 (p_base_url        IN VARCHAR2,
  p_job_name        IN VARCHAR2,
  p_params_json     IN VARCHAR2,
  x_job_status_url OUT NOCOPY VARCHAR2) IS

  l_complete_url     VARCHAR2(500);
  lc_job_path        CONSTANT VARCHAR2(100) := 'arm/rest/v1/jobs';
  l_payload_json     VARCHAR2(4000);
  l_response_clob    CLOB;
  l_response_obj     JSON_OBJECT_T;
  l_links_array      JSON_ARRAY_T;

BEGIN

  -- Set the HTTP Headers.
  apex_web_service.clear_request_headers;
  apex_web_service.g_request_headers(1).NAME  := 'Content-Type';
  apex_web_service.g_request_headers(1).VALUE := 'application/json';
  
  -- Build complete URL to the jobs REST API. 
  l_complete_url := p_base_url || lc_job_path;
  apex_debug.info('XX Complete URL [%s]', l_complete_url);
  
  -- Build the complete JSON Payload, including the passed in parameters object.
  l_payload_json := '{' || 
                    ' "jobName": '|| apex_json.stringify(p_job_name) || ',' ||
                    ' "parameters": '|| p_params_json ||
                    '}';
  apex_debug.info('XX Payload JSON [%s]', l_payload_json);

  -- Call REST API to Start the ESS Job.
  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_json,
     p_credential_static_id => GC_APEX_WEB_CREDENTIAL_ARCS);
  apex_debug.info('XX g_status_code: [%s]', apex_web_service.g_status_code);
  apex_debug.info('XX Response: [%s]', 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.
    IF l_response_obj.get_number('status') <> -1 THEN
      apex_debug.error('XX Job Completed in Error: [%s]', l_response_obj.get_number('status'), l_response_obj.get_number('details'));
      raise_application_error(-20001, 'Job Completed in Error ['||l_response_obj.get_string('details')||']');
    END IF;

    -- Build an array of from the 'links' array in the 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') = 'self' THEN
        x_job_status_url := JSON_OBJECT_T(l_links_array.get(i)).get_string('href');
      END IF;
    END LOOP;

    IF x_job_status_url IS NOT NULL THEN
      apex_debug.info('XX Success, URL: [%s]', x_job_status_url);
    ELSE
      -- Could not find a 'self' link in the 'links' array.
      apex_debug.error('XX Link to Job Status API not found');
      raise_application_error(-20002, 'Failed to Start Job (link not found)');
    END IF;

  ELSE
    apex_debug.error('XX HTTP Status Error [%0]', apex_web_service.g_status_code);
    raise_application_error(-20003, 'HTTP Error from jobs API ['||apex_web_service.g_status_code||']');
  END IF;

EXCEPTION WHEN OTHERS THEN
  apex_debug.error('XX Unhandled Error [%0]', SQLERRM);
  RAISE;
END start_job;