Mastering Shared Components in Oracle APEX

1. Introduction and Real-World Problem

When developing enterprise-grade systems in Oracle APEX—such as a multi-property hotel management system or a multi-tenant SaaS platform—development teams often hit a productivity wall after the first five or six pages.

Consider a common scenario: you have multiple pages handling operations like booking rooms, assigning housekeeping staff, logging bills, and managing tenant portfolios. On each of these pages, users must select an active hotel property, view current room rates, and display tenant-specific branding.

A junior or rushed developer might implement this by copying and pasting identical SQL queries into every Select List, hardcoding lookup values ('ACTIVE', 'PENDING', 'CANCELLED') across dozens of page items, and hand-crafting custom sidebars on every screen.

This approach creates severe maintainability issues:

  • Schema Drift: A change to the query filtering active hotels requires updating SQL statements on ten separate pages.
  • Inconsistent User Experience: Different pages display slightly different labels or sort orders for the same data entities.
  • Session Leakage: Missing centralized session variables means tenant filters must be repeatedly passed across URLs, increasing security risks and URL tampering vulnerabilities.

APEX Shared Components solve this architectural challenge by acting as the single source of truth for your application logic, layout elements, and data definitions.

2. Why the Feature Matters

Shared Components are common repository elements that can be declared once at the application level and reused anywhere across your pages.

  • DRY Codebase (Don't Repeat Yourself): Define queries for Lists of Values (LOVs), authorization logic, and business calculations in one central place. If business logic changes, you adjust it once in Shared Components.
  • Centralized Session State Management: Application Items hold global values—such as G_CURRENT_TENANT_ID or G_ACTIVE_PROPERTY_ID—that remain accessible across all pages without cluttering page-level session state.
  • Standardized Navigation: Dynamic Lists power global navigation menus, user bars, and side drawers with role-based access control evaluated automatically.
  • Maintainability & Governance: Separating reusable definitions from page rendering logic speeds up developer handoffs, code reviews, and performance tuning.

3. Architecture and Workflow Diagram

The diagram below illustrates how an APEX runtime session references shared elements during page evaluation and rendering:

4. Required Database Objects

To demonstrate this architecture, we use a multi-tenant hotel reservation model where data visibility and selection depend on the active tenant context.

-- Clean up previous demo tables if necessary

-- 1. Tenant Owner Entity

-- 2. Hotel Property Entity (Belongs to Tenant)

-- 3. Room Booking Entity

-- Seed Data


5. Complete SQL and PL/SQL Examples

A. Centralized Application Logic Package

Placing shared application logic into a database package keeps your PL/SQL accessible to Shared Components, Application Processes, and custom reports.


    -- Function to format status indicators across all LOVs


   

6. Step-by-Step Oracle APEX Configuration

Follow these steps in your APEX App Builder to implement the shared foundation:

Step 1: Create Global Application Items

Application Items store global state values that are available across every page in your session.

1.      Navigate to Shared Components $\rightarrow$ Application Logic $\rightarrow$ Application Items.

2.      Click Create.

3.      Set Name to: G_CURRENT_TENANT_ID.

4.      Set Scope to: Application.

5.      Under Security, set Session State Protection to Restricted - Cannot be Set from Browser. This prevents malicious users from tampering with the tenant identifier via client-side JavaScript or URL parameters.

Step 2: Create an Application Computation or Process

Automatically initialize your global application items immediately after a user logs in.

1.      Navigate to Shared Components $\rightarrow$ Application Logic $\rightarrow$ Application Processes.

2.      Click Create.

3.      Set Name to: Initialize_Tenant_Context.

4.      Set Point to: After Authentication (or On New Instance (new session)).

5.      Enter the following PL/SQL code block:


6.      Click Create Process.

Step 3: Build a Shared List of Values (LOV)

Centralizing dropdown definitions ensures consistent labels and queries across forms and interactive reports.

1.      Navigate to Shared Components $\rightarrow$ Other Components $\rightarrow$ List of Values.

2.      Click Create $\rightarrow$ select From Scratch.

3.      Set Name to: LOV_ACTIVE_PROPERTIES.

4.      Set Type to: Dynamic.

5.      In the Data Source, select Local Database and input the following query:


6.      Under Cascading LOV Parent Items, enter G_CURRENT_TENANT_ID (so that APEX automatically refreshes the options if the tenant variable changes).

7.      Save the List of Values.

Step 4: Configure a Dynamic Navigation Menu List

Instead of hardcoding your navigation bar, drive it dynamically from database tables using a shared list.

1.      Navigate to Shared Components $\rightarrow$ Navigation $\rightarrow$ Lists.

2.      Click Create $\rightarrow$ select From Scratch.

3.      Set Name to: DESKTOP_NAVIGATION_MENU.

4.      Choose Dynamic query mode and input:


5.      Apply this List under User Interface Attributes $\rightarrow$ Navigation Menu.

7. Integration with Earlier Topics

Shared Components tie directly into standard page-level components:

·         Interactive Reports & Interactive Grids: Instead of writing raw table joins in your report queries, use shared LOVs to translate Foreign Key IDs into human-readable descriptions. Set the report column type to Select List and select the Shared LOV LOV_ACTIVE_PROPERTIES.

·         Form Pages: Page items (such as P20_HOTEL_PROPERTY_ID) can use LOV_ACTIVE_PROPERTIES as their data source. Setting Display Null Value to Yes and specifying a label like - Select Property - ensures standard UX across your app.

·         APEX Approval Workflows: Workflow approval processes evaluate conditions against global items (like :G_CURRENT_TENANT_ID), ensuring notifications and approval routes target the right administrators without re-querying user roles on every state change.

8. Security and Performance Considerations

·         Session State Protection (SSP): Never leave global Application Items unprotected. Set SSP to Restricted - Cannot be Set from Browser for identifiers like tenant IDs or organization keys. This stops URL tampering attacks (e.g., modifying &G_CURRENT_TENANT_ID=2 in the browser query string).

·         Cross-Site Scripting (XSS) Prevention: When generating dynamic HTML (such as status badges or custom link labels), use apex_escape.html() or apex_escape.html_attribute() to sanitize column outputs.

·         Result Cache on Static LOVs: For global lookups that rarely change (e.g., country lists, currency codes, room categories), open the List of Values and toggle Cache to Enabled. Set a cache timeout or use database result caching. This prevents round-trips to the database engine every time a page renders.

·         Session State Binding: Always query application items using bind variable syntax (:G_CURRENT_TENANT_ID) rather than dynamic string concatenation ('WHERE tenant_id = ' || v_tenant). Bind variables allow the database to reuse parsed execution plans in the SGA Shared Pool, preventing SQL injection and hard parse contention.

9. Common Errors and Troubleshooting

1. ORA-01403: no data found in Application Processes

·         Symptom: The user authenticates, and the application immediately displays an uncaught internal error.

·         Cause: The After Authentication process queried a database table expecting a matching email for :APP_USER, but no row existed.

·         Fix: Always wrap initialization queries inside an explicit BEGIN ... EXCEPTION WHEN NO_DATA_FOUND THEN ... END; block with sensible fallback defaults.

2. Cascading LOV Returns Empty or Shows Stale Data

·         Symptom: Changing a parent selection does not update the child select list, or it shows choices belonging to another tenant.

·         Cause: The parent Application Item or Page Item was omitted from the Page Items to Submit property in the dependent item.

·         Fix: In Page Designer, select the dependent item, find List of Values, and ensure the parent item is listed under Items to Submit.

3. Application Item Cannot Be Set via AJAX

·         Symptom: Setting an Application Item via a Dynamic Action (e.g., Execute Server-side Code) triggers a session state security violation.

·         Cause: The item's Session State Protection is set to Restricted - Cannot be Set from Browser, which also blocks unverified client-side AJAX updates.

·         Fix: Use an explicit Application Process or Dynamic Action process that verifies security parameters on the server before mutating the variable with apex_util.set_session_state.

10. Conclusion and Next Article Preview

Using APEX Shared Components shifts your development from one-off page designs to a scalable, unified architecture. Defining Application Items, Shared Lists of Values, and dynamic navigation centrally reduces code duplication, secures tenant boundaries, and maintains a clean user experience across your platform.

What’s Coming Next:

In the next article, "Deep Dive into APEX Authorization Schemes: Implementing Fine-Grained Role-Based Access Control (RBAC)," we will build on our shared foundation. We will create custom PL/SQL authorization schemes that evaluate :G_CURRENT_TENANT_ID and dynamic user privileges to conditionally render regions, buttons, and endpoints based on user roles.

 


Comments

Popular posts from this blog

Implementing Authentication and Authorization in Oracle APEX

Designing a Scalable Database Architecture for Oracle APEX Applications