ARCS Start Job, Wait and Get Status Example

arcs_start_job_wait_example.sql

DECLARE
  lc_arcs_base_url       CONSTANT VARCHAR2(100) := '<BaseURL>';
  lc_job_name            CONSTANT VARCHAR2(100) := 'importtmpremappedtransactions';
  l_job_params           VARCHAR2(1000);
  l_job_status_url       VARCHAR2(500);
  l_file_name            VARCHAR2(100);
  l_job_complete         VARCHAR2(1);
  l_job_status_code      NUMBER;
  l_jobs_status_details  VARCHAR2(32000);
BEGIN

  -- Build up the parameters JSON for the Import Transactions Job.
  l_file_name := 'inbox/JD_AP_5.csv';
  l_job_params := 
    '{' ||
    ' "file": ' || apex_json.stringify(l_file_name) || ',' ||
    '"dateFormat": "MM/dd/yyyy",'||
    '"reconciliationType": "PO2Inv",'||
    '"dataSource": "AP"'||
    '}';

  -- Start the Job.
  arcs_utl_pk.start_job
   (p_base_url       => lc_arcs_base_url,
    p_job_name       => lc_job_name,
    p_params_json    => l_job_params,
    x_job_status_url => l_job_status_url);

  dbms_output.put_line('Job Status URL ['||l_job_status_url||']');
  
  -- Check the job status 10 times, waiting 5 seconds between each check.
  FOR i in 1..10 LOOP
    -- Sleep for 5 seconds between each check of job status.
    sys.dbms_session.sleep(5);
    arcs_utl_pk.job_status
     (p_job_status_url     => l_job_status_url,
      x_job_complete       => l_job_complete,
      x_job_status_code    => l_job_status_code,
      x_job_status_details => l_jobs_status_details);

    dbms_output.put_line('Iteration ['||i||'], Job Complete ['||
                         l_job_complete||'], Job Status Code ['||
                         l_job_status_code||'], Details ['||l_jobs_status_details||']');
      
    IF l_job_complete = 'Y' THEN
      -- Stop looping when the job is complete.
      EXIT;
    END IF;
  END LOOP;
  
  IF l_job_complete <> 'Y' THEN
    dbms_output.put_line('Job Failed to Complete');
  END IF;

END;