|Oracle8 ConText Cartridge Application Developer's Guide
This chapter contains details for using the ConText SQL functions in SELECT statements to perform one-step queries.
The following topics are covered in this chapter:
In addition to the functions in the PL/SQL packages, ConText provides the following functions for performing one-step queries in SQL*Plus:
|CONTAINS||Specifies the query expression and SCORE label for a one-step query.|
|SCORE||Returns the score generated by CONTAINS.|
Before one-step queries can be executed, the database in which the text resides must be text enabled by setting the ConText initialization parameter TEXT_ENABLE = TRUE. This can be done in two ways:
Use the CONTAINS function in the WHERE clause of a SELECT statement to specify the query expression for a one-step query. You can also define a numeric label for the scores generated by the function so that the SCORE function can be used in other clauses of the SELECT statement.
Specify the text column to be searched in the table.
Specify the query expression for the text or theme to search for in column_id.
For more information about how to write query expressions, see Chapter 3, "Understanding Query Expressions".
Specify the label that identifies the score generated by the CONTAINS function (required only if CONTAINS called more than once in a query).
Specify which policy to use for text columns that have multiple policies.
See the SELECT statement syntax in this chapter.
Each CONTAINS function in a query produces a separate set of score values. When there are multiple CONTAINS functions, each CONTAINS function must have a label specified.
If only one CONTAINS function is used in a SELECT statement, the label parameter is not required in the CONTAINS function; however, a SCORE label value of zero (0) is automatically generated. When the SCORE function is call (e.g. in a SELECT clause), the function must reference the label value.
The CONTAINS function may only be used in the WHERE clause of a SELECT statement; it may not be issued in the WHERE clause of an UPDATE, INSERT or DELETE statement.
In order to specify pol_hint, you must specify label as a place holder. pol_hint must name a policy that is indexed either by text or theme. Do not specify user.policy_name notation for pol_hint; specify only policy name, otherwise ConText will raise an error. You cannot specify bind variables for pol_hint.
When you do not specify pol_hint and column_id has more than one indexed policy attached to it, ConText uses the policy whose name is lexicographically first. For example, if a text column had policies named POL1 and POL2 associated with it and you did not specify pol_hint, ConText uses POL1.
The SCORE function returns the score values produced by the CONTAINS function in a one-step query.
Identifies the scores produced by a query.
The SCORE function may be used in any of these clauses: SELECT, ORDER BY, or GROUP BY.
The value specified for LABEL is the same value defined by the LABEL argument in the CONTAINS function that generated the scores and is referenced by the SCORE function in all other clauses.
If only one CONTAINS function is used in a SELECT statement, the LABEL parameter is not required in the CONTAINS clause, but a SCORE label value of zero (0) will be generated. All other clauses must then refer to SCORE(0) or SCORE(*).
This example returns the score and title of all articles (documents) in the DOCUMENTS.TEXT column that contain the word dog, sorted by score.
You perform one-step queries in SQL*Plus using the SELECT statement. The following syntax illustrates how the CONTAINS and SCORE query functions can be used in a SELECT statement.
SELECT SCORE(label1), SCORE(label2), ...SCORE(labeln),
column1, column2, ... columnn
WHERE CONTAINS (column_id, 'text_query', label1, polhint1) > 0
CONTAINS (column_id, 'text_query', label2, polhint2) > 0
CONTAINS (column_id, 'text_query', labeln, polhintn) > 0
ORDER BY SCORE(labeln)
Specify the numeric label that identifies the specific CONTAINS function that generated the score (required only when CONTAINS is called more than once in a query).
Specify the columns to be returned by the query. Each CONTAINS clause produces a virtual SCORE column that can be referenced by its numeric label (labelx) and included in the query output.
Specify the name of the table that contains the text column to be searched.
If a database link has been created for a remote database, the table specified in a one-step query can reside in the remote database. The table name must include the database link (@dblink) to access the remote table.
For more information about database links and remote queries, see Oracle8 Server Concepts.
Specify the name of the text column.
Specify the query expression to be used to return the relevant text.
Specify the policy to be used when column_id has multiple policies.
The CONTAINS function must always be followed by the > 0 syntax which specifies that the score value calculated by the CONTAINS function must be greater than zero for the row to be selected.
The following example returns the names of all employees who have listed trumpet in their resume or who have been in an orchestra, sorted by the value of the score for the first CONTAINS (trumpet) and the second CONTAINS (orchestra).
SELECT employee_name, SCORE(10), SCORE(20)
WHERE CONTAINS (emp.resume, 'trumpet', 10) > 0 OR
CONTAINS (emp.history, 'orchestra', 20) > 0
ORDER BY NVL(SCORE(10),0), NVL(SCORE(20),0);