Refuse an upload from a hook

All tutorials

By the end of this lesson, you can refuse an upload from a hook, and the user reads the reason.

Lesson 1 of 6~20 minScript: 00_setup.sql, 01_guard.sql, 99_teardown.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

Apply one rule across upload paths

Register your own PL/SQL once, and ADM calls it for uploads from its APEX app, REST API, and other applications using its document API. You can refuse an upload or start later work without changing ADM’s packages. An APEX page process handles uploads through that page; a hook applies your rule across these upload paths.

In this lesson, you attach a guard that refuses executable files and empty files. The guard’s message reaches the uploading application, and a separate log keeps the refusal for an administrator.

Later, the same hook starts shared background processing. It adds category tags and tags files with matching checksums as duplicate-of. The final lesson gives new projects a standard folder layout through a folder hook.

Choose where processing belongs

What you need	Where to put it
One application needs an analysis result before its next step	Call the analysis directly after uploading in that application’s flow.
Every ADM upload path must pass a quick rule	Run the rule in an upload hook.
Every ADM upload path needs shared processing, and the result can arrive later	Queue the work from an upload hook for a background worker.

For example, an invoice application can extract fields before opening its approval form. A shared category tag belongs in the hook workflow if all uploaded documents need it, regardless of the uploading application. This course uses a queue for that shared work. An accepted upload and completed processing are separate results.

This course uses the upload event and the folder event. ADM also has events for new versions, moves, the trash, deletes, shares, retention dates and signed PDFs. The AI Pack adds an event for form extractions. The Hooks reference lists all events and the parameters your procedure receives.

Prepare the tutorial schema

Get the scripts from United-Codes/adm or use the copy in your delivery archive. The lesson header links each script. You need ADM installed in a schema and the following prerequisites:

• SQLcl or SQL*Plus, connected as the ADM schema owner. Open it in examples/hooks-demo-app/tutorial from the public repo or delivery archive. Use the same schema for all six lessons.
• An ADM user. The examples use ADMIN. If your user has another name, change the define line in 00_setup.sql and 99_teardown.sql. Replace ADMIN in the lesson blocks and /users/admin in their folder paths too.
• Two grants from a DBA: execute on sys.dbms_aq and sys.dbms_aqadm, given to the schema directly and not through a role. Lesson 2 needs them. You can do this lesson without them, and the setup script warns you when they are missing. The schema also needs the privileges create table, create procedure, create type and create job.
• Free hook slots. ADM keeps one snippet for each hook event. This course registers its own, so the setup script stops when another snippet is already there. It never overwrites one. If you already use hooks, run the course in a schema without them. After trying the course, use the combined-call example below to attach another rule to your existing registration.

Recorded database version

The examples were recorded on Oracle Database 26ai Free. The scripts target Oracle Database 19c or later; 19c execution has not been verified. Use a test schema before applying the examples to your application.

99_teardown.sql removes everything the course creates, frees the hook slots, and moves the course folder to the trash. Run it when you are finished.

The excerpts in this course show the code to learn from. Run the named installation scripts for the complete packages and supporting objects. Run the later test blocks separately when instructed.

1. Run @00_setup.sql. It prints one line for each prerequisite, creates two tables (hkd_blocked_extensions and hkd_rejections), and creates the folder hkd_tutorial in the home folder of your user. All objects of the course start with hkd_. You can run the setup script again.

The recordings in this course leave out the line PL/SQL procedure successfully completed. that SQLcl prints after each block.

=== Prerequisites ===
1. ADM is installed (3 hook events).
2. The schema can use Advanced Queuing.
3. The hook slots are free.
4. The ADM user ADMIN exists.
=== Tables ===
=== Working folder ===
RESULT
______________________________________________________________________________________
setup OK: 4 blocked extensions, 0 refusals logged, folder /users/admin/hkd_tutorial
1 row selected.

Upload guard rules

The upload guard rejects blocked extensions and empty files. You keep these rules in your own package, hkd_hook_api. Its upload procedure receives the document and version IDs:

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);
end after_new_file_upload;

guard_file receives the hook key and the uploaded IDs from that call. It reads the name and size using those IDs:

procedure guard_file (
  p_hook_key    in varchar2
, p_document_id in adm_documents.document_id%type
, p_version_id  in adm_document_versions.version_id%type
)
as
  l_name      adm_documents.document_name%type;
  l_file_size adm_document_versions.file_size%type;
  l_extension varchar2(20 char);
  l_reason    hkd_blocked_extensions.reason%type;
  l_message   varchar2(1000 char);
begin
  select d.document_name
       , v.file_size
    into l_name
       , l_file_size
    from adm_documents d
    join adm_document_versions v
      on v.document_id = d.document_id
   where d.document_id = p_document_id
     and v.version_id  = p_version_id;
  -- The guard rules below follow this query in the complete procedure.

The blocked-extension rule looks up the filename’s extension and raises a user-facing error if it finds one:

if instr(l_name, '.') > 0 then
  l_extension := lower(regexp_substr(l_name, '[^.]+$'));

  begin
    select reason
      into l_reason
      from hkd_blocked_extensions
     where extension = l_extension;

    l_message := 'Files of type .' || l_extension || ' are not accepted: ' || l_reason;
    log_rejection(p_hook_key, l_name, l_message);
    raise_application_error(c_err_blocked_extension, l_message);
  exception
    when no_data_found then
      null;
  end;
end if;

The package declares c_err_blocked_extension as -20701. Its second rule follows the same pattern for a zero-byte file, with c_err_empty_file set to -20702.

The guard reads only the name and the size of the file, never its content. The content can be in object storage. Reading it adds a network call to every upload.

The error numbers -20701 and -20702 are in the range -20700 to -20999. ADM keeps this range free for your code, and it does not wrap an error from it. The caller receives your error number and your message, so the user can read the reason. Write the message for the user.

Keep diagnostics out of the message

The user reads the message as you wrote it. Do not put sqlerrm, a backtrace, or a table name in it. Write those to the APEX debug log with apex_debug instead.

Register the upload guard

Register the procedure for AFTER_NEW_FILE_UPLOAD so that it runs for each new file. This excerpt shows the registration in the installation script. The document and version parameters identify the uploaded file:

update adm_hooks
   set hook_plsql   = 'hkd_hook_api.after_new_file_upload(p_document_id => :p_document_id, p_version_id => :p_version_id);'
     , updated_by   = 'HKD_TUTORIAL'
     , updated_date = current_timestamp
 where hook_key = 'AFTER_NEW_FILE_UPLOAD';

The script first makes sure that this registration does not overwrite another hook. It also compiles the complete package, including both guard rules and the rejection log.

Run @01_guard.sql to install the upload guard and register it.

Package HKD_HOOK_API compiled
Package Body HKD_HOOK_API compiled
No errors.
Registered hooks:
AFTER_NEW_FILE_UPLOAD:
  hkd_hook_api.after_new_file_upload(p_document_id => :p_document_id, p_version_id => :p_version_id);

Upload a blocked file

Run this block. It uploads a file with the extension .exe, and it rolls back when the upload fails.

set serveroutput on
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  => 'setup.exe'
                   , p_file_content   => to_blob(utl_raw.cast_to_raw('MZ'))
                   , p_file_mime_type => 'application/octet-stream'
                   );
  dbms_output.put_line('stored document ' || l_document_id);
  rollback;
exception
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

ORA-20701: Files of type .exe are not accepted: Executables are not documents

The upload fails with the error number and the message of the guard. In an APEX application that uses adm_error.apex_error_handler, the user sees the same sentence without the ORA-20701: prefix.

ADM preserves customer error messages in -20700 to -20999, so your guard can explain a refusal. Unexpected hook failures are normally wrapped in ORA-20109, with a message that names the event. ADM writes the cause to the APEX debug log. See Hook error handling for details.

Roll back rejected uploads in your caller

If your PL/SQL caller catches an upload error, it must roll back the upload. Catching the error and then committing can store the rejected file. APEX rolls back a failed request.

Keep a record of refusals

The user reads the reason in the error, but the refused upload leaves nothing behind in ADM: the document is rolled back. An administrator cannot see what was refused, for whom, and why. The guard saves each refusal in hkd_rejections before it raises the error. Run this query:

select subject
     , reason
     , rejected_by
  from hkd_rejections
 order by rejection_id;

SUBJECT      REASON                                                                REJECTED_BY
____________ _____________________________________________________________________ ______________
setup.exe    Files of type .exe are not accepted: Executables are not documents    ADMIN

An application can read this table and show the refusals to an administrator. The Hooks demo app does that on the Queue & Outbox page.

Log a refusal in an autonomous transaction

An ordinary insert in the upload transaction rolls back with the upload. The record of a refusal needs a separate transaction to survive.

This excerpt shows how log_rejection writes the row in an autonomous transaction:

procedure log_rejection (
  p_hook_key in varchar2
, p_subject  in varchar2
, p_reason   in varchar2
)
as
  pragma autonomous_transaction;
begin
  insert into hkd_rejections (hook_key, subject, reason, rejected_by)
  values (p_hook_key, substr(p_subject, 1, 1000), substr(p_reason, 1, 1000), sys_context('ADM_CONTEXT', 'ADM_USERNAME'));

  commit;
end log_rejection;

An autonomous transaction has its own commit. This commit saves the log row and does not touch the transaction of the upload. It is the one case where a hook can commit. A hook must not commit in any other case, because the transaction belongs to the caller.

Combine your own upload rules

ADM has one registration per event. In your application, that registration can call more than one procedure. For example, if my_adm_hooks.check_upload is your existing procedure with the same parameters, you can enter both calls in Administration → Hooks:

my_adm_hooks.check_upload(
  p_document_id => :p_document_id
, p_version_id  => :p_version_id
);
hkd_hook_api.after_new_file_upload(
  p_document_id => :p_document_id
, p_version_id  => :p_version_id
);

The procedures run in order. An error in the first prevents the second from running. Use this pattern with your own packages after trying the tutorial in a separate schema. The tutorial’s setup and teardown manage its registration; they do not manage this combined registration.

Next: Queue upload events with Oracle AQEnqueue an upload event in the same transaction as the file.