Handle failed background workflows

Process queued uploads in a worker

By the end of this lesson, you can keep processing failures separate from uploads and recover a failed workflow.

Lesson 4 of 6~15 minScript: 04_failures.sql

1. Lesson 1: Refuse an upload from a hook
2. Lesson 2: Queue upload events with Oracle AQ
3. Lesson 3: Process queued uploads in a worker
4. Lesson 4: Handle failed background workflows
5. Lesson 5: Start background processing automatically
6. Lesson 6: Create a standard folder layout from a hook

Add failure handling to the worker

A workflow error needs two actions: return the message to the queue and record the failed delivery. The worker rolls back the dequeue and workflow changes. It then writes a failure row in a separate transaction, so the log survives the rollback.

This excerpt shows the failure handler in the worker loop:

begin
  process_event(l_event, l_delivery);
  commit;
  l_handled := l_handled + 1;
exception
  when others then
    rollback;
    record_failure(l_event, l_delivery, sqlerrm);
end;

Run @04_failures.sql to install this version of the worker. It includes the failure log and the requeue_failed procedure for events that exhaust their retries.

Package HKD_WORKER_API compiled
Package Body HKD_WORKER_API compiled
No errors.

Keep the upload separate from processing failures

A classification error does not remove the uploaded document. The worker rolls back its tag changes and tries the event again later. After the sample’s retry limit, the event stays in a queue for failed messages until you fix the cause and requeue it.

This separation lets users upload documents while a processing dependency is unavailable. It also means that a successful upload does not tell the user whether classification finished. Your application should show processing as pending, completed, or failed if users need that result.

See a processing failure without losing the upload

The sample deliberately fails classification when a filename contains poison. This simulates an unavailable processing dependency. The condition is in the worker, so it does not refuse the upload:

if lower(l_name) like '%poison%' then
  raise_application_error(c_err_simulated_failure, 'Simulated workflow failure for "' || l_name || '"');
end if;

Run this block to upload poison-pill.txt and process its event once:

set serveroutput on
declare
  l_document_id adm_documents.document_id%type;
  l_handled number;
begin
  adm_context_api.system_user_login('ADMIN');
  l_document_id := adm_document_api.create_document(
                     p_folder_id      => adm_folder_api.get_folder_id('/users/admin/hkd_tutorial')
                   , p_document_name  => 'poison-pill.txt'
                   , p_file_content   => to_blob(utl_raw.cast_to_raw('Poison pill, first version'))
                   , p_file_mime_type => 'text/plain'
                   );
  commit;
  dbms_output.put_line('upload committed');

  l_handled := hkd_worker_api.process_queue(p_max_messages => 1);
  dbms_output.put_line('events processed successfully: ' || l_handled);
end;
/

select d.document_name
     , r.status
     , r.summary
  from adm_documents d
  join hkd_workflow_runs r
    on r.document_id = d.document_id
 where d.folder_id = adm_folder_api.get_folder_id('/users/admin/hkd_tutorial')
   and d.document_name = 'poison-pill.txt'
   and d.deleted_flag = 'N'
   and r.status = 'FAILED'
 order by r.run_id desc
 fetch first 1 row only;

upload committed
events processed successfully: 0


DOCUMENT_NAME      STATUS    SUMMARY
__________________ _________ ______________________________________________________________
poison-pill.txt    FAILED    ORA-20720: Simulated workflow failure for "poison-pill.txt"

The query returns the document and its failed attempt together. The upload is still available; its classification did not complete. The worker saved the error and left the event ready for another attempt after the retry delay.

Fix the cause and retry processing

Here the filename causes the error. Run this block to rename the document, wait out the five-second retry delay, and process the event again:

set serveroutput on
declare
  l_document_id adm_documents.document_id%type;
  l_handled number;
begin
  adm_context_api.system_user_login('ADMIN');
  select document_id
    into l_document_id
    from adm_documents
   where folder_id = adm_folder_api.get_folder_id('/users/admin/hkd_tutorial')
     and document_name = 'poison-pill.txt'
     and deleted_flag = 'N';

  adm_document_api.rename_document(l_document_id, 'pill.txt');
  commit;
  dbms_session.sleep(5);

  l_handled := hkd_worker_api.process_queue(p_max_messages => 1);
  dbms_output.put_line('events processed successfully: ' || l_handled);
end;
/

select r.workflow_code
     , r.status
     , r.summary
  from hkd_workflow_runs r
  join adm_documents d
    on d.document_id = r.document_id
 where d.folder_id = adm_folder_api.get_folder_id('/users/admin/hkd_tutorial')
   and r.subject = 'pill.txt'
 order by r.run_id;

events processed successfully: 1


WORKFLOW_CODE      STATUS    SUMMARY
__________________ _________ ___________________________________________________
INTAKE_CLASSIFY    OK        Classified as document, tagged category=document
DUPLICATE_SCAN     OK        Unique content, 26 B

The same uploaded document now has a category=document tag. You did not upload it again. In a real workflow, fix the unavailable service or faulty processing code before retrying.

Recover events that exhaust their retries

This example retries after one failure. After all retries fail, the sample keeps the event in its exception queue. Use the demo app’s Queue & Outbox page to inspect it and requeue it after fixing the cause.

Retries can repeat external actions

A rollback cannot undo an email or an HTTP request. If a later step fails, another attempt can repeat that action. For external services, use a stable idempotency key based on the document, version, and action when the service supports it. Recording a completed call locally can help with recovery, but it cannot guarantee that a remote action runs only once.

A queued document can change before processing

A user can delete a document or replace its version while the event waits. The sample workflows read the document or version first and return SKIPPED if it no longer exists. This avoids retrying work whose subject is gone.

Next: Start background processing automaticallyStart the worker with notifications and a job, then see tags appear without calling it yourself.