Skip to content
APEX Document Management

Calling ADM over a database link

This page is for the case where ADM runs in one database and your application in another, and you want to reach ADM through a database link. Part of the API works that way, a large part does not, and one group of procedures does something worse than fail. None of this is specific to ADM: these are Oracle’s rules for remote PL/SQL calls. ADM’s API is shaped by them because files are LOBs and several results are collections or pipelined functions.

In short:

  • Reading metadata works: the adm_my_*_v views and the API calls that take and return plain values.
  • Files, content search and bulk actions do not. Use a small wrapper on the ADM side (see What to do instead).
  • Never call a job or configuration procedure over a link. See Procedures that commit.

What works

Section titled “What works”

The session context. Log in over the link, and the ADM context holds for every later call and query on that link connection:

begin
  adm.adm_context_api.system_user_login@adm_link(p_username => 'JANE');
end;
/


select document_name
  from adm.adm_my_documents_v@adm_link
 where folder_id = :folder_id;

The adm_my_*_v views, including their LOB columns: file_content and index_content can be selected into local variables or inserted into a local table.

Calls with plain arguments, from PL/SQL: VARCHAR2, NUMBER, DATE and timestamps, BOOLEAN, and the record types a package declares (adm.adm_fs_api.t_folder_ctx@adm_link). Creating a folder, renaming or trashing a document, tagging, sharing, checking permissions (adm_access_control_api.user_is_allowed_to_view_document and the rest) all work this way.

Transactions. Work done over the link is part of your transaction: it is visible to you before the commit, and it rolls back with it.

Errors. ADM’s error codes (-20100..-20299, and -20600..-20699 for the AI Pack) arrive unchanged, so the checks in Error handling work on the calling side:

exception
  when others then
    if adm.adm_error.is_not_found(sqlcode) then
      ...

ADM’s named exceptions (adm_error.e_*) are declarations in a remote package, like its constants. Rather than reference them over the link, compare sqlcode, or declare your own exception for the code you care about:

e_folder_not_found exception;
pragma exception_init(e_folder_not_found, -20170);  -- adm_error.c_err_folder_not_found

What does not work

Section titled “What does not work”
Restriction Error Affected
LOB arguments and return values in remote calls PLS-00564: lob arguments are not permitted in calls to remote server Everything that takes or returns a file or a CLOB: adm_document_api.create_document, add_document_version, add_comment; adm_storage_api.get_file_content; the chunked upload in adm_fs_upload_api; adm_pdf_region_api; adm_embed_perm_api (permissions JSON); adm_taxonomy_configs_api.save_config. AI Pack: adm_ai_rag_api.create_rag_collection, update_rag_collection, generate_answer, and the agent tools.
Pipelined (table) functions PLS-00907: cannot load library unit adm_search_api.search_documents and search_user_documents, adm_access_control_api.documents_in_shared_folders and folders_in_shared_folders. AI Pack: adm_ai_rag_api.search_chunks and prepare_sources.
Schema-level collection types as arguments. Your apex_t_varchar2 is not the remote database’s apex_t_varchar2. PLS-00306: wrong number or types of arguments The bulk file-browser commands in adm_fs_cmd_api (move_items, trash_items, restore_items, share_items, permanently_delete_items, create_folder_tree), adm_search_api.combine_facets, adm_cache_api.forget_*.
Oracle Text on remote tables ORA-22992: cannot use LOB locators from remote table contains() against adm_document_versions@adm_link. Together with the pipelined functions above, this means content search is not available over a link at all.
Package constants, read directly PLS-00224 adm_settings_api.c_sett_* and the other c_* constants. Use the literal value.
Defaults that refer to a package constant PLS-00512: Cannot directly access remote package variable About a dozen parameters, among them adm_context_api.system_login’s p_debug_level and adm_fs_api.resolve_folder’s p_require. Pass the argument explicitly and the call works.
APEX-bound entry points — adm_context_api.apex_login, the file-browser plug-in (adm_fs_plugin) and anything that reads APEX session state. They need an APEX session in the ADM database, which a link session does not have. Use system_user_login instead.

Procedures that commit

Section titled “Procedures that commit”

A few procedures end with a COMMIT, or call ctx_ddl, which commits. Called over a link, they do not merely fail. They commit your transaction as well: work you did locally beforehand is committed, and a rollback afterwards no longer undoes it. Depending on the release, Oracle may instead refuse the call with ORA-02074: cannot COMMIT in a distributed transaction. Neither outcome is one you want.

Do not call these over a link:

  • adm_job_automations_api.daily_job and indexing_job
  • adm_storage_index_api.sync_text_index and optimize_text_index
  • adm_settings_api.configure_object_storage and disable_object_storage
  • adm_cache_api.run_cache_update_job
  • AI Pack: the procedures in adm_ai_job_automations_api

They are administrative and scheduled work anyway. They belong in the ADM database, run by its own jobs or by an administrator connected there.

Section titled “A login lasts as long as the link connection”

The ADM context lives in the remote session that the link opens, and that session stays open until your session ends or you close the link. Everything your session does through that link until then runs as the user you logged in as.

That matters most for adm_context_api.system_login, which makes the remote session an administrator. Use system_user_login with the actual user wherever you can. If you do need the system login, close the connection afterwards:

commit;
alter session close database link adm_link;

Connect the link as a dedicated user with execute rights on the ADM packages and read access to the adm_my_*_v views, not as the ADM schema owner.

What to do instead

Section titled “What to do instead”

Files. A plain insert ... select of a LOB across a link does work; only LOB parameters in remote calls are refused. So a small wrapper package that you own in the ADM database can take the file from a table:

-- in the ADM database, owned by you, granted to the link user
create table my_inbox (
  inbox_id     number generated always as identity primary key
, folder_id    number        not null
, file_name    varchar2(255) not null
, mime_type    varchar2(255) not null
, file_content blob          not null
);


create or replace package my_adm_bridge as
  function import_file(p_inbox_id in number) return number;  -- returns the document_id
end;
/

On the calling side, insert into my_inbox@adm_link, then call my_adm_bridge.import_file@adm_link(:id). The bridge reads the row and calls adm_document_api.create_document locally, where the LOB restriction does not apply. Downloads work the same way in reverse, or by selecting file_content from adm_my_document_versions_v@adm_link.

Search and bulk actions. The same pattern: a wrapper in the ADM database that takes plain arguments (a search term, a comma-separated list of ids) and either returns a count or writes its result to a table you then select from over the link.

Metadata. Nothing to do. The adm_my_*_v views over the link are the intended interface.

Also refer to

Section titled “Also refer to”