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
Post a Comment