ADDING FIELDS TO THE APPLICATION

 In Oracle APEX, “adding fields to the application” is really two related activities that must stay in sync:

Adding (or exposing) the data attribute in your data layer (a table column, a view column, a REST attribute, or a PL/SQL record field).

Adding (or updating) the APEX UI and logic that reads/writes that attribute (page items, region columns, validations, processes, computations, and any downstream integrations).

A key idea to internalize is that APEX does not store your business data in page items. Page items are a UI/state mechanism. The “field” you add becomes durable only when it is persisted (typically to a table) by a DML process such as Automatic Row Processing (DML), an Interactive Grid DML handler, or your own PL/SQL code.

When people get stuck, it is usually because they add the column in the database but do not update the APEX region/page item sources and DML processes, or they add UI items without ensuring the data source and DML layer know what to do with the new attribute.

The most common places you “add fields” in APEX are:

A form page (Form region + page items + Automatic Row Processing).

An Interactive Grid (region columns + DML settings).

A report (Classic Report / Interactive Report / Cards) where you expose the column for display and filtering.

A data source that is not a table (views, REST Data Sources, PL/SQL function returning SQL query), where you must explicitly expose the attribute in the query and map it into UI items.

A practical way to approach this is to treat each new field as a small lifecycle: define → expose → validate → persist → secure → test → deploy.

Start by being explicit about what kind of “field” you are adding:

A persisted attribute: it needs a database column (or an attribute in a REST backend) and a DML pathway.

A derived attribute: it is computed from other data, may not be stored, and often should be calculated in SQL, a view, or a virtual column rather than saved.

A transient attribute: used for UI-only behavior (search terms, checkboxes, switches). It may never be stored and often lives only in session state.

If your target is a standard APEX table-backed form, the fastest and most reliable path is: add the column in the table, then let APEX generate or add the corresponding page item and wire it into Automatic Row Processing.

Example of adding a new database column, then adding it to a form page

Assume you have a table used by a form page:

CREATE TABLE customers (

  customer_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

  customer_name VARCHAR2(200) NOT NULL,

  email         VARCHAR2(320),

  created_on    DATE DEFAULT SYSDATE NOT NULL

);

You want to add a new field, phone_number, and capture it in the application.

Step 1: Add the column to the database

ALTER TABLE customers

  ADD (phone_number VARCHAR2(30));

If you need constraints (recommended whenever the business rules are stable), add them now so the database enforces the rule even outside APEX:

ALTER TABLE customers

  ADD CONSTRAINT customers_phone_chk

  CHECK (phone_number IS NULL OR REGEXP_LIKE(phone_number, '^[0-9+(). -]{7,30}$'));

Step 2: Ensure your form’s source query includes the new column

If your form region is based on the table directly (typical “Form” region), APEX already knows the table structure. If your form region is based on a SQL query or a view, you must include the column:

SELECT

  customer_id,

  customer_name,

  email,

  phone_number,

  created_on

FROM customers

WHERE customer_id = :P10_CUSTOMER_ID;

If you forget this step for query-based forms, the UI item may exist but never populate (because it is not selected), and/or never save (because the DML mapping cannot see it).

Step 3: Add the page item and map it to the column

In Page Designer on the form page:

Confirm the Form region’s source is the CUSTOMERS table (or your query includes PHONE_NUMBER).

Add an item for the column. In APEX, you can:

Use the form region’s capability to add a new item (often exposed as “Add Field” or “Create Item” for missing columns), or

Create a new page item manually and set its Source.

For a manual item, you would create P10_PHONE_NUMBER and configure:

Type: Text Field (or Tel, depending on your UI preference).

Source:

Type: Database Column

Column: PHONE_NUMBER

Session State:

Ensure “Maintain Session State” is enabled (default for most items).

Step 4: Ensure the page has a DML process that includes the new item

On classic APEX forms, you typically have:

Process: Automatic Row Processing (DML)

Associated with the Form region

“Items to Submit” includes the relevant items (APEX often manages this automatically when items are correctly sourced to columns, but you must verify if you have custom processes).

If you are using Automatic Row Processing, and the item source is correctly set to PHONE_NUMBER, the DML process will update that column without you writing SQL.

If you are using your own PL/SQL process instead of ARP, you must update it:

UPDATE customers

   SET customer_name = :P10_CUSTOMER_NAME,

       email         = :P10_EMAIL,

       phone_number  = :P10_PHONE_NUMBER

 WHERE customer_id   = :P10_CUSTOMER_ID;

And for inserts:

INSERT INTO customers (customer_name, email, phone_number)

VALUES (:P10_CUSTOMER_NAME, :P10_EMAIL, :P10_PHONE_NUMBER)

RETURNING customer_id INTO :P10_CUSTOMER_ID;

Step 5: Add validations where they belong

Use a layered strategy:

Database constraints for invariants (format rules, NOT NULL, foreign keys, uniqueness).

APEX validations for user-friendly messaging and UI flow.

Example APEX validation approach:

Validation type: PL/SQL Expression

Expression:

:P10_PHONE_NUMBER IS NULL

OR REGEXP_LIKE(:P10_PHONE_NUMBER, '^[0-9+(). -]{7,30}$')

Error message: “Enter a valid phone number (7–30 characters; digits and + ( ) . - allowed).”

Validation scope: “Page and Column” is fine for forms; if you want it to run only when the item is not null, include that logic as shown.

Step 6: Decide how it displays and behaves

Common enhancements:

Format mask: usually not ideal for phone numbers globally; better to store normalized digits plus country code, then format on display.

Help text: explain expectations (“Include country code for non-US numbers.”).

Conditional display: show only for certain customer types.

Read-only rules: make it read-only after verification, etc.

Example: make it required only for certain cases using Server-side Condition + Validation:

Condition: :P10_CUSTOMER_TYPE = 'BUSINESS'

Validation: :P10_PHONE_NUMBER IS NOT NULL

Adding fields to an Interactive Grid (IG)

IGs are column-driven. Adding a new database column does not automatically show up until you add/configure the grid column.

Step 1: Add the database column

ALTER TABLE customers ADD (phone_number VARCHAR2(30));

Step 2: Confirm the grid query includes the column

If the IG is “Table” based, APEX can usually see it. If it is based on a custom SQL query, add the column:

SELECT customer_id, customer_name, email, phone_number

FROM customers

Step 3: Add the IG column in Page Designer

In Page Designer:

Select the IG region.

Under Columns:

Create a new column for PHONE_NUMBER (or refresh/add columns from the query, depending on how the IG was created).

Configure:

Type: Text Field

Heading: Phone

Read Only: No (if you want editable)

Validation: as needed

Step 4: Ensure IG DML knows about it

For table-based IGs with APEX-managed DML:

Verify the IG is editable and has Primary Key configured (often CUSTOMER_ID).

Make sure the PHONE_NUMBER column is not marked “Query Only” if you want edits to persist.

If you use custom DML (PL/SQL), update your handler to include the new column.

Adding fields to a report (Classic Report / Interactive Report)

A report field is primarily a SELECT-list attribute plus column configuration.

Step 1: Add/Expose the column in the SQL query or underlying view

SELECT customer_id, customer_name, email, phone_number

FROM customers

Step 2: Add the column to the report attributes

Classic Report: add the column in the query and then configure the column display attributes.

Interactive Report: add it in the query; then it becomes available as a column that users can show/hide, filter, and sort (subject to authorization and column settings).

Step 3: Secure it if needed

If PHONE_NUMBER is sensitive, you may:

Restrict the column with Authorization Scheme (so it does not render for unauthorized users).

Mask it in SQL for unauthorized users:

SELECT

  customer_id,

  customer_name,

  email,

  CASE

    WHEN apex_authorization.is_authorized('CAN_VIEW_PHONE') = 1

    THEN phone_number

    ELSE '***-***-****'

  END AS phone_number

FROM customers

Adding fields when your data source is not a table

This is where many APEX applications become “mysteriously” out of sync because developers assume APEX will discover the new attribute automatically.

If your region source is:

A view: add the column to the view definition.

A SQL query: add the column to the SELECT list and ensure the alias matches what the UI expects.

A REST Data Source: add the attribute in the REST Data Source definition and map it to columns/items.

A PL/SQL function returning SQL query: update the SQL string returned.

Example: view-backed form

CREATE OR REPLACE VIEW v_customers AS

SELECT customer_id, customer_name, email, phone_number

FROM customers;

If your form is based on V_CUSTOMERS, you must alter the view to include PHONE_NUMBER (and ensure the DML layer can update the base table, typically via INSTEAD OF triggers or switching the form to table-based DML). In many cases, the best practice is: use the view for reporting and the table for editing, unless you have a clear update strategy.

Example: REST Data Source attribute

Add the new JSON attribute (for example phoneNumber) to the REST Data Source profile.

Map it to a column/item.

Ensure your POST/PATCH operation includes it when sending updates.

Handling “Add Field” in terms of APEX metadata and session state

Every page item is metadata that APEX stores and uses to generate runtime behavior. The item’s “Source” determines how it is populated (for example from a database column), and “Session State” determines whether its value is retained across page requests.

Practical implications:

If an item is not submitted, its value may never reach your DML process. Ensure the relevant items are submitted on save. APEX’s standard Form + ARP patterns handle this well when items are correctly configured.

If you use Dynamic Actions with “Set Value” or JavaScript updates, you may need to ensure the item is submitted (or explicitly sent via AJAX) before DML executes.

If you compute a derived value (for example, normalize the phone number), decide whether to do it:

In the database (preferred for consistency), or

In an APEX process (acceptable if you standardize it and test it thoroughly).

Example normalization before save (APEX process “Before Submit”)

IF :P10_PHONE_NUMBER IS NOT NULL THEN

  :P10_PHONE_NUMBER := REGEXP_REPLACE(:P10_PHONE_NUMBER, '[^0-9+]', '');

END IF;

Adding fields safely across DEV/UAT/PROD

Because “adding a field” often includes both database DDL and APEX metadata changes, treat it as a coordinated deployment:

Database change script (DDL): ALTER TABLE ... ADD ...

APEX app export/import (or deployment package) including page item/region changes

Post-deploy verification:

Can the page render without errors?

Can you create and update a record and see the value persisted?

Does security/authorization behave as expected?

Do reports/IGs show the column correctly?

Common failure patterns and how to avoid them

Column exists in DB, but item is blank: your form/report query does not select the column, or the item source is not mapped to the column.

Item shows value, but save does nothing: the DML layer does not include the item (custom DML not updated, IG column is query-only, or item not submitted).

Works in DEV but fails in UAT/PROD: database DDL not deployed, or view/trigger/package dependencies differ.

Validations inconsistent: only APEX validations exist, but API loads bypass APEX; add database constraints for core rules.

Performance degradation: adding many new fields to large reports increases data transfer and rendering. Only select columns you need for the page, and consider lazy-loading or drill-down patterns for “detail” attributes.

A compact checklist you can reuse for every new field

Add or expose the attribute in the data source (table/view/query/REST).

Add the field to the UI (page item or region column) and map its Source.

Ensure the DML path persists it (ARP/IG DML/custom PL/SQL).

Add validations (DB constraints for invariants; APEX validations for UX).

Apply security (authorization, masking, read-only rules).

Test create/update/reporting end-to-end.

Deploy database + APEX metadata together, then re-test.

 

 

Manual example for adding fields in the Page

1.      Right click on “Body”

2.      Select “Create region”

Next:

1.      Drag a “text Field”

2.      …Into the “Region Body”

Now you should see your new control in two places (on the left and the right), both represents the same region. FYI: regions are somewhat synonymous to “DIV” in an HTML web page.