Create a standard folder layout from a hook

Start background processing automatically

By the end of this lesson, you can give each new project a standard folder layout, regardless of where it is created.

Lesson 6 of 6~20 minScript: 06_blueprint.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

Give each project the same starting folders

Hooks also apply to folder creation. When a user creates a project inside a folder named Projects, this workflow adds 01_Contracts, 02_Deliverables, and 03_Archive.

Users get the same starting layout whether they create the project in ADM’s APEX app or through another application using ADM’s folder API. Each application can create the project folder without building the standard layout itself.

Add standard subfolders to new projects

First, limit the workflow to folders directly inside Projects. This excerpt shows the condition:

if lower(l_parent_name) is null or lower(l_parent_name) != 'projects' then
  return outcome('SKIPPED', 'Not directly inside a folder named Projects');
end if;

The folder workflow uses a list named c_blueprint: 01_Contracts, 02_Deliverables, and 03_Archive. This excerpt shows how it creates each missing subfolder through ADM’s folder API:

for i in 1 .. c_blueprint.count loop
  select count(*)
    into l_exists
    from adm_folders
   where parent_folder_id = p_event.folder_id
     and folder_name      = c_blueprint(i)
     and deleted_flag     = 'N';

  if l_exists = 0 then
    adm_folder_api.add_folder(
      p_folder_name      => c_blueprint(i)
    , p_parent_folder_id => p_event.folder_id
    );
    l_created := l_created + 1;
  end if;
end loop;

The existence check makes another delivery safe: the worker creates only subfolders that are missing.

Creating a folder also runs its hook

Each adm_folder_api.add_folder call runs AFTER_NEW_FOLDER_CREATION, so the new subfolders produce their own events. The project condition above skips those events because their parent is the project, not Projects. Without a condition, the worker could keep creating subfolders and new events.

Register the folder workflow

The folder hook receives the new folder ID and queues it for the same worker. This excerpt shows the entry procedure:

procedure after_new_folder_creation (
  p_folder_id in adm_folders.folder_id%type
)
as
begin
  enqueue_event(adm_hooks_api.c_after_new_folder_creation, p_folder_id => p_folder_id);
end after_new_folder_creation;

Register that call for AFTER_NEW_FOLDER_CREATION:

update adm_hooks
   set hook_plsql = 'hkd_hook_api.after_new_folder_creation(p_folder_id => :p_folder_id);'
     , updated_by = 'HKD_TUTORIAL'
     , updated_date = current_timestamp
 where hook_key = 'AFTER_NEW_FOLDER_CREATION';

Run @06_blueprint.sql to install this extension. It adds the folder entry procedure and workflow to the tutorial packages, then registers the folder hook. Your upload guard, tagging workflows, failure log, and automatic processing remain in place. Existing tables and logs are preserved. It does not install the full demo or add a version workflow.

Create a project with standard subfolders

Run this block to create Projects and the project Alpha. It waits for the worker to create the three standard subfolders.

declare
  l_folder_id   adm_folders.folder_id%type;
  l_projects_id adm_folders.folder_id%type;
  l_alpha_id    adm_folders.folder_id%type;
  l_subfolders number := 0;
begin
  adm_context_api.system_user_login('ADMIN');
  l_folder_id := adm_folder_api.get_folder_id('/users/admin/hkd_tutorial');

  l_projects_id := adm_folder_api.add_folder('Projects', l_folder_id);
  l_alpha_id    := adm_folder_api.add_folder('Alpha', l_projects_id);
  commit;

  for i in 1 .. 300 loop
    select count(*)
      into l_subfolders
      from adm_folders
     where parent_folder_id = l_alpha_id
       and folder_name in ('01_Contracts', '02_Deliverables', '03_Archive')
       and deleted_flag = 'N';
    exit when l_subfolders = 3;
    dbms_session.sleep(0.2);
  end loop;

  if l_subfolders != 3 then
    raise_application_error(-20721, 'Project saved, but its standard folders were not created within 60 seconds. Review pending work and failures.');
  end if;
end;
/

Run this query to see the standard layout:

select f.folder_path
     , f.created_by
  from adm_folders f
 where f.folder_path like '/users/admin/hkd_tutorial/projects/alpha/%'
   and f.deleted_flag = 'N'
 order by f.folder_path;

FOLDER_PATH                                                 CREATED_BY
___________________________________________________________ _____________
/users/admin/hkd_tutorial/projects/alpha/01_contracts       ADMIN
/users/admin/hkd_tutorial/projects/alpha/02_deliverables    ADMIN
/users/admin/hkd_tutorial/projects/alpha/03_archive         ADMIN

The three subfolders exist, and ADMIN created them. The workflow switched to the user in the message before it created the folders, so the user owns them and can work in them. The other workflows act as the system user. They tag documents, and the audit log of ADM shows that an automation did it.

Folder creation and queued work commit together

The folder hook uses the same policy as the upload hook: if it cannot queue the work, folder creation fails. The caller must roll back the failed operation.

Keep folder events from starting an endless chain

The project condition skips the standard subfolders because their parent is the project, not Projects. The sample also carries a generation count in each event and rejects events beyond generation 5. This bounds the chain if a condition is faulty. It does not limit how many events one workflow can create.

ADM’s nested-hook limit applies within one call stack. Queued work runs in separate transactions, so the sample must carry its own generation count. See Hook recursion.

Skipping a hook also skips its rules

Administrators and the system user can pass p_run_hooks => false for an individual folder or document creation call. Permission checks, validation, and auditing still apply. This workflow acts as the project creator and keeps hooks enabled; its project condition stops further folder creation.

Use the same pattern on other events

This course uses two events: AFTER_NEW_FILE_UPLOAD and AFTER_NEW_FOLDER_CREATION. You register a procedure for the other events in the same way. Each event gives your procedure its own parameters.

What happens in ADM	Events
A document gets a new version	AFTER_NEW_FILE_VERSION
A document or folder moves	AFTER_DOCUMENT_MOVED, AFTER_FOLDER_MOVED
A document goes to the trash or comes back	AFTER_DOCUMENT_TRASHED, AFTER_DOCUMENT_RESTORED
A document is deleted for good	BEFORE_DOCUMENT_DELETE
A user shares a document or folder, or creates a share link	AFTER_SHARE_CREATED
A retention is added, extended or removed	AFTER_RETENTION_CHANGED
A retention ends soon, or has ended	RETENTION_ENDING_SOON, RETENTION_ENDED
ADM signs a PDF	AFTER_PDF_SIGNED
The AI Pack accepts a form extraction	AFTER_FORM_EXTRACTION

Most of these events run in the transaction of the operation, as the upload hook does. A hook can refuse the operation with an error from your own range. The two retention end events are different: the daily job runs them as the system user. The Hooks reference gives the parameters and the rules of each event.

Continue with the demo app or delete tutorial objects

You now have shared upload rules, document tags, and standard project folders without changing ADM’s packages. The Hooks demo app can display these workflows and their results in APEX. Its separate database installer adds the APEX support package, an outbox, and a version-change workflow. Run that installer only if you want to extend the tutorial with the complete demo. It replaces the sample packages and registers the version hook too.

The teardown deletes the tutorial objects, frees the hook slots, and moves hkd_tutorial to the trash. Workflow tags remain in ADM. When you finish the course, run @99_teardown.sql.

Hook events and error handling references

The Hooks page lists when a hook fires. The page Error handling explains the error range for your own code.

Hooks demo appInstall the APEX application on top of the database objects you built, and read the code behind each page.