How to Get a Message After a Background Job Completes in Oracle APEX?

Oracle APEX provides a powerful platform for developing data-driven applications. One common requirement in APEX applications is to execute long-running processes, such as data import/export, report generation, or other background tasks, without blocking the user interface (UI). In such cases, it’s useful to notify the user when the background job is complete, allowing them to continue working while the process runs.

In this blog post, we’ll explore how to set up a background job in Oracle APEX and send a message or notification once the job completes.

We did this with this approach:

1. Create an Application Process:

declare
  l_status_code VARCHAR2(50);
  l_execution_id NUMBER := TO_NUMBER(apex_application.g_x01);
BEGIN
  -- Log the input execution ID
  apex_debug.message('Execution ID: ' || l_execution_id);
if l_execution_id is not null then 
  SELECT status_code
    INTO l_status_code
    FROM apex_appl_page_bg_proc_status
   WHERE session_id   = :APP_SESSION
     AND execution_id = l_execution_id
     AND created_on   > TRUNC(SYSDATE);

  -- Log the retrieved status code
  apex_debug.message('Retrieved Status Code: ' || l_status_code);

  apex_json.open_object;
  IF l_status_code = 'SUCCESS' THEN
    apex_debug.message('Execution Complete');
    apex_json.write('executionComplete', 'true');
  ELSE
    apex_debug.message('Execution Incomplete');
    apex_json.write('executionComplete', 'false');
  END IF;
  apex_json.close_object;
end if;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    apex_debug.message('No Data Found for Execution ID: ' || l_execution_id);
    apex_json.open_object;
    apex_json.write('executionComplete', 'false');
    apex_json.close_object;
  WHEN OTHERS THEN
    apex_debug.message('Error: ' || SQLERRM);
    raise;
END;

2. Add an DA on Page Load

3. Check every 3 seconds - with this Javascript in TRUE Action:

(function() { 
    function checkForExecutionCompletion() {
        apex.server.process('CHECK_BG_PROCESS', { x01: $v('P1_EXECUTION_ID') }, { dataType: 'json' })
        .then(function(data) {
            console.log('Response data:', data);
            if (data.executionComplete === 'true') {
                console.log('Job ok');
                apex.message.showPageSuccess('Job erfolgreich durchgeführt');
                apex.region('collection_region').refresh();
                apex.item('P1_EXECUTION_ID').setValue('');
                // No need to schedule the next check
            } else {
                console.log('Job is running');
                apex.region('background_process_region').refresh();
                // Schedule the next check
                setTimeout(checkForExecutionCompletion, 3000);
            }
        })
        .catch(function(error) {
            console.error('AJAX Error:', error);
        });
    }
    checkForExecutionCompletion();
})();

And now if you stay on the page.. the Job message is comming!

also you can Query BG Process info in interactive grid with static id “background_process_region”

select execution_id,
       created_on,
       process_id,
       process_name,
       current_process_name,
       status,
       status_code,
       status_message,
       coalesce(sofar, 0) as sofar,
       coalesce(totalwork, 100) as totalwork,
       coalesce(sofar, 0) / coalesce(totalwork, 30) * 100 progress,
       case when execution_id =:P1_EXECUTION_ID then 1 else 0 end is_actual_execution,
      case when status = 'Executing' then systimestamp else last_updated_on end - created_on execution_time,
       session_id
      from apex_appl_page_bg_proc_status
 where 
    session_id = :app_session
   and created_on > trunc(sysdate)

Best wishes Tom