How to create a global search bar in Oracle Application Express

A feature that is becoming increasingly common in web applications is a global search utility.

Imagine an accounting application where there is a single search box that can be used to search for accounts, client names, supplier names or invoice numbers.

Or, a medical clinic management application where a single search box allows searching for doctor names, patient names, appointment dates or medicines.

That is, something like this: 

It is quite easy to create this in Oracle Application Express, for any application using the Universal Theme.

To do so, just follow these simple steps:

1 - Select database tables or views to be searched

Identify “objects” in your application that a user would like to search by number, name or code -- things like “Users”, “Contracts”, “Invoices”, “Clients” or “Branches”.

As an example, consider an application that contains the tables defined in

https://github.com/oracle/db-sample-schemas/blob/master/human_resources/hr_cre.sql

populated as per https://github.com/oracle/db-sample-schemas/blob/master/human_resources/hr_popul.sql

That is, the database tables are

• COUNTRIES
• LOCATIONS
• DEPARTMENTS
• JOBS
• EMPLOYEES
• JOB_HISTORY

Of these, the only database tables that should be searched are the below

Database Table	Object	Search expression
COUNTRIES	Country	COUNTRY_NAME
LOCATIONS	Location	DEPARTMENT_NAME
DEPARTMENTS	Department	FIRST_NAME||' '||LAST_NAME
JOBS	Job	JOB_TITLE
EMPLOYEES	Employee	STREET_ADDRESS||' '||POSTAL_CODE
REGIONS	Region	REGION_NAME

2 - Assign database tables to application pages

The table above now needs to be completed with the pages used to view these objects -- that is, that allow the visualization records in each of the given database tables.

Continuing with the example above, consider the simple application in

https://github.com/rbarata/universal_search_box/blob/master/f500_AutoComplete.sql

It contains one view/edit record page for each of the above database tables. For example, page 10 allows the viewing of records from the DEPARTMENTS database table.

The item that contains the ID of the record to be visualized is P10_EMPLOYEE_ID

With that information, the table above can now be completed:

Database Table	Object	Search expression	View page	ID item
COUNTRIES	Country	COUNTRY_NAME	9	P9_COUNTRY_ID
LOCATIONS	Location	DEPARTMENT_NAME	12	P12_LOCATION_ID
DEPARTMENTS	Department	FIRST_NAME||' '||LAST_NAME	8	P8_DEPARTMENT_ID
JOBS	Job	JOB_TITLE	11	P11_JOB_ID
EMPLOYEES	Employee	STREET_ADDRESS||' '||POSTAL_CODE	10	P10_EMPLOYEE_ID
REGIONS	Region	REGION_NAME	13	P13_REGION_ID

3 - Create a search view

3.1 - Adding the database tables

The information above can be used to create a view that provides the APEX URL that can be used to view each record from the above tables.

For example, consider the COUNTRIES database table

The query below returns, for every record in that database table, both a search text and a URL that opens an APEX page showing the data it contains.

SELECT    'Country: '||country_name AS entry_text,    'f?p=&'||'APP_ID.:9:&'||'SESSION.::&'||'DEBUG.:RP,9:P9_COUNTRY_ID:'||country_id AS entry_targetFROM countries;

This can now be done for all the other database tables in the table above.

Some care must be taken in ensuring that column “entry_text” is guaranteed to be unique.

For instance, consider this query for the “employees” database table:

SELECT    'Employee: '||first_name||' '||last_name,    'f?p=&'||'APP_ID.:10:&'||'SESSION.::&'||'DEBUG.:RP,10:P10_EMPLOYEE_ID:'||employee_idFROM employees

This won’t work properly unless there is a restriction in table "employees" that prevents different records from having the same "first_name" and "last_name", If this doesn’t exist, it is safer to use something like this:

SELECT    'Employee: '||first_name||' '||last_name||' ('||employee_id||')',    'f?p=&'||'APP_ID.:10:&'||'SESSION.::&'||'DEBUG.:RP,10:P10_EMPLOYEE_ID:'||employee_idFROM employees

After all the queries are done, they can be turned into a single combined query by glueing them all with UNION ALL.

SELECT    'Country: '||country_name AS entry_text,    'f?p=&'||'APP_ID.:9:&'||'SESSION.::&'||'DEBUG.:RP,9:P9_COUNTRY_ID:'||country_id AS entry_targetFROM countriesUNION ALLSELECT    'Departments: '||department_name,    'f?p=&'||'APP_ID.:8:&'||'SESSION.::&'||'DEBUG.:RP,8:P8_DEPARTMENT_ID:'||department_idFROM departmentsUNION ALLSELECT    'Employee: '||first_name||' '||last_name||' ('||employee_id||')',    'f?p=&'||'APP_ID.:10:&'||'SESSION.::&'||'DEBUG.:RP,10:P10_EMPLOYEE_ID:'||employee_idFROM employeesUNION ALLSELECT    'Job: '||job_title,    'f?p=&'||'APP_ID.:11:&'||'SESSION.::&'||'DEBUG.:RP,11:P11_JOB_ID:'||job_idFROM jobsUNION ALLSELECT    'Location: '||street_address||' '||postal_code,    'f?p=&'||'APP_ID.:12:&'||'SESSION.::&'||'DEBUG.:RP,12:P12_LOCATION_ID:'||location_idFROM locationsUNION ALLSELECT    'Region: '||region_name,    'f?p=&'||'APP_ID.:13:&'||'SESSION.::&'||'DEBUG.:RP,13:P13_REGION_ID:'||region_idFROM regions

3.2 - Adding the APEX menu entries

Note that the application menu entries can also be treated in the same way as the database tables above. That is, assuming that the id of this APEX application is 500, the below query will return all of the menu entries in the same format as above.

SELECT    'Page: '||entry_text AS entry_text,    entry_targetFROM apex_application_list_entriesWHERE    list_name='Desktop Navigation Menu'    AND application_id=500;

Adding this block to the view allows users to search for menu entries, as shown below.

3.3 - The complete database view

CREATE OR REPLACE VIEW v_universal_search_app500 AS SELECT    'Page: '||entry_text AS entry_text,    entry_targetFROM apex_application_list_entriesWHERE    list_name='Desktop Navigation Menu'    AND application_id=500 UNION ALLSELECT    'Country: '||country_name,    'f?p=&'||'APP_ID.:9:&'||'SESSION.::&'||'DEBUG.:RP,9:P9_COUNTRY_ID:'||country_idFROM countriesUNION ALLSELECT    'Departments: '||department_name,    'f?p=&'||'APP_ID.:8:&'||'SESSION.::&'||'DEBUG.:RP,8:P8_DEPARTMENT_ID:'||department_idFROM departmentsUNION ALLSELECT    'Employee: '||first_name||' '||last_name||' ('||employee_id||')',    'f?p=&'||'APP_ID.:10:&'||'SESSION.::&'||'DEBUG.:RP,10:P10_EMPLOYEE_ID:'||employee_idFROM employeesUNION ALLSELECT    'Job: '||job_title,    'f?p=&'||'APP_ID.:11:&'||'SESSION.::&'||'DEBUG.:RP,11:P11_JOB_ID:'||job_idFROM jobsUNION ALLSELECT    'Location: '||street_address||' '||postal_code,    'f?p=&'||'APP_ID.:12:&'||'SESSION.::&'||'DEBUG.:RP,12:P12_LOCATION_ID:'||location_idFROM locationsUNION ALLSELECT    'Region: '||region_name,    'f?p=&'||'APP_ID.:13:&'||'SESSION.::&'||'DEBUG.:RP,13:P13_REGION_ID:'||region_idFROM regions ;

Note that for this view to work correctly care must be taken to ensure that the first column (entry_text) is unique.

4 - Adding the search to the application

4.1 - Create the search box

Add a region to page 0 — this is where the search box will reside

• Identification > Title: “Navigation Bar Custom Items”
• Layout > Position: “Content Body”
• Appearance > Template: “Blank with Attributes”
• Advanced > Static ID: “navigation_bar_custom_items”
• Server-side Condition > Type: “PL/SQL Function Body”
• Server-side Condition > PL/SQL Function Body:

DECLARE    v_return BOOLEAN;    v_rowcount INTEGER;    BEGIN    v_return := apex_authentication.is_authenticated;    IF v_return THEN        SELECT COUNT(*)        INTO v_rowcount        FROM apex_application_pages        WHERE            application_id = :APP_ID            AND page_id = :APP_PAGE_ID            AND page_mode = 'Normal'            ;              v_return := (v_rowcount > 0);    END IF;      RETURN v_return;END;

The Server-side condition hides this region for pages that are:

• Pop-up pages
• Pages available without authentication, as e.g. the login page

Add an item to region “Navigation Bar Custom Items”

• Identification > Name: “P0_UNIVERSAL_SEARCH”
• Identification > Type: “Text Field with autocomplete”
• Label > Label: (empty)
• Settings > Search: “Contains & Ignore Case”
• Settings > Lazy Loading: Enabled
• Settings > Cache: Enabled
• Settings > Maximum Values List: 10
• Settings > Minimum Search: 2
• Appearance > Template: Optional
• Appearance > Template Options: “Stretch Form Item”
• Appearance > Icon: “fa-search”
• Validation > Value Required: Disabled
• List of Values > SQL Query: “SELECT entry_text from v_universal_search_app500”
• List of Values > Display Extra Values: Enabled
• Advanced > Warn on Unsaved Changes: “Ignore”
• Security > Session State Protection: Unrestricted

Add a second, “filler”, item to the region

• Identification > Name: “P0_FILLER”
• Identification > Type> “Display Only”
• Label > Label: (empty)
• Layout > Start New Row: Disabled
• Layout > New Column: Enabled

Add a third, hidden, item to the region

• Identification > Name: “P0_URL”
• Identification > Type: “Hidden”
• Security > Session State Protection: Unrestricted

This is how it should look in the Builder

… and this is how it looks when running

4.2 - Position the search box

Add a Dynamic Action to page 0

• Identification > Name: “Build Custom Navigation Bar”
• When > Event: “Page Load”

Add a “True” action to the Dynamic Action

• Identification > Action: “Execute JavaScript Code”
• Settings > Code:

$('#navigation_bar_custom_items').insertBefore($('.t-Header-navBar'));

This code positions the search box on the top of the page.

4.3 - Enable the search box

Add another Dynamic Action to page 0

• Identification > Name: “Go”
• When > Event: “Custom”
• When > Custom Event: “ojupdate”
• When > Selection Type: “Item(s)”
• When > Item(s): “P0_UNIVERSAL_SEARCH”

Note that event “ojupdate” is triggered from items of type “Text Field with autocomplete” whenever the value it contains is changed -- as explained here.

Add a “True” action to the Dynamic Action

• Identification > Action: “Execute PL/SQL Code”
• Settings > PL/SQL Code:

DECLARE    v_url VARCHAR2(4000);    BEGIN    SELECT entry_target    INTO v_url    FROM v_universal_search_app500    WHERE entry_text=:P0_UNIVERSAL_SEARCH    ;    v_url := REPLACE(v_url, '&'||'SESSION.', '&SESSION.');    v_url := REPLACE(v_url, '&'||'APP_SESSION.', '&APP_SESSION.');    v_url := REPLACE(v_url, '&'||'DEBUG.', '&DEBUG.');    v_url := REPLACE(v_url, '&'||'APP_ID.', '&APP_ID.');    :P0_URL := APEX_UTIL.PREPARE_URL(        p_url => v_url    );    :P0_UNIVERSAL_SEARCH := NULL;END;

• Settings > Items to Submit: “P0_UNIVERSAL_SEARCH”
• Settings > “Items to Return: “P0_URL,P0_UNIVERSAL_SEARCH”

This code block does the following:

• queries v_universal_search_app500 to get the URL associated with the option clicked by the user
• replaces all the substitution variables (“&SESSION”, etc) with their proper values.
• adds the checksum to the URL, and saves it to hidden item P0_URL
• blanks the original value, so that it does not appear after the page is refreshed.

Add a second “True” action to the Dynamic Action

• Identification > Action: “Execute JavaScript Code”
• Settings > Code:

apex.page.cancelWarnOnUnsavedChanges(); apex.navigation.redirect($v('P0_URL'));

This code block unconditionally redirects the user’s browser to the URL contained in P0_URL.

That’s it! The Universal search bar is ready to go.

5 - Customization

5.1 - Expanding the search

As all the logic that ties particular tables to APEX pages is in the view (v_universal_search_app500 in the example), more tables can be added to it without having to change the application itself.

5.2 - access permissions management

The example application is simple enough that it really doesn’t have any permissions management -- in the sense that any logged-in user can access all the information available in it. This is generally not the case in real-world applications.

But this can be solved by integrating the permission checks into the view itself.

As an example, suppose that there exists a custom function that checks whether a user has access to any given page, by querying the application’s permission management tables:

CREATE OR REPLACE FUNCTION f$is_user_allowed_to_page_YN(    p_user VARCHAR2,    p_page_id INTEGER ) RETURN VARCHAR2   -- returns 'Y' if access is granted, 'N' otherwise ...

This can be integrated into the view, which then becomes

CREATE OR REPLACE VIEW v_universal_search_app500 AS WITH accessible_pages AS (    SELECT pg.page_id    FROM apex_application_pages pg    WHERE        f$is_user_allowed_to_page_YN(            p_user => v('APP_USER'),            p_page_id => pg.page_id        ) = 'Y' )SELECT    'Page: '||entry_text AS entry_text,    entry_targetFROM apex_application_list_entriesWHERE    list_name='Desktop Navigation Menu'    AND application_id=500    AND current_for_pages_expression IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Country: '||country_name,    'f?p=&'||'APP_ID.:9:&'||'SESSION.::&'||'DEBUG.:RP,9:P9_COUNTRY_ID:'||country_idFROM countriesWHERE 9 IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Departments: '||department_name,    'f?p=&'||'APP_ID.:8:&'||'SESSION.::&'||'DEBUG.:RP,8:P8_DEPARTMENT_ID:'||department_idFROM departmentsWHERE 8 IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Employee: '||first_name||' '||last_name||' ('||employee_id||')',    'f?p=&'||'APP_ID.:10:&'||'SESSION.::&'||'DEBUG.:RP,10:P10_EMPLOYEE_ID:'||employee_idFROM employeesWHERE 10 IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Job: '||job_title,    'f?p=&'||'APP_ID.:11:&'||'SESSION.::&'||'DEBUG.:RP,11:P11_JOB_ID:'||job_idFROM jobsWHERE 10 IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Location: '||street_address||' '||postal_code,    'f?p=&'||'APP_ID.:12:&'||'SESSION.::&'||'DEBUG.:RP,12:P12_LOCATION_ID:'||location_idFROM locationsWHERE 12 IN (SELECT page_id FROM accessible_pages)UNION ALLSELECT    'Region: '||region_name,    'f?p=&'||'APP_ID.:13:&'||'SESSION.::&'||'DEBUG.:RP,13:P13_REGION_ID:'||region_idFROM regionsWHERE 13 IN (SELECT page_id FROM accessible_pages) ;

Note: The WITH block is there just to avoid constructs like this:

SELECT    'Location: '||street_address||' '||postal_code,    'f?p=&'||'APP_ID.:12:&'||'SESSION.::&'||'DEBUG.:RP,12:P12_LOCATION_ID:'||location_idFROM locationsWHERE f$is_user_allowed_to_page_YN(            p_user => v('APP_USER'),            p_page_id => 12        ) = 'Y

These tend to kill the performance of the view by forcing Oracle to execute “f$is_user_allowed_to_page_YN” once for every record that exists in the database tables.

5.3 - Changing the look of the search box

As the search box is just a regular APEX item, it can be changed in the same way as any other item in an application -- or even replaced with any item that offers equivalent functionality.

As an example, if P0_UNIVERSAL_SEARCH is changed to use the excellent Select2 plugin, the search box will look like this:

This example can be downloaded from 

https://github.com/rbarata/universal_search_box/blob/master/f500_SELECT2.sql.