72 questions — APEX · REST API · SOAP API · Basic to Advanced · Click to expand
Oracle APEX (Application Express) is a low-code web application development platform built on Oracle Database.
It allows developers to build scalable, secure enterprise web applications using only a web browser.
APEX provides built-in features like authentication, authorization, UI components, and direct database integration, making it ideal for rapidly building data-driven applications without needing deep front-end expertise.
-- APEX runs entirely inside Oracle Database
-- No separate app server needed
-- Access via browser: https://apex.oracle.com
-- Check APEX version
SELECT version_no, api_compatibility
FROM apex_release;
-- Check your workspace
SELECT workspace, workspace_id
FROM apex_workspaces
WHERE workspace = 'MY_WORKSPACE';| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Oracle APEX (Application Express) is a low-code web application development platform built on Oracle Database.
An Oracle APEX application consists of Pages (UI screens), Shared Components (reusable elements like navigation menus, lists of values, templates), Application Processes and Computations (server-side logic), Authentication Schemes (who can log in), Authorization Schemes (what they can access), and Substitution Strings (global variables).
Each page has regions, items, buttons, and processes that define its layout and behaviour.
-- Query your APEX application pages
SELECT page_id, page_name, page_mode, authentication_scheme
FROM apex_application_pages
WHERE application_id = :APP_ID
ORDER BY page_id;
-- Query shared components - LOVs
SELECT list_of_values_name, lov_query
FROM apex_application_lovs
WHERE application_id = :APP_ID;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
An Oracle APEX application consists of Pages (UI screens), Shared Components (reusable elements like navigation menus, lists of values, templates), Application Processes and Computations (server-side logic), Authentication Schemes (who can log in), Authorization Schemes (what they can access), and Substitution Strings (global variables).
An APEX workspace is a shared work area where developers build applications.
Each workspace is mapped to one or more Oracle database schemas.
The workspace controls which schemas developers can access, and all APEX metadata (pages, regions, items) is stored in the APEX internal schema (APEX_xxxxxx).
A workspace can have multiple developers with different roles (Developer, Workspace Admin, End User).
-- Check workspace-schema mapping (run as APEX admin)
SELECT workspace, schema
FROM apex_workspace_schemas
WHERE workspace = 'MY_WORKSPACE';
-- Create workspace (run as SYS or APEX admin)
BEGIN
apex_instance_admin.add_workspace(
p_workspace => 'MY_WORKSPACE',
p_primary_schema => 'HR'
);
END;
/| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
An APEX workspace is a shared work area where developers build applications.
APEX pages can be Normal (full page render), Modal Dialog (popup overlay on top of current page), Non-Modal Dialog (floating window), or Drawer (slide-in panel).
Normal pages replace the current view.
Modal dialogs are ideal for forms and confirmations as they keep context on the parent page.
Drawers are used for secondary content without leaving the main view.
The page mode affects how the page is rendered and how it closes.
-- Normal page: navigates away from current page
apex_util.redirect_url('f?p=&APP_ID.:2:&SESSION.');
-- Open Modal Dialog via dynamic action
-- Set Action = 'Open Page in Dialog'
-- Target page mode must be: Modal Dialog
-- Close modal and refresh parent from PL/SQL process
apex_util.redirect_url(
apex_page.get_url(p_page => 0));
-- OR use: Close Dialog action in dynamic action| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
APEX pages can be Normal (full page render), Modal Dialog (popup overlay on top of current page), Non-Modal Dialog (floating window), or Drawer (slide-in panel).
APEX Items are form fields on a page that hold values — they map to HTML input elements and store data in session state.
Types include Text Field, Text Area, Select List, Radio Group, Checkbox, Date Picker, File Browse, Hidden, Display Only, Rich Text Editor, Shuttle, and more.
Items are referenced in SQL queries and PL/SQL using bind syntax :P1_ITEM_NAME.
Their values persist in session state between page submissions.
-- Reference item value in SQL query
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = :P1_DEPT_ID -- bind to page item
AND salary >= :P1_MIN_SALARY;
-- Set item value in PL/SQL process
BEGIN
:P1_EMPLOYEE_NAME := 'Alice Johnson';
:P1_STATUS := 'ACTIVE';
END;
-- Set via apex_util
apex_util.set_session_state('P1_STATUS', 'ACTIVE');| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 101 | Alice Johnson | 50,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
| 104 | Dave Brown | 70,000 |
All 4 rows returned
APEX Items are form fields on a page that hold values — they map to HTML input elements and store data in session state.
Session State is APEX's mechanism for storing page item values on the server between page requests.
When a user submits a page, all item values are saved in session state in the APEX schema.
These values can be read using bind variables (:ITEM_NAME) in SQL and PL/SQL, or using the V() and NV() functions.
Session state persists for the duration of the user's session and can be cleared programmatically.
-- Read session state value
SELECT V('P1_USERNAME') AS username, -- returns VARCHAR2
NV('P1_SALARY') AS salary, -- returns NUMBER
V('APP_USER') AS app_user,
V('APP_SESSION') AS session_id
FROM dual;
-- Set session state in PL/SQL
apex_util.set_session_state('P1_STATUS','APPROVED');
-- Clear session state
apex_util.clear_page_cache(p_page => 1);
apex_util.clear_app_cache(p_app_id => :APP_ID);| EMPLOYEE_NAME | SALARY |
|---|---|
| Alice Johnson | 50,000 |
| Bob Smith | 62,000 |
| Carol White | 55,000 |
| Dave Brown | 70,000 |
All 4 rows returned
Session State is APEX's mechanism for storing page item values on the server between page requests.
A Region is a container on an APEX page that holds content.
Regions can be of type Classic Report, Interactive Report, Interactive Grid, Form, Chart, Calendar, Tree, Map, PL/SQL Dynamic Content, Static Content, and more.
Each region has a position (slot) on the page, a template controlling its visual style, and optional conditions that control when it renders.
Regions are the primary building blocks for APEX page layouts.
-- Query regions on a page
SELECT region_name, region_type, display_position, condition_type
FROM apex_application_page_regions
WHERE application_id = :APP_ID
AND page_id = 1
ORDER BY display_sequence;
-- PL/SQL Dynamic Content region example
BEGIN
htp.p('<div class="info-box">');
htp.p('Welcome, ' || V('APP_USER'));
htp.p('Last login: ' || TO_CHAR(SYSDATE,'DD-MON-YYYY'));
htp.p('</div>');
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
A Region is a container on an APEX page that holds content.
A Classic Report renders a fixed SQL query result as an HTML table with limited user control — the layout is fully defined by the developer.
An Interactive Report (IR) gives end users powerful self-service features: column search, filter, sort, highlight, aggregate, pivot, download (CSV/Excel), and saved report views.
IRs store user customisations in the database per user.
Use Classic Reports when you need precise control over layout; use Interactive Reports when users need to explore data.
-- Classic Report: fixed output
SELECT employee_id, employee_name, salary, department_id
FROM employees
WHERE department_id = :P1_DEPT_ID
ORDER BY employee_name;
-- Interactive Report: same query but users can:
-- ✓ Add filters ✓ Sort any column
-- ✓ Group by ✓ Download CSV/Excel
-- ✓ Save views ✓ Create charts
-- ✓ Highlight rows ✓ Control columns shown
-- Reset IR programmatically
apex_ir.reset_report(
p_page_id => 2,
p_region_id => 1234567);| EMPLOYEE_NAME | SALARY | DEPT_NAME |
|---|---|---|
| Alice Johnson | 50,000 | Engineering |
| Bob Smith | 62,000 | Marketing |
| Carol White | 55,000 | Engineering |
| Dave Brown | 70,000 | Marketing |
All 4 rows returned — ordered by salary ASC
A Classic Report renders a fixed SQL query result as an HTML table with limited user control — the layout is fully defined by the developer.
An Interactive Grid is an APEX region type that displays data in an editable spreadsheet-like grid.
Users can add, edit, and delete rows directly in the grid without navigating to separate forms.
IGs support inline editing, row selection, bulk operations, and all the column manipulation features of Interactive Reports (filtering, sorting, grouping).
IGs require a primary key column and use APEX's built-in DML processing or custom PL/SQL.
-- Interactive Grid SQL (must include primary key)
SELECT employee_id, -- primary key
employee_name,
salary,
department_id,
hire_date
FROM employees
WHERE department_id = :P2_DEPT_FILTER;
-- IG Lost Update Protection (optimistic locking)
-- Add to IG attributes:
-- Duplicate Submission: Prevent
-- Lost Update: Row Version
-- Refresh IG via JavaScript
apex.region('employee-grid').refresh();| EMPLOYEE_NAME | SALARY | DEPT_NAME |
|---|---|---|
| Alice Johnson | 50,000 | Engineering |
| Bob Smith | 62,000 | Marketing |
| Carol White | 55,000 | Engineering |
| Dave Brown | 70,000 | Marketing |
All 4 rows returned
An Interactive Grid is an APEX region type that displays data in an editable spreadsheet-like grid.
Dynamic Actions are APEX's declarative event-driven framework for adding client-side interactivity without writing JavaScript.
You define a When condition (which event on which element), optional True/False conditions, and True/False Actions (like Show, Hide, Set Value, Execute JavaScript, Refresh, Disable, Enable, etc.).
Dynamic Actions fire in the browser and can also execute server-side AJAX callbacks to run PL/SQL.
They are the primary way to add interactivity in APEX.
-- Dynamic Action Setup (declarative):
-- Event : Change
-- Selection: Item(s) → P1_DEPARTMENT
-- Condition : Item = Value → 'SALES'
-- TRUE Actions:
-- 1. Set Value (PL/SQL) → :P1_TARGET := 150000;
-- 2. Refresh → Region: Employees Report
-- 3. Show → Item: P1_BONUS_SECTION
-- FALSE Actions:
-- 1. Hide → Item: P1_BONUS_SECTION
-- Execute PL/SQL action (server-side AJAX)
BEGIN
:P1_EMPLOYEE_COUNT := apex_util.count_rows(
p_query => 'SELECT COUNT(*) FROM employees WHERE dept_id = :P1_DEPT'
);
END;| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
Dynamic Actions are APEX's declarative event-driven framework for adding client-side interactivity without writing JavaScript.
Substitution Strings are APEX built-in variables prefixed with & and suffixed with . (e.g. &APP_ID.) that get replaced with their values when pages render.
Common ones include &APP_ID. (application ID), &APP_SESSION. (session ID), &APP_USER. (logged-in username), &APP_PAGE_ID. (current page), and &ITEM_NAME. (page item value).
They can be used in HTML, SQL, URLs, and text regions.
Use V('ITEM_NAME') in PL/SQL contexts instead.
-- In HTML/Static Text regions:
<p>Welcome, &APP_USER.!</p>
<p>App: &APP_ID. | Page: &APP_PAGE_ID.</p>
-- In URL targets:
f?p=&APP_ID.:2:&APP_SESSION.::NO::P2_EMP_ID:&P1_EMP_ID.
-- In SQL (use bind variables instead of substitution):
SELECT * FROM employees WHERE created_by = :APP_USER;
-- Common substitution strings:
-- &APP_ID. Application ID
-- &APP_SESSION. Session ID
-- &APP_USER. Logged in user
-- &APP_PAGE_ID. Current page number| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Substitution Strings are APEX built-in variables prefixed with & and suffixed with .
A List of Values (LOV) is a shared component in APEX that defines the options available in select lists, radio groups, checkboxes, and popup LOVs.
LOVs can be Static (hardcoded values like Yes/No), or Dynamic (based on a SQL query returning display/return value pairs).
Shared LOVs are defined once and reused across multiple pages.
LOVs can also be defined inline on a specific page item when not needed elsewhere.
-- Dynamic LOV query (display value | return value)
SELECT dept_name AS display_value,
dept_id AS return_value
FROM departments
WHERE active_flag = 'Y'
ORDER BY dept_name;
-- Static LOV example (in LOV definition):
-- STATIC2:Active;Y,Inactive;N,Pending;P
-- Cascading LOV: filter by parent item
SELECT job_title AS d, job_id AS r
FROM jobs
WHERE department_id = :P1_DEPT_ID -- depends on parent item
ORDER BY job_title;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
A List of Values (LOV) is a shared component in APEX that defines the options available in select lists, radio groups, checkboxes, and popup LOVs.
An Authentication Scheme defines how users prove their identity to access an APEX application.
APEX supports several types: Application Express Accounts (users stored in APEX), Database Accounts (Oracle DB users), LDAP Directory, Social Sign-In (OAuth2 like Google), Custom (PL/SQL function returning true/false), and No Authentication (public access).
Each application has exactly one active authentication scheme.
You can switch schemes without changing application logic.
-- Custom authentication function
CREATE OR REPLACE FUNCTION fn_authenticate(
p_username IN VARCHAR2,
p_password IN VARCHAR2
) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM app_users
WHERE UPPER(username) = UPPER(p_username)
AND password_hash = fn_hash(p_password)
AND active_flag = 'Y';
RETURN v_count > 0;
EXCEPTION
WHEN OTHERS THEN RETURN FALSE;
END;
/
-- Configure in: App Builder → Shared Components → Authentication Schemes| EXCEPTION | WHEN_RAISED | HANDLED_BY | OUTCOME |
|---|---|---|---|
| NO_DATA_FOUND | SELECT INTO = 0 rows | WHEN NO_DATA_FOUND | Continue after message |
| TOO_MANY_ROWS | SELECT INTO > 1 row | WHEN OTHERS | Rollback + log |
Exceptions prevent unhandled errors from crashing the PL/SQL block
An Authentication Scheme defines how users prove their identity to access an APEX application.
An Authorization Scheme controls what authenticated users can do — it answers 'what are you allowed to access?' after authentication answers 'who are you?'.
Authorization schemes can be PL/SQL functions, EXISTS SQL queries, or built-in types like 'Has Role'.
They can be applied to pages, regions, items, buttons, and menu entries.
If a user fails an authorization check, they see an unauthorized error or are redirected.
Multiple schemes can be combined.
-- Authorization scheme: PL/SQL returning Boolean
CREATE OR REPLACE FUNCTION fn_is_manager RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM app_user_roles
WHERE username = V('APP_USER')
AND role_code = 'MANAGER';
RETURN v_count > 0;
END;
/
-- SQL EXISTS type authorization
SELECT 1 FROM app_user_roles
WHERE username = :APP_USER
AND role_code IN ('ADMIN','SUPER_USER')
-- Apply to page: Page → Security → Authorization Scheme| COUNT | AVG_SALARY | MIN_SALARY | MAX_SALARY | SUM_SALARY |
|---|---|---|---|---|
| 4 | 59,250 | 50,000 | 70,000 | 237,000 |
Aggregate over all 4 employees — AVG=(50+62+55+70)/4=59,250
An Authorization Scheme controls what authenticated users can do — it answers 'what are you allowed to access?' after authentication answers 'who are you?'.
Values can be passed between APEX pages in several ways: via URL parameters (f?p=APP:PAGE:SESSION:::ITEMS:VALUES), by setting session state in a process before branching, using the Request value, via hidden items on the target page, or using the APEX_PAGE.GET_URL function with item parameters.
For modal dialogs, you can close the dialog and refresh the parent using apex_util.close_dialog or the Close Dialog dynamic action with return items.
-- Method 1: URL with item values
f?p=&APP_ID.:3:&APP_SESSION.::NO:P3_EMP_ID,P3_DEPT_ID:101,10
-- Method 2: apex_page.get_url (PL/SQL)
DECLARE v_url VARCHAR2(4000);
BEGIN
v_url := apex_page.get_url(
p_page => 3,
p_items => 'P3_EMP_ID,P3_DEPT_ID',
p_values => '101,10',
p_clear_cache => '3'
);
apex_util.redirect_url(v_url);
END;
-- Method 3: Close modal dialog with return value
apex_util.close_dialog(
p_items => 'P1_SELECTED_EMP',
p_values => :P3_EMP_ID);| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
Values can be passed between APEX pages in several ways: via URL parameters (f?p=APP:PAGE:SESSION:::ITEMS:VALUES), by setting session state in a process before branching, using the Request value, via hidden items on the target page, or using the APEX_PAGE.
The APEX f?p URL syntax is: f?p=APP_ID:PAGE_ID:SESSION:REQUEST:DEBUG:CLEAR_CACHE:ITEMS:VALUES:PRINTER_FRIENDLY.
Each position is separated by colons.
APP_ID is the application ID or alias, PAGE_ID is the target page, SESSION is the session ID, REQUEST triggers a page process, CLEAR_CACHE clears specific page caches, and ITEMS/VALUES are colon-separated lists of items to set.
Positions can be left empty using consecutive colons.
-- Full URL syntax:
-- f?p=APP:PAGE:SESSION:REQUEST:DEBUG:CLEAR_CACHE:ITEMS:VALUES
-- Examples:
-- Navigate to page 2, clear its cache
f?p=100:2:&APP_SESSION.::NO:2::
-- Navigate and set item values
f?p=100:3:&APP_SESSION.::NO::P3_EMP_ID,P3_DEPT:101,10
-- Trigger REQUEST (branch condition)
f?p=100:1:&APP_SESSION.:APPROVE:::
-- Using alias instead of ID
f?p=MY_APP:home:&APP_SESSION.
-- apex_page.get_url (recommended)
SELECT apex_page.get_url(
p_application => 100,
p_page => 3
) FROM dual;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
The APEX f?p URL syntax is: f?p=APP_ID:PAGE_ID:SESSION:REQUEST:DEBUG:CLEAR_CACHE:ITEMS:VALUES:PRINTER_FRIENDLY.
Processes are server-side PL/SQL blocks or built-in actions that execute at specific points during page processing.
They can run On Load (before rendering), On Submit (after a button is clicked), On Demand (AJAX callback), or as Background Jobs.
Common built-in process types include Form - Automatic Row Processing (DML), Clear Session State, Close Dialog, and Execute Code.
Processes can have conditions, error handling, and success messages.
They run in sequence order.
-- PL/SQL Process: After Submit
BEGIN
-- Validate
IF :P1_SALARY < 0 THEN
apex_error.add_error(
p_message => 'Salary cannot be negative',
p_display_location => apex_error.c_inline_in_notification,
p_page_item_name => 'P1_SALARY'
);
END IF;
-- DML
UPDATE employees
SET salary = :P1_SALARY,
employee_name = :P1_EMP_NAME,
updated_by = V('APP_USER'),
updated_date = SYSDATE
WHERE employee_id = :P1_EMP_ID;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001,'Record not found');
END IF;
END;| EMP_ID | EMP_NAME | OLD_SALARY | NEW_SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 50,000 | 55,000 |
| 103 | Carol White | 55,000 | 60,500 |
2 rows updated — COMMIT to make permanent
Processes are server-side PL/SQL blocks or built-in actions that execute at specific points during page processing.
The APEX_ERROR package provides APIs to add, manage, and display errors on APEX pages.
The main procedure is APEX_ERROR.ADD_ERROR which lets you add errors programmatically during page processing.
Errors can be displayed inline next to a specific item, in a notification at the top of the page, or in a separate error region.
This is used in validation processes and after-submit processes to return meaningful errors to users without using RAISE_APPLICATION_ERROR.
-- Add error linked to specific item
apex_error.add_error(
p_message => 'Email address is already registered.',
p_display_location => apex_error.c_inline_with_field,
p_page_item_name => 'P1_EMAIL'
);
-- Add error in notification area
apex_error.add_error(
p_message => 'You do not have permission to delete this record.',
p_display_location => apex_error.c_inline_in_notification
);
-- Add error with additional info
apex_error.add_error(
p_message => 'Save failed.',
p_additional_info => SQLERRM,
p_display_location => apex_error.c_on_error_page
);| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
The APEX_ERROR package provides APIs to add, manage, and display errors on APEX pages.
Validations are declarative or PL/SQL-based checks that run on page submission before processes execute.
If a validation fails, it displays an error and stops processing.
Types include: Item is NOT NULL, Item is Numeric, Item contains valid date, SQL query returns no rows (EXISTS check), PL/SQL function returns TRUE, and Regular Expression.
Validations can be attached to specific items or the whole page, and can have conditions controlling when they fire.
-- PL/SQL validation: Function returning error text
-- Returns NULL = valid, Returns message = invalid
BEGIN
IF :P1_END_DATE < :P1_START_DATE THEN
RETURN 'End date must be after start date';
END IF;
IF :P1_SALARY > 500000 THEN
RETURN 'Salary cannot exceed 500,000';
END IF;
RETURN NULL; -- valid
END;
-- SQL EXISTS validation: valid if query returns rows
SELECT 1 FROM departments
WHERE dept_id = :P1_DEPT_ID;
-- If no rows returned → validation fails
-- Condition: only validate when inserting
-- Condition Type: Request = Expression 1
-- Expression 1: CREATE| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Validations are declarative or PL/SQL-based checks that run on page submission before processes execute.
APEX handles DML in two ways.
Automatic Row Processing (ARP) uses the built-in Form process type that automatically generates INSERT/UPDATE/DELETE SQL based on the form's source table and items.
The form determines whether to insert or update based on the primary key value.
Alternatively, you can write custom PL/SQL processes for full control.
ARP supports optimistic locking (row version checking) to prevent lost updates when multiple users edit the same record.
-- Automatic Row Processing (built-in)
-- Configure Form region source:
-- Table: EMPLOYEES
-- Primary Key: EMPLOYEE_ID
-- Add Process: Form - Automatic Row Processing (DML)
-- APEX handles INSERT/UPDATE/DELETE automatically
-- Custom DML (for complex logic)
DECLARE
v_emp_id employees.employee_id%TYPE := :P1_EMP_ID;
BEGIN
IF v_emp_id IS NULL THEN
-- INSERT
INSERT INTO employees(employee_name, salary, dept_id, hire_date, created_by)
VALUES(:P1_EMP_NAME, :P1_SALARY, :P1_DEPT_ID, SYSDATE, V('APP_USER'))
RETURNING employee_id INTO :P1_EMP_ID;
ELSE
-- UPDATE
UPDATE employees
SET employee_name=:P1_EMP_NAME, salary=:P1_SALARY
WHERE employee_id = v_emp_id;
END IF;
COMMIT;
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY | STATUS |
|---|---|---|---|---|
| 205 | New Employee | 10 | 50,000 | ✅ 1 row inserted |
INSERT successful — use COMMIT to make permanent
APEX handles DML in two ways.
APEX_UTIL is a core APEX package providing utility functions for session state management, user management, security, date handling, and more.
Key procedures include: SET_SESSION_STATE (set item values), GET_SESSION_STATE / V() (read values), REDIRECT_URL (navigate), COUNT_ROWS (count query results), IS_LOGIN_PASSWORD_VALID (authentication), GET_USER_ROLES, EXPIRE_PASSWORD, and DOWNLOAD_FILE.
It's the Swiss army knife of APEX server-side development.
-- Common apex_util operations
-- Set and get session state
apex_util.set_session_state('P1_STATUS', 'APPROVED');
DBMS_OUTPUT.PUT_LINE(apex_util.get_session_state('P1_STATUS'));
-- Check if user has role
IF apex_util.current_user_in_group('MANAGERS') THEN
apex_util.set_session_state('P1_CAN_APPROVE','Y');
END IF;
-- Redirect
apex_util.redirect_url(
apex_page.get_url(p_page => 2)
);
-- Download a BLOB file
apex_util.download_file(
p_file => v_blob,
p_mime_type => 'application/pdf',
p_filename => 'report.pdf'
);| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
APEX_UTIL is a core APEX package providing utility functions for session state management, user management, security, date handling, and more.
APP_USER is an APEX substitution string that holds the username of the currently authenticated user.
It is set during authentication and available throughout the session.
In PL/SQL you access it via V('APP_USER') or :APP_USER.
APEX_APPLICATION_GLOBAL is a package that provides access to global application-level information at runtime, such as the application ID, security group ID, and user info.
These are essential for building multi-user, personalised applications.
-- Get current user in different contexts
-- In SQL query
SELECT employee_name, salary
FROM employees
WHERE UPPER(created_by) = UPPER(V('APP_USER'));
-- In PL/SQL
DECLARE v_user VARCHAR2(100);
BEGIN
v_user := V('APP_USER'); -- or :APP_USER
INSERT INTO audit_log(action, performed_by, log_date)
VALUES('LOGIN', v_user, SYSDATE);
COMMIT;
END;
-- Substitution string in HTML
<p>Logged in as: &APP_USER.</p>
<p>Application: &APP_ID. | Session: &APP_SESSION.</p>| EMPLOYEE_NAME | SALARY |
|---|---|
| Alice Johnson | 50,000 |
| Bob Smith | 62,000 |
| Carol White | 55,000 |
| Dave Brown | 70,000 |
All 4 rows returned
APP_USER is an APEX substitution string that holds the username of the currently authenticated user.
APEX Collections are temporary multi-column data stores (like session-level tables) that persist for the duration of a session.
They are useful for storing intermediate data during multi-step wizards, shopping cart items, or data that hasn't been committed yet.
Collections are created with APEX_COLLECTION.CREATE_COLLECTION, rows are added with ADD_MEMBER, modified with UPDATE_MEMBER, and queried via the APEX_COLLECTIONS view.
All data is lost when the session ends.
-- Create collection
apex_collection.create_or_truncate_collection('CART_ITEMS');
-- Add rows to collection (up to 50 VARCHAR2 + CLOB + BLOB columns)
apex_collection.add_member(
p_collection_name => 'CART_ITEMS',
p_c001 => :P1_PRODUCT_ID,
p_c002 => :P1_PRODUCT_NAME,
p_n001 => :P1_QUANTITY,
p_d001 => SYSDATE
);
-- Query collection
SELECT seq_id, c001 AS product_id,
c002 AS product_name, n001 AS qty
FROM apex_collections
WHERE collection_name = 'CART_ITEMS'
ORDER BY seq_id;
-- Delete collection
apex_collection.delete_collection('CART_ITEMS');| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
APEX Collections are temporary multi-column data stores (like session-level tables) that persist for the duration of a session.
Oracle REST Data Services (ORDS) enables you to expose Oracle Database objects and APEX applications as RESTful APIs.
You can enable REST on database objects using ORDS.ENABLE_OBJECT or through APEX's RESTful Services module.
REST handlers are defined for GET, POST, PUT, DELETE HTTP methods with URL patterns.
Parameters are bound using :param syntax.
APEX also provides REST Enabled SQL for ad-hoc REST calls and the APEX_WEB_SERVICE package for consuming external REST APIs.
-- Enable REST on a table (ORDS)
BEGIN
ORDS.ENABLE_OBJECT(
p_enabled => TRUE,
p_schema => 'HR',
p_object => 'EMPLOYEES',
p_object_type => 'TABLE',
p_object_alias => 'employees',
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/
-- Now accessible at:
-- GET /ords/hr/employees/
-- GET /ords/hr/employees/:id
-- POST /ords/hr/employees/
-- Custom REST handler (GET by dept)
SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = :dept_id;
-- URL: GET /ords/hr/emp/dept/10| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY |
|---|---|---|
| 101 | Alice Johnson | 50,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
| 104 | Dave Brown | 70,000 |
All 4 rows returned
Oracle REST Data Services (ORDS) enables you to expose Oracle Database objects and APEX applications as RESTful APIs.
APEX Plugins extend the built-in component types with custom functionality packaged as reusable components.
Plugin types include Region (custom UI containers), Item (custom form inputs), Process (custom server-side actions), Dynamic Action (custom client-side events/actions), and Authentication/Authorization.
Plugins consist of a PL/SQL callback function, JavaScript/CSS files, and metadata.
They can be exported, imported, and shared across applications. apex.oracle.com/plugins has a community plugin library.
-- Create a simple Item Plugin
-- PL/SQL render function
FUNCTION render_star_rating(
p_item IN apex_plugin.t_item,
p_plugin IN apex_plugin.t_plugin,
p_param IN apex_plugin.t_item_render_param,
p_result IN OUT NOCOPY apex_plugin.t_item_render_result
) RETURN apex_plugin.t_item_render_result IS
BEGIN
-- Output HTML for star rating widget
apex_plugin_util.print_hidden_if_readonly(
p_item_name => p_item.name,
p_value => p_param.value,
p_is_readonly=> p_param.is_readonly,
p_is_printer_friendly => p_param.is_printer_friendly
);
-- Render star rating HTML/JS
htp.p('<div class="star-rating" id="' || p_item.name || '">');
htp.p('</div>');
RETURN p_result;
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
APEX Plugins extend the built-in component types with custom functionality packaged as reusable components.
APEX handles file uploads via the File Browse item type, which stores uploaded files in the APEX_APPLICATION_FILES or WWV_FLOW_FILES table as BLOBs.
For downloads, you use the Download File process or a custom PL/SQL process that uses APEX_UTIL.DOWNLOAD_FILE or WPG_DOCLOAD.DOWNLOAD_FILE to stream a BLOB to the browser.
For large files, you should use chunked uploads or Oracle's BLOB storage with proper MIME types and Content-Disposition headers.
-- Store uploaded file from APEX file browse item
DECLARE
v_blob BLOB;
v_mime VARCHAR2(255);
v_fname VARCHAR2(255);
BEGIN
SELECT blob_content, mime_type, filename
INTO v_blob, v_mime, v_fname
FROM apex_application_temp_files
WHERE name = :P1_FILE_UPLOAD;
INSERT INTO employee_docs(
doc_id, employee_id, file_name, file_content, mime_type, uploaded_by, uploaded_date
) VALUES(
doc_seq.NEXTVAL, :P1_EMP_ID, v_fname, v_blob, v_mime, V('APP_USER'), SYSDATE
);
COMMIT;
END;
-- Download file
apex_util.download_file(
p_file => v_blob,
p_mime_type => 'application/pdf',
p_filename => 'employee_report.pdf'
);| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
APEX handles file uploads via the File Browse item type, which stores uploaded files in the APEX_APPLICATION_FILES or WWV_FLOW_FILES table as BLOBs.
Page Load processing runs when a page is first rendered in the browser — it includes before-header processes, computations, regions rendering (SQL queries executing), and after-footer processes.
On Submit processing runs when a user clicks a submit button — it includes validations (if any fail, processing stops), processes (DML, PL/SQL), and then branches (redirects).
Understanding this sequence is critical for controlling when logic executes and avoiding issues like running DML on page load.
-- PAGE LOAD sequence:
-- 1. Before Header Processes
-- 2. Computations (Before Header)
-- 3. Fetch row from table (if form)
-- 4. Computations (After Header)
-- 5. Render regions/items/buttons
-- 6. After Footer Processes
-- ON SUBMIT sequence:
-- 1. Computations (After Submit)
-- 2. Validations (if any fail → stop, show errors)
-- 3. Processes (After Submit) → DML, PL/SQL
-- 4. Branch (redirect to next page)
-- Example: Audit process only on submit
BEGIN
-- This runs only when form is submitted
INSERT INTO audit_trail(user_id, action, table_name, record_id, action_date)
VALUES(V('APP_USER'), V('REQUEST'), 'EMPLOYEES', :P1_EMP_ID, SYSDATE);
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY | STATUS |
|---|---|---|---|---|
| 205 | New Employee | 10 | 50,000 | ✅ 1 row inserted |
INSERT successful — use COMMIT to make permanent
Page Load processing runs when a page is first rendered in the browser — it includes before-header processes, computations, regions rendering (SQL queries executing), and after-footer processes.
Row Level Security (RLS) in APEX can be implemented using Oracle VPD (Virtual Private Database) policies, APEX authorization schemes on regions with WHERE clause conditions, or by using the current user context in all SQL queries.
VPD automatically appends WHERE clauses to queries based on the user context.
In APEX, you can set the client identifier using DBMS_SESSION.SET_IDENTIFIER in an application initialization process, making the user context available to VPD policies.
-- Set client identifier in APEX initialization process
BEGIN
DBMS_SESSION.SET_IDENTIFIER(V('APP_USER'));
END;
-- VPD Policy function
CREATE OR REPLACE FUNCTION fn_emp_security(
p_schema IN VARCHAR2,
p_object IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
-- Each user only sees their department's employees
RETURN 'department_id IN (
SELECT dept_id FROM user_departments
WHERE username = SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')
)';
END;
/
-- Apply VPD policy
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_DEPT_POLICY',
function_schema => 'HR',
policy_function => 'FN_EMP_SECURITY',
statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Row Level Security (RLS) in APEX can be implemented using Oracle VPD (Virtual Private Database) policies, APEX authorization schemes on regions with WHERE clause conditions, or by using the current user context in all SQL queries.
APEX applications can trigger long-running background jobs using DBMS_SCHEDULER or APEX_BACKGROUND_PROCESS.
You create a scheduler job from an APEX process, and optionally track progress via an APEX collection or a status table.
APEX 21.1+ introduced the APEX_BACKGROUND_PROCESS package which integrates jobs with the APEX session context, allowing you to track job status in the UI and display progress bars.
-- Create background job from APEX process
DECLARE
v_job_name VARCHAR2(100) := 'REPORT_GEN_' || :APP_SESSION;
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_name,
job_type => 'PLSQL_BLOCK',
job_action => '
BEGIN
prc_generate_monthly_report(
p_month => TO_DATE(''' || :P1_MONTH || ''',''YYYY-MM''),
p_user => ''' || V('APP_USER') || '''
);
END;',
start_date => SYSTIMESTAMP,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Monthly report: ' || V('APP_USER')
);
:P1_JOB_NAME := v_job_name;
END;
-- Check job status
SELECT job_name, state, run_count, last_run_duration
FROM user_scheduler_jobs
WHERE job_name = :P1_JOB_NAME;| COUNT(*) |
|---|
| 4 |
4 rows match the query conditions
APEX applications can trigger long-running background jobs using DBMS_SCHEDULER or APEX_BACKGROUND_PROCESS.
APEX_WEB_SERVICE is the APEX package for calling external REST and SOAP web services.
For REST, use APEX_WEB_SERVICE.MAKE_REST_REQUEST which returns the response as a CLOB.
You set headers (including Authorization), specify the HTTP method, and parse the JSON/XML response.
For OAuth2 APIs, you first get a token, then use it in subsequent calls.
ACLs (Access Control Lists) must be granted for the database to make outbound HTTP calls.
-- Grant ACL for outbound calls (run as DBA)
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.example.com',
ace => xs$ace_type(
privilege_list => xs$name_list('connect','resolve'),
principal_name => 'HR',
principal_type => xs_acl.ptype_db
)
);
END;
/
-- Call REST API
DECLARE
v_response CLOB;
v_emp_name VARCHAR2(200);
BEGIN
apex_web_service.set_request_headers(
p_name_01 => 'Authorization',
p_value_01 => 'Bearer ' || :P1_API_TOKEN,
p_name_02 => 'Content-Type',
p_value_02 => 'application/json'
);
v_response := apex_web_service.make_rest_request(
p_url => 'https://api.example.com/employees/101',
p_http_method => 'GET'
);
-- Parse JSON response
v_emp_name := apex_json.get_varchar2(
p_path => 'employee_name',
p_values => apex_json.parse(v_response)
);
:P1_API_EMP_NAME := v_emp_name;
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned from employees table
APEX_WEB_SERVICE is the APEX package for calling external REST and SOAP web services.
APEX_JSON is the APEX package for generating and parsing JSON.
APEX_JSON.OPEN_OBJECT/CLOSE_OBJECT/WRITE generates JSON output in PL/SQL Dynamic Content regions or REST handlers.
APEX_JSON.PARSE parses a JSON string into an internal structure, and APEX_JSON.GET_VARCHAR2/GET_NUMBER/GET_DATE reads values by JSON path.
It's used extensively in AJAX callbacks, REST API responses, and processing JSON data from external APIs.
-- Generate JSON (in AJAX callback or REST handler)
BEGIN
apex_json.open_object; -- {
apex_json.write('status', 'success'); -- "status":"success"
apex_json.write('employee_id', 101); -- "employee_id":101
apex_json.open_array('departments'); -- "departments":[
FOR r IN (SELECT dept_id,dept_name FROM departments) LOOP
apex_json.open_object; -- {
apex_json.write('id', r.dept_id); -- "id":10
apex_json.write('name', r.dept_name); -- "name":"Engineering"
apex_json.close_object; -- }
END LOOP;
apex_json.close_array; -- ]
apex_json.close_object; -- }
END;
-- Parse JSON
DECLARE
v_json CLOB := '{"name":"Alice","salary":50000}';
v_vals apex_json.t_values;
BEGIN
apex_json.parse(v_vals, v_json);
:P1_NAME := apex_json.get_varchar2(p_path=>'name', p_values=>v_vals);
:P1_SALARY := apex_json.get_number (p_path=>'salary', p_values=>v_vals);
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
APEX_JSON is the APEX package for generating and parsing JSON.
APEX Interactive Reports and Classic Reports have built-in pagination — you set rows per page and pagination type (Row Ranges with Next/Previous, or row-by-row).
For custom lazy loading, you can use APEX's scroll-to-load feature in Interactive Grids or implement it with JavaScript and AJAX callbacks that call apex.server.process to fetch the next batch of rows and append to the DOM.
Proper indexing and ROWNUM/FETCH FIRST logic in SQL is critical for performance.
-- Classic Report with pagination SQL
SELECT *
FROM (
SELECT employee_id, employee_name, salary,
ROWNUM AS rn
FROM (
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY employee_name
)
WHERE ROWNUM <= :P1_PAGE_NUM * :P1_PAGE_SIZE
)
WHERE rn > (:P1_PAGE_NUM - 1) * :P1_PAGE_SIZE;
-- Modern Oracle 12c+ pagination
SELECT employee_id, employee_name, salary
FROM employees
ORDER BY employee_name
OFFSET (:P1_PAGE_NUM-1)*:P1_PAGE_SIZE ROWS
FETCH NEXT :P1_PAGE_SIZE ROWS ONLY;
-- APEX server-side AJAX call (JS)
apex.server.process('LOAD_MORE_ROWS', {
pageItems: '#P1_LAST_EMP_ID'
}, {
success: function(data) {
$('#emp-list').append(data.html);
}
});| EMP_ID | EMP_NAME | SALARY |
|---|---|---|
| 104 | Dave Brown | 70,000 |
| 102 | Bob Smith | 62,000 |
| 103 | Carol White | 55,000 |
Top 3 rows — ROWNUM/FETCH FIRST limits result set
APEX Interactive Reports and Classic Reports have built-in pagination — you set rows per page and pagination type (Row Ranges with Next/Previous, or row-by-row).
The APEX_IR package provides PL/SQL APIs to programmatically manage Interactive Report saved reports, filters, highlights, and settings.
Key procedures include APEX_IR.RESET_REPORT (clear all user customisations), ADD_FILTER (add a WHERE filter), DELETE_FILTER, GET_REPORT (get current report metadata), and CHANGE_REPORT_OWNER.
This is useful for applying default filters on page load, resetting reports via buttons, and building report management features.
-- Reset IR (remove all user customizations)
apex_ir.reset_report(
p_page_id => 2,
p_region_id => apex_ir.g_report_region_id,
p_report_id => apex_ir.g_report_id
);
-- Add a filter programmatically
apex_ir.add_filter(
p_page_id => 2,
p_region_id => 1234567890, -- region static ID or ID
p_report_column => 'DEPARTMENT_ID',
p_filter_value => :P1_DEPT_ID,
p_operator_abbr => 'EQ' -- EQ, NEQ, GT, LT, LIKE, etc.
);
-- Get current report ID
SELECT ir_report_id, report_name, report_type
FROM apex_ir_reports
WHERE application_id = :APP_ID
AND page_id = 2
AND base_report_id = 0;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
The APEX_IR package provides PL/SQL APIs to programmatically manage Interactive Report saved reports, filters, highlights, and settings.
APEX has built-in XSS protection: all item values are HTML-escaped by default when displayed.
For CSRF, APEX generates and validates a hidden session token on every page submission — if the token doesn't match, the request is rejected.
Best practices include: never using APEX_UTIL.RAW_ESCAPE to bypass escaping, always using bind variables in SQL (not concatenation), enabling checksum validation on URL parameters, using HTTPS, setting secure cookie attributes, and restricting public page access properly.
-- APEX automatically escapes output, but verify:
-- In item Display Only: Escape Special Chars = Yes (default)
-- Never concatenate user input into SQL (SQL injection):
-- BAD:
v_sql := 'SELECT * FROM emp WHERE name = ''' || :P1_NAME || '''';
-- GOOD: use bind variables
SELECT * FROM employees WHERE employee_name = :P1_NAME;
-- CSRF: APEX auto-generates hidden token
-- Verify checksum on URL items
apex_util.set_security_group_id;
-- Content Security Policy header
BEGIN
owa_util.mime_header('text/html', FALSE);
htp.p('Content-Security-Policy: default-src ''self''; '
|| 'script-src ''self'' ''unsafe-inline''; '
|| 'style-src ''self'' ''unsafe-inline'';');
owa_util.http_header_close;
END;
-- Restrict page to authenticated users
-- Page → Security → Authentication = Page Requires Authentication| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
APEX has built-in XSS protection: all item values are HTML-escaped by default when displayed.
Oracle APEX 22.1+ introduced Approvals (Task Manager) built-in functionality that allows developers to create human approval workflows without custom code.
You define task definitions with assignees, deadlines, actions, and parameters.
Tasks can be initiated from APEX pages, monitored in a unified My Tasks interface, and integrated with email notifications.
The APEX_APPROVAL package provides PL/SQL APIs to create, complete, and query tasks programmatically.
-- Create approval task from PL/SQL process
DECLARE
v_task_id NUMBER;
BEGIN
v_task_id := apex_approval.create_task(
p_application_id => :APP_ID,
p_task_def_static_id => 'EXPENSE_APPROVAL',
p_initiator => V('APP_USER'),
p_parameters => apex_approval.t_task_parameters(
apex_approval.t_task_parameter(
p_static_id => 'AMOUNT',
p_value => :P1_AMOUNT
),
apex_approval.t_task_parameter(
p_static_id => 'EMP_NAME',
p_value => :P1_EMP_NAME
)
)
);
:P1_TASK_ID := v_task_id;
END;
-- Check task status
SELECT task_id, task_status, assignee, due_on
FROM apex_tasks
WHERE application_id = :APP_ID
AND task_id = :P1_TASK_ID;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Oracle APEX 22.
Oracle APEX 21.1+ supports Progressive Web App (PWA) features including Push Notifications.
You enable PWA mode in Application Definition, configure the push credentials (VAPID keys), and use APEX_PWA.PUSH_NOTIFICATION to send notifications from PL/SQL.
On the client side, a service worker handles the push events.
This enables APEX applications to send real-time alerts to users even when the browser tab is not active.
-- Enable PWA in Application Definition
-- Application → Definition → Progressive Web App → Yes
-- Generate VAPID keys (once per instance)
apex_pwa.generate_vapid_keys;
-- Subscribe user to push notifications (JS)
apex.pwa.subscribe();
-- Send push notification from PL/SQL
BEGIN
FOR r IN (
SELECT subscription_id
FROM apex_pwa_push_subscriptions
WHERE application_id = :APP_ID
AND user_name = UPPER(:P1_TARGET_USER)
) LOOP
apex_pwa.push_notification(
p_subscription_id => r.subscription_id,
p_title => 'Expense Approved',
p_body => 'Your expense of $' || :P1_AMOUNT || ' was approved.',
p_icon => '/images/app-icon.png',
p_target_url => apex_page.get_url(p_page => 10)
);
END LOOP;
END;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned
Oracle APEX 21.
APEX_DEBUG is the package for writing debug messages in APEX applications.
You can write messages at different log levels (INFO, WARN, ERROR) using APEX_DEBUG.MESSAGE, APEX_DEBUG.WARN, APEX_DEBUG.ERROR.
Debug mode can be enabled from the URL (&p_debug=YES), from the Developer Toolbar, or programmatically.
Debug messages are stored in APEX_DEBUG_MESSAGES and visible in the Debug view in APEX Builder.
This is essential for troubleshooting complex applications in production without affecting all users.
-- Enable debug in URL
f?p=100:1:&APP_SESSION.:::::&p_debug=YES
-- Write debug messages in PL/SQL
BEGIN
apex_debug.message('Starting salary update for emp: %s', :P1_EMP_ID);
apex_debug.info('Old salary: %s, New salary: %s', :P1_OLD_SAL, :P1_NEW_SALARY);
UPDATE employees SET salary = :P1_NEW_SALARY
WHERE employee_id = :P1_EMP_ID;
IF SQL%ROWCOUNT = 0 THEN
apex_debug.warn('No rows updated for emp_id: %s', :P1_EMP_ID);
ELSE
apex_debug.message('Update successful: %s row(s)', SQL%ROWCOUNT);
END IF;
END;
-- Query debug messages
SELECT message_timestamp, component_name, message
FROM apex_debug_messages
WHERE page_id = 1
AND session_id = :APP_SESSION
ORDER BY message_timestamp;| EMP_ID | EMP_NAME | DEPT_ID | SALARY |
|---|---|---|---|
| 101 | Alice Johnson | 10 | 50,000 |
| 102 | Bob Smith | 20 | 62,000 |
| 103 | Carol White | 10 | 55,000 |
| 104 | Dave Brown | 20 | 70,000 |
All 4 rows returned — ordered by salary ASC
APEX_DEBUG is the package for writing debug messages in APEX applications.
REST stands for Representational State Transfer.
It is an architectural style for designing networked applications using HTTP.
REST treats every resource as a URL-addressable object and is stateless — each request contains all information needed.
REST uses standard HTTP methods: GET (read), POST (create), PUT (update), DELETE (delete), PATCH (partial update).
A web service following REST principles is called a RESTful web service.
| Constraint | Meaning |
|---|---|
| Stateless | Each request is independent — no server session |
| Client-Server | UI and data are separated |
| Cacheable | Responses can be cached |
| Uniform Interface | Standard HTTP methods and URLs |
REST = Representational State Transfer. Stateless architectural style using HTTP methods (GET, POST, PUT, DELETE) to interact with resources identified by URLs.
GET retrieves data — safe and idempotent, no body needed.
POST creates a new resource — sends data in body, not idempotent (each call creates new record).
PUT updates an existing resource completely — replaces entire resource, idempotent.
PATCH partially updates — only changed fields.
DELETE removes a resource — idempotent.
HEAD returns only headers.
OPTIONS returns supported methods — used in CORS preflight.
| Method | CRUD | Body | Idempotent | Safe |
|---|---|---|---|---|
| GET | Read | No | Yes | Yes |
| POST | Create | Yes | No | No |
| PUT | Update (full) | Yes | Yes | No |
| PATCH | Update (partial) | Yes | No | No |
| DELETE | Delete | No | Yes | No |
GET=Read, POST=Create, PUT=Full Update, PATCH=Partial, DELETE=Remove. Idempotent = same result if called multiple times.
HTTP status codes are 3-digit numbers indicating the result of a request. 2xx = Success: 200 OK, 201 Created, 204 No Content. 3xx = Redirection. 4xx = Client Error: 400 Bad Request, 401 Unauthorized, 403 Forbidden, 404 Not Found, 405 Method Not Allowed, 422 Unprocessable Entity. 5xx = Server Error: 500 Internal Server Error, 503 Service Unavailable.
| Code | Name | When Used |
|---|---|---|
| 200 | OK | Successful GET, PUT, DELETE |
| 201 | Created | Successful POST |
| 204 | No Content | Success, no body returned |
| 400 | Bad Request | Invalid input |
| 401 | Unauthorized | No valid auth |
| 403 | Forbidden | No permission |
| 404 | Not Found | Resource doesn't exist |
| 500 | Internal Server Error | Unexpected server error |
2xx=Success, 4xx=Client Error, 5xx=Server Error. Most important: 200, 201, 400, 401, 403, 404, 500.
ORDS (Oracle REST Data Services) is Java-based middleware that converts Oracle Database tables and PL/SQL into REST API endpoints exposing data as JSON over HTTP.
In Oracle APEX, ORDS is the engine powering REST Enabled SQL, RESTful Services, and APEX itself.
Create a REST API in ORDS by defining a Module (base path), Resource Template (URL pattern), and Handler (SQL/PL/SQL that runs).
-- Format: https://server/ords/schema/module/template
GET /customers/employees/ -- get all
GET /customers/employees/1 -- get one by ID
POST /customers/employees/ -- create (JSON body)
PUT /customers/employees/1 -- update (JSON body)
DELETE /customers/employees/1 -- deleteORDS converts Oracle DB into REST API. Module = base path grouping, Template = URL pattern, Handler = SQL/PL/SQL logic. APEX runs ON TOP of ORDS.
JSON (JavaScript Object Notation) is a lightweight data-interchange format using key-value pairs.
REST APIs use JSON because it is much smaller than XML (less bandwidth), natively supported in JavaScript, universally supported in all programming languages, and maps naturally to objects/arrays.
ORDS automatically returns JSON from Collection Query handlers with items array, hasMore, count, and links.
-- Single object: { "id":1, "name":"Tharun", "dept":"IT", "salary":5000 }
-- ORDS array response:
{ "items":[{"id":1,"name":"Tharun"},{"id":2,"name":"Ishwarya"}], "hasMore":false, "count":2 }JSON = lightweight key-value format. Smaller than XML, human-readable, natively supported in JavaScript. ORDS wraps arrays in items[] with hasMore and count.
Stateless means the server stores NO client session information between requests.
Every request must contain ALL information needed — authentication, parameters, context.
The server treats each request as new and independent.
State lives in the CLIENT (browser, app), not the server.
Benefits: scalability (any server handles any request), cacheability, simplicity.
Stateless = server stores NO session. Each request is self-contained with all needed info. Benefits: scalability, cacheability, simplicity.
REST is an architectural style using HTTP and JSON — lightweight, stateless, suitable for web/mobile.
SOAP (Simple Object Access Protocol) is a strict protocol using XML envelopes, requires WSDL contract, always uses POST, heavier but enterprise-grade.
REST is the modern standard.
SOAP is common in banking, insurance, and legacy systems needing strict contracts and WS-Security.
| Feature | REST | SOAP |
|---|---|---|
| Format | JSON / XML | XML only |
| HTTP Method | GET, POST, PUT, DELETE | Always POST |
| Contract | Not required | WSDL required |
| Speed | Faster / Lightweight | Slower / Heavier |
| Used in | Modern Web/Mobile | Enterprise / Legacy |
REST = lightweight JSON over HTTP. SOAP = strict XML protocol with WSDL. REST is modern standard; SOAP is enterprise legacy.
Use APEX_WEB_SERVICE.MAKE_REST_REQUEST with p_url (endpoint), p_http_method (GET/POST/PUT/DELETE), p_body (JSON body for POST/PUT), and p_http_headers.
Returns CLOB response.
Parse JSON using APEX_JSON or JSON_TABLE.
For no-code approach use Web Source Modules in APEX — define the REST source declaratively and use directly in report regions.
-- GET:
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url => 'https://api.weather.com/data?q=London&appid=KEY',
p_http_method => 'GET'
);
APEX_JSON.PARSE(l_response);
DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_VARCHAR2(p_path => 'name'));
-- POST:
APEX_WEB_SERVICE.SET_REQUEST_HEADERS(p_name_01=>'Content-Type', p_value_01=>'application/json');
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url => 'https://your-api.com/employees',
p_http_method => 'POST',
p_body => '{"name":"Tharun","dept":"IT","salary":5000}'
);APEX_WEB_SERVICE.MAKE_REST_REQUEST calls REST APIs from PL/SQL. Returns CLOB. Parse with APEX_JSON or JSON_TABLE. Web Source Modules = no-code alternative for display.
Idempotency means calling the same endpoint multiple times gives the same result as once.
GET: always returns same data — idempotent.
PUT: updating same record ten times gives same final state — idempotent.
DELETE: deleting already-deleted gives same end state (record gone) — idempotent.
POST: each call creates a new record — NOT idempotent.
PATCH: generally not idempotent.
Idempotent = multiple calls give same result. GET, PUT, DELETE are idempotent. POST is NOT — each call creates a new resource.
Step 1: Create database table.
Step 2: SQL Workshop → RESTful Services → Modules → Create Module (Name, Base Path /customers/, Status=Published).
Step 3: Create Templates: employees/ (all records) and employees/:id (single with ID).
Step 4: Create Handlers — GET Collection Query (SELECT *), POST PL/SQL (INSERT), GET One Row, PUT (UPDATE WHERE id=:id), DELETE (DELETE WHERE id=:id).
Step 5: Test GET in browser, POST/PUT/DELETE in Postman.
-- GET all: SELECT id,name,dept,city,salary FROM employees ORDER BY id;
-- POST: INSERT INTO employees(name,dept,city,salary) VALUES(:name,:dept,:city,:salary); COMMIT;
-- PUT: UPDATE employees SET name=:name,dept=:dept,city=:city,salary=:salary WHERE id=:id; COMMIT;
-- DEL: DELETE FROM employees WHERE id=:id; COMMIT;ORDS REST = Module + Template + Handler. Two templates: without :id (GET all/POST) and with :id (GET one/PUT/DELETE). Test GET in browser, others in Postman.
PUT replaces the ENTIRE resource — must send ALL fields.
Omitting a field sets it to null.
PATCH partially updates — send only changed fields, others stay unchanged.
Example: changing only salary with PUT requires sending name, dept, city, salary.
With PATCH only salary is sent.
In ORDS practice, PUT is more commonly implemented as it is simpler to code.
-- PUT: send ALL fields (omitting city makes city NULL!)
{ "name":"Tharun K", "dept":"IT", "city":"Chemnitz", "salary":6000 }
-- PATCH: send only changed field
{ "salary": 6000 }PUT = full replacement (send ALL). PATCH = partial update (send only changed). Omitting field in PUT sets it null.
APEX_JSON: call APEX_JSON.PARSE(clob) then GET_VARCHAR2, GET_NUMBER, GET_DATE by path.
Use for simple key extraction in PL/SQL.
JSON_TABLE is a SQL function converting JSON arrays into relational rows — better for report regions.
APEX_COLLECTION can store results for use in Classic Report source.
APEX_JSON path uses dot notation: 'main.temp' for nested, 'weather[1].description' for arrays.
-- APEX_JSON:
APEX_JSON.PARSE(l_response);
DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_VARCHAR2(p_path => 'name'));
DBMS_OUTPUT.PUT_LINE(APEX_JSON.GET_NUMBER(p_path => 'main.temp'));
-- JSON_TABLE (for arrays in SQL):
SELECT j.id, j.name, j.salary
FROM JSON_TABLE(l_clob, '$[*]'
COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(100) PATH '$.name')) j;APEX_JSON for simple values in PL/SQL. JSON_TABLE for arrays in SQL regions. Both work with CLOB REST responses.
Basic Authentication sends username:password Base64-encoded in Authorization header — simple but only safe over HTTPS.
Bearer Token (OAuth 2.0) — client first gets a token from auth server, then sends Authorization: Bearer TOKEN in all API calls.
API Key is simpler — static key as header or query param.
In ORDS, protect endpoints using ORDS OAuth 2.0 client credentials flow.
-- Set Bearer Token header:
APEX_WEB_SERVICE.SET_REQUEST_HEADERS(
p_name_01 => 'Authorization',
p_value_01 => 'Bearer ' || :APP_TOKEN,
p_name_02 => 'Content-Type',
p_value_02 => 'application/json'
);Basic Auth = Base64 credentials. Bearer Token = OAuth 2.0 token. API Key = static key. Always use HTTPS. Set headers with APEX_WEB_SERVICE.SET_REQUEST_HEADERS.
CORS (Cross-Origin Resource Sharing) is a browser security mechanism blocking web pages from requesting a different domain.
If APEX on apex.oracle.com calls an API on api.external.com, the browser blocks it unless the API returns Access-Control-Allow-Origin header.
Key: CORS only affects browser JavaScript calls — APEX server-side PL/SQL calls (APEX_WEB_SERVICE) are NOT restricted by CORS.
CORS = browser security blocking cross-domain calls. APEX PL/SQL server-side calls are NOT affected by CORS. Only JavaScript fetch/XHR in browser is restricted.
Headers are key-value pairs providing metadata.
Content-Type tells format of body sent (application/json for REST, text/xml for SOAP).
Accept tells expected response format.
Authorization carries credentials (Basic, Bearer token).
Cache-Control controls caching.
In ORDS/APEX: Content-Type: application/json is required for POST/PUT requests, Authorization for secured endpoints.
| Header | Used In | Example Value |
|---|---|---|
| Content-Type | POST/PUT requests | application/json |
| Accept | All requests | application/json |
| Authorization | Secured endpoints | Bearer eyJhbGci... |
Content-Type (body format), Authorization (auth token), Accept (expected response). Content-Type: application/json required for POST/PUT in ORDS.
Versioning lets you make breaking changes without breaking existing clients.
Most common: URL versioning (/api/v1/employees, /api/v2/employees).
Also: header versioning (Accept: version=2 in header) and query parameter (?version=2).
In ORDS create separate modules with different base paths: customers.api.v1 at /v1/customers/ and customers.api.v2 at /v2/customers/.
Versioning prevents breaking existing clients. URL versioning (/v1/, /v2/) is most common. In ORDS create separate modules per version.
Pagination limits records per call, preventing performance issues.
ORDS automatically adds pagination to Collection Query handlers.
Response includes hasMore, limit, offset, count, and links with rel=next.
Default is 25 records.
Control with ?limit=10&offset=0.
Set default in Module definition.
For custom pagination use OFFSET FETCH NEXT in SQL handler.
{ "items":[...], "hasMore":true, "limit":25, "offset":0, "count":25,
"links":[{"rel":"next","href":".../employees/?offset=25"}] }ORDS auto-paginates. Response has hasMore, limit, offset, count, links.next. Control with ?limit=N&offset=N. Default set in Module.
Return meaningful HTTP status codes with structured JSON bodies.
Never 200 OK for errors.
Use 400 bad input, 401 auth failure, 403 no permission, 404 not found, 422 validation, 500 server error.
JSON body should include: status code, error type, human-readable message, timestamp.
In ORDS use RAISE_APPLICATION_ERROR in PL/SQL handlers to trigger error responses.
-- Good error JSON: { "status":404, "error":"Not Found", "message":"Employee 999 not found" }
-- ORDS PL/SQL handler:
BEGIN
IF :salary <= 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive'); END IF;
UPDATE employees SET salary=:salary WHERE id=:id; COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20002, 'Employee not found: '||:id);
END;Never 200 for errors. Use correct HTTP codes. Include status, error type, message in JSON body. In ORDS use RAISE_APPLICATION_ERROR to trigger error responses.
APEX_WEB_SERVICE is a PL/SQL API — write code to call REST endpoint, parse JSON manually.
Full control, all HTTP methods, needed for write operations.
Web Source Modules are declarative — define REST source in APEX UI, APEX auto-generates calls.
Mainly GET only, use directly in report regions with no code.
Choose Web Source for display, APEX_WEB_SERVICE for complex/write operations.
| Feature | APEX_WEB_SERVICE | Web Source Module |
|---|---|---|
| Approach | PL/SQL code | Declarative UI |
| Methods | GET, POST, PUT, DELETE | Mainly GET |
| JSON Parsing | Manual (APEX_JSON) | Automatic |
| Best For | Complex / write ops | Simple display |
Web Source = declarative, easy, GET-focused. APEX_WEB_SERVICE = programmatic, full control, all methods. Use Web Source for display, APEX_WEB_SERVICE for complex logic.
ORDS OAuth 2.0 Client Credentials: create privilege in ORDS, create OAuth client with privilege.
Client gets client_id and client_secret.
First POST to /ords/schema/oauth/token to get Bearer token, then include in all API calls.
ORDS Privilege: assign privilege role to module/template.
Always use HTTPS.
On ADB additional ACL controls apply at database level.
-- Get token:
POST /ords/schema/oauth/token
grant_type=client_credentials&client_id=ID&client_secret=SECRET
-- Use token:
GET /ords/schema/customers/employees/
Authorization: Bearer eyJhbGci...Secure ORDS with OAuth 2.0 — get token first, include as Bearer in all calls. Always HTTPS. Create ORDS Privileges for module-level protection.
SOAP stands for Simple Object Access Protocol.
It is a messaging protocol for exchanging structured information in web services using XML over HTTP.
Unlike REST (architectural style), SOAP is a strict protocol with defined rules.
Every SOAP message must follow XML envelope structure with Header and Body sections.
SOAP is platform-independent — any system that can process XML can use it.
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header><!-- Optional: auth, transaction --></soap:Header>
<soap:Body>
<HelloRequest xmlns="http://learnwebservices.com/services/hello">
<Name>Tharun</Name>
</HelloRequest>
</soap:Body>
</soap:Envelope>SOAP = Simple Object Access Protocol. XML over HTTP. Strict protocol: Envelope → Header (optional) → Body (mandatory).
WSDL = Web Services Description Language.
An XML document describing a SOAP service — operations available, input/output types, SOAPAction values, and endpoint URL.
It is the contract.
Access by adding ?WSDL to service URL.
Key sections: types (data types), portType (operation names), binding (SOAPAction values, protocol), service (endpoint URL to call).
| Section | Contains |
|---|---|
| types | XSD schema — field names and data types |
| portType | Available operation names |
| binding | SOAPAction values, HTTP transport |
| service | Endpoint URL to call |
WSDL = SOAP contract. Contains operations, types, SOAPAction, endpoint. Access by appending ?WSDL to URL.
SOAPAction is an HTTP header telling the server which operation to execute.
Since all SOAP requests POST to the same URL, SOAPAction differentiates operations (e.g. http://tempuri.org/Add vs http://tempuri.org/Subtract).
Get the value from the WSDL binding section.
Some services use empty SOAPAction ("") — operation identified by XML body element name instead.
In APEX: pass as p_action parameter in MAKE_REQUEST.
SOAPAction identifies which operation to run. All SOAP requests POST to same URL — SOAPAction differentiates them. Get value from WSDL binding. Pass as p_action in MAKE_REQUEST.
Use APEX_WEB_SERVICE.MAKE_REQUEST with: p_url (endpoint from WSDL), p_action (SOAPAction from WSDL binding), p_envelope (complete XML SOAP envelope string).
Returns XMLTYPE.
Extract values using XMLTYPE.EXTRACT with XPath and namespace declarations.
On Oracle ADB service must be HTTPS — HTTP gives ORA-20987.
DECLARE
l_envelope VARCHAR2(4000); l_response XMLTYPE; l_result VARCHAR2(500);
BEGIN
l_envelope := '<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/><soapenv:Body>
<HelloRequest xmlns="http://learnwebservices.com/services/hello">
<Name>Tharun</Name>
</HelloRequest>
</soapenv:Body></soapenv:Envelope>';
l_response := APEX_WEB_SERVICE.MAKE_REQUEST(
p_url=>'https://apps.learnwebservices.com/services/hello', p_action=>'', p_envelope=>l_envelope);
l_result := l_response.EXTRACT('//ns:Message/text()','xmlns:ns="http://learnwebservices.com/services/hello"').GETSTRINGVAL();
DBMS_OUTPUT.PUT_LINE(l_result); -- Hello Tharun!
END;APEX SOAP = MAKE_REQUEST(p_url, p_action, p_envelope). Returns XMLTYPE. Extract with XMLTYPE.EXTRACT + XPath. ADB requires HTTPS.
XML namespaces prevent element name conflicts. xmlns:soap declares soap prefix for envelope, service namespace scopes operation elements.
Without namespaces parser cannot distinguish SOAP structure elements from business elements.
In APEX parsing with XMLTYPE.EXTRACT or XMLTABLE — must declare ALL namespaces in XPath including soap envelope namespace.
Missing namespace causes ORA-19228 error.
Namespaces prevent XML name conflicts. Declare ALL namespaces in XPath including soap envelope namespace. Missing namespace = ORA-19228 error.
SOAP 1.1: namespace http://schemas.xmlsoap.org/soap/envelope/, Content-Type text/xml, SOAPAction is a separate HTTP header, fault codes Client/Server.
SOAP 1.2: namespace http://www.w3.org/2003/05/soap-envelope, Content-Type application/soap+xml, SOAPAction inside Content-Type header, fault codes Sender/Receiver.
Check WSDL for soap:binding (1.1) vs soap12:binding (1.2).
Most services support both.
1.1 = text/xml + separate SOAPAction header. 1.2 = application/soap+xml + SOAPAction inside Content-Type. Check WSDL binding section.
Single values: XMLTYPE.EXTRACT(xpath, namespace_string).GETSTRINGVAL() or GETNUMBERVAL().
Multiple rows: XMLTABLE SQL function with XMLNAMESPACES (declare all namespaces), row XPath (points to repeating element), COLUMNS (child elements become columns).
Large responses (200+ rows): store XMLTYPE in variable, load into APEX_COLLECTION — do NOT embed in dynamic SQL (causes ORA-06502).
FOR r IN (SELECT x.iso_code, x.country_name
FROM XMLTABLE(XMLNAMESPACES(
'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
'http://www.oorsprong.org/websamples.countryinfo' AS "m"),
'/soap:Envelope/soap:Body/m:ListOfCountryNamesByNameResponse/m:ListOfCountryNamesByNameResult/m:tCountryCodeAndName'
PASSING l_xml
COLUMNS iso_code VARCHAR2(10) PATH 'm:sISOCode', country_name VARCHAR2(200) PATH 'm:sName') x)
LOOP
APEX_COLLECTION.ADD_MEMBER('SOAP_COUNTRIES', p_c001=>r.iso_code, p_c002=>r.country_name);
END LOOP;Single: XMLTYPE.EXTRACT(xpath,ns).GETSTRINGVAL(). Multiple: XMLTABLE + XMLNAMESPACES. Large: APEX_COLLECTION to avoid ORA-06502.
SOAP Fault is the error response inside soap:Body when service cannot process the request.
Contains: faultcode (Client=bad input, Server=server error), faultstring (human-readable description), faultactor (optional — which service), detail (optional — technical details).
SOAP Fault always returns HTTP 500 regardless of whether it is client or server error — unlike REST which uses 4xx for client errors.
Always check for Fault before extracting normal response data.
l_fault := l_response.EXTRACT('//soap:Fault/faultstring/text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"').GETSTRINGVAL();
IF l_fault IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'SOAP Error: ' || l_fault);
END IF;SOAP Fault = error in soap:Body. faultcode (Client/Server) + faultstring. Always HTTP 500. Check for Fault before extracting normal response.
Wrap all SOAP calls in a dedicated PL/SQL package (e.g.
PCK_SOAP_API).
Centralizes URL, endpoint, envelope logic.
Functions return data (VARCHAR2/CLOB).
Procedures load APEX Collections for report regions.
Call from APEX Dynamic Actions (Execute Server-side Code) or Before Header processes.
Package makes code reusable across pages and applications.
CREATE OR REPLACE PACKAGE PCK_SOAP_API AS
FUNCTION f_SOAP_API_Hello(p_name VARCHAR2) RETURN VARCHAR2;
FUNCTION f_Get_Countries RETURN CLOB;
PROCEDURE p_Load_Countries;
END PCK_SOAP_API;
-- Dynamic Action: :P220_RESULT := PCK_SOAP_API.f_SOAP_API_Hello(:P220_YOUR_NAME);
-- Before Header: BEGIN PCK_SOAP_API.p_Load_Countries; END;
-- Classic Report: SELECT c001, c002 FROM apex_collections WHERE collection_name='SOAP_COUNTRIES';Wrap SOAP in package. Functions return data. Procedures load APEX Collections. Before Header loads collections for report regions.
APEX_COLLECTION is session-level temporary storage.
Columns: c001-c050 (VARCHAR2), n001-n005 (NUMBER), clob001 (CLOB).
Useful for SOAP because large XML responses cannot be embedded in dynamic SQL (causes ORA-06502 value conversion error).
Workflow: SOAP API call → parse XML with XMLTABLE → insert rows into APEX_COLLECTION → query collection in Classic Report via apex_collections view.
IF APEX_COLLECTION.COLLECTION_EXISTS('MY_COL') THEN
APEX_COLLECTION.DELETE_COLLECTION('MY_COL');
END IF;
APEX_COLLECTION.CREATE_COLLECTION('MY_COL');
APEX_COLLECTION.ADD_MEMBER('MY_COL', p_c001=>'DE', p_c002=>'Germany');
-- Query:
SELECT c001 AS code, c002 AS country FROM apex_collections WHERE collection_name='MY_COL';APEX_COLLECTION = session temp storage. c001-c050=VARCHAR2, n001-n005=NUMBER. Avoids ORA-06502. Query via apex_collections view.
Oracle ADB blocks all outbound HTTP by default — only HTTPS is allowed.
HTTP SOAP services give ORA-20987 (URL prohibited).
Solutions: 1) Use HTTPS SOAP services only (URL starts with https://). 2) Configure ACL using DBMS_NETWORK_ACL_ADMIN as DBA — requires DBA role not available to regular APEX schema. 3) OCI Console network settings.
Always design SOAP integrations with HTTPS-only services on ADB.
ADB blocks HTTP — HTTPS only. ORA-20987 = URL prohibited. Always use HTTPS SOAP on ADB. DBMS_NETWORK_ACL_ADMIN needs DBA role.
WS-Security is message-level security for SOAP (vs HTTPS = transport-level).
Security info goes in SOAP Header: UsernameToken (username + password/hash), BinarySecurityToken (X.509 cert), Signature (digital signature), Timestamp (prevents replay).
Message is secure even through intermediaries — HTTPS only secures point-to-point.
Used in banking, healthcare, government SOAP services requiring non-repudiation and message integrity.
<soap:Header>
<wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/...">
<wsse:UsernameToken>
<wsse:Username>tharun</wsse:Username>
<wsse:Password>mypassword</wsse:Password>
<wsu:Created>2026-05-30T10:00:00Z</wsu:Created>
</wsse:UsernameToken>
</wsse:Security>
</soap:Header>WS-Security = message-level security in SOAP Header. Supports UsernameToken, certificates, signatures. Secure through intermediaries unlike HTTPS.
Choose SOAP when: formal WSDL contracts with strict type validation needed (banking/finance), WS-Security for message-level security, ACID transactions (WS-AtomicTransaction), integrating with legacy systems exposing only SOAP (SAP, older Oracle EBS), or organisation compliance mandates XML/SOAP.
In APEX projects — if third-party system only provides SOAP, you must use it.
If you control both ends, prefer REST.
| Scenario | Use SOAP | Use REST |
|---|---|---|
| Banking / Payment | Yes | Some modern |
| Legacy SAP/EBS | Yes — only SOAP | No |
| Mobile/Web API | No | Yes |
| New ORDS API | No | Yes |
SOAP for legacy/banking/strict contracts. REST for new APIs/mobile/microservices. In APEX use SOAP when external system forces it.
Step 1 — WSDL: open serviceURL?WSDL, identify operations, input/output names, SOAPAction values, endpoint.
Step 2 — Postman: POST, Content-Type: text/xml, SOAPAction header, raw XML body.
Verify response XML and namespace prefixes.
Step 3 — SQL Workshop: build envelope, APEX_WEB_SERVICE.MAKE_REQUEST, XMLTYPE.EXTRACT result.
Step 4 — Package PCK_SOAP_API: spec + body with functions/procedures.
Step 5 — APEX Page: text item + button + Dynamic Action for simple, or Before Header process + collection + Classic Report for lists.
Step 6 — Documentation region.
| Step | Tool | What You Do |
|---|---|---|
| 1. WSDL | Browser | Find operations, SOAPAction, endpoint, namespaces |
| 2. Postman | Postman | Test all operations, verify response XML |
| 3. PL/SQL | SQL Workshop | MAKE_REQUEST + XMLTYPE.EXTRACT |
| 4. Package | SQL Workshop | PCK_SOAP_API spec + body |
| 5. Page | Page Designer | Items + DA or Collection + Report |
| 6. Docs | Page Designer | Static Content documentation |
SOAP integration: WSDL → Postman → SQL Workshop → Package → APEX Page. Test each step. Use APEX_COLLECTION for multi-row XML responses.
REST: MAKE_REST_REQUEST returns CLOB (JSON text).
Parse with APEX_JSON (GET_VARCHAR2/GET_NUMBER) or JSON_TABLE for arrays.
Web Source Modules give declarative no-code option.
SOAP: MAKE_REQUEST returns XMLTYPE (XML object).
Parse with XMLTYPE.EXTRACT + XPath + namespace declarations, or XMLTABLE for multiple rows.
No declarative equivalent — must write PL/SQL.
Key: REST=CLOB/JSON, SOAP=XMLTYPE/XML, namespaces required for SOAP.
| Feature | REST in APEX | SOAP in APEX |
|---|---|---|
| APEX function | MAKE_REST_REQUEST | MAKE_REQUEST |
| Returns | CLOB (JSON) | XMLTYPE (XML) |
| Parse single | APEX_JSON.GET_VARCHAR2 | XMLTYPE.EXTRACT + XPath |
| Parse multiple | JSON_TABLE | XMLTABLE + XMLNAMESPACES |
| No-code option | Web Source Modules | None — must code |
| Namespaces | Not required | Required — ORA-19228 if missing |
REST=CLOB/JSON, SOAP=XMLTYPE/XML. REST has Web Source Modules (no-code). SOAP needs PL/SQL + namespaces. Both support APEX_COLLECTION for storing results.