What is Oracle APEX?

Oracle Application Express (APEX) is the modern successor to Oracle Forms. It is a low-code development platform that enables developers to build scalable, secure enterprise applications using nothing but SQL, PL/SQL, and a web browser.

Unlike Forms, which required clunky Java applets, APEX produces native, responsive HTML5 and JavaScript. It automatically handles UI themes, responsive design for mobile devices, and session state management.

The key advantage: if you know SQL and PL/SQL, you already know 80% of APEX development. There is no new programming language to learn. Your existing Oracle database skills translate directly.

The ORDS Architecture

The true genius of APEX is its architecture. APEX is not a standalone application server. The entire APEX engine is installed inside the Oracle Database itself as a set of PL/SQL packages and metadata tables.

Request Flow

┌──────────────┐     ┌──────────────┐     ┌──────────────────────────────────┐
│   BROWSER    │────▶│    ORDS       │────▶│       ORACLE DATABASE            │
│              │     │ (Oracle REST  │     │                                  │
│ HTTP Request │     │  Data Services│     │  ┌─────────────────────────┐    │
│ GET /app/101 │     │              │     │  │   APEX PL/SQL ENGINE    │    │
│              │     │ Translates   │     │  │                         │    │
│              │     │ HTTP → DB    │     │  │  1. Read page metadata  │    │
│              │     │ call         │     │  │  2. Execute SQL/PLSQL   │    │
│              │◀────│              │◀────│  │  3. Generate HTML       │    │
│ HTML Response│     │ Sends HTML   │     │  └─────────────────────────┘    │
└──────────────┘     └──────────────┘     └──────────────────────────────────┘

1. A user navigates to an APEX application in their browser
2. The HTTP request hits Oracle REST Data Services (ORDS) — a lightweight Java application running in Tomcat, WebLogic, or as a standalone server
3. ORDS translates the HTTP request into a database call and invokes the APEX PL/SQL engine
4. The APEX engine queries metadata tables to determine what the page should look like, runs any SQL to fetch data, and generates pure HTML
5. ORDS sends the HTML back to the browser

Because the engine lives in the database, data access is incredibly fast. There is no middle-tier network latency when querying tables — the UI generation and data retrieval happen in the same process.

Workspaces & Application Builder

Workspaces

An APEX workspace is a logical container within the database that holds one or more applications. Workspaces provide:

• Schema isolation — each workspace is mapped to one or more database schemas
• Developer accounts — multiple developers can work in the same workspace
• Application separation — dev, test, and prod environments can use different workspaces

Application Builder

The Application Builder is the primary development interface. Everything is done through a web-based IDE:

• Page Designer — drag-and-drop page layout editor with a property pane, rendering tree, and component gallery
• SQL Workshop — browser-based SQL editor for running queries, creating objects, and managing data
• Team Development — built-in issue tracker, milestones, and feature tracking
• Shared Components — reusable elements (LOVs, templates, navigation, authentication schemes) shared across pages

Creating an Application

1. Create Workspace → mapped to your database schema
2. Create Application → give it a name and choose a theme
3. Add Pages → use wizards to create report pages, form pages, dashboard pages
4. Define Navigation → set up the sidebar menu or top navigation bar
5. Run → immediately test in the browser (no compilation, no deployment)

Page Types & Components

APEX provides pre-built page types that cover 90% of enterprise application needs:

Interactive Reports & Grids

The Interactive Report (IR) is APEX's killer feature. A developer writes a single SQL query, and APEX automatically generates a fully interactive data grid with:

• Column sorting (click any column header)
• Full-text search across all columns
• Column-level filters with operators (=, !=, contains, between, etc.)
• Highlighting rules (e.g., highlight rows where STATUS = 'OVERDUE' in red)
• Aggregations (sum, count, average on any numeric column)
• Group By pivoting
• Chart view (switch between table and chart with one click)
• Saved Reports (users save their favorite filter/sort configurations)
• Export to CSV, Excel, PDF, HTML, or email

-- This single query creates a fully interactive report
-- APEX handles ALL the UI, filtering, sorting, and export
SELECT 
    item,
    item_desc,
    dept,
    class,
    subclass,
    status,
    item_level,
    tran_level,
    TO_CHAR(create_datetime, 'DD-MON-YYYY') AS created,
    last_update_id
FROM 
    item_master
WHERE 
    dept = :P1_DEPT   -- Page item binding (declarative filter)
ORDER BY 
    item;

That's it. One SQL query. APEX generates the entire interactive grid, search bar, filter panel, export buttons, and pagination automatically.

Universal Theme & UI Customization

APEX ships with Universal Theme — a responsive, modern CSS framework built on top of Oracle JET components. Key features:

• Responsive Layout — automatically adjusts for desktop, tablet, and mobile screens
• Template Options — change component appearance (card layout, floating labels, stacked fields) through declarative properties
• Theme Roller — a live CSS customization tool that lets you change colors, fonts, borders, and spacing without writing CSS
• Custom CSS/JS — for advanced customization, add custom CSS and JavaScript at the page or application level

UI Patterns

REST Modules & Web Services

APEX integrates with ORDS to create and consume REST APIs:

Creating REST APIs (ORDS)

-- Define a REST module
BEGIN
    ORDS.DEFINE_MODULE(
        p_module_name    => 'rms_items',
        p_base_path      => '/items/',
        p_items_per_page => 25,
        p_status         => 'PUBLISHED',
        p_comments       => 'Oracle RMS Item Master API'
    );
    
    -- Define a GET handler
    ORDS.DEFINE_HANDLER(
        p_module_name    => 'rms_items',
        p_pattern        => ':item_id',
        p_method         => 'GET',
        p_source_type    => 'json/item',
        p_source         => '
            SELECT item, item_desc, dept, class, 
                   subclass, status, item_level
            FROM   item_master
            WHERE  item = :item_id'
    );
    
    COMMIT;
END;
/
-- Access: GET https://server/ords/schema/items/100400012345

Consuming External APIs

APEX provides APEX_WEB_SERVICE — a PL/SQL API for making HTTP calls to external REST/SOAP services:

-- Call an external API from APEX
DECLARE
    l_response CLOB;
BEGIN
    l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        p_url         => 'https://api.example.com/exchange-rates',
        p_http_method => 'GET',
        p_parm_name   => APEX_UTIL.STRING_TO_TABLE('base:symbols'),
        p_parm_value  => APEX_UTIL.STRING_TO_TABLE('USD:EUR,GBP')
    );
    -- Parse JSON response
    APEX_JSON.PARSE(l_response);
END;

Authentication & Authorization

APEX provides enterprise-grade security out of the box:

Authentication Schemes

• APEX Accounts — built-in user management (good for small apps)
• LDAP / Active Directory — integrate with corporate directory
• Social Sign-In — Google, Microsoft, Apple login
• Custom Authentication — write your own PL/SQL authentication function
• SAML / OAuth2 — enterprise SSO integration

Authorization Schemes

• Role-Based Access — define roles and assign them to pages, regions, buttons, or individual components
• Row-Level Security — use VPD (Virtual Private Database) policies to restrict data access by user
• Component-Level — show/hide buttons, regions, and menu items based on user privileges

Built-in Protections

• Session State Protection — prevents URL tampering and parameter manipulation
• CSRF Tokens — automatic Cross-Site Request Forgery prevention
• SQL Injection Prevention — bind variable enforcement in all queries
• XSS Prevention — automatic HTML escaping of all output

APEX in the Cloud

APEX has become a first-class citizen in Oracle Cloud Infrastructure (OCI):

• APEX Application Development Service — fully managed APEX environment in OCI. No infrastructure to manage.
• Always Free Tier — Oracle offers APEX for free on the Always Free OCI tier with an Autonomous Database
• Autonomous Database Integration — APEX runs natively on Oracle Autonomous Database with automatic scaling, patching, and backup
• APEX on Oracle Retail Cloud — For retailers using MFCS (Merchandising Foundation Cloud Service), APEX is the officially supported tool for building custom data-entry screens and administrative UIs on top of custom extension schemas

APEX for Oracle Retail

In the Oracle Retail ecosystem, APEX is used for:

1. Custom Extension UIs: Building data entry screens for custom extension tables (CUST_ITEM_ATTR, CUST_LOC_ATTR) that are not covered by the standard RMS UI
2. Reporting Dashboards: Interactive dashboards for inventory analysis, PO tracking, and vendor performance — faster to build than OBIEE reports
3. Data Correction Tools: Admin screens for fixing data issues (e.g., correcting item attributes, updating supplier records, managing reference data)
4. Integration Monitoring: Dashboards showing RIB message status, batch job execution results, and error queue depths
5. Workflow Applications: Custom approval workflows for PO approval, price change authorization, and vendor onboarding that extend RMS capabilities