Enhancing Search for End-Users

Searching for data is a fundamental part of any enterprise app. Oracle APEX has multiple native components you can use to deliver compelling search experiences tailored to the task at hand.

When end users need to narrow results based on one or more categories of filter criteria, the Faceted Search region presents a familiar paradigm to get the job done. The Smart Filters region offers similar features in a more compact design, leaving even more space for search results. The Search region cooperates with the search configurations you define to format results from across multiple sources in a consistent way. For ultimate control over how end users can specify targeted search criteria, create a query by example page using any of APEX's page item types.

• Narrowing a Search Using Facet Filters
  Use a Faceted Search region to let users narrow results by choosing options from groups called facets.
• More Space for Results with Smart Filters
  The Smart Filters region gives users faceted search functionality in a compact experience.
• Unified Search Results Across Sources
  End users find things on the web by typing a few words into a search bar, reviewing top results, and clicking on promising links to learn more. You can mirror this intuitive search experience in your APEX apps using a Search region that references Search Configurations.
• Showing Search Results in Other Regions
  You can use the dedicated SEARCH API to present results from search configurations in any region. However, when the user filters data with a Faceted Search, Smart Filters, Interactive Report, or Interactive Grid, showing the filtered results in another region requires a different approach.
• Using Page Items for Query by Example
  Page items, including list-driven ones, are useful not only for data entry, but also for query by example use cases. The user can enter a value or pick a choice from a list, and another region on the same page can filter its data based on their choice.

Use a Faceted Search region to let users narrow results by choosing options from groups called facets.

Each facet reflects specific values of an interesting characteristic on which the user might want to filter. Users familiar with internet shopping sites find the experience intuitive. Just as they search for a perfect laptop by indicating preferences in categories like Memory, Screen Size, or Price Range, they can use the same paradigm to find any kind of data in your APEX app.

As shown below, you typically place a faceted search region in the Left Column slot of a page using the Left Side Column page template. It can filter a Classic Report, Cards, or Map region as well as Content Row, template components, and custom plug-ins that support facets. As a user selects criteria, the results refresh automatically. You control all aspects of how the facet behaves at runtime by configuring appropriate settings in the Property Editor.

• Defining Your Facets
  For each facet, you configure a Label, a Type, and a Database Column to filter on.
• Configuring Range Facets
  For Range facets, ensure the source column's data type is NUMBER, DATE, or TIMESTAMP. The return values in the list for the different range intervals uses the vertical bar (|) character to separate a low value and high value.
• Behavior of Multiple Selections
  When using a Checkbox Group or Range facet, if users select multiple options, end users see results that match any of the selected choices as shown below.
• Counts, Dependency, and Charts
  You can enhance the user experience by showing how many rows match each facet option value, optionally sorting by this count, and hiding options with no matches if useful. Facets can depend on other facets, and you can enable a chart view of a facet to help the user see a distribution of results.
• Working with Multi-Value Columns
  If a facet's filtered column potentially stores multiple values in each row, then set the Multiple Values property to either Delimiter or JSON Array. Once selected, additional options appear.
• Displaying Facets in Add Filter Dialog
  When you configure a facet, in the Appearance section of the Property Editor you decide whether it will display inline or appear in the Add Filter dialog.
• Finding Text Across Columns
  By default, every Faceted Search region has a Search facet that lets users find text across columns. It can appear inline in the region, or reference a page item placed elsewhere on the page.
• Showing Filter Chips and Total Row Count
  Offer users additional filtering feedback with "chips" showing applied filters and a total row count.

The Smart Filters region gives users faceted search functionality in a compact experience.

It's perfect when screen space is limited or when your search results need the full page width. It can filter a Classic Report, Cards, or Map region as well as Content Row, template components, and custom plug-ins that support facets. As shown below, it occupies only the space of a single search field on the page.

• Defining Your Filters
  For each filter, you configure a Type, a Label, and a Database Column to filter on.
• Smart Filters User Experience
  Learn about the user experience for Smart Filters.
• Configuring Suggestions
  Sometimes end users appreciate a suggestion for common or popular filters to apply. When no other filters are applied, the Smart Filters region can display suggestion "chips" to help a user get started.
• Even More Space for Search Results
  When you want to dedicate even more space to the search results, you can move the Smart Filters region into the Navigation Bar.

End users find things on the web by typing a few words into a search bar, reviewing top results, and clicking on promising links to learn more. You can mirror this intuitive search experience in your APEX apps using a Search region that references Search Configurations.

• Defining a Search Configuration
  Define a Search Configuration shared component for each kind of source data users need to search. Each one identifies a local or remote data source and its searchable columns. You also choose which columns play standard roles like primary key, title, description, and icon.
• Using Search Configurations
  After adding a Search region to a page, reference one or more search configurations as search sources.
• Search Results Formatting Options
  You can customize how unified search results appear in three ways.

You can use the dedicated SEARCH API to present results from search configurations in any region. However, when the user filters data with a Faceted Search, Smart Filters, Interactive Report, or Interactive Grid, showing the filtered results in another region requires a different approach.

• 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.
• Showing Filtered Data in Another Region
  To show one region's data in another region, you can reference the primary keys of a source region's search results in another region's query or WHERE clause. Whenever the source region's results change, a dynamic action can automatically refresh the target region.

Page items, including list-driven ones, are useful not only for data entry, but also for query by example use cases. The user can enter a value or pick a choice from a list, and another region on the same page can filter its data based on their choice.

You accomplish this by referencing page item values in the other region's WHERE clause. When you use page items values as query by example filters, you can also easily disable the default warning the user receives when leaving the page with unsaved pending changes.

• Using Null Display Value
  When using the Select List, Radio Group, and Popup LOV you can optionally include an item in the list that represents the null value, and if included it can have a custom label.
• Filtering on a Page Item's Value
  You can use any page item's value in a query filter in another region. Most page item types, including single-selection list-driven items, store just one value that can also be null.
• Filtering on a Multi-Selection Page Item
  When a query-by-example item allows multiple selection, filtering by its value requires an IN clause with a subquery.
• Optimal Approach to Query by Example
  Two approaches exist for query-by-example WHERE clause filters. One requires less typing; the other performs better.
• Disabling Warn on Unsaved Changes
  Query-by-example items change values by design. Disable the unsaved changes warning so users aren't confused when navigating away.

For each facet, you configure a Label, a Type, and a Database Column to filter on.

As shown in the Page Designer below, when using the Left Side Column page template, the Layout pane shows the Left Column and Body slots. Notice the Faceted Search region in the Left Column has five facets defined, with the P23_JOB facet selected in the component tree. Facet names look like page item names since they behave identically. At runtime, their values reflect the choices the end user makes in each facet group.

For facets of type Checkbox Group, Radio Group, and Select List you provide a list of values to determine the choices the user sees. You specify the list in the same ways as for a list-valued page item, with one handy additional option: Distinct Values. When selected, APEX computes filter options using the distinct values of the underlying column in rows meeting the currently applied filters.

The figure highlights the Type, Label, and Database Column properties of the selected P23_JOB facet in Page Designer.

For Range facets, ensure the source column's data type is NUMBER, DATE, or TIMESTAMP. The return values in the list for the different range intervals uses the vertical bar (|) character to separate a low value and high value.

For example, the figure below shows the Static Values list for the P23_SAL range facet. The Display Value can use the substitution string #G# for the group (thousands) separator and #D# for the decimal separator. Notice how the Return Value uses the format:

LowValue|HighValue.

Omit either a low value or a high value to indicate an interval that is "unbounded" on that side. Notice the option with display value "<900" has a corresponding return value of |900 with no low value.

For date and timestamp columns, the low and high values use the canonical format YYYYMMDDHH24MISS. For example, a date range option with display value "Before 2020" would have a return value with no low value and a high value representing December 31st, 2019 at 23:59:59 as follows:

|20191231235959

An option with display value "2020 to 2024" would have a corresponding return value with a low value of January 1st, 2020 at 00:00:00 and a high value of December 31st, 2024 at 23:59:59 like this:

20200101000000|20241231235959

When using a Checkbox Group or Range facet, if users select multiple options, end users see results that match any of the selected choices as shown below.

The figure shows an Employees faceted search page with Salary range facet options "900‑1300" and "2000‑2500" selected. Employees with salaries in either range display in the results.

You can enhance the user experience by showing how many rows match each facet option value, optionally sorting by this count, and hiding options with no matches if useful. Facets can depend on other facets, and you can enable a chart view of a facet to help the user see a distribution of results.

You can include counts to show how many rows match each option and sort options by match count. If you show the counts, you can decide whether zero-count options are hidden. You can also choose to show the facet options in descending order by top counts for Checkbox Group and Radio Group facets.

One facet can depend on another, so it appears only if the user picks a value from the other one.

As shown below, you can also configure a facet to let end users review a pie or bar chart to understand how filtered rows are distributed across the facet's option values. The figure shows the Chart modal dialog that appears when the user chooses Show Chart from the Salary facet group's kebab menu. Users can toggle between a pie and bar chart to better understand how matching results are distributed across facet values.

If a facet's filtered column potentially stores multiple values in each row, then set the Multiple Values property to either Delimiter or JSON Array. Once selected, additional options appear.

For example, imagine the EMP table had an additional column INTERESTS of type VARCHAR2(1000) storing multiple values using the colon character as a delimiter. Assume employee SMITH has the following value for this column:

Art:Animation:Urban Sketching:Math

If you configure a P23_INTERESTS facet with colon as the multi-value delimiter and a filter combination setting of AND (Intersect), then SMITH will match when the end user selects both "Animation" and "Math" group filter options, but employees with INTERESTS containing only Art or only Math will not match.

When you configure a facet, in the Appearance section of the Property Editor you decide whether it will display inline or appear in the Add Filter dialog.

As shown below, the end user can click the Add Filter button to access a potentially large number of facets using a modal dialog. The Select One item at the top of the dialog lets users type to find the facet they want to use. Clicking the (Apply) button applies their filter options and dismisses the dialog.

By default, every Faceted Search region has a Search facet that lets users find text across columns. It can appear inline in the region, or reference a page item placed elsewhere on the page.

Configure that columns the search facet will search over by setting the Database Columns property in the Source group of the Search facet. Use a comma-separated list of column names.

Users type one or more words they are looking for. When they press [Enter], APEX finds rows containing any of the words in any column you've configured. If other facet filter options are applied, the results include only rows that match all of the filters. In the figure below, the user types acc followed by cl and the matches include one row with CLARK in Employee Name and ACCOUNTING in the Deptno column and another with CLERK in the Job column and ACCOUNTING in the Deptno column.

Offer users additional filtering feedback with "chips" showing applied filters and a total row count.

Since there can be many inline facets and more in the Add Filter dialog, users find it useful to see what filter criteria from which facets are applied without scrolling or opening dialogs. Use the Show Current Facets property of the Faceted Search region to show "filter chips" that display this information.

The chips display the facet label and applied filter criteria display value, along with an "x" icon users can click to remove a particular filter. A "Clear All" link lets them remove all filters in a single click as well.

Set Show Current Facets to Yes to show the chips at the top of the faceted search region itself. However, it's usually in the Left Column slot that is narrow by design. Instead, set the HTML DOM ID of a static content region in the Body slot to a unique name like active_facets and then set Show Current Facets to Selector. This setting causes the additional Current Facets Selector to appear in the Property Editor. After setting this to #active_facets, referencing the HTML DOM ID of the static content region, the filter chips will display wherever on the page this static content region is positioned.

Set the Show Total Row Count property of the faceted search region to Yes to have the total row count appear at the top of the current facets area. For more flexibility, you can set this property to Selector and provide a CSS selector like #total_count_area to show the count with no label in the static content region having the HTML DOM ID of total_count_area. The figure below shows a faceted search page with both options enabled.

For each filter, you configure a Type, a Label, and a Database Column to filter on.

As shown below, the Smart Filters region in the breadcrumb's Search Field and Smart Filters slot has five filters defined, with the P24_JOB filter selected in the component tree. Filter names look like page item names since they behave the same. At runtime, their values reflect the choices the end user makes in each filter.

For filters of type Checkbox Group and Radio Group you define a list of values to provide the choices the user sees. You configure the list in the same ways as for a list-valued page item, with one extra option: Distinct Values. When selected, APEX shows filter options using the distinct values of the underlying column in rows matching the currently applied filters.

Configuring range facets, counts, dependency, and multi-valued columns works the same way as for the Faceted Search region's facets described in the previous section. When computing counts, you can show the filter options in descending order by top counts for Checkbox Group and Radio Group filters.

Learn about the user experience for Smart Filters.

When a user navigates to the Smart Filters search field, as shown below, they see the display name and icon of each filter you've defined.

When they select a particular filter like Salary, as shown below, a corresponding "chip" appears in the search field with the filter display name and the filter options show in a dropdown. If configured to allow multiple selections, the user can do so. As the user selects filter options, the filtered region immediately updates to reflect the narrowed results.

Once a filter is in use, as shown below, its chip stays visible in the search field to represent applied filters and the dropdown list shows remaining filters. A user can navigate to the Salary filter chip to revise their choices or click its "x" icon to remove it.

If the user picks the Manager filter and selects BLAKE an additional chip appears and results refresh to show employees BLAKE manages whose salary is between 900 and 1300.

At any time the user can type into the search field to enter one or more terms to find across columns. You configure which columns APEX searches using the Database Columns property of the Search filter. As shown below, as the user types into the search field any matching filter options appear in the dropdown as well. For example, after typing cl, suggestions show for Job: CLERK and Manager: CLARK. The user can keep typing to complete their search term and press [Enter] to see the results. Alternatively, they can pick one of the suggested filter options.

Sometimes end users appreciate a suggestion for common or popular filters to apply. When no other filters are applied, the Smart Filters region can display suggestion "chips" to help a user get started.

For each additional filter you add beyond the required Search one, you can configure its suggestion chip behavior with its Suggestions > Type property. Set to Dynamic to show the first visible filter value. If the filter computes counts, then it will be the most frequently occurring filter option. Otherwise, it's the first option in sorted order. Use a Type of Static Values to set a hard-coded value. The SQL Query option gives you a flexible way to configure the suggestion based on a custom scheme, perhaps involving the current username (:APP_USER) or any other data-driven approach. In all cases, the suggestion value must be one of the return values defined in the filter's list of values. To have no suggestion for a particular filter, its its Suggestions > Type to None.

You can limit the number of suggestion chips using the Maximum Suggestion Chips region property. If left unset, then up to five suggestions can appear, one per suggestion-enabled filter. To suppress suggestions if they don't add value to a particular Smart Filters usage, set this property to zero (0).

When you want to dedicate even more space to the search results, you can move the Smart Filters region into the Navigation Bar.

By setting the Smart Filters region's Parent Region property to No Parent, choosing the Before Navigation Bar slot, and setting CSS Classes to w100p, you can make it occupy the empty space in the nav bar as shown below. This position works best with Maximum Suggestion Chips set to zero (0). The figure shows the Smart Filters search field tucked into the navigation bar between the Companion application name on the left and the Navigation Bar showing the logged-in username leo on the right.

Define a Search Configuration shared component for each kind of source data users need to search. Each one identifies a local or remote data source and its searchable columns. You also choose which columns play standard roles like primary key, title, description, and icon.

To let users drill-down on a result from a particular search configuration, configure a link target on it. Users will navigate to the indicated page when they click a search result that comes from that source.

Depending on the search configuration type, APEX performs:

• Standard LIKE filtering over local tables, queries, APEX list entries, or REST Data Sources
• Full text searching using Oracle Text or Oracle AI Database 26ai Ubiquitous Search, or
• Semantic similarity searching using Oracle AI Database 26ai Vector Search.

For example, the Employees search configuration shown in the editor below uses the following SQL query as its Source and identifies searchable columns: ENAME, JOB, DNAME, EMPNO, and WORKS_FOR_ENAME.

select  e.empno,
        e.ename,
        m.ename as works_for_ename,
        d.dname,
        e.job,
        case e.job
           when 'PRESIDENT' then 'fa-badgerine'
           when 'ANALYST'   then 'fa-line-chart'
           when 'CLERK'     then 'fa-user-headset'
           when 'SALESMAN'  then 'fa-badge-dollar'
           when 'MANAGER'   then 'fa-user'
        end as icon
  from  emp e
  join  dept d
    on  e.deptno = d.deptno
  left join emp m
         on m.empno = e.mgr

As shown below, it maps EMPNO as primary key, ENAME as the title, JOB as subtitle, DNAME as description, and shows a badge using the EMPNO column value.

After configuring that the search result icon name comes from the ICON column on the Icon and Display tab, you define the link information. As shown below, when the user clicks on a search result from this Employees search source, they should navigate to a modal Employee edit page number 4, passing the value of the result's EMPNO column as the P4_EMPNO parameter. The figure shows the Link section of the Search Configuration edit page where your set up these options.

In a similar way, you could define a Departments search configuration based directly on the DEPT table using the fa-building-o icon for every row, mapping columns appropriately, and setting the link information to open a Department edit page.

After adding a Search region to a page, reference one or more search configurations as search sources.

For example, the figure below shows a Search region in Page Designer, with Employees and Departments search configurations added as sources. By default a search region shows an integrated search field above the results area. For more layout flexibility, as shown below you can add your own P27_SEARCH page item that you position where you want and reference it in the region's Search Page Item property. Using other settings, you control how sorting and pagination of search results behave. If needed, you can configure a Custom Layout in the Appearance section to format results from all sources in a consistent, custom way. When doing so, your HTML can reference template directives for conditional output as well as use substitutions with names like TITLE, SUBTITLE, DESCRIPTION, ICON, LINK, and more. See the Help tab in Page Designer for the Result Row Template property for the complete list.

At runtime, as shown below, when the user types a term into the search field like acc matching results from both the Departments and Employees sources appear in the unified list of results. Clicking on a result like CLARK who works in ACCOUNTING, the appropriate employee-related target page opens to show the details.

You can customize how unified search results appear in three ways.

Using a combination of a Search region with one more Search Configurations, you can customize how your unified search results appear by:

1. Defining custom CSS rules to change how the default search results appear,
2. Providing a custom Result Row Template on the search region,
3. Configuring a Default Result Row Template on some or all of your search configurations

The CSS approach is good for simpler changes to spacing, fonts, and colors. The other two options give you full formatting control. Define a Default Result Row Template on a search configuration to style its result row. It can reference column names from the data source as substitutions. This template gets used automatically unless you configure the Result Row Template on the Search region to format results of all sources in a consistent way.

• Custom Search Region Row Template
  By default, the search region formats results using a built-in result row template when a search configuration does not specify its own. This "blueprint" combines HTML tags with &NAME. references and HTML directives for conditional formatting.
• Custom Search Configuration Template
  To format a specific search configuration's results in a custom way, use a Default Result Row Template. Find this setting in the Icon and Display section of the shared component's edit page.

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.

To show one region's data in another region, you can reference the primary keys of a source region's search results in another region's query or WHERE clause. Whenever the source region's results change, a dynamic action can automatically refresh the target region.

Consider the Smart Filters region shown below that searches EMP data in a Cards region. As a user narrows the results, seeing the same data on a chart or a map can be useful. The figure below shows a Smart Filters region positioned in the navigation bar. In the body of the page, a Cards region occupies the first six columns, while a Map and Chart region show one above the other in the right six columns.

• Getting Filtered Data Primary Keys
  A helper function can return the primary keys of a filtered region's results.
• Using Filtered Data Primary Keys
  Use the SQL table() operator to use rows from a pipelined function in the data source query of any APEX region.
• Refreshing the Target Region
  To keep the source region and the target region's data in sync, define a dynamic action on the source region that triggers when the source region's results are refreshed.

When using the Select List, Radio Group, and Popup LOV you can optionally include an item in the list that represents the null value, and if included it can have a custom label.

This can be useful to help an end user understand what not selecting a value means. For example, consider the Department Select List page item below used as an optional query by example criteria field. The Null Display Value of "<All>" suggests that employees from all departments will be shown if they don't select a specific department.

You can use any page item's value in a query filter in another region. Most page item types, including single-selection list-driven items, store just one value that can also be null.

Assuming your query by example page item is a Select List named P5_SELECTED_DEPARTMENT, you can reference its value in the WHERE clause of another region on the page using the following syntax:

DEPTNO = :P5_SELECTED_DEPARTMENT

If the page item's value can be null, then your WHERE clause must anticipate this accordingly using this modified version of the filter clause:

/*  Either no department is selected,
 or match the selected department */
(   :P5_SELECTED_DEPARTMENT IS NULL
 OR DEPTNO = :P5_SELECTED_DEPARTMENT)

When a query-by-example item allows multiple selection, filtering by its value requires an IN clause with a subquery.

By default, the value of a multi-select page item will contain the colon-delimited (:) return values of the user's selections. Assuming your item is named P5_SELECTED_DEPARTMENTS, you can reference its value in the WHERE clause of another region on the page using the syntax below. If you use a different delimiter, pass it instead of the colon in APEX_STRING.SPLIT()'s second parameter.

DEPTNO IN (SELECT COLUMN_VALUE
             FROM APEX_STRING.SPLIT(:P5_SELECTED_DEPARTMENTS,':'))

If the page item is optional, then your WHERE clause must anticipate that with this modified version of the filter clause:

/* Either no department is selected,
or match the selected departments */
(   :P5_SELECTED_DEPARTMENTS IS NULL
 OR DEPTNO IN (SELECT COLUMN_VALUE
                 FROM APEX_STRING.SPLIT(:P5_SELECTED_DEPARTMENTS,':')))

Two approaches exist for query-by-example WHERE clause filters. One requires less typing; the other performs better.

The simpler-to-understand technique shown below uses an OR clause that first checks if the page item value IS NULL and then provides a filter on the desired column involving the page item bind variable.

/* Either no department is selected,
or match the selected department */
(   :P5_SELECTED_DEPARTMENT IS NULL
 OR DEPTNO = :P5_SELECTED_DEPARTMENT)

When processing a query for the first time, the database considers bind variable values when deciding which indexes to use for fast data retrieval. Then, it reuses this "query plan" on subsequent executions of the same SQL statement, even if the bind variable values have changed. This means that using the simpler approach above can result in not always using the best index for the filtering job at hand, especially when your query might contain many such clauses.

For the best performance, include a query by example filter clause only when it is relevant. In other words, if the corresponding page item value is not null, then include the clause that filters a column based on its value. Otherwise, if the page item value is null, leave that filtering clause out of the query.

Employing this strategy requires a dynamic approach to constructing the SQL query. APEX lets you write a Function Body Returning SQL to easily implement this technique. Instead of specifying the SQL statement verbatim, you use PL/SQL to build it up as needed and return the resulting SQL statement. For example, the code below returns a SQL statement that includes the filter predicate if the P5_SELECTED_DEPARTMENT page item value is not null, or leaves it out otherwise.

return q'~
SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
~'
|| CASE WHEN :P5_SELECTED_DEPARTMENT IS NOT NULL THEN
q'~
AND DEPTNO = :P5_SELECTED_DEPARTMENT
~' END;   

The code above is what you would type into the Property Editor for a region's data source after selecting the Function Body Returning SQL option. Notice it's using the PL/SQL syntax for a multi-line string literal using q'~ to begin the string and ~' to end it. This makes the parts of your SQL statement being concatenated more readable and avoids the need to handle single quotes in a special way. The double vertical bar symbol is the Oracle SQL string concatenation operator. Finally, recall that the SQL CASE expression returns null if none of its WHEN or optional ELSE conditions evaluates to true, and that concatenating a null value to a string leaves the original string unchanged.

At runtime, whenever your region retrieves its data the APEX engine "wraps" your code with an enclosing "envelope" shown below. Then it evaluates the function to compute the effective text of the SQL statement it asks the database to execute.

function x
return varchar2
is
begin
   /* Your Function Body Here */
end x;

Using this technique, when P5_SELECTED_DEPARTMENT is null the database sees the query:

SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1

In contrast, when P5_SELECTED_DEPARTMENT has a non-null value the query executed is:

SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
AND DEPTNO = :P5_SELECTED_DEPARTMENT

You can adopt the same technique to conditionally include multiple filter clauses. For example, the following function body evaluates three different query by example page items to include the relevant filter clauses. Using the always true predicate WHERE 1=1 ensures a syntactically correct query for any number of added filters. It also lets each conditionally-included predicate uniformly include the leading AND conjunction.

return q'~
SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
  FROM EMP
 WHERE 1=1
~'
|| CASE WHEN :P5_SELECTED_DEPARTMENT IS NOT NULL THEN
q'~
AND DEPTNO = :P5_SELECTED_DEPARTMENT
~' END
|| CASE WHEN :P5_ENAME_CONTAINS IS NOT NULL THEN
q'~
AND INSTR(UPPER(ENAME), UPPER(:P5_ENAME_CONTAINS)) > 0
~' END
|| CASE WHEN :P5_MAX_SALARY IS NOT NULL THEN
q'~
AND SAL <= :P5_MAX_SALARY
~' END; 

To maximize maintainability, you can put your query-building code into a PL/SQL package function. Imagine you have a UTILS package, and added to its specification and body the p5_employees_query function shown below. In that case, your region's function body returning SQL becomes:

return utils.p5_employees_query(:P5_SELECTED_DEPARTMENT,
                                :P5_ENAME_CONTAINS,
                                :P5_MAX_SALARY);

The p5_employees_query function would look like this:

function p5_employees_query(
   p_selected_deptno in number,
   p_ename_contains  in varchar2,
   p_max_salary      in number)
   return               varchar2
is
begin
   return q'~
   SELECT EMPNO, ENAME, SAL, DEPTNO, HIREDATE
     FROM EMP
    WHERE 1=1
   ~'
   || CASE WHEN p_selected_deptno IS NOT NULL THEN
   q'~
   AND DEPTNO = :P5_SELECTED_DEPARTMENT
   ~' END
   || CASE WHEN p_ename_contains IS NOT NULL THEN
   q'~
   AND INSTR(UPPER(ENAME), UPPER(:P5_ENAME_CONTAINS)) > 0
   ~' END
   || CASE WHEN p_max_salary IS NOT NULL THEN
   q'~
   AND SAL <= :P5_MAX_SALARY
   ~' END;
end p5_employees_query;

Whenever your function body might include a reference to a page item value as a bind variable in a region data source query or WHERE clause, list that item in the region's Page Items to Submit property. Omit the colon and comma-separate multiple item names. If you forget this step, the bind variable evaluates to null on refresh and the query may return no rows.

Query-by-example items change values by design. Disable the unsaved changes warning so users aren't confused when navigating away.

As shown below, by default APEX warns about unsaved changes when navigating to another page. This can alarm the user if they were not performing a data entry task. When using a page item for query by example, consider disabling its Warn on Unsaved Changes setting. You can configure this setting once at page level, and also set it per page item as appropriate for your use case.

By default, the search region formats results using a built-in result row template when a search configuration does not specify its own. This "blueprint" combines HTML tags with &NAME. references and HTML directives for conditional formatting.

For example, if a search configuration provides a badge column, then certain HTML tags will appear in the rendered output to properly display the badge value. Otherwise, the badge markup is omitted.

To change how all search results appear, specify a Result Row Template on the search region. As shown below, after enabling the Custom Layout switch, the Help tab for the Result Row Template property lists all of the standard substitution strings you can reference using &NAME. syntax. Scrolling the Help tab page, you can also find the built-in result row template.

To format a specific search configuration's results in a custom way, use a Default Result Row Template. Find this setting in the Icon and Display section of the shared component's edit page.

Your template can use HTML tags, data source &COLUMNNAME. substitutions, and HTML template directives for conditional formatting. As shown below, the inline help dialog mentions additional substitution strings available and provides an example template for inspiration. APEX uses your custom search configuration's default result row template unless a search region provides one that overrides it to format all results in the same way.

A helper function can return the primary keys of a filtered region's results.

The region_pks function below accepts the static ID of the filtered region as a parameter. It represents the source whose filtered results you want to present in an alternative way using another region. If ID is the primary key, then only the first parameter is required. If not, pass its name in the optional p_pk_column_name parameter. The function returns a pipelined list of numbers representing the primary keys of the indicated region's filtered results. It uses the OPEN_QUERY_CONTEXT() function in the APEX_REGION package to reference the source region's filtered results. Then it iterates over them with NEXT_ROW() and GET_NUMBER() in the APEX_EXEC package. The pipe row returns the primary key of each result.

create or replace function region_pks(
    p_region_static_id in varchar2,
    p_pk_column_name   in varchar2 default 'ID')
return apex_t_number
pipelined
is
    c_page_id constant number := V('APP_PAGE_ID');
    l_context          apex_exec.t_context;
begin
    l_context := apex_region.open_query_context(
                    p_page_id   => c_page_id,
                    p_region_id => apex_region.get_id(
                                     p_page_id       => c_page_id,
                                     p_dom_static_id => p_region_static_id));
    while apex_exec.next_row(l_context) loop
        pipe row (apex_exec.get_number(l_context, p_pk_column_name));
    end loop;
    apex_exec.close(l_context);
    return;
exception
    when no_data_needed then
        apex_exec.close(l_context);
        return;
end region_pks;

Use the SQL table() operator to use rows from a pipelined function in the data source query of any APEX region.

Using the SQL WITH clause as shown below, you can name the filtered region's list of primary keys returned by the region_pks() function. Then, your query can SELECT from the result_pks common table expression joined with the emp table on their respective empno column values. The join condition between left and right tables uses a common column name, so the shorthand using (empno) is easier to type than on emp.empno = result_pks.empno.

with result_pks as (
    select column_value as empno
      from table(region_pks('employees','EMPNO'))
)
select ename,
       sal
from result_pks
join emp e using (empno)

You can also involve the filtered results' primary keys in a WHERE clause when needed like this:

empno in (select column_value
            from table(region_pks('employees','EMPNO')))

To keep the source region and the target region's data in sync, define a dynamic action on the source region that triggers when the source region's results are refreshed.

For a Classic Report region, the event is After Refresh. For a Cards region, use Page Change (Cards) instead. In the list of True actions, add a Refresh dynamic action step that refreshes the target region.