Oracle Context Option Application Developer's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

CHAPTER 10. SQL Functions


This chapter contains details for using the ConText Option SQL functions in SELECT statements to perform one-step queries.

The following topics are covered in this chapter:

Query Functions

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

NAME DESCRIPTION
CONTAINS Specifies the query expression and SCORE label for a one-step query
HANDLE Returns a handle for an operand
SCORE Returns the score generated by CONTAINS
Table 10 - 1. ConText Option Query Functions (Page 1 of 1)



Attention: Before one-step queries can be executed, the database in which the text resides must be "text enabled" by setting the ConText Option initialization parameter TEXT_ENABLE = TRUE. This can be done in two ways:

For more information about initialization parameters and the initsid.ora file, see Oracle7 Server Administrator's Guide.

For more information about using the ALTER SESSION command, see Oracle7 Server SQL Reference.

CONTAINS

The CONTAINS function in the WHERE clause of a SELECT statement specifies the query expression for a one-step query and defines 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, TEXT_QUERY, LABEL, POL_HINT)

Argument Datatype Default Purpose
COLUMN_ ID NUMBER Specifies the text column to be searched in the table.
TEXT_ QUERY VARCHAR2 Defines the text to be found in the specified database. (See "Query Expressions" for format and syntax of queries)
LABEL NUMBER Defines the label that identifies the score generated by the CONTAINS function (required only if CONTAINS called more than once in a query)
POL_HINT VARCHAR2 Specifies 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.

HANDLE

The HANDLE function returns a universal handle for its operand.

Syntax

HANDLE(CID, KEY)

Argument Datatype Default Purpose
CID NUMBER Column identifier.
IKEY NUMBER Value of the indexing key

Notes

The HANDLE function can be called from the SELECT list of a SQL query to obtain a reference to a specific cell in the results of the SELECT statement. This function can be useful for post-query operations such as viewing and highlighting.

HANDLE is a reserved word in a query and cannot be aliased.

HANDLE values are abstract.

SCORE

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

Syntax

SCORE(LABEL)

Argument Datatype Default Purpose
LABEL NUMBER 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, 'moriarity', 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 moriarity, sorted by score.

SELECT Syntax Using Query SQL Functions

Queries using the ConText Option query functions are performed with a single 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, pol_hint1) > 0
		CONTAINS (column_id, 'text_query', label2, pol_hint2) > 0
		...
		CONTAINS (column_id, 'text_query', labeln, pol_hintn) > 0
ORDER BY SCORE(labeln)

Where:

labelx

is a 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

are 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

is 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 Oracle7 Server Concepts.

column_id

identifies the column of the database that contains the text.

text_query

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

pol_hintx

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

Example

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




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index