Queue upload events with Oracle AQ

Refuse an upload from a hook

By the end of this lesson, you can start shared processing from the upload hook without making each application call the analysis.

Lesson 2 of 6~15 minScript: 02_queue.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

Start shared processing from the upload hook

The guard already applies across upload paths. Now extend that same registration to request category tags and duplicate checks for accepted files. Each uploading application continues to call ADM as before; it does not need to call either workflow.

As chosen in lesson 1, this work can finish later. The hook adds an event to Oracle Advanced Queuing (AQ), and a worker reads the event after the upload commits. A processing failure can then be retried without repeating the upload.

The upload procedure now guards the file before it enqueues the event. If the guard raises an error, the enqueue call does not run. This excerpt shows the two calls together:

procedure after_new_file_upload (
  p_document_id in adm_documents.document_id%type
, p_version_id  in adm_document_versions.version_id%type
)
as
begin
  guard_file(adm_hooks_api.c_after_new_file_upload, p_document_id, p_version_id);
  enqueue_event(adm_hooks_api.c_after_new_file_upload, p_document_id, p_version_id);
end after_new_file_upload;

The sample’s enqueue_event helper stores the document and version IDs, the user, and the event type in the queue. It queues a reference to the upload, not a copy of the file.

Install the queue and updated hook

The script creates the event type, queue table, and queue. This excerpt shows its retry configuration:

sys.dbms_aqadm.create_queue(
  queue_name     => 'HKD_EVENT_Q'
, queue_table    => 'HKD_EVENT_QT'
, max_retries    => 3
, retry_delay    => 5
, retention_time => 86400
);

The queue allows three retries, with a five-second retry delay, and keeps processed messages for one day. After the retries are exhausted, a failed event waits for recovery. Lesson 4 shows the effect on your workflow. The complete script supplies the payload and queue setup; you do not need to assemble those objects from excerpts.

Create the recurring job during setup

Creating a job with dbms_scheduler.create_job commits the current transaction, so do not create one inside the upload hook. Lesson 5 creates one recurring job during setup to process queued work.

Run @02_queue.sql to install the queue objects and this version of hkd_hook_api. The script needs execute on sys.dbms_aqadm. It starts the main queue and opens the exception queue for lesson 4.

Function HKD_VISIBLE_MESSAGES compiled
Package HKD_HOOK_API compiled
Package Body HKD_HOOK_API compiled
No errors.

Queue an upload

The message commits or rolls back with the upload

With the default ON_COMMIT visibility, another session can read the message only after the upload commits. If the upload rolls back, its message disappears too. The caller owns that transaction. The hook must not commit it.

Run this block to upload INV-1001.txt and commit. Its hook queues the event that the worker processes in lesson 3.

declare
  l_folder_id   adm_folders.folder_id%type;
  l_document_id adm_documents.document_id%type;
begin
  adm_context_api.system_user_login('ADMIN');
  l_folder_id := adm_folder_api.get_folder_id('/users/admin/hkd_tutorial');

  l_document_id := adm_document_api.create_document(
                     p_folder_id      => l_folder_id
                   , p_document_name  => 'INV-1001.txt'
                   , p_file_content   => to_blob(utl_raw.cast_to_raw('Invoice 1001'))
                   , p_file_mime_type => 'text/plain'
                   );

  commit;
end;
/

See the pending upload work

Run this query to see the event the hook recorded:

select q.user_data.hook_key as hook_key
     , d.document_name
     , q.msg_state
  from aq$hkd_event_qt q
  join adm_documents d
    on d.document_id = q.user_data.document_id
 where q.queue = 'HKD_EVENT_Q'
   and d.folder_id = adm_folder_api.get_folder_id('/users/admin/hkd_tutorial')
   and d.document_name = 'INV-1001.txt'
   and q.msg_state in ('READY', 'WAIT');

HOOK_KEY                 DOCUMENT_NAME    MSG_STATE
________________________ ________________ ____________
AFTER_NEW_FILE_UPLOAD    INV-1001.txt     READY

One pending event exists for INV-1001.txt. No worker runs yet, so the upload exists without classification tags. Lesson 3 processes this event and shows the tags in ADM.

Next: Process queued uploads in a workerClassify queued uploads, find duplicates, and read the workflow results.