Querying ADM from your own application

This page is for the case where ADM is part of your own application: your invoice page needs to list the files attached to the invoice, your project screen needs a document report, your ORDS feed needs to return a user’s files. You want a query you can put in a region, and you want it to return what this user may see and nothing else.

The answer is the adm_my_*_v views. They take their identity from the ADM session context, they return only what that user may see, and they are a supported interface — column names, row scope and uniqueness are part of the contract and will not change under you within a major release.

Not the same page as the admin guide

Managing the filesystem from the database is the administrator’s guide: it logs in as the system user, bypasses every permission check and writes. This page reads, as a named user, with the checks on.

Two families of views, and the prefix tells you which

adm_my_*_v

Session scoped. Returns only what the ADM user established in the current session may see. No username parameter. No context, no rows. This is what you build an application on.

adm_report_*_v

Unfiltered. Returns every row in the instance, whoever is asking. Establishing a context does not narrow it. For administrative reports and dashboards that carry their own authorization.

Do not put a reporting view on a user-facing page

adm_report_documents_v will happily list every document in the instance to whoever runs it. It is not a bug and it is not going to change — the view exists for administrative reporting, and there are consumers relying on that scope. Reach for it only when “every row, regardless of who is asking” is what you actually want.

The full inventory is in the views reference. The six integration views are:

View	One row per	Notes
adm_my_documents_v	active document you may see	Current-version metadata joined. Unique by document_id.
adm_my_folders_v	folder you may see	Unique by folder_id. No content counts — see below.
adm_my_document_tags_v	tag on a document you may see	Unique by document_tag_id. tag_name resolved.
adm_my_document_versions_v	version of a document you may see	Metadata only. Unique by version_id.
adm_my_document_annotations_v	annotation on a document you may see	Unique by annotation_id.
adm_my_folder_annotations_v	annotation on a folder you may see	Unique by annotation_id.

They are deliberately kept apart rather than folded into one omnibus query: tags, versions and annotations are all one-to-many, so joining them into the document view would multiply your document rows. Join them yourself, when you need them.

Establishing an identity

The views read sys_context('ADM_CONTEXT', 'ADM_USERNAME') and sys_context('ADM_CONTEXT', 'ADM_ROLE'). There is no username parameter anywhere, on purpose: a name that arrives from a browser must never become authorization.

From your own APEX application

Set both of these in Shared Components → Application Definition → Security:

-- Initialization PL/SQL Code
begin
  adm_context_api.system_user_login(:APP_USER);
end;

-- Cleanup PL/SQL Code
begin
  adm_context_api.clear_context;
end;

The cleanup half is not optional

ADM_CONTEXT is scoped to the database session, and an APEX session is served from a connection pool. Without the cleanup code, the connection goes back to the pool still carrying the last request’s identity, and the next request to land on it — possibly from another user, possibly from a job — starts out as that person until something overwrites it. Initialization alone is not enough, because a request whose initialization fails never overwrites anything.

system_user_login takes the ADM username, upper-cases it, looks up the user’s role in adm_users and maps it onto the context role. It clears the context before the lookup, so a failed login leaves the session as nobody rather than as whoever used the connection before.

From a script, a job or an ORDS handler

Same call:

begin
  adm_context_api.system_user_login('JDOE');

  for r in (select document_id, document_name, document_path
              from adm_my_documents_v
             order by document_name)
  loop
    dbms_output.put_line(r.document_path);
  end loop;

  adm_context_api.clear_context;
end;
/

Do not use adm_context_api.system_login for this. It logs in as _UC_SYSTEM_ with the ADMIN role, which means the views return every active document in the instance — see Administrators below.

What happens when identity is missing or wrong

Situation	Behaviour
No context established at all	Every adm_my_*_v view returns zero rows. It does not raise, and it does not fall back to anything.
Context cleared mid-session	Same — zero rows from that point on.
system_user_login('NOBODY') for a user that does not exist	Raises adm_error.e_user_not_found (ORA-20128), and leaves the session with no context.
Username set but role missing	Zero rows. The role is checked with nvl(..., 'NONE'), so a half-established identity is treated as no identity, never as an administrator.

Zero rows rather than an error is deliberate for the views: they are row sources, used in regions and reports where raising is the wrong shape. If you want an error, ask adm_context_api or adm_access_control_api, both of which raise.

Administrators

A session whose context role is ADMIN sees every active document and every non-trashed folder, through all six views. That is not a special case invented for the views — it is what adm_access_control_api.user_is_allowed_to_view_document and is_allowed_to_view_folder do for an administrator, and the views agree with them on purpose.

Two consequences worth planning for:

• Do not test a listing as an administrator and conclude the filtering works. Test as an ordinary user.
• If your application has a page that must show only the user’s own files even to an administrator, filter it yourself — for example where user_owner = :APP_USER.

What the views do not honour

Share-URL tokens and embed tokens are not honoured. Those are unauthenticated routes with their own identity and visibility rules, and mixing them into a view whose contract is “the current user” would blur two different things. A public share link goes through adm_link_shares_api; an embedded region goes through embedding.

Worked examples

List a folder

The everyday case. Filter on folder_id when you have it — that is the cheap predicate:

select document_id
     , document_name
     , file_mime_type
     , latest_file_size
     , latest_version_created_date
  from adm_my_documents_v
 where folder_id = :P1_FOLDER_ID
 order by document_name

By path, when the path is what your application stores:

select document_id
     , document_name
     , latest_file_size
  from adm_my_documents_v
 where folder_path = '/groups/finance/invoices/2026'
 order by document_name

To include everything below a folder rather than just its direct contents, filter on the path prefix — and escape it, because _ is a like wildcard and folder names contain underscores routinely:

select document_id, document_path, latest_file_size
  from adm_my_documents_v
 where folder_path = :P1_PATH
    or folder_path like adm_utils.escape_like(:P1_PATH) || '/%' escape '\'
 order by document_path

Resolve a path to a folder you may use

adm_folder_api.get_folder_id resolves a path without asking whether you may see it. Going through the view instead makes the access check part of the lookup:

select folder_id
  from adm_my_folders_v
 where folder_path = lower(:P1_PATH)

No row means either “no such folder” or “not yours” — deliberately indistinguishable, so a probe cannot map the filesystem.

Attach documents to your own business records

This is the one thing the views cannot do for you

Nothing in ADM knows that invoice 4711 relates to a document. Views can tell you what a user may see; they cannot invent a durable relationship between your rows and ADM’s. Own that relationship yourself, in a table of your own.

The pattern is a small mapping table on your side:

create table inv_attachments (
    attachment_id   number generated always as identity
    -- number, never integer: an ADM id runs to 32 decimal digits
  , invoice_id      number not null
  , adm_document_id number not null
  , attached_date   timestamp with local time zone default current_timestamp not null
  , attached_by     varchar2(255 char)
  , constraint inv_attachments_pk primary key (attachment_id)
  , constraint inv_attachments_invoice_fk foreign key (invoice_id)
      references invoices (invoice_id) on delete cascade
    -- on delete cascade, never restricting - see the warning below
  , constraint inv_attachments_document_fk foreign key (adm_document_id)
      references adm_documents (document_id) on delete cascade
  , constraint inv_attachments_uk unique (invoice_id, adm_document_id)
);

Store adm_document_id, never the name or the path — both change when somebody renames or moves the file, and your link would silently stop resolving.

Never make the foreign key restricting

A restricting foreign key to adm_documents makes ADM’s own permanent delete fail, which turns an ordinary user action into an error nobody can clear. Use on delete cascade when the link has no meaning without the document, or on delete set null (and a nullable column) when you want to keep your row and record that the file is gone.

Then the region query joins your table to the view:

select a.attachment_id
     , d.document_id
     , d.document_name
     , d.latest_file_size
     , d.latest_version_created_date
  from inv_attachments a
  join adm_my_documents_v d
    on d.document_id = a.adm_document_id
 where a.invoice_id = :P10_INVOICE_ID
 order by d.document_name

An inner join is the point: a row of yours whose document the current user may not see, or which has been trashed or archived, simply does not appear. You get the filtering for free and you never have to reproduce ADM’s access rules. If you would rather show a placeholder than nothing, use a left join and handle the null document_id.

Do not use an annotation for this. Annotations are a metadata bag with no referential integrity: nothing stops the value going stale, nothing cascades, and nothing indexes it for the join you actually want.

Filter by tags

Facet-style, one tag:

select d.document_id
     , d.document_name
  from adm_my_documents_v d
 where exists (select 1
                 from adm_my_document_tags_v t
                where t.document_id = d.document_id
                  and t.tag_name = 'contract')
 order by d.document_name

All of several tags (and semantics), without multiplying document rows:

select d.document_id
     , d.document_name
  from adm_my_documents_v d
 where (select count(distinct t.tag_name)
          from adm_my_document_tags_v t
         where t.document_id = d.document_id
           and t.tag_name in ('contract', 'signed')) = 2
 order by d.document_name

The tags of one document, for a detail region:

select tag_name, tag_value
  from adm_my_document_tags_v
 where document_id = :P10_DOCUMENT_ID
 order by tag_name

And the facet counts for a sidebar — over the tag view, so a tag on a document the user cannot see contributes nothing:

select tag_name
     , count(distinct document_id) as document_count
  from adm_my_document_tags_v
 group by tag_name
 having count(distinct document_id) > 0
 order by document_count desc, tag_name

Use the tag view for counts, not the reporting view

adm_report_tags_v.documents_count counts every document carrying the tag — including trashed, archived and inaccessible ones. Shown to an end user it both looks wrong and leaks how much else is in the instance.

Show version history

select version_number
     , file_size
     , created_date
     , created_by
     , version_status
  from adm_my_document_versions_v
 where document_id = :P10_DOCUMENT_ID
 order by version_number desc

version_status is CURRENT for exactly one version per document and HISTORICAL for the rest. The permission rule is simple and deliberate: if you may view a document, you may see all of its versions. ADM has no per-version grant. If your application needs one, it has to be your own layer on top.

Read the file metadata ADM extracted

Upload runs adm_file_metadata_api, which writes technical metadata as annotations under the reserved file. prefix:

select d.document_name
     , max(case when a.annotation_key = 'file.title'      then a.annotation_value end) as title
     , max(case when a.annotation_key = 'file.author'     then a.annotation_value end) as author
     , max(case when a.annotation_key = 'file.page_count' then a.annotation_value end) as pages
  from adm_my_documents_v d
  left join adm_my_document_annotations_v a
    on a.document_id = d.document_id
   and a.key_namespace = 'FILE'
 where d.folder_id = :P1_FOLDER_ID
 group by d.document_id, d.document_name
 order by d.document_name

Your own metadata is everything with key_namespace = 'CUSTOM'. Write it with adm_annotations_api.add_document_annotation, and stay out of the file. prefix — extraction rewrites that whole set on every new version and deletes the keys that no longer apply.

Fetch the file content

The views carry no BLOBs, on purpose: content has to come out through the storage abstraction so that a file in OCI Object Storage behaves like one in the database.

get_file_content does not check anything

adm_storage_api.get_file_content is the storage abstraction, not the authorization gate. It hands back the bytes for any version_id you name. Resolving the version through an access-filtered row source is what makes the call safe — which is exactly what ADM’s own download processes do.

So resolve through the view, in the same statement:

select d.document_name
     , d.file_mime_type
     , adm_storage_api.get_file_content(d.latest_version_id) as file_content
  from adm_my_documents_v d
 where d.document_id = :P10_DOCUMENT_ID

If the user may not see the document the query returns no row, and nothing is fetched. For a specific historical version, resolve it through adm_my_document_versions_v the same way.

In PL/SQL, where you have an id and no row source to hang the check on, ask explicitly first:

declare
  l_content blob;
begin
  if not adm_access_control_api.user_is_allowed_to_view_document(
           p_document_id => p_document_id
         )
  then
    -- -20700..-20999 is the range adm_error leaves free for customer and hook code
    raise_application_error(-20900, 'Not allowed to read this document');
  end if;

  select adm_storage_api.get_file_content(latest_version_id)
    into l_content
    from adm_documents
   where document_id = p_document_id;
end;
/

Count your own documents per folder

adm_my_folders_v deliberately carries no document count. A count that ignored access would leak the existence of files the user cannot see, and a per-row correlated count over the access check is far too expensive for a listing. Group the document view instead — one pass, and right by construction:

select f.folder_id
     , f.folder_name
     , f.folder_path
     , count(d.document_id) as my_document_count
  from adm_my_folders_v f
  left join adm_my_documents_v d
    on d.folder_id = f.folder_id
 where f.parent_folder_id = :P1_PARENT_FOLDER_ID
 group by f.folder_id, f.folder_name, f.folder_path
 order by f.folder_name

Walk the folder tree

parent_folder_id may point at a folder you cannot see — the scaffolding folders /, /users and /groups are ordinary rows that nobody shares — so a connect by that assumes every parent is present will stop short. Anchor it on the folders you can see:

select folder_id
     , folder_name
     , folder_path
     , level as tree_level
  from adm_my_folders_v
 start with parent_folder_id is null
         or parent_folder_id not in (select folder_id from adm_my_folders_v)
 connect by prior folder_id = parent_folder_id
 order siblings by folder_name

What these views will not do

Mutations. Reads only. Creating, renaming, moving, versioning, sharing, tagging and deleting all go through the APIs — adm_document_api, adm_folder_api, adm_shares_api, adm_tag_api, adm_annotations_api — which run their own checks. A view being filtered does not make a subsequent update authorized.

Downloads and URLs. No BLOBs, no storage keys, no signed URLs, no share or embed tokens. See Fetch the file content. A per-row URL-generating function is also deliberately absent: it would tie an ordinary SQL report to APEX session state, and the result would not be usable from a job or a batch.

Incremental synchronisation. updated_date is not a change feed. It moves for a new version, a rename, a move and a lifecycle change — and it does not move when a tag, comment, annotation, share or permission changes, or when a document is permanently deleted. Polling it will miss things. If you need a real feed, drive it off hooks or the audit log, and define what “changed” means for your case first.

Embedding. A token-scoped embed has different identity and visibility rules; a current-user view is not a substitute. See embedding.

Trash. The views are active-only. A trashed or archived document is not returned, not even to its owner — even though adm_access_control_api.user_is_allowed_to_view_document does still say yes for that case, which is what lets a user look into their own trash inside ADM itself. If you need a trash screen in your own application, use adm_fs_api or query the tables with your own checks.

Performance

The access filtering is one hash semi-join, evaluated once per statement, not a check per row. Measured on a local instance with 21 205 active documents, 10 416 of them visible to the test user:

Query	Time
count(*) over adm_my_documents_v, ordinary user	0.18 s
count(*) over adm_my_documents_v, administrator	0.04 s
First 25 rows of one folder, ordered by name	0.09 s
count(*) with no context (zero rows)	0.01 s

Two things to know:

• Filter on folder_id or document_id where you can. They are indexed and the predicate pushes into the view.
• If a query over one of these views is unexpectedly slow, look at the plan for a FILTER above the access subquery. That means the semi-join stopped being unnested and is now running once per candidate row, which is roughly a thousand times slower. Wrapping the view in a construct that blocks view merging can do it. Restructuring your own predicates — particularly getting an or out of the way — is usually the fix.

Also refer to

• Views reference — every column of every view, with its access scope
• The security model — where access comes from, and how to check rights in your own code
• Managing the filesystem from the database — the write side, as an administrator
• Hooks — reacting to what ADM does
• Embedding — the token-scoped route
• Error handling — catching adm_error exceptions by name
• Extensibility — the rules that keep you upgradeable