How to Manage an APEX Instance and Generate Test Data from PL/SQL

Administration work in Oracle APEX usually happens in Administration Services or SQL Workshop, but production instances are often run by scripts, and some have no App Builder at all. A group of PL/SQL packages covers that ground: APEX_INSTANCE_ADMIN and APEX_INSTANCE_DEBUG for the instance, APEX_APPLICATION_ADMIN for installed applications, APEX_UI_DEFAULT_UPDATE for UI defaults, APEX_DB_DICTIONARY for describing tables to language models, and APEX_DG_DATA_GEN for test data.

This guide covers each package with a tested example and the real output it produced in Oracle APEX 26.1.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 in a workspace named APEXBOOK, whose schema ORBIT holds the Orbit Outfitters sample tables from the orb_tables repository on GitHub. Application 200, called API Lab, is the test application. The instance examples ran as SYS, because they need administrator rights the application schema should not have. The others ran as ORBIT after setting the workspace with apex_util.set_workspace, and only the data generator example needs an APEX session, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. The output under each example is exactly what the database printed.

The Instance: APEX_INSTANCE_ADMIN

APEX_INSTANCE_ADMIN does what the Administration Services pages do: instance parameters, workspaces, schemas, and cleanup. It needs the database role APEX_ADMINISTRATOR_ROLE, or APEX_ADMINISTRATOR_READ_ROLE for reading. An application schema should not have either role, which is why the example runs as SYS and only reads. The declarative side is covered in the Oracle APEX administration guide.

GET_PARAMETER and SET_PARAMETER

These read and set an instance parameter by name, such as SMTP_HOST_ADDRESS, SMTP_HOST_PORT, SMTP_USERNAME, INSTANCE_URL, MAX_SESSION_LENGTH_SEC, MAX_SESSION_IDLE_SEC, PASSWORD_HISTORY_DAYS, ALLOW_PUBLIC_FILE_UPLOAD, WALLET_PATH, PRINT_BIB_LICENSED, and about 200 more listed in the Oracle documentation. GET_WORKSPACE_PARAMETER and SET_WORKSPACE_PARAMETER do the same for the settings a workspace can override. Changes take effect immediately.

Syntax:

apex_instance_admin.get_parameter(p_parameter in varchar2) return varchar2
apex_instance_admin.set_parameter(p_parameter in varchar2, p_value in varchar2 default 'N')
apex_instance_admin.get_workspace_parameter(p_workspace in varchar2, p_parameter in varchar2) return varchar2
apex_instance_admin.set_workspace_parameter(p_workspace in varchar2, p_parameter in varchar2, p_value in varchar2)

This example runs as SYS and needs no APEX session.

Example:

begin
    -- instance settings (the Administration Services pages), for a user with APEX_ADMINISTRATOR_ROLE
    for p in (select column_value as name from table(apex_t_varchar2(
                  'SMTP_HOST_ADDRESS', 'SMTP_HOST_PORT', 'MAX_SESSION_LENGTH_SEC', 'ACCOUNT_LIFETIME_DAYS',
                  'ALLOW_PUBLIC_FILE_UPLOAD', 'WORKSPACE_PROVISION_DEMO_OBJECTS'))) loop
        dbms_output.put_line(rpad(p.name, 33) || nvl(apex_instance_admin.get_parameter(p.name), '(null)'));
    end loop;

    dbms_output.put_line('schemas of APEXBOOK: ' || apex_instance_admin.get_schemas(p_workspace => 'APEXBOOK'));
    dbms_output.put_line('MAX_SESSION_IDLE_SEC of APEXBOOK: '
        || nvl(apex_instance_admin.get_workspace_parameter(p_workspace => 'APEXBOOK', p_parameter => 'MAX_SESSION_IDLE_SEC'), '(instance default)'));
    dbms_output.put_line('database signature valid: '
        || case when apex_instance_admin.is_db_signature_valid then 'yes' else 'no' end);
end;
/

Output:

SMTP_HOST_ADDRESS                localhost
SMTP_HOST_PORT                   25
MAX_SESSION_LENGTH_SEC           2592000
ACCOUNT_LIFETIME_DAYS            (null)
ALLOW_PUBLIC_FILE_UPLOAD         N
WORKSPACE_PROVISION_DEMO_OBJECTS N
schemas of APEXBOOK: ORBIT
MAX_SESSION_IDLE_SEC of APEXBOOK: (instance default)
database signature valid: yes

A parameter that was never set returns null, as ACCOUNT_LIFETIME_DAYS did, and a workspace parameter that is not overridden returns null too, which means the instance value applies. MAX_SESSION_LENGTH_SEC of 2592000 seconds is 30 days.

The Other Subprograms of APEX_INSTANCE_ADMIN

CREATE_OR_UPDATE_ADMIN_USER creates or resets an instance administrator from PL/SQL. If you are locked out, the article on two ways to unlock the Oracle APEX admin account shows other ways back in, including the apxchpwd.sql script. Extension workspaces are explained in the guide to APEX_EXPORT and APEX_APPLICATION_INSTALL.

Instance Debugging: APEX_INSTANCE_DEBUG

New in 26.1, for administrators. ENABLE turns on debugging for every request of the instance, which helps with problems you cannot reproduce with DEBUG=YES in your own session. DISABLE turns it off and IS_ENABLED reports it. LIST_PAGE_VIEWS, LIST_MESSAGES(p_page_view_id), and LIST_ACTIVITY print the recent debugged page views, the messages of one page view, and the activity log, as text for SQL*Plus.

This example runs as SYS and needs no APEX session.

Example:

begin
    -- instance-wide debugging, for administrators: is it on, and what did debugged requests log?
    dbms_output.put_line('instance debug: ' || case when apex_instance_debug.is_enabled then 'on' else 'off' end);
    apex_instance_debug.list_page_views(p_max_rows => 3);
end;
/

Output:

instance debug: off
PAGE VIEW ID STARTED  SECS LVL COUNT PATH INFO APP:PAGE    SESSION ID WORKSPACE USER
------------ -------- ---- --- ----- --------- -------- ------------- --------- -----
       66553 11:54:04 0.00 ERR     3 SQL*Plus/                      0 Unknown
       66554 11:55:01 0.00 WRN     1                                0 Unknown
       66555 11:57:25 0.00 ERR     3 SQL*Plus/ 200:1    2639588629050 APEXBOOK  ADMIN

Even with instance debugging off, the list showed recent page views, each with its debug level (ERR or WRN), app and page, and user. Pass a page view ID from the first column to LIST_MESSAGES to see what it logged. Debugging a single session is covered in the guide to APEX_ERROR, APEX_DEBUG, and APEX_LANG.

Applications in Production: APEX_APPLICATION_ADMIN

APEX_APPLICATION_ADMIN changes the attributes of an installed application without App Builder, which production instances often do not have. Each SET_ procedure has a GET_ function. They take the application ID, and outside APEX they need the workspace set with apex_util.set_workspace.

This example needs no APEX session. It takes application 200 offline for maintenance, shows a banner, and then puts everything back.

Example:

begin
    apex_util.set_workspace('APEXBOOK');
    dbms_output.put_line('name:    ' || apex_application_admin.get_application_name(200)
                         || ', alias ' || apex_application_admin.get_application_alias(200)
                         || ', version ' || apex_application_admin.get_application_version(200));
    dbms_output.put_line('schema:  ' || apex_application_admin.get_parsing_schema(200)
                         || ', build ' || apex_application_admin.get_build_status(200)
                         || ', auth ' || apex_application_admin.get_authentication_scheme(200));
    dbms_output.put_line('status:  ' || apex_application_admin.get_application_status(200));

    -- take the application offline for maintenance: only ADMIN may still use it
    apex_application_admin.set_application_status(
        p_application_id     => 200,
        p_application_status => 'RESTRICTED_ACCESS',
        p_allowed_users_list => apex_t_varchar2('ADMIN'),
        p_message            => 'The API Lab is being updated. Back at 14:00.');
    apex_application_admin.set_global_notification(200, 'Maintenance today from 13:00 to 14:00.');
    dbms_output.put_line('now:     ' || apex_application_admin.get_application_status(200)
                         || ' - ' || apex_application_admin.get_global_notification(200));

    apex_application_admin.set_application_status(p_application_id => 200, p_application_status => 'AVAILABLE_W_EDIT_LINK');
    apex_application_admin.set_global_notification(200, null);
    apex_application_admin.set_application_version(200, apex_application_admin.get_application_version(200));
    dbms_output.put_line('back to: ' || apex_application_admin.get_application_status(200));
end;
/

Output:

name:    API Lab, alias API-LAB, version Release 1.0
schema:  ORBIT, build RUN_AND_BUILD, auth Open Door (API Lab)
status:  AVAILABLE_W_EDIT_LINK
now:     RESTRICTED_ACCESS - Maintenance today from 13:00 to 14:00.
back to: AVAILABLE_W_EDIT_LINK

RESTRICTED_ACCESS with a list of allowed users is the practical maintenance mode: administrators can still test the new version while everyone else sees the message.

UI Defaults: APEX_UI_DEFAULT_UPDATE

UI defaults are labels, format masks, help texts, and display settings stored per table and column (in SQL Workshop, Utilities, User Interface Defaults), and per column name in the attribute dictionary. Pages and regions created on a table start with them, so setting them once saves repeating the same edits on every new form and report.

The example works on a scratch table. Before it runs, it drops the table if it exists and creates it:

Create the scratch table first:

create table lab_leads (lead_id number primary key, name varchar2(100), city varchar2(60), score number(3), created_on date)

This example needs no APEX session. It removes the UI defaults again at the end.

Example:

begin
    apex_util.set_workspace('APEXBOOK');

    -- create the UI defaults of a table from its columns, then adjust them:
    -- forms and reports created on the table later use these labels, masks, and help texts
    apex_ui_default_update.synch_table(p_table_name => 'LAB_LEADS');
    apex_ui_default_update.upd_form_region_title(p_table_name => 'LAB_LEADS', p_form_region_title => 'Lead');
    apex_ui_default_update.upd_report_region_title(p_table_name => 'LAB_LEADS', p_report_region_title => 'Leads');
    apex_ui_default_update.upd_label(p_table_name => 'LAB_LEADS', p_column_name => 'CREATED_ON', p_label => 'Created');
    apex_ui_default_update.upd_item_format_mask(p_table_name => 'LAB_LEADS', p_column_name => 'CREATED_ON',
                                                p_format_mask => 'DD-MON-YYYY');
    apex_ui_default_update.upd_report_alignment(p_table_name => 'LAB_LEADS', p_column_name => 'SCORE',
                                                p_report_alignment => 'R');
    apex_ui_default_update.upd_item_help(p_table_name => 'LAB_LEADS', p_column_name => 'SCORE',
                                         p_help_text => 'How likely the lead is to buy, from 0 to 100.');
    apex_ui_default_update.upd_display_in_report(p_table_name => 'LAB_LEADS', p_column_name => 'LEAD_ID',
                                                 p_display_in_report => 'N');

    for c in (select column_name, label, display_in_report, mask_form, alignment, help_text
                from apex_ui_defaults_columns where table_name = 'LAB_LEADS' order by display_seq_form) loop
        dbms_output.put_line(rpad(c.column_name, 11) || rpad(c.label, 9) || 'report ' || c.display_in_report
            || nvl2(c.mask_form, ', mask ' || c.mask_form, '') || nvl2(c.alignment, ', align ' || c.alignment, '')
            || nvl2(c.help_text, ', help', ''));
    end loop;

    apex_ui_default_update.del_table(p_table_name => 'LAB_LEADS');     -- remove them again
end;
/

Output:

LEAD_ID    Lead ID  report N, align R
NAME       Name     report Y, align L
CITY       City     report Y, align L
SCORE      Score    report Y, align R, help
CREATED_ON Created  report Y, mask DD-MON-YYYY, align L

SYNCH_TABLE turned column names into readable labels, such as Lead ID, and aligned the numeric columns to the right. The UPD_ calls then set the custom label, mask, help text, and report visibility that the next form or report on LAB_LEADS will pick up.

Describing Tables for LLMs: APEX_DB_DICTIONARY

New in 26.1. APEX_DB_DICTIONARY describes tables and views in compact text, either Markdown (apex_db_dictionary.c_markdown, the default) or plain text (c_plain), with columns, keys, constraints, indexes, comments, annotations, and domains. APEX's own AI features put this text into prompts so a model can write SQL for your schema, and you can do the same in your own prompts to APEX_AI, as shown in the guide to APEX_AI and APEX_SEARCH.

This example needs no APEX session.

Example:

begin
    -- descriptions of tables for a language model (Markdown by default, or plain text)
    dbms_output.put_line('supported: ' || case when apex_db_dictionary.is_supported then 'yes' else 'no' end);
    dbms_output.put_line('tables: ' || apex_string.join(apex_db_dictionary.get_tables_array(p_regex => '^ORB_(ORDERS|CUSTOMERS)$'), ', '));
    dbms_output.put_line('primary key of ORB_ORDER_ITEMS: ' || apex_db_dictionary.get_primary_key_columns(p_table => 'ORB_ORDER_ITEMS'));
    dbms_output.put_line(apex_db_dictionary.get_table_info(p_table_names => 'ORB_CATEGORIES',
                                                            p_include_indexes => false));
end;
/

Output:

supported: yes
tables: "ORBIT"."ORB_CUSTOMERS", "ORBIT"."ORB_ORDERS"
primary key of ORB_ORDER_ITEMS: ORDER_ITEM_ID
# Table: ORB_CATEGORIES

## Columns:
  - CATEGORY_ID - NUMBER NOT NULL [pk]
  - PARENT_CATEGORY_ID - NUMBER [fk]
  - CATEGORY_NAME - VARCHAR2(100) NOT NULL [uk]
  - DESCRIPTION - VARCHAR2(400)
  - DISPLAY_ORDER - NUMBER(3)

## Constraints:
  - ORB_CATEGORIES_PK - PRIMARY KEY (CATEGORY_ID)
  - ORB_CATEGORIES_NAME_UK - UNIQUE (CATEGORY_NAME)
  - ORB_CATEGORIES_PARENT_FK - FOREIGN KEY (PARENT_CATEGORY_ID) -> ORB_CATEGORIES(CATEGORY_ID)

The description marks keys inline with [pk], [fk], and [uk] and spells out the foreign key's target, which is exactly what a model needs to write correct joins. GET_TABLES_SUMMARY gives the shorter overview, here in plain text:

Example:

begin
    dbms_output.put_line(apex_db_dictionary.get_tables_summary(p_regex => '^ORB_(STORES|WAREHOUSES|SUPPLIERS)$',
                                                                p_format => apex_db_dictionary.c_plain));
end;
/

Output:

ORBIT Tables and Views (/^ORB_(STORES|WAREHOUSES|SUPPLIERS)$/)

Tables:
  ORB_STORES
  ORB_SUPPLIERS
  ORB_WAREHOUSES

A common pattern is to send the summary first so the model can pick the relevant tables, then send the full GET_TABLE_INFO for just those tables, which keeps prompts short.

Test Data: APEX_DG_DATA_GEN

The Data Generator (SQL Workshop, Utilities) creates realistic test data from a blueprint: tables, row counts, and a data source for each column. A data source can be a built-in such as person.first_name or location.city (the APEX_DG_BUILTINS view lists 193 of them), a sequence, a formula, weighted inline values such as NEW,60;WON,10, another table, or a column of another table in the blueprint.

This example needs a session of application 200, page 1. Before it runs, it removes any ORBIT_LEADS blueprint left over from an earlier run, and it removes the blueprint again at the end.

Example:

declare
    l_bp     number;
    l_output clob;
    l_ext    varchar2(20);
    l_mime   varchar2(200);
    l_errors clob;
begin
    -- a blueprint: a table LEADS with generated names, cities, dates, and weighted statuses
    apex_dg_data_gen.add_blueprint(p_name => 'ORBIT_LEADS', p_default_schema => 'ORBIT', p_blueprint_id => l_bp);
    apex_dg_data_gen.add_table(p_blueprint => 'ORBIT_LEADS', p_sequence => 1, p_table_name => 'LEADS',
                               p_rows => 4, p_table_id => l_bp);
    apex_dg_data_gen.add_column(p_blueprint => 'ORBIT_LEADS', p_sequence => 1, p_table_name => 'LEADS',
        p_column_name => 'LEAD_ID', p_data_source_type => 'SEQUENCE', p_column_id => l_bp);
    apex_dg_data_gen.add_column(p_blueprint => 'ORBIT_LEADS', p_sequence => 2, p_table_name => 'LEADS',
        p_column_name => 'NAME', p_data_source_type => 'BUILTIN', p_data_source => 'person.forward_name', p_column_id => l_bp);
    apex_dg_data_gen.add_column(p_blueprint => 'ORBIT_LEADS', p_sequence => 3, p_table_name => 'LEADS',
        p_column_name => 'CITY', p_data_source_type => 'BUILTIN', p_data_source => 'location.city', p_column_id => l_bp);
    apex_dg_data_gen.add_column(p_blueprint => 'ORBIT_LEADS', p_sequence => 4, p_table_name => 'LEADS',
        p_column_name => 'STATUS', p_data_source_type => 'INLINE', p_data_source => 'NEW,60;CONTACTED,30;WON,10',
        p_column_id => l_bp);
    apex_dg_data_gen.add_column(p_blueprint => 'ORBIT_LEADS', p_sequence => 5, p_table_name => 'LEADS',
        p_column_name => 'CREATED_ON', p_data_source_type => 'BUILTIN', p_data_source => 'date.date_between_min_and_max',
        p_min_date_value => date '2026-01-01', p_max_date_value => date '2026-06-30', p_format_mask => 'YYYY-MM-DD',
        p_column_id => l_bp);

    apex_dg_data_gen.generate_data(p_blueprint => 'ORBIT_LEADS', p_format => 'CSV',
                                   p_output => l_output, p_file_ext => l_ext, p_mime_type => l_mime, p_errors => l_errors);
    dbms_output.put_line('leads.' || l_ext || ' (' || l_mime || '):');
    dbms_output.put_line(l_output);

    dbms_output.put_line('examples of person.first_name: '
        || apex_string.join(apex_dg_data_gen.get_example(p_friendly_name => 'person.first_name', p_rows => 4), ', '));
    dbms_output.put_line('blueprint JSON: ' || dbms_lob.getlength(apex_dg_data_gen.export_blueprint(p_name => 'ORBIT_LEADS'))
                         || ' characters');
    apex_dg_data_gen.remove_blueprint(p_name => 'ORBIT_LEADS');
end;
/

Output:

leads.csv (text/csv):
LEAD_ID,NAME,CITY,STATUS,CREATED_ON
1,Norberto Febre,Brooklyn,WON,2026-04-23
2,Novella Shackleton,Staten Island,NEW,2026-02-07
3,Norene Pensky,Cherry Hill,NEW,2026-02-09
4,Numbers Hitzler,Bronx,NEW,2026-01-19

examples of person.first_name: Colby, Delia, Marvella, Monty
blueprint JSON: 5234 characters

The values are random, so your names, cities, and dates will differ. The dates stayed within the range given, and the statuses follow the 60/30/10 weights only on average, which is why four rows can include a WON. For volume testing, raise p_rows and use the INSERT INTO format to fill the table directly.

Conclusion

APEX_INSTANCE_ADMIN reads and sets instance and workspace parameters and manages workspaces, schemas, logs, and cleanup, while APEX_INSTANCE_DEBUG debugs the whole instance. APEX_APPLICATION_ADMIN changes an installed application's status, banner, build options, and settings in production. APEX_UI_DEFAULT_UPDATE maintains the UI defaults and attribute dictionary that new pages inherit. APEX_DB_DICTIONARY describes tables for language models, and APEX_DG_DATA_GEN generates test data from blueprints.