Process queued uploads in a worker

Queue upload events with Oracle AQ

By the end of this lesson, you can process queued uploads, classify files, and tag duplicate content through ADM APIs.

Lesson 3 of 6~30 minScript: 03_worker.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 tags to the upload accepted by the hook

The upload hook queued INV-1001.txt in lesson 2. Now add the worker that turns that event into useful ADM data. It adds a category tag and a review flag for invoices and contracts. When another document has the same checksum, it adds a duplicate-of tag.

The worker runs after the upload commits. Its tag changes and the queue delivery commit together; if processing fails, both roll back while the uploaded file remains available.

Create the worker

The worker runs two workflows for each upload event: file classification and duplicate detection.

The classification workflow chooses a category from the document name and MIME type. For example, INV-1001.txt gets the category invoice, and an image file gets the category image. These rules are part of the sample; you can change them to match your own files:

function classify_document (
  p_name in varchar2
, p_mime in varchar2
) return varchar2
as
  l_name varchar2(1000 char) := lower(p_name);
begin
  return case
           when regexp_like(l_name, '^(inv|invoice|rechnung)[-_ 0-9]') then 'invoice'
           when regexp_like(l_name, 'contract|agreement|nda|vertrag') then 'contract'
           when p_mime like 'image/%' then 'image'
           when regexp_like(l_name, '\.(xlsx?|csv|ods)$') or p_mime like '%spreadsheet%' then 'spreadsheet'
           when regexp_like(l_name, '\.(pptx?|odp)$') or p_mime like '%presentation%' then 'presentation'
           when regexp_like(l_name, '\.(docx?|pdf|txt|md|odt|rtf)$') or p_mime like 'text/%' then 'document'
           else 'other'
         end;
end classify_document;

Demo classification rules

These simple rules demonstrate classification in a hook workflow. A more sophisticated approach could use a large language model (LLM) to classify document content. The course Classify uploads with an LLM builds that approach.

The workflow adds the category as an ADM tag. Invoices and contracts also receive a needs-review tag:

l_category := classify_document(l_name, l_mime);
adm_tag_api.add_tag_to_document(p_event.document_id, 'category', l_category);

if l_category in ('invoice', 'contract') then
  adm_tag_api.add_tag_to_document(p_event.document_id, 'needs-review');
end if;

The duplicate workflow compares the uploaded version’s checksum with the latest version of each other live document. The worker reads l_checksum from the version identified by the queued p_event.version_id. If it finds a match, it adds a duplicate-of tag with the other document’s name:

begin
  select d.document_name
    into l_other_name
    from adm_documents d
    join adm_document_versions v
      on v.version_id = d.latest_version_id
   where v.checksum     = l_checksum
     and d.document_id != p_event.document_id
     and d.deleted_flag = 'N'
   order by d.created_date
   fetch first 1 row only;

  adm_tag_api.add_tag_to_document(p_event.document_id, 'duplicate-of', l_other_name);
exception
  when no_data_found then
    null;
end;

The complete workflow also adds a large-file tag when the uploaded version exceeds the sample’s size limit. Both workflows use ADM’s tag API and act as the system user. ADM records that user in its audit log.

Run @03_worker.sql to create the workflow tables and install the complete worker package. You call the worker directly in this lesson; automatic processing starts in lesson 5.

2 rows merged.
Package HKD_WORKER_API compiled
Package Body HKD_WORKER_API compiled
No errors.

Run the worker on a queued upload

The queue still holds the message of INV-1001.txt from lesson 2. Run this block to call the worker:

set serveroutput on
declare
  l_handled number;
begin
  l_handled := hkd_worker_api.process_queue;
  dbms_output.put_line('messages handled: ' || l_handled);
end;
/

select r.workflow_code
     , r.status
     , r.summary
  from hkd_workflow_runs r
 order by r.run_id;

messages handled: 1
WORKFLOW_CODE      STATUS    SUMMARY
__________________ _________ _______________________________________________________________
INTAKE_CLASSIFY    OK        Classified as invoice, tagged category=invoice, needs-review
DUPLICATE_SCAN     OK        Unique content, 12 B

The upload received the category=invoice and needs-review tags. No other live document had identical content. hkd_workflow_runs stores the status and summary of each workflow so you can inspect the result.

Read document tags in ADM

The classification is stored in ADM as tags. Run this block. It signs in as your user and reads the tags through the view adm_my_document_tags_v, which shows only what the signed-in user can see:

begin
  adm_context_api.system_user_login('ADMIN');
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'
 order by d.document_name
        , t.tag_name;

DOCUMENT_NAME    TAG_NAME        TAG_VALUE
________________ _______________ ____________
INV-1001.txt     category        invoice
INV-1001.txt     needs-review

The tags are real ADM data. The document shows them in ADM’s own file browser, and you can search by them.

Upload a duplicate file

The duplicate scan needs a second file with the same content. Run this block. It uploads the bytes of INV-1001.txt under another name and runs the worker.

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

  -- the same bytes as INV-1001.txt, under a second name
  l_document_id := adm_document_api.create_document(
                     p_folder_id      => l_folder_id
                   , p_document_name  => 'copy-of-inv.txt'
                   , p_file_content   => to_blob(utl_raw.cast_to_raw('Invoice 1001'))
                   , p_file_mime_type => 'text/plain'
                   );
  commit;

  l_handled := hkd_worker_api.process_queue;
  dbms_output.put_line('messages handled: ' || l_handled);
end;
/

select r.workflow_code
     , r.status
     , r.summary
  from hkd_workflow_runs r
 where r.subject = 'copy-of-inv.txt'
 order by r.run_id;

messages handled: 1
WORKFLOW_CODE      STATUS     SUMMARY
__________________ __________ ___________________________________________________
INTAKE_CLASSIFY    OK         Classified as document, tagged category=document
DUPLICATE_SCAN     FLAGGED    Identical to "INV-1001.txt"

The result is a checksum match: the two files contain the same 12 bytes, stored under different names. The workflow adds duplicate-of=INV-1001.txt to the second document. It checks the latest versions of all live documents in ADM, not only documents in the tutorial folder. It does not compare document meaning or extract invoice fields.

Next: Handle failed background workflowsKeep uploads available when analysis fails, inspect the failure, and recover processing.