6.4.1 Custom Unified Search Experience

The Search region manages a search field and displays results using result row templates. When this region's user interface doesn't meet your requirements, you can build a custom search experience using the SEARCH function in the APEX_SEARCH package.

This function accepts a search term and list of search configuration static IDs. It returns a table of search results you can use in the FROM clause of a SQL query source in any APEX region. Simply wrap the function call with the table() operator. Each result row contains standard column names that match the substitutions available in the Search region's Result Row Template. This example query returns the results of searching the regions with static ID employees and departments using a search term from the P31_SEARCH page item:

select *
  from table(
         apex_search.search(
           p_search_expression => :P31_SEARCH,
           p_search_static_ids => apex_t_varchar2('employees',
                                                  'departments')))

If you include an ORDER BY clause in the query, then pass one additional parameter to avoid unnecessary sorting as follows:

select *
  from table(
         apex_search.search(
           p_search_expression => :P31_SEARCH,
           p_search_static_ids => apex_t_varchar2('employees',
                                                  'departments'),
           p_apply_order_bys   => 'N'))
 order by title

This approach lets you collect the search term in any way necessary and display results in Cards, Map, Chart, Classic Report, template component, or any other kind of APEX region.

See the Oracle APEX API Reference APEX_SEARCH for the full list of standard columns you can reference. The list includes columns like PRIMARY_KEY_1 and PRIMARY_KEY_2, TITLE, SUBTITLE, DESCRIPTION, and BADGE. You can also map three custom columns in your search configuration that show up in the results. For example, if your search configuration contains a LATITUDE and LONGITUDE column, you can map those to Custom Column 1 and Custom Column 2 and then refer to their values in the search results using columns CUSTOM_01 and CUSTOM_02.