Oracle Text Index with Oracle Apex Faceted Search Not Working

Bilal

Hi,

Oracle Apex 22.1.1

I have created Oracle Index in the autonomous database using the following script:

BEGIN
   -- Dropping biomarkers datastore
   BEGIN CTX_DDL.DROP_PREFERENCE('biomarkers_datastore'); EXCEPTION WHEN others THEN NULL; END;
   -- Creating biomarkers text index
   CTX_DDL.CREATE_PREFERENCE('biomarkers_datastore','multi_column_datastore');
   CTX_DDL.SET_ATTRIBUTE('biomarkers_datastore','columns','biomarker_name,biomarker_descrip,biomarker_type,early_detection_stage,sample_type,measuring_technique,biomarker_approval_stage,measurement_type');
   -- Dropping biomarker text index
   EXECUTE IMMEDIATE 'DROP INDEX biomarkers_text_index';
   EXECUTE IMMEDIATE q'[CREATE INDEX biomarkers_text_index ON biomarkers (biomarker_name) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(q'{DATASTORE biomarkers_datastore SYNC (ON COMMIT)}')]';
END;

It is working perfectly from the SQL command.

I want to attach it with the Search field on the Oracle Faceted Search Page. Here is what I did:

Firstly, I selected the Search component as shown below

And, changed the Search Type to Oracle Text as shown below:

And also set the database column(s) to biomarker_name which is the column for which the text index is created. See below:

But when I run the app and try to query the page using the search field, it raises the following error:

Any idea what am I missing? Can someone guide me to relevant APEX documentation that explains Oracle Text integration with Oracle Apex. I watched several videos from Insum, Apex Office hours and other Apex Youtubers but couldn't figure it out.

I will greatly appreciate your help on this.

Many Thanks and

Kind Regards,

Bilal