Universal Search Box for Oracle APEX

A demo showing how to build a Universal Search Box in Oracle APEX using the Universal Theme. A single search widget lets users navigate to any entity — employees, departments, jobs, locations, countries, regions, or application pages — from one place.

How it works

All searchable entities are aggregated into a single database view (v_universal_search_app500) with two columns:

• entry_text — a prefixed, human-readable label (e.g. Employee: John Doe (101))
• entry_target — the APEX URL to navigate to on selection

The search widget queries this view and redirects the user to entry_target when they pick a result.

Files

File	Description
DDL.sql	Creates the Oracle HR sample schema: REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, JOBS, EMPLOYEES, JOB_HISTORY, EMP_DETAILS_VIEW
DML.sql	Populates the HR schema with sample data
Universal Search view.sql	Creates v_universal_search_app500, the core search view
f500_AutoComplete.sql	APEX app export using the built-in AutoComplete item type
f500_SELECT2.sql	APEX app export using the SELECT2 plug-in

Installation

Run the scripts in this order, connected to SQL*Plus as the parsing schema owner (e.g. RBARATA) or as APEX_180200:

@DDL.sql
@DML.sql
@"Universal Search view.sql"

Then import either APEX application export:

• f500_AutoComplete.sql — no plug-in dependencies, uses the APEX built-in AutoComplete item
• f500_SELECT2.sql — uses the SELECT2 plug-in for a richer autocomplete experience

Both target App ID 500 and APEX 18.2. You can override the app ID at import time using apex_application_install.

Requirements

• Oracle Database (tested with the HR sample schema)
• Oracle APEX 18.2 or later
• SQL*Plus or a compatible client for running the scripts

Extending the search

To add a new entity type, add a UNION ALL block to v_universal_search_app500:

UNION ALL
SELECT
    'MyEntity: ' || display_column,
    'f?p=&' || 'APP_ID.:<page>:&' || 'SESSION.::&' || 'DEBUG.:RP,<page>:<item>:' || id_column
FROM my_table

The & is split across string literals to prevent APEX from interpreting it as a substitution string when the view source is stored.

APEX URL format

Navigation targets use the standard APEX URL pattern with the RP cache group to reset page item state on arrival:

f?p=&APP_ID.:<page>:&SESSION.::&DEBUG.:RP,<page>:<item>:<value>