Introduction
In Oracle APEX Version 22.2, the APEX development team introduced a new search method called Application Search. With a dedicated Search Region type and multiple places to source data from, Application Search has put an end to homegrown search solutions for APEX.
This post will describe the significant features of APEX Application Search, review the APEX components involved, demonstrate an example use case with multiple data sources, and provide some recommendations when using this feature.
I won't be able to cover every feature of APEX Application Search, but it should be a good starting point.
Marquee Features
With APEX Application Search, you can:
Combine data from multiple sources into a single search. Sources include the local Database, a remote database (via REST Enabled SQL), or just about any REST API (via REST Data Sources).
Search across multiple fields from the data source.
Utilize the power of Oracle Text in your searches.
Position the search entry item independently from the search results region.
Perform tokenized row search. Tokenized search treats each word of a search term separately to match a record where the words are contained in any of the searchable columns, together or independently.
Customize the search results UI using a customized row template and the flexibility of Template Directives.
Example Use Case
The video below shows APEX Application Search in action. The application being demonstrated is a project billing application. The search region allows users to search across Customers, Users, and Projects that they have access to without having to navigate to different pages to find them. The results are combined into one set of search results where the user can select a specific item.
APEX Application Search Terminology
Let's level set on some key terminology about APEX Application Search.
Search Configurations
Search Configurations contain information about a searchable data source. They can be referenced from Search regions to add declarative searches in your application. Searches can be based on SQL from the local Database, APEX REST Enabled SQL Queries (from a Remote DB), APEX REST Data Sources (Web Services), pre-defined Oracle Text queries, or APEX Shared Component Lists.
Search Configurations are the building blocks with which you build the data sets that will be used in your search.
Search Regions
Search Regions represent the implementation of one or more Search Configurations on an APEX Page. You can include multiple Search Configurations to implement searches across multiple data sources. For example, you can combine a Search Configuration based on a SQL statement in the local DB with a Search Configuration based on a REST Data Source pulling data from a REST Web Service.
No matter how many Search Configurations you reference, the results are combined in the Search Region into a single set of search results.
Search Page Item
The Search Page Item is an APEX Page item the user enters their search criteria into. The value from this field is used to execute searches in all of the Search Configurations associated with the Search Region. Although the Search Page Item is linked to a Search Region to perform these searches, you can place the Search Page Item anywhere on the page.
💡
Set Session State Storage to Per Request (Memory Only) for this item. Otherwise, your previous search will run whenever you refresh the page or navigate to the page without clearing the page cache.
Building the Use Case
The use case aims to demonstrate the most common features of APEX Application Search. It does not represent all of the possible Application Search features.
Search Configurations
My example has three search configurations or data sources (customers, projects, and users). As they are all based on SQL statements and therefore behave the same way, I will focus on the customer search configuration.
Navigation: Application > Shared Components > Search Configurations > Create > Standard
Settings
Label - Enter a name for your Search Configuration
Search Query Prefix - The search query prefix can be entered in the search item to restrict a search to just data from that specific source. In my example, I have three search configurations associated with my Search Region. The search configurations have Search Query Prefixes named customer, project, and user. An end user can decide to restrict the search to only customers by prefixing the search with 'customer:' e.g.
Static ID - Enter a unique identifier for the Search Configuration
Source
Data Source - Identify the source of the data for the Search Configuration. This is where the power of Search Configurations starts to show.
Local Database allows you to select a table, view or provide your own SQL statement to use as the source of the Search Configuration.
REST Enabled SQL Service allows you to use the results from a SQL statement run in another database as the source of the Search Configuration.
REST Data Source allows you to use the results of a REST Web Service call as the source of the Search Configuration. This means you can source the data for a Search Configuration from just about anywhere 🔥
Source Type - Because I selected Local Database as the Data Source, I can choose a source type of Table, SQL Query, or Function Body returning SQL.
Order By Clause - You can add an optional ORDER BY clause for all the Source Types related to the Local Database Data Source.
Searchable Column(s) - This works the same way for a List of Values. You can select which columns participate in the search and which do not.
Column Mapping
This section of the Search Configuration maps the columns from your Data Source to the values in the Search Region results.
Title Column is the primary search result. It also contains the link if you define one (see the Link section below).
Subtitle and Description are displayed in the search results if selected and a not null value is present.
Custom Column 1 thru Custom Column 3 are also displayed in the search results if selected and a not null value is present.
Badge Column - Displays the value as a badge to the right of the Title Column in the search results.
Score Column - This column can be used in the Search Region to provide a match score for search results.
Last Modified Column - This column can be used in the Search Region to display a date field in the search results.
Icon and Display
Icon Source - Include an icon in the search results for this source. The Icon source can be a static Font APEX icon or derived from a column in the data source.
Default Result Row Template - Build your own custom search results UI using HTML, CSS, and Template Directives. 💡Click the help icon for this field to see the default template and use that code as a guideline to build your own.
Result CSS Classes - Any classes you add here will be injected into each row of the search result for this data source. This can be useful if you want to add CSS styling to specific data sources. In my example, I added the class cn-customer to the Customers Search Specification. I used this class to color the badge in the search results using the below page-level CSS.
.a-ResultsItemcn-customer .a-ResultsItem-badge {
background-color: var(--u-color-27);
}
No Results Found Message - Message displayed to the user if no records are found for the search. This can be overridden in the Search Results Region.
Maximum Rows to Return - The maximum number of rows to return for this particular Search Configuration. You should consider setting this, especially if you combine multiple Search Configurations into one search. This can be overridden in the Search Results Region.
Link
- In this section, you can define a link to a page in an APEX application. Out of the box, the link is associated with the Title field. You can use any substitution variables in the 'With these values' attribute, including column names from your data source. In my example, I am linking to a page in another application to view customer details when the customer name is clicked.
Search Page Item
There is nothing special about a Search Page Item. I usually create a Text Field item with a Search Subtype and use the icon fa-search.
Search Region
A Search Region defines how the search results will be displayed, which Search Configurations will be involved in the search, and which Search Page Item will be used to provide the search criteria. To create a Search Region, add a region to your page and select the Region Type of Search. You can then add one or more Data Sources (or Search Configurations) to the region. I have three Search Configurations associated with the Search region below.
- Besides selecting the Type of Search, there is little to do in the region definition. You should include your Search Page Item in the
Page Items to Submit field, and you can change the search results' appearance using Template Options.
Region Attributes provide several options to change the way the search works:
Search Page Item - This is where you link your Search Page Item to your Search Region. Whatever is typed into the Search Page Item is used to conduct the search.
Search as You Type & Minimum Characters - This acts the same as it does with the Popup LOV. In the example screenshot above, the search will be automatically executed after the user types two characters. If you turn this off, the user must hit the enter key to initiate the search.
Lazy Loading - This works the same way as it does for other regions. APEX will load the region independently of the data for the region, making the initial page rendering appear faster to the user.
Use Pagination & Results Per Page - Determines if the search results will be paginated and how many search results will appear on each page.
Show Result Count - Shows a total count of results. This incurs a performance overhead as APEX has to calculate the total. Only turn this on if you need it.
Custom Layout - if you turn this flag on, you can provide your own HTML and CSS to use as the Result Row Template for the search.
Last Modified Format Mask - You can provide the date format string here if you configured a column mapping for the Last Modified Date in your Search Configuration.
Overall Sort & Sort Direction - Determines how the overall results are sorted. I have three data sources in my example, so I want to keep results from the same source (customers, projects, users) together in the search results. I did this by including a column with a constant value in my data source queries which I mapped to Badge Value and used Badge Value for the overall sort.
No Query Entered Message - Any text you enter here will be displayed in the region whenever the Search Page Item is empty. This is a great place to include search help (see below for details).
No Results Found Message - This overrides the Search Configuration setting with the same name. If you are combining results from multiple sources, including some text here is a good idea.
Search Help
Depending on how complex your search is, It is a good idea to include help for the user so that they can get the most out of your search. This help should include what is being searched, how to perform tokenized searches, and how to limit the search to specific data sources.
This is what the search help looks like for my example:
Application Search Recommendations
Make it clear what data sources are being searched. Users will get frustrated if they think they can search for something that isn't in your data sources.
Provide clear help and example searches so that users can best use the search.
If you have multiple data sources, ensure the UI clearly shows which data source each search result belongs to.
Make it obvious which field is the search field.
If one or more of your data sources is based on REST Enabled SQL or a REST Data Source, then be careful when using Search as You Type. Every key press will result in a call to the remote DB / Web Service.
Consider performance at every step. Tune data source SQL, limit the maximum number of rows returned by each data source, and do not include the total results count unless you need it.
Conclusion
APEX Application Search is a big step forward in providing powerful multi-source search capabilities in your APEX Applications. I encourage you to try out this powerful feature and look at incorporating APEX Application Search into your APEX Applications.
🔗 Read More
- 🩳 APEX Shorts
- 📝 APEX Posts
