UI Defaults

 In Oracle APEX, User Interface (UI) Defaults are a set of metadata-driven, table- and column-scoped attributes that APEX consults when it generates pages and regions from database objects. The purpose is straightforward: you define how a table and its columns should look and behave in the user interface one time, and APEX reuses those decisions whenever you create (or regenerate) Forms, Interactive Reports, Classic Reports, and related components that are based on those objects. In other words, UI Defaults are a “design contract” between your data model and your APEX page generator.

UI Defaults typically control the “first draft” decisions APEX makes for you during generation, such as the label text, item type selection, formatting, alignment, and other presentation and behavior settings. They also serve as a consistency mechanism across a workspace or a team: two developers generating a form from the same table will produce pages that look and behave consistently if UI Defaults are established and maintained.

At a conceptual level, UI Defaults exist because APEX generation is template-based and repeatable, but without a standard configuration layer you would constantly re-apply the same design decisions. UI Defaults reduce that repetition by capturing conventions such as “all currency fields are right-aligned with a currency format mask,” “email columns should use an email item,” “status columns should be radio groups,” and “audit columns should be hidden in reports.”

When you generate a page, APEX inspects the underlying table (or view) and its columns, merges information from the data dictionary (data type, nullability, constraints, comments), and then applies your UI Defaults to decide what the UI components should be. A simple example is an EMAIL column. If you define its UI Default to use an “Email” item type rather than a generic text field, then every generated form that includes that column will use an email-aware control by default. The result is not merely cosmetic; it affects client-side behavior, validation expectations, and the overall correctness of the UI for that data type.

The value proposition is practical and immediate. APEX development time is reduced because you stop reconfiguring the same controls repeatedly across pages and applications. Consistency improves because labels, masks, alignments, and control choices follow a repeatable standard, which makes your application feel cohesive to users and makes it easier for developers to understand and extend. Maintainability improves because changes are localized: if the organization decides to change a date display standard or currency format, you can update the defaults and have a predictable baseline for newly generated components. It also encourages best practices by nudging developers toward appropriate control types and formatting rather than leaving everything as a generic text item.

From a storage perspective, UI Defaults are associated with database objects: they are defined for tables and for columns. That distinction matters. Table-level defaults establish general behaviors for that table’s usage in APEX generation, while column-level defaults encode specific behaviors for each attribute. Column-level defaults are usually where most of the value lives because they capture the semantics of each field: formatting, item types, and display decisions that are unique to the attribute.

A common example is a SALARY column. Without defaults, it might appear as a plain number field with minimal formatting. With UI Defaults, you can define it as a number item, attach a currency-style format mask, enforce a minimum value rule, and ensure right alignment in reports. Then every time you generate a form or report that includes SALARY, APEX begins with those decisions already applied, rather than forcing you to re-implement them manually.

A representative configuration for such a column would look like this in intent:

• Item type is numeric (not a text field).

• Format mask is currency-oriented (for example $999,999.99 or a locale-appropriate mask).

• Validation expectation is that values must be greater than zero (with the database enforcing the invariant if required).

The operational reality is that UI Defaults are not magic; they are defaults. They influence generation and initial configuration. After generation, you can override any specific component in the page designer. This balance is intentional: UI Defaults provide standards and acceleration, while the page-level configuration still allows for context-specific customization.

Configuring UI Defaults is typically performed through SQL Workshop tooling or in workflows that expose the UI Defaults editor for a table and its columns. The practical pattern is consistent regardless of the path you use: you pick the table, open its UI Defaults configuration, and then define defaults per column.

A typical workflow in SQL Workshop is:

• Navigate to SQL Workshop and open the Object Browser.

• Select the target table.

• Open the UI Defaults section for that table.

• For each column, define the default item type and relevant display and formatting behaviors.

• Apply changes.

The types of settings you routinely define at the column level include:

• Item type choice (text field, textarea, email, date picker, number field, switch, checkbox, select list, popup LOV).

• Display characteristics (read-only behavior, label text, tooltip, alignment).

• Formatting (format masks for numbers and dates; currency and precision rules).

• Visibility defaults for reporting (hide internal IDs, hide audit fields).

• Validation expectations (required vs optional, basic patterns).

Once UI Defaults exist for a table, APEX’s page creation wizards can apply them automatically during page generation. The developer flow is simple: you create a page, choose a form or report, select the table, and APEX applies the table/column UI Defaults as the initial configuration. This is where the time savings show up: generation becomes less about reconfiguring every item and more about implementing page-specific logic, security, and user experience.

The strongest impact of UI Defaults occurs in forms, because forms involve both display and data entry controls. APEX must decide what item control to use for each column, and UI Defaults provide that decision. The most practical way to think about this is as a mapping layer between database data types (and column semantics) and UI controls.

Common mappings that teams standardize include:

• Text columns (VARCHAR2):

  • Text Field for short strings

  • Textarea for longer free-form text

  • Email item for email-like semantics

• Numeric columns (NUMBER):

  • Number Field with explicit mask and precision behavior

  • Slider for bounded numeric inputs when appropriate

  • Currency masks for monetary values

• Date columns (DATE or timestamp types):

  • Date Picker control

  • Standard display format (for example DD-MON-YYYY, or an organizational standard)

• Large text columns (CLOB):

  • Textarea or Rich Text Editor based on whether HTML formatting is allowed

• Boolean-like columns (Y/N, 1/0, constrained values):

  • Yes/No Switch

  • Checkbox

  • Radio group when multiple explicit states exist

• Foreign key columns:

  • Select List or Popup LOV (with a display value from a referenced table)

A concrete example many applications use is a STATUS column that stores a constrained set of values such as Active and Inactive. Left to defaults, APEX might treat it as a text item. With UI Defaults, you can configure it as a radio group (or select list) so the user chooses from valid values rather than typing. This improves data integrity and user experience simultaneously. If the database enforces valid values via a constraint, you get a full stack of correctness: the UI prevents invalid input, and the database rejects it if it occurs by any path.

UI Defaults also matter for reporting, because reports emphasize readability and consistency. In report regions, defaults can influence:

• Column alignment (left for text, right for numbers, center for codes where appropriate).

• Display type (plain text vs link vs HTML expression).

• Format masks (currency, number precision, standardized date formats).

• Default visibility (hide surrogate keys, hide audit fields, hide internal columns).

• Summarization behaviors where applicable (such as showing a SUM for numeric columns).

A typical reporting example is a PRICE column. With UI Defaults, you can define it so that generated reports automatically right-align it, apply a currency format mask, and optionally define a default summarization approach. This means that every new report region created from the same table begins with the correct display conventions rather than forcing a developer to remember to “fix” columns after generation.

Overriding UI Defaults is a normal part of development and does not defeat the value of defaults. The intended model is:

• UI Defaults define the standard baseline.

• Page-level configuration refines the baseline for the specific use case.

For example, a column might generally be editable, but on a specific page you want it read-only due to workflow state or authorization rules. You would keep the UI Default as editable, then override the item on the relevant page with read-only conditions. Similarly, you might keep a general currency format mask in UI Defaults, but on a particular report you may want abbreviated notation or different decimal precision; that becomes a report-level override. This approach ensures you do not build one-off “standards” into pages that should have been standardized globally, while still allowing legitimate local exceptions.

Exporting and importing UI Defaults is a critical capability when you operate multiple environments (development, test, production) or multiple workspaces with shared standards. The general approach is to export the UI Defaults definitions as SQL and apply them in the target environment so the same table/column conventions are enforced consistently. This becomes especially important in regulated or enterprise contexts where UI consistency and validation standards are part of delivery expectations. The operational pattern is:

• Export UI Defaults to a SQL script from the source environment.

• Execute that script in the target environment to apply the same UI Defaults.

This enables a disciplined promotion path where UI behavior remains consistent across environments, and where newly generated pages in each environment inherit the same baseline configuration.

Best practices for UI Defaults are mostly about timing, discipline, and scope. Establish UI Defaults before you start mass page generation so you capture maximum benefit. Standardize format masks for dates, numbers, and currency early, and treat them as organization-level conventions rather than page-level preferences. Enforce critical invariants in the database (constraints, foreign keys, check constraints) and use UI Defaults to ensure the UI respects those invariants with user-friendly controls and messaging. Review UI Defaults periodically as the application evolves; defaults that made sense early might need refinement once you learn more about real user behavior. Use meaningful labels and tooltips at the default level so generated pages begin with usable semantics rather than requiring manual polish.

A practical way to adopt UI Defaults on an existing application is incremental: pick one high-traffic table, define strong UI Defaults for it, then regenerate or create new pages that use that table to see the benefit immediately. As confidence grows, extend UI Defaults to other core tables and begin treating them as part of your application’s architectural standards. Over time, this makes APEX generation a reliable accelerator rather than a one-time scaffold that developers must heavily rewrite.

Examples of Code:

Example 1: Add database comments to support consistent labeling intentions

While UI Defaults are configured in APEX tooling, database comments often serve as the source of truth for column meaning and can be aligned with UI Defaults decisions.

COMMENT ON COLUMN customers.email IS 'Customer email address used for notifications';
COMMENT ON COLUMN customers.salary IS 'Annual salary in USD, must be greater than zero';
COMMENT ON COLUMN customers.status IS 'Account status: Active or Inactive';

Example 2: Constrain values so UI Defaults can safely use select/radio controls

ALTER TABLE customers
  ADD CONSTRAINT customers_status_chk
  CHECK (status IN ('Active','Inactive'));

Example 3: Numeric constraint that aligns with a “minimum > 0” UI expectation

ALTER TABLE employees
  ADD CONSTRAINT employees_salary_chk
  CHECK (salary > 0);

Example 4: Foreign key that supports a Popup LOV default

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id);

With this constraint in place, the UI Default for ORDERS.CUSTOMER_ID can be a Popup LOV that displays CUSTOMER_NAME while returning CUSTOMER_ID, and APEX-generated forms and grids will start from the correct control type instead of a raw numeric input.

UI Defaults are one of the most practical “leverage features” in Oracle APEX because they convert repeated manual configuration into a reusable standard. When implemented with discipline—especially alongside database constraints and consistent formatting conventions—they become a structural asset: they accelerate delivery, improve consistency, and reduce long-term maintenance effort by keeping the generated UI aligned with the underlying data model’s intent.

CHANGING THE NAME OF THE FIELD

 In Oracle APEX, “changing the name of the field” can mean three different things, and the correct method depends entirely on which layer you are renaming:

The database attribute name (table column / view column).

The APEX UI label and presentation name (what users see on the page).

The APEX component identifier (page item name like P10_PHONE_NUMBER, report column identifier, IG column name), including any references to it in validations, processes, dynamic actions, and code.

APEX applications are metadata-driven, so “names” are not cosmetic; they become dependencies across SQL, PL/SQL, dynamic actions, authorization rules, and even JavaScript. A safe rename requires understanding what is being renamed and what must be updated to keep those dependencies consistent.

If your goal is only to change what the user sees (for example, rename “Phone Number” to “Mobile”), you do not rename the database column or the item name. You change the label (and optionally help text). If your goal is to align the schema (for example, PHONE → PHONE_NUMBER), you must consider database-level renames plus APEX mappings. If your goal is to standardize APEX naming (for example, P10_PHONE → P10_PHONE_NUMBER), you must update every reference to the old item name across the application.

Changing what the user sees (most common and lowest risk)

This is a UI-only rename. It is the preferred approach when the underlying data attribute stays the same.

Where you do it:

Page item: Label, Heading, and optionally Placeholder/Help Text.

Report/IG column: Heading and column label settings.

LOV display: display value label in the LOV query if needed.

Example: rename a page item label without changing anything else

Assume you have a form item P10_PHONE_NUMBER sourced from column PHONE_NUMBER.

In Page Designer for P10_PHONE_NUMBER, adjust:

Label: Mobile

(Optional) Heading: Mobile

(Optional) Help Text: Enter a mobile number including country code if outside the US.

Nothing else changes. The database column remains PHONE_NUMBER. The item name remains P10_PHONE_NUMBER. Validations and processes continue to work unchanged.

Example: rename an Interactive Grid column heading

In the IG region’s Columns, select PHONE_NUMBER column and set:

Heading: Mobile

(Optional) Tooltip: Customer mobile number

Again, no dependency breakage because you did not change identifiers.

Changing the database column name (schema rename)

This is a structural rename. It affects every SQL statement, view, trigger, package, and APEX region that references that column. It is appropriate when your schema naming standard requires it or when the column meaning has changed.

Key rule: do not rename the database column unless you are ready to update all dependent objects and redeploy APEX metadata that relies on it.

Oracle database column rename example

You currently have:

Table: CUSTOMERS

Column: PHONE

You want it to become PHONE_NUMBER.

ALTER TABLE customers RENAME COLUMN phone TO phone_number;

What you must update afterward in APEX:

Any region source queries that reference PHONE must be updated to PHONE_NUMBER.

Any form items whose Source is PHONE must be remapped to PHONE_NUMBER.

Any computations/validations/processes containing SQL or PL/SQL referencing PHONE must be updated.

Any views used by reports/forms must be updated and recompiled.

Any packages that reference the old column must be recompiled.

Example: report query before and after

Before:

SELECT customer_id, customer_name, phone

FROM customers;

After:

SELECT customer_id, customer_name, phone_number

FROM customers;

Example: form item Source remap

If you have item P10_PHONE with Source Type “Database Column” and Column PHONE, you must change:

Column: PHONE_NUMBER

If your form is based on a table and uses Automatic Row Processing (DML), APEX can persist the new column as long as the item’s Source points to it. If you use custom DML, you must update the SQL.

Example: custom DML update before and after

Before:

UPDATE customers

   SET phone = :P10_PHONE

 WHERE customer_id = :P10_CUSTOMER_ID;

After:

UPDATE customers

   SET phone_number = :P10_PHONE

 WHERE customer_id = :P10_CUSTOMER_ID;

Notice that you can keep the APEX item name the same (P10_PHONE) even though the database column changed. This is often a practical compromise if the application is large and you want to avoid renaming the item identifier (which has many dependencies).

Renaming the APEX item name (identifier rename)

This is the highest-risk type of rename in APEX because the item name is referenced throughout the application:

Validations (:P10_OLD_NAME)

Processes and computations

Branch conditions

Dynamic actions (client-side and server-side)

Authorization checks that reference item values

JavaScript code ($v('P10_OLD_NAME'), apex.item('P10_OLD_NAME'))

Interactive Grid dynamic actions or JavaScript that refer to column/static IDs

Plugins, templates, and region conditions

APEX does not behave like a “refactoring IDE” across all these references automatically in every case. There are built-in tools to help find references, but you must still verify.

When you should rename an item name:

You have an established naming standard and are early enough in development.

The item name is misleading and causing repeated developer errors.

You are consolidating items and want clarity, and you can test thoroughly.

When you should avoid it:

Mature, production applications unless there is a compelling reason and a strong test plan.

Areas with extensive JavaScript or dynamic actions that reference the item.

Example: rename page item P10_PHONE to P10_PHONE_NUMBER

Approach:

Create the new item with the new name and same source, then migrate references, then remove the old item. This is safer than renaming in-place if you want controlled change and rollback. If you rename in-place, you still must update references, but you have less “dual-run” capability.

Step 1: Create new item P10_PHONE_NUMBER

Source Type: Database Column

Column: PHONE_NUMBER (or PHONE if unchanged)

Label: Phone Number

Step 2: Update processes/validations to use the new item

Before:

IF :P10_PHONE IS NOT NULL THEN

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

END IF;

After:

IF :P10_PHONE_NUMBER IS NOT NULL THEN

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

END IF;

Step 3: Update dynamic actions and JavaScript

Before:

var phone = $v('P10_PHONE');

apex.item('P10_PHONE').setValue(phone.trim());

After:

var phone = $v('P10_PHONE_NUMBER');

apex.item('P10_PHONE_NUMBER').setValue(phone.trim());

Step 4: Ensure submit includes the new item

If you have a dynamic action that submits items (or a process that relies on specific items being submitted), include the new item name. In many standard forms, this is handled implicitly, but any custom “Items to Submit” lists must be updated.

Step 5: Provide a temporary compatibility copy (optional but useful during transition)

If other pages/processes still reference the old item while you are migrating:

Add a computation (Before Submit) to keep them aligned:

:P10_PHONE := :P10_PHONE_NUMBER;

Or the other direction, depending on which is authoritative. This is a transitional tactic, not a permanent design.

Step 6: Remove the old item once you have verified no references remain

Use APEX’s utilities to locate references:

Page Designer search within the page.

Application-wide search (search for P10_PHONE).

Check dynamic actions, validations, processes, shared components.

Changing the name of a report column or IG column identifier vs heading

Report and IG columns effectively have:

The SQL expression / column alias (data identity).

The display heading (user-facing).

Sometimes a “Static ID” or internal identifier used by JavaScript or dynamic actions.

User-facing rename: change the heading.

Developer-facing rename: change the column alias or static ID, but be careful—IG JavaScript and saved reports can depend on it.

Example: rename a SQL alias for clarity

Before:

SELECT phone_number AS phone

FROM customers;

After:

SELECT phone_number AS phone_number

FROM customers;

If an Interactive Report has saved user reports, changing the alias can affect existing saved layouts because column identifiers are tied to the query’s select list. In production systems, prefer leaving the alias stable and changing only the heading unless you have a plan for saved report impact.

Changing the name of an LOV “field” (display/return)

In APEX, LOVs have:

Return value (what is stored in the item).

Display value (what the user sees).

Renaming in this context often means changing labels or what is displayed, not the return value.

Example: employee LOV display label change

Before:

SELECT last_name || ', ' || first_name AS display_value,

       employee_id AS return_value

FROM employees

ORDER BY 1;

After (display shows department too):

SELECT last_name || ', ' || first_name || ' (' || department_name || ')' AS display_value,

       employee_id AS return_value

FROM employees e

JOIN departments d ON d.department_id = e.department_id

ORDER BY 1;

The “name of the field” from the user perspective changed, but the stored value did not.

A disciplined rename checklist (what you actually verify)

What exactly is being renamed: UI label, APEX identifier, database column, SQL alias, IG column, LOV display?

Where is it referenced:

SQL queries (regions, LOVs, validations)

PL/SQL processes (DML, computations)

Dynamic actions (true/false actions, items to submit)

JavaScript and client-side code

Authorization conditions

What downstream artifacts are impacted:

Saved Interactive Reports

Integrations (REST, file exports, external consumers of views)

Audit triggers and replication

Deployment coordination:

Apply database rename first (or together) so the application does not reference a non-existent column.

Import APEX metadata that uses the new mapping.

Recompile invalid objects.

Functional test:

Load existing record, verify field populates.

Update and save, verify persistence.

Create new record, verify insert.

Validate security/masking rules.

Validate exports/reports.

Concrete example: change what users see, keep everything else stable (recommended default)

You have CUSTOMERS.PHONE_NUMBER and item P10_PHONE_NUMBER, and you want the UI to say “Mobile”.

Do not rename the column.

Do not rename the item.

Change item label and report/IG headings.

Update help text and validation message to use the new terminology.

Concrete example: schema rename but keep APEX item stable (practical for large apps)

You rename PHONE → PHONE_NUMBER at the database level:

ALTER TABLE customers RENAME COLUMN phone TO phone_number;

In APEX, keep P10_PHONE to avoid widespread refactoring, but update the item source to PHONE_NUMBER and update DML/process SQL accordingly. This gives you a clean schema without forcing a full APEX identifier refactor.

Concrete example: full refactor (schema + item identifier)

You rename the column and item identifiers:

PHONE → PHONE_NUMBER in the database.

P10_PHONE → P10_PHONE_NUMBER in APEX.

You must update all dependencies and test thoroughly, especially JavaScript and dynamic actions. This is best done early in the application lifecycle or in a controlled release with strong regression testing.

If you tell me which one you mean by “field name” in your application—database column name, item name (like Pxx_SOMETHING), or just the label users see—I can tailor the safest step-by-step approach for that specific scenario and the page type (Form, Interactive Grid, Interactive Report).

 

You can rename a field by  going to the right hand “Page Item” area. In the “Name” field, change the current field name to the new name.

 

 

The field name has now displays the new name given to the control.

 

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.