How to add global application search to your APEX app

If you build large APEX applications, it will be difficult for your users to maintain an overview of what is available. Well-designed hierarchies supported by a navigation menu and breadcrumbs can help, but sometimes it will still be unclear how to navigate from point A to point B. The best solution for this is to offer search.

Luckily, Oracle has had our back since Oracle APEX version 22.2 by offering the Application Search feature, which makes it easy to define search sources and later use them in a designated search region.

To demonstrate this functionality, I am implementing it into the “Customers” app that is available from the “Starter Apps” section in the gallery.

To get started, we need to create a search configuration. These are available in the shared components of any app.

In there, we can create a new one and get prompted for a name and type. Our first search should cover the entries the navigation menu offers, so I will give it that name. The four offered search types are:

We could go the easy route and choose the list type and reference the navigation menu, but I wanted to give some hierarchical information in my example (e. g. Administration > Code Tables > Status instead of just showing the page title), which we have to manually query. So instead, I built this rather complex query:

1                             -- regexp_replace to remove badges with count (Customers [19])
2with list_entries as (select regexp_replace(entry_text, ' \[&[A-Z]+\.\]') as title
3                             , connect_by_root entry_image as icon
4                             , regexp_substr( entry_target, '&APP_ID\.:([0-9]+):&', 1, 1, null, 1 ) as page_id
5                             -- build path like Admin > Code Tables > Status
6                             , substr( sys_connect_by_path( regexp_replace(entry_text, ' \[&[A-Z]+\.\]'), ' > ' ), 4 ) as path
7                          from apex_application_list_entries
8                         where application_id = :APP_ID
9                           and list_name = 'Application Navigation'
10                           and entry_target is not null
11                           and (condition_type_code is null or condition_type_code = 'USER_IS_NOT_PUBLIC_USER')
12                           -- remove if you are using older apex releases
13                           and working_copy_name is null
14                           -- might only work in 23c because the = true
15                           and ( authorization_scheme is null or apex_authorization.is_authorized(authorization_scheme) = true )
16                         start with list_entry_parent_id is null
17                       connect by prior list_entry_id = list_entry_parent_id)
18select title
19     , icon
20     , path
21     , apex_page.get_url( p_page => page_id ) as target
22  from list_entries
23 where page_id is not null
24

If you are not on database 23c/ai yet, you can use this query instead that works without boolean support.

Next we can assign our columns:

After clicking create, we will get redirected to the details page, where we can refine our settings. In searchable columns we can define which columns are used for answering the search requests. You can use more columns than you actually show to the users. In our case, we should make sure that only path and title are selected as searchable columns, as we don’t want the URL or icon to be considered.

We also need to set the URL target of our search results. In the section “Link”, we choose “redirect to URL” as the type and pass our target as a substitution string: &TARGET.

There are also a lot slots you can use to visually enrich your search results. You can map additional columns to subtitles, badges, scores, last modified and additional attributes.

To make use of our search definition, we can create a new page. Luckily, there is a create-page template we can use that gives us a ready-to-run page. We just have to check our search configuration and test it.

In the page designer we can see that it has a search input item, a region of type “Search”, and our search configuration as a child. We could add multiple definitions so that the region looks at multiple sources. In the attributes of the search region, we disable “search as you type”, set minimum characters, configure messages for nothing found and no prompt, and much more.

When we run the page, we can test the search. The title is the clickable link to open its target, and our path is displayed below it.

For now, the search only returns results for the outer surface of our app. What about all the data that is stored in the database? It would be neat to be able to look for each customer and be able to jump to their details page.

Luckily this is fairly easy to accomplish. We can just create another search configuration and this time use a query that returns all the customers data:

1select id
2     , customer_name
3     , tags
4     , summary
5     , web_site
6     , apex_page.get_url(p_page => 50, p_items => 'P50_ID', p_values => id) as target
7  from eba_cust_customers;
8

We define id as our primary key, customer_name and summary as title and subtitle, and fa-user as our static icon class. Furthermore, we also query the columns tags and web_site to add them to searchable columns as they contain valuable context that should be searchable.

Back in the page designer, we can right-click on “Search Sources” to add our new configuration. Now our search page can return customers and directly link to their details page. If we search for “cloud”, which is used in the tags column for some customers, we get the following results:

We should additionally add other entities, such as products, partners, etc. in the same way.

Currently, our search is not really accessible to users. It is hidden in the menu on a separate page, but what all the big websites have taught us is that it needs to be everywhere and on the same page. Luckily, we can do that in APEX too, but this is quite hacky…

First, we need to go to page 0 - the global page. Any region, item or button in there will be displayed on every page. Perfect for our case. Now we just have to create a new text field page item and set it to the position “After Logo”. Now I will list all the tweaks to get it to look in a way I like, you are, of course, free to do it differently:

And voilà, this is what it looks like now:

Note that the title bar is a little bigger now; I guess you can fix that by decreasing the items margin with CSS if you don’t like that.

Unfortunately, the item does not do anything currently… What it should do, in my opinion, is redirect to the search page when the user presses enter (after typing something in there). Achieving this is not as straightforward as it seems. At P0, we can’t define branches that run after submitting the page. Instead, we need to:

1. Check the pressed key is enter
2. Send the current item value to the DB
3. Ask the DB for a link to the search page (with checksums included)
4. Navigate to that URL

This is tricky to do manually, but luckily, there is a Plug-In for that! It is called UC Redirect and you can get it from Plug-Ins Pro. (Coincidentally, it is currently maintained by my employer, United Codes. They took over the FOEX Plug-Ins after their acquisition. This is not sponsored content).

Now we just need to create a dynamic action of the type “Keydown” on the search input item. To only run the actions when the “Enter” key is pressed, we add this client-side condition:

1this.browserEvent.originalEvent.code === 'Enter';
2

For the true action, we choose the UC Redirect plug-in, set the URL Source to PL/SQL Expression and add the following call to the get_url API: apex_page.get_url(p_page => 500, p_items => 'P500_SEARCH', p_values => :P0_SEARCH). Additionally, we need to check “submit items before redirect” and add our item.

Now we can access our search from anywhere.

To use this feature to its full potential, you must know how people use the app. If users create plenty of customers and products, or if you have an important button that opens a modal somewhere, you can and probably should add this to the search results. The goal is to make the global application search the entry portal for your entire Oracle APEX app.