Information Retrieval in PL/SQL Using Oracle Text

Information Retrieval in PL/SQL involves extracting meaningful, relevant data using advanced techniques like Oracle Text, rather than just using basic SELECT statements. Unlike simple queries that return raw data, IR methods filter and rank information based on context, proximity, and significance.

What Is Information Retrieval?

Before we dive into the Oracle Text implementation, it’s important to differentiate between data and information:

Data: Raw characters or numbers stored in the database.
Information: Data filtered by meaning and relevance to become useful for business decisions.

Oracle provides a powerful full-text search solution called Oracle Text to support intelligent information retrieval.

Oracle Text for Information Retrieval in PL/SQL

Oracle Text (previously called ConText/interMedia) is an integrated full-featured information retrieval engine. It allows PL/SQL developers to:

Search over 150 document formats.
Perform full-text and LOB content indexing.
Handle case-insensitive, fuzzy, wildcard, and proximity searches.
Support multiple languages.

Use Cases

Document archives
Product catalogs
Knowledge repositories
Data warehouse search features

Oracle Text Index Types

Index Type	Description
CONTEXT	Full-text search for large/static data
CTXCAT	Optimized for e-commerce catalogs
CTXRULE	Best for classification and routing
CTXXPATH	Designed for XML document indexing

Using the CONTAINS Operator for Information Retrieval in PL/SQL

To search using Oracle Text, use the CONTAINS operator in your WHERE clause. Syntax:

SELECT [SCORE(label), ] column_list
INTO variable_list
FROM table_name
WHERE CONTAINS(column_name, 'search_string', label) > 0;

Important Points:

SCORE(label): Indicates relevance ranking.
label: A numeric tag that matches in both SELECT and WHERE.
> 0: Ensures results with a positive match are returned.

Information Retrieval in PL/SQL – Case-Insensitive Search Example

SET SERVEROUTPUT ON
DECLARE
  v_isbn BOOK_DESCRIPTIONS.ISBN%TYPE;
  v_score NUMBER(10);
BEGIN
  SELECT score(1), isbn
  INTO v_score, v_isbn
  FROM book_descriptions
  WHERE CONTAINS(description, '10G or oracle', 1) > 0;

  DBMS_OUTPUT.PUT_LINE('Score: ' || v_score || ', ISBN: ' || v_isbn);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Output:

Score: 3, ISBN: 72230665

Information Retrieval in PL/SQL – Proximity Search Example

SET SERVEROUTPUT ON
DECLARE
  v_isbn BOOK_DESCRIPTIONS.ISBN%TYPE;
  v_score NUMBER(10);
BEGIN
  SELECT score(1), isbn
  INTO v_score, v_isbn
  FROM book_descriptions
  WHERE CONTAINS(description, '10g near Oracle', 1) > 0;

  DBMS_OUTPUT.PUT_LINE('Score: ' || v_score || ', ISBN: ' || v_isbn);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Output:

Score: 14, ISBN: 72230665

The proximity term NEAR boosts the score due to close contextual placement of 10g and Oracle.

Best Practices

Always index columns used with CONTAINS.
Use SCORE() when relevance matters.
Avoid running CONTAINS on unindexed columns.
Combine with other WHERE clauses for refined search.

➡️ Learn more about Oracle Text Official Docs

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.