Text Query

In 8.1, the Text query replaces the pre-8.1 two-step method. The Text query is akin to the pre-8.1 one-step query in so far as it is executed with a single SELECT statement. In addition, the new 8.1 Text query uses no result tables.

This section describes how to migrate your two-step queries to the new Text query.

Pre-8.1 Method

In the pre-8.1 method, you create a result table as follows:

create table CTX_TEMP(
    textkey varchar2(64),
    score number,
    conid number);

Alternatively, you can also create a result table using CTX_QUERY.GETTAB.

You execute the CONTAINS procedure as follows:

execute ctx_query.contains('ARTICLE_POLICY','petroleum','CTX_TEMP');

You then join the result table with the base table to retrieve the document text as follows:

SELECT SCORE, title 
FROM CTX_TEMP, TEXTTAB 
WHERE texttab.PK=ctx_temp.textkey 
ORDER BY SCORE DESC;

8.1 Method

SQL Example

In the SELECT statement, specify the query in the WHERE clause with the CONTAINS operator. Also specify the SCORE operator to return the score of each hit in the hitlist. The following example shows how to issue a query:

SELECT SCORE(1) title from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0;

You can order the results from the highest scoring documents to the lowest scoring documents using the ORDER BY clause as follows:

SELECT SCORE(1), title from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           ORDER BY SCORE(1) DESC;

PL/SQL Example

In a PL/SQL application, you can use a cursor to fetch the results of the query. The following example issues a query against the NEWS table to find all articles that contain the word oracle. The titles and scores of the first ten hits are output to standard out.

declare 
  rowno number := 0; 
begin 
  for c1 in (SELECT SCORE(1) score, title FROM news 
              WHERE CONTAINS(text, 'oracle', 1) > 0
              ORDER BY SCORE(1) DESC) 
  loop 
    rowno := rowno + 1; 
    dbms_output.put_line(c1.title||': '||c1.score); 
    exit when rowno = 10; 
  end loop; 
end; 

This example uses a cursor FOR loop to retrieve the first ten hits. An alias score is declared for the return value of the SCORE operator. The score and title are output to standard out using cursor dot notation.

You can also optimize this query for response time.