|Oracle8i interMedia Text Migration
Release 2 (8.1.6)
Part Number A77061-01
Querying, 3 of 12
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.
In the pre-8.1 method, you create a result table as follows:
Alternatively, you can also create a result table using CTX_QUERY.GETTAB.
You execute the CONTAINS procedure as follows:
You then join the result table with the base table to retrieve the document text as follows:
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:
You can order the results from the highest scoring documents to the lowest scoring documents using the ORDER BY clause as follows:
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.
For more information about optimizing for response time, see "Cursor Query" in this chapter.