A Quick Guide to Oracle Text for Full Text Search in APEX Applications

Beyond LIKE and INSTR

Most APEX applications implement search using WHERE column LIKE '%search_term%'. This works for small tables, but forces full table scans, cannot rank results by relevance, and does not handle word variations. Oracle Text provides true full-text search capabilities built into the database with no external search engine required.

Creating a Basic Oracle Text Index

CREATE INDEX idx_product_desc ON products(description)
    INDEXTYPE IS CTXSYS.CONTEXT;

-- Multi column index
BEGIN
    CTX_DDL.CREATE_PREFERENCE('product_search_ds', 'MULTI_COLUMN_DATASTORE');
    CTX_DDL.SET_ATTRIBUTE('product_search_ds', 'COLUMNS',
                          'product_name, description, category');
END;
/

CREATE INDEX idx_product_search ON products(description)
    INDEXTYPE IS CTXSYS.CONTEXT
    PARAMETERS('DATASTORE product_search_ds');

Querying With CONTAINS

-- Simple word search with relevance ranking
SELECT product_id, product_name, SCORE(1) AS relevance
  FROM products
 WHERE CONTAINS(description, 'wireless bluetooth', 1) > 0
 ORDER BY SCORE(1) DESC;

-- Phrase search
WHERE CONTAINS(description, '{noise cancelling headphones}', 1) > 0

-- Boolean operators
WHERE CONTAINS(description, 'wireless AND (bluetooth OR wifi) NOT adapter', 1) > 0

-- Fuzzy matching for typos
WHERE CONTAINS(description, 'fuzzy(bluetooth, 70, 5, weight)', 1) > 0

-- Stemming: finds running, ran, runs
WHERE CONTAINS(description, '$run', 1) > 0

Integrating With APEX

SELECT product_id, product_name, description, SCORE(1) AS relevance
  FROM products
 WHERE (:P1_SEARCH IS NULL
        OR CONTAINS(description, :P1_SEARCH, 1) > 0)
 ORDER BY CASE WHEN :P1_SEARCH IS NOT NULL THEN SCORE(1) ELSE 0 END DESC,
          product_name;

Keeping the Index Current

Oracle Text indexes are not automatically updated when data changes. Schedule periodic synchronization:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'SYNC_TEXT_INDEX',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN CTX_DDL.SYNC_INDEX(''IDX_PRODUCT_SEARCH''); END;',
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled         => TRUE
    );
END;
/

For APEX applications where search results must be immediately up to date after data entry, call CTX_DDL.SYNC_INDEX in an After Submit process on the data entry page.