Do you wonder how to apply a Google-like search (“ubiquitous search”) engine on the Oracle database?
“Oracle Text” is a product within the database capable of producing a full-text (keyword) search from different documents. It is fully integrated and available in all versions of the database without any further installation. It has been around for many years, but I think not everybody necessarily is aware of or has been making full use of the power of this engine.
The simplest use case of this engine is to perform a search within a single column, and more advanced features involve searching on multiple columns in the same table (Multicolumn Datastore) and on multiple columns in different tables (User Datastore). In all scenarios, only one index is utilized to fulfill all kinds of full-text search criteria.
With the 23c version of the Oracle Database, the Ubiquitous search is much easier now to use “Oracle Text” in more advanced cases where multiple data sources are referenced. There is a new simple package DBMS_SEARCH to create an index across multiple objects, easily add data sources into the index, or also remove them. This simplifies the indexing tasks.
The new Oracle Apex version 23.2 takes advantage of this new ubiquitous search available from the database, and it is added to the search configuration.
Without further ado, let’s dive in on how to build it from the database and then utilize it from the Oracle Apex application
Create Index for Ubiquitous Database Search
Let’s create the ubiquitous index using the create_index from DBMS_SEARCH package and name it as “search_index”.
begin
dbms_search.create_index('search_index');
end;
It creates the infrastructure for “Oracle Text” which includes a table SEARCH_INDEX with two JSON columns METADATA and DATA.
desc search_index;
Name Null? Type
-------- -------- --------------
METADATA NOT NULL JSON
DATA JSON
OWNER VARCHAR2(128)
SOURCE VARCHAR2(128)
KEY VARCHAR2(1024)
Add Data Source
By utilizing the add_source procedure, you can incorporate tables into the DBMS_SEARCH index as a data source. Additionally, it’s worth noting that not only sets of tables can be added, but also external tables or views.
For this exercise, I used the Oracle Database sample “CO” schemes available from github. I added the two tables : Customers and Products into the SEARCH_INDEX.
BEGIN
dbms_search.add_source(index_name =>'search_index', source_name => 'CUSTOMERS');
dbms_search.add_source(index_name =>'search_index', source_name => 'PRODUCTS');
END;
Review the Virtual Document
You can employ the GET_DOCUMENT procedure to gain insight into what you can search for. This procedure generates a virtual indexed document, which is created by populating it with data from multiple columns, tables, or views. In the provided example, it utilizes the CONTAINS operator to search for the word “green”. Additionally, there exists a FUZZY operator that can be applied to CONTAINS, broadening queries to include words spelled similarly to the specified term. You have the option to explore other available operators in the Oracle documentation.
select dbms_search.get_document(index_name=>'search_index', document_metadata=>metadata)
from search_index
WHERE contains(data, 'green') > 0;
--WHERE CONTAINS(data,'fuzzy(boy)')>0;
{"TALA":{"PRODUCTS":{"PRODUCT_ID":2,"PRODUCT_NAME":"Women's Shirt (Green)","UNIT_PRICE":16.67,"PRODUCT_DETAILS":{"colour":"green","gender":"Women's","brand":"FLEETMIX","description":"Excepteur anim adipisicing aliqua ad. Ex aliquip ad tempor cupidatat dolore ipsum ex anim Lorem aute amet.","sizes":[0,2,4,6,8,10,12,14,16,18,20],"reviews":[{"rating":8,"review":"Laborum ipsum adipisicing magna nulla tempor incididunt."},{"rating":10,"review":"Cupidatat dolore nulla pariatur quis quis."},{"rating":9,"review":"Pariatur mollit dolor in deserunt cillum consectetur."},{"rating":3,"review":"Dolore occaecat mollit id ad aliqua irure reprehenderit amet eiusmod pariatur."},{"rating":10,"review":"Est pariatur et qui minim velit non consectetur sint fugiat ad."},{"rating":6,"review":"Et pariatur ipsum eu qui."},{"rating":6,"review":"Voluptate labore irure cupidatat mollit irure quis fugiat enim laborum consectetur officia sunt."},{"rating":8,"review":"Irure elit do et elit aute veniam proident sunt."},{"rating":8,"review":"Aute mollit proident id veniam occaecat dolore mollit dolore nostrud."}]},"PRODUCT_IMAGE":null,"IMAGE_MIME_TYPE":null,"IMAGE_FILENAME":null,"IMAGE_CHARSET":null,"IMAGE_LAST_UPDATED":null}}}
{"TALA":{"PRODUCTS":{"PRODUCT_ID":3,"PRODUCT_NAME":"Boy's Sweater (Green)","UNIT_PRICE":44.17,"PRODUCT_DETAILS":{"colour":"green","gender":"Boy's","brand":"KINETICA","description":"Occaecat dolore in ut et ad pariatur laborum mollit nulla exercitation. Laboris esse tempor quis velit nostrud exercitation veniam reprehenderit minim minim exercitation.","sizes":["1 Yr","2 Yr","3-4 Yr","5-6 Yr","7-8 Yr","9-10 Yr"],"reviews":[{"rating":5,"review":"Sunt ad proident excepteur laboris officia eu reprehenderit dolor nostrud elit nulla pariatur incididunt Lorem."},{"rating":2,"review":"Ullamco ad amet fugiat adipisicing."}]},"PRODUCT_IMAGE":null,"IMAGE_MIME_TYPE":null,"IMAGE_FILENAME":null,"IMAGE_CHARSET":null,"IMAGE_LAST_UPDATED":null}}}
{"TALA":{"PRODUCTS":{"PRODUCT_ID":10,"PRODUCT_NAME":"Women's Skirt (Red)","UNIT_PRICE":30.69,"PRODUCT_DETAILS":{"colour":"green","gender":"Women's","brand":"FLYBOYZ","description":"Qui aliquip dolor aute labore amet nostrud deserunt nulla ut veniam id. Ut aute velit tempor anim ex sit nisi.","sizes":[0,2,4,6,8,10,12,14,16,18,20],"reviews":[{"rating":7,"review":"Mollit consequat minim sit consequat deserunt duis."},{"rating":8,"review":"Quis eu esse proident elit eu aliqua magna voluptate labore adipisicing voluptate ex do."},{"rating":6,"review":"Laborum nulla aliquip nulla adipisicing aliquip qui cupidatat aliquip in."},{"rating":3,"review":"Exercitation aute voluptate voluptate tempor sit enim ut veniam do."},{"rating":8,"review":"Cillum cillum anim aliqua eu deserunt amet eu ut veniam in qui."},{"rating":7,"review":"Nostrud aliqua ullamco irure consectetur elit nisi eu elit reprehenderit ut."},{"rating":5,"review":"Irure nisi dolore dolore ut non ad minim pariatur."},{"rating":2,"review":"Laboris aliqua sint est incididunt sunt non tempor irure reprehenderit labore."}]},"PRODUCT_IMAGE":null,"IMAGE_MIME_TYPE":null,"IMAGE_FILENAME":null,"IMAGE_CHARSET":null,"IMAGE_LAST_UPDATED":null}}}
Now, this is ready to be utilized in Oracle APEX 23.2.
Create the Ubiquitous Search Configuration from Oracle Apex
Navigate to Shared Components > Search Configuration > Create
A new search configuration “Oracle Ubiquitous Search” is added on this 23.2 Oracle Apex version.
Choose the SEARCH_INDEX for the Index Name and choose the CUSTOMERS for the source.
For the column mapping, the Primary Key Column is automatically populated. Enter values for Title and Description Columns. After clicking the button, it will create the search configuration
Create Search Page
In the Create Page, choose the Search Page from the component
Enter the name and for the search configuration, choose your Oracle Ubiquitous Search. Then press the Create Page button.
Viola, the search page has been created using the “Obiquitous Search”.
