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.
