9
SQL Functions

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:

• Query Functions
• SELECT Statement

Query Functions

In addition to the functions in the PL/SQL packages, ConText provides the following functions for performing one-step queries in SQL*Plus:

Name	Description
CONTAINS	Specifies the query expression and SCORE label for a one-step query.
SCORE	Returns the score generated by CONTAINS.

 

Prerequisites

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:

• setting it in the initsid.ora system initialization file
• using the ALTER SESSION command

 

See Also:  For more information about initialization parameters and the initsid.ora file, see Oracle8 Administrator's Guide.  For more information about using the ALTER SESSION command, see Oracle8 SQL Reference.

   

---

CONTAINS

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.

Syntax

CONTAINS(
         column_id        NUMBER,
         text_query       VARCHAR2,
         label            NUMBER, 
         pol_hint         VARCHAR2)

column_id

Specify the text column to be searched in the table.

text_query

Specify the query expression for the text or theme to search for in column_id.
 

See Also:  For more information about how to write query expressions, see Chapter 3, "Understanding Query Expressions".

   

label

Specify the label that identifies the score generated by the CONTAINS function (required only if CONTAINS called more than once in a query).

pol_hint

Specify which policy to use for text columns that have multiple policies.

Example

See the SELECT statement syntax in this chapter.

Notes

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.
 

Suggestion:  Oracle Corporation does not recommend relying on ConText to select a policy when you perform queries on columns with multiple policies. In this situation, always specify a policy name in pol_hint.

   

---

SCORE

The SCORE function returns the score values produced by the CONTAINS function in a one-step query.

Syntax

SCORE(label NUMBER)

label

Identifies the scores produced by a query.

Notes

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(*).

Example

SELECT SCORE(10), title FROM documents
WHERE CONTAINS(text, 'dog', 10) > 0
ORDER BY SCORE(10);

This example returns the score and title of all articles (documents) in the DOCUMENTS.TEXT column that contain the word dog, sorted by score.

SELECT Statement

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.

Syntax

SELECT SCORE(label1), SCORE(label2), ...SCORE(labeln),
column1, column2, ... columnn
FROM table[@dblink]
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)

labelx

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).

columnn

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.

table

Specify the name of the table that contains the text column to be searched.
 

Note:  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 Concepts.

   

column_id

Specify the name of the text column.

text_query

Specify the query expression to be used to return the relevant text.

pol_hint_x

Specify the policy to be used when column_id has multiple policies.

Notes

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.
 

Note:  Other comparison operators and other numeric values can be used to satisfy this requirement and select rows with specific SCORE values; however, this method of refining the selection criteria is significantly less efficient than using the threshold and weight query expression operators.

   

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)
FROM employee_database
WHERE CONTAINS (emp.resume, 'trumpet', 10) > 0 OR
CONTAINS (emp.history, 'orchestra', 20) > 0
ORDER BY NVL(SCORE(10),0), NVL(SCORE(20),0);

---