Start background processing automatically

Handle failed background workflows

By the end of this lesson, you can upload through ADM and see classification tags appear without calling the worker yourself.

Lesson 5 of 6~20 minScript: 05_wake.sql

Run shared processing without an application call

Your upload hook already requests classification for each accepted file. Until now, you called the worker to see the result. Notifications and a recurring job now start it automatically. The uploading application only uploads and commits; no application-specific page process needs to call the classification workflow.

Configure AQ notifications and a scheduler job

Until now you called the worker directly. An AQ notification starts it for new messages, and a scheduler job handles messages that need another attempt:

Mechanism	What it does	Case it covers
An AQ notification	Oracle calls `hkd_worker_api.on_message` in a background session when a message is committed.	Newly committed upload events.
A scheduler job	`HKD_PROCESS_QUEUE_JOB` calls the worker. Its repeat interval is 30 seconds.	Messages ready for retry and messages left in the queue while automatic processing was unavailable.

Add an on_message procedure to the worker package specification and body. The callback calls the same queue processing function that you used in lesson 3. This excerpt shows its implementation:

procedure on_message (
  context raw
, reginfo sys.aq$_reg_info
, descr   sys.aq$_descriptor
, payload raw
, payloadl number
)
as
  l_handled number;
begin
  l_handled := process_queue();
end on_message;

Register the callback for HKD_EVENT_Q. The plsql:// address identifies the procedure to call. This excerpt shows the registration:

sys.dbms_aq.register(
  reg_list => sys.aq$_reg_info_list(
                sys.aq$_reg_info(
                  user || '.HKD_EVENT_Q'
                , sys.dbms_aq.namespace_aq
                , 'plsql://' || user || '.HKD_WORKER_API.ON_MESSAGE'
                , hextoraw('FF')
                )
              )
, reg_count => 1
);

Create a job that calls the worker every 30 seconds. It processes retries and any messages left while the notification was unavailable. This excerpt shows its configuration:

sys.dbms_scheduler.create_job(
  job_name        => 'HKD_PROCESS_QUEUE_JOB'
, job_type        => 'STORED_PROCEDURE'
, job_action      => 'HKD_WORKER_API.PROCESS_QUEUE'
, repeat_interval => 'FREQ=SECONDLY;INTERVAL=30'
, enabled         => true
, comments        => 'Hooks demo: sweeps the event queue for retried messages'
);

Run @05_wake.sql to install the callback, register the notification, and create the job. The script makes sure that the notification and job do not already exist before it creates them.

Package HKD_WORKER_API compiled
Package Body HKD_WORKER_API compiled
No errors.

The notification needs job_queue_processes greater than zero, because Oracle runs the callback in a job process. The job needs the create job privilege. If your database cannot register the notification, the job alone still processes every message. A message then waits until the next run of the job.

Wait for the upload’s classification tags

Run this block. It uploads INV-2026-012.pdf and waits up to 60 seconds for that document’s tags. It does not call the worker. A timeout raises an error rather than leaving you with an empty result:

set serveroutput on
declare
  l_document_id adm_documents.document_id%type;
  l_tags number := 0;
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  => 'INV-2026-012.pdf'
                   , p_file_content   => to_blob(utl_raw.cast_to_raw('Invoice 2026-012'))
                   , p_file_mime_type => 'application/pdf'
                   );
  commit;

  for i in 1 .. 300 loop
    select count(*)
      into l_tags
      from adm_my_document_tags_v
     where document_id = l_document_id
       and ((tag_name = 'category' and tag_value = 'invoice') or tag_name = 'needs-review');
    exit when l_tags = 2;
    dbms_session.sleep(0.2);
  end loop;

  if l_tags != 2 then
    raise_application_error(-20721, 'Upload saved, but classification did not finish within 60 seconds. Review pending work and failures.');
  end if;
  dbms_output.put_line('upload committed; classification tags are ready');
end;
/

select d.document_name
     , t.tag_name
     , t.tag_value
  from adm_my_document_tags_v t
  join adm_my_documents_v d
    on d.document_id = t.document_id
 where d.folder_path = '/users/admin/hkd_tutorial'
   and d.document_name = 'INV-2026-012.pdf'
 order by t.tag_name;

upload committed; classification tags are ready


DOCUMENT_NAME       TAG_NAME        TAG_VALUE
___________________ _______________ ____________
INV-2026-012.pdf    category        invoice
INV-2026-012.pdf    needs-review

Open the document in ADM to see the same tags. Uploading through ADM’s APEX app, REST API, or another application using ADM’s document API reaches the same registered hook. Those applications do not need their own classification call. The file contains sample text, not a PDF invoice; the classification here comes from its name and MIME type.

Monitor pending work

Use this query to count events that still need processing:

select count(*) as waiting
  from aq$hkd_event_qt
 where queue     = 'HKD_EVENT_Q'
   and msg_state in ('READY', 'WAIT');

A growing backlog means that uploads are arriving faster than the worker can process them, or that processing has stopped. Review the failed attempts from lesson 4 as well; an empty main queue does not prove that every workflow succeeded.

Next: Create a standard folder layout from a hookGive each new project the same starting folders across applications.