ARCS Job Status Procedure

arcs_job_status_proc.sql

PROCEDURE job_status
 (p_job_status_url      IN VARCHAR2,
  x_job_complete       OUT NOCOPY VARCHAR2,
  x_job_status_code    OUT NOCOPY NUMBER,
  x_job_status_details OUT NOCOPY VARCHAR2) IS

  l_response_clob    CLOB;
  l_response_obj     JSON_OBJECT_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';
  
  -- Call REST API to Start the ESS Job.
  l_response_clob := apex_web_service.make_rest_request
    (p_url                  => p_job_status_url,
     p_http_method          => 'GET',
     p_transfer_timeout     => GC_REST_TIMEOUT_SECS,
     p_credential_static_id => GC_APEX_WEB_CREDENTIAL_ARCS);
  apex_debug.info('XX [%s] - g_status_code: [%s]', utl_call_stack.subprogram(1)(2), apex_web_service.g_status_code);
  apex_debug.info('XX [%s] - Response: [%s]', utl_call_stack.subprogram(1)(2), l_response_clob);

  IF apex_web_service.g_status_code = 200 THEN
    -- Parse the JSON Response and Return the Status Code and Details.
    l_response_obj       := json_object_t.parse(l_response_clob);
    x_job_status_code    := l_response_obj.get_number('status');
    x_job_status_details := l_response_obj.get_string('details');
    IF x_job_status_code = -1 AND x_job_status_details = 'Job Still Running..' THEN 
      x_job_complete := 'N';
    ELSE
      x_job_complete := 'Y';
    END IF;
    apex_debug.info('XX [%s] - Job Complete [%], Status Code: [%s], Details [%s]', utl_call_stack.subprogram(1)(2), x_job_complete,  x_job_status_code, x_job_status_details);

  ELSE
    apex_debug.error('XX [%s] - HTTP Status Error [%0]', utl_call_stack.subprogram(1)(2), 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 [%s] - Unhandled Error [%0]', utl_call_stack.subprogram(1)(2), SQLERRM);
  RAISE;
END job_status;