Managing the Filesystem from the Database

Overview

As an administrator, you can manage the entire filesystem structure directly from the database using the ADM (APEX Document Management) APIs. This guide demonstrates common administrative tasks for folders and documents.

Getting Started

Before performing any administrative operations, you must establish a system login context:

-- Required for all admin operations
begin
  adm_context_api.system_login;
end;
/

Managing Folders

Creating Folder Structures

declare
  l_project_folder_id number;
  l_docs_folder_id    number;
  l_scripts_folder_id number;
begin
  -- System login required
  adm_context_api.system_login;

  -- Get the project folder
  l_project_folder_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade');

  -- Create project subfolders
  l_docs_folder_id := adm_folder_api.add_folder(
    p_folder_name => 'documentation',
    p_parent_folder_id => l_project_folder_id
  );

  l_scripts_folder_id := adm_folder_api.add_folder(
    p_folder_name => 'migration_scripts',
    p_parent_folder_id => l_project_folder_id
  );

  adm_folder_api.add_folder(
    p_folder_name => 'test_results',
    p_parent_folder_id => l_project_folder_id
  );

  commit;
end;
/

Renaming and Moving Folders

declare
  l_folder_id        number;
  l_new_parent_id    number;
begin
  adm_context_api.system_login;

  -- Get folder to rename/move
  l_folder_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade/migration_scripts');
  l_new_parent_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade');

  -- Rename folder
  adm_folder_api.rename_folder(
    p_folder_id => l_folder_id,
    p_new_folder_name => 'db_upgrade_scripts'
  );

  -- Move to different parent (if needed)
  adm_folder_api.move_folder(
    p_folder_id => l_folder_id,
    p_new_parent_folder_id => l_new_parent_id
  );

  commit;
end;
/

Deleting Folders

declare
  l_folder_id number;
begin
  adm_context_api.system_login;

  l_folder_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade/test_results');

  -- Move to trash (soft delete)
  adm_folder_api.trash_folder(
    p_folder_id => l_folder_id,
    p_user_id => 'USERNAME' -- put the name of the user into which trash folder the folder should be moved
  );

  -- Permanently delete from trash
  adm_folder_api.permanently_delete_folder(p_folder_id => l_folder_id);

  commit;
end;
/

Managing Documents

Creating Documents

declare
  l_folder_id    number;
  l_document_id  number;
  l_file_content blob;
begin
  adm_context_api.system_login;

  -- Get target folder
  l_folder_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade/documentation');

  -- Create document content (example with text)
  l_file_content := utl_raw.cast_to_raw('Oracle Database Upgrade Plan

  Phase 1: Pre-upgrade Assessment
  - Analyze current database version (19c -> 23ai)
  - Review compatibility matrix
  - Identify deprecated features

  Phase 2: Upgrade Execution
  - Create upgrade scripts
  - Test in staging environment
  - Schedule production upgrade');

  -- Create document
  l_document_id := adm_document_api.create_document(
    p_folder_id => l_folder_id,
    p_document_name => 'upgrade_plan.txt',
    p_file_content => l_file_content,
    p_file_mime_type => 'text/plain'
  );

  -- Add a new version
  l_file_content := utl_raw.cast_to_raw('Oracle Database Upgrade Plan - Updated

  Phase 1: Pre-upgrade Assessment (COMPLETED)
  - Current database version analyzed (19c -> 23ai)
  - Compatibility matrix reviewed
  - Deprecated features identified

  Phase 2: Upgrade Execution (IN PROGRESS)
  - Upgrade scripts created
  - Testing in staging environment
  - Production upgrade scheduled for next maintenance window');

  adm_document_api.add_document_version(
    p_document_id => l_document_id,
    p_file_content => l_file_content
  );

  commit;
end;
/

Document Operations

declare
  l_document_id number;
begin
  adm_context_api.system_login;

  -- Find document by querying the table directly
  select document_id
    into l_document_id
    from adm_documents d
    join adm_folders f on f.folder_id = d.folder_id
   where d.document_name = 'upgrade_plan.txt'
     and f.folder_path = '/groups/it_department/oracle_upgrade/documentation'
     and d.deleted_flag = 'N';

  -- Rename document
  adm_document_api.rename_document(
    p_document_id => l_document_id,
    p_new_name => 'oracle_23ai_upgrade_plan.txt'
  );

  -- Add retention policy (90 days from now)
  adm_document_api.add_file_retention(
    p_document_id => l_document_id,
    p_retention_delete_date => systimestamp + interval '90' day,
    p_retention_category => 'CIRIICAL_XYZ'
  );

  -- Add legal hold
  adm_document_api.add_legal_hold(p_document_id => l_document_id);

  commit;
end;
/

Deleting Documents

declare
  l_document_id number;
begin
  adm_context_api.system_login;

  select document_id
    into l_document_id
    from adm_documents d
    join adm_folders f on f.folder_id = d.folder_id
   where d.document_name = 'oracle_23ai_upgrade_plan.txt'
     and f.folder_path = '/groups/it_department/oracle_upgrade/documentation'
     and d.deleted_flag = 'N';

  -- Soft delete (move to trash)
  adm_document_api.trash_document(
    p_document_id => l_document_id,
    p_user_id => 'USERNAME' -- put the name of the user into which trash folder the folder should be moved
  );

  -- Permanently delete
  adm_document_api.permanently_delete_document(p_document_id => l_document_id);

  commit;
end;
/

Bulk Operations

Creating Multiple Folders and Documents

declare
  l_project_root_id  number;
  l_phase_folder_id  number;
  l_doc_content      blob;
  l_document_id      number;

  type t_phases is table of varchar2(100);
  l_phases t_phases := t_phases('planning', 'testing', 'production');

  type t_docs is table of varchar2(100);
  l_docs t_docs := t_docs('checklist.md', 'runbook.md', 'rollback_plan.sql');
begin
  adm_context_api.system_login;

  l_project_root_id := adm_folder_api.get_folder_id('/groups/it_department/oracle_upgrade');

  -- Create phase folders and documents
  for i in 1..l_phases.count loop
    l_phase_folder_id := adm_folder_api.add_folder(
      p_folder_name => l_phases(i),
      p_parent_folder_id => l_project_root_id
    );

    -- Create standard documents for each phase
    for j in 1..l_docs.count loop
      l_doc_content := utl_raw.cast_to_raw('Oracle Upgrade ' || initcap(l_phases(i)) || ' Phase - ' || l_docs(j) || chr(10) ||
                                          'Created for Oracle 23ai upgrade project' || chr(10) ||
                                          'Phase: ' || l_phases(i) || chr(10) ||
                                          'Document: ' || l_docs(j));

      l_document_id := adm_document_api.create_document(
        p_folder_id => l_phase_folder_id,
        p_document_name => l_docs(j),
        p_file_content => l_doc_content,
        p_file_mime_type => case
          when l_docs(j) like '%.md' then 'text/markdown'
          when l_docs(j) like '%.sql' then 'text/plain'
          else 'text/plain'
        end
      );
    end loop;
  end loop;

  commit;
end;
/

User Management

Creating Users with Folder Structure

declare
  l_admin_role_id number;
  l_edit_role_id  number;
begin
  adm_context_api.system_login;

  -- Get role IDs
  select role_id into l_admin_role_id from adm_roles where role_name = 'ADMIN';
  select role_id into l_edit_role_id from adm_roles where role_name = 'CONTRIBUTOR';

  -- Create admin user
  adm_user_api.add_user(
    p_username => 'JOHNDOE',
    p_role_id => l_admin_role_id
  );

  -- Create regular user
  adm_user_api.add_user(
    p_username => 'JANESMITH',
    p_role_id => l_edit_role_id
  );

  commit;
end;
/

Restoration Operations

Restoring Deleted Items

declare
  l_folder_id   number;
  l_document_id number;
begin
  adm_context_api.system_login;

  -- Find and restore deleted folder
  select folder_id
    into l_folder_id
    from adm_folders
   where folder_name like '%test_results%'
     and deleted_flag = 'Y'
     and folder_path like '/groups/it_department/oracle_upgrade%';

  adm_folder_api.restore_folder(p_folder_id => l_folder_id);

  -- Find and restore deleted document
  select document_id
    into l_document_id
    from adm_documents d
    join adm_folders f on f.folder_id = d.folder_id
   where d.document_name like '%upgrade_plan%'
     and d.deleted_flag = 'Y'
     and f.folder_path like '/groups/it_department/oracle_upgrade%';

  adm_document_api.restore_document(p_document_id => l_document_id);

  commit;
end;
/

API Reference

For detailed parameter information and additional functions, see:

• adm_folder_api - Complete folder management operations
• adm_document_api - Document creation, versioning, and lifecycle management
• adm_context_api - System authentication and context management

Important Notes

• Always call adm_context_api.system_login before administrative operations
• Documents with legal holds cannot be deleted until the hold is lifted
• Documents with active retention policies cannot be permanently deleted
• Folder operations cascade to all contained documents and subfolders
• All operations are audited and logged automatically
• Use commit to persist changes to the database