11
PL/SQL Packages

This chapter provides reference information for using the PL/SQL packages provided with ConText to administer ConText servers and queues, and manage text.

Administration packages:

Text management packages:

Miscellaneous packages:

CTX_INFO: Product Information

CTX_ADM: ConText Administration

The CTX_ADM PL/SQL package is used to manage ConText servers and queues.

CTX_ADM contains the following stored procedures and functions:

Name	Description
CHANGE_MASK	Modifies the personality mask for a ConText server
GET_QUEUE_STATUS	Returns the status of the specified queue
RECOVER	Cleans up database objects for deleted text tables
SET_QUERY_BUFFER_SIZE	Increases the size of the pipe used for queries
SHUTDOWN	Shuts down a single ConText server or all currently running ConText servers
UPDATE_QUEUE_STATUS	Updates the status of the specified queue

CHANGE_MASK

The CHANGE_MASK procedure changes the personality mask of the specified ConText server.

Syntax

CTX_ADM.CHANGE_MASK(name            IN VARCHAR2
                   personality_mask IN VARCHAR2 DEFAULT 'QDM');
name

Specify the name (internal identifier) of the server for which you are changing the personality mask.

personality_mask

Specify the new personality mask that you want to assign to the server. Can be any combination of:

R (Loader)
Q (Query)
D (DDL)
M (DML)
L (Linguistic)

Default is QDM.

Examples

execute ctx_adm.change_mask('DRSRV_8025', 'D')

Notes

The names of all currently running ConText servers can be obtained from the CTX_SERVERS or CTX_ALL_SERVERS views.

GET_QUEUE_STATUS

The GET_QUEUE_STATUS function returns the status of the specified ConText queue.

Syntax

CTX_ADM.GET_QUEUE_STATUS(qname IN VARCHAR2)
RETURN VARCHAR2;
qname

Specify the queue/pipe for which you want to return the status:

TEXT_QUEUE (DDL and Query pipes)
DML_QUEUE
SERVICES_QUEUE

Returns

Status of the queue, which is one of the following:

ENABLED

The specified queue is enabled.

DISABLED

The specified queue is disabled.

Examples

declare status varchar2(8);
begin
  status := ctx_adm.get_queue_status('DML_QUEUE');
end;

Notes

A status of DISABLED indicates the queue or pipe is inactive and requests in the queue will not be processed by any of the available ConText servers.

When a queue or pipe has a status of DISABLED, the queue continues to accept requests. The ConText administrator should regularly monitor the status of the queues and pipes to prevent accumulation of requests in disabled queues.

To enable a disabled queue, you must call CTX_ADM.UPDATE_QUEUE_STATUS.

RECOVER

The RECOVER procedure deletes all database objects for text tables that have been deleted without first dropping the index or policies for the tables.

Syntax

CTX_ADM.RECOVER;

Examples

execute ctx_adm.recover

Notes

ConText Servers automatically perform recovery approximately every fifteen minutes. CTX_ADM.RECOVER provides a method for users to manually perform recovery on command.

SET_QUERY_BUFFER_SIZE

The SET_QUERY_BUFFER_SIZE procedure sets the size of the database pipe used for queries.

Syntax

CTX_ADM.SET_QUERY_BUFFER_SIZE(buffer_size IN NUMBER);
buffer_size

Specify the size, in bytes, of the query buffer.

Examples

execute ctx_adm.set_query_buffer_size(100000);

Notes

The default size of the buffer is 8192 bytes.

CTX_ADM.SET_QUERY_BUFFER_SIZE can only be used to increase the size of the buffer from the default size.

SHUTDOWN

The SHUTDOWN procedure shuts down the specified ConText server.

Syntax

CTX_ADM.SHUTDOWN(name   IN VARCHAR2 DEFAULT 'ALL',
                 sdmode IN NUMBER   DEFAULT NULL);
name

Specify the name (internal identifier) of the ConText server to shutdown.

Default is ALL.

sdmode

Specify the shutdown mode for the server:

0 or NULL (Normal)
1 (Immediate)
2 (Abort)

Default is NULL.

Examples

execute ctx_adm.shutdown('DRSRV_3321', 1)

Notes

If you do not specify a ConText server to shut down, CTX_ADM.SHUTDOWN shuts down all currently running ConText servers.

The names of all currently running ConText servers can be obtained from the CTX_SERVERS view.

UPDATE_QUEUE_STATUS

The UPDATE_QUEUE_STATUS procedure is used to change the status of the specified ConText queue (Text, DML, or Services).

For example, the GET_QUEUE_STATUS returns a status of DISABLED for one of the queues. Once the error that caused the queue to become disabled is cleared, UPDATE_QUEUE_STATUS can be called with an action of ENABLE_QUEUE to reactivate the queue.

UPDATE_QUEUE_STATUS can also be used to control request processing in the system. When you disable a queue, you prevent any currently running ConText servers from picking up queued requests until you enable the queue.

Syntax

CTX_ADM.UPDATE_QUEUE_STATUS(qname IN VARCHAR2,
                            qstatus IN VARCHAR2 DEFAULT ENABLE_QUEUE);
qname

Specify the queue or pipe for which you want to return the status:

TEXT_QUEUE (DDL and Query pipes)
DML_QUEUE
SERVICES_QUEUE qstatus

Specify the action to perform on the queue:

DISABLE_QUEUE
ENABLE_QUEUE

Default is ENABLE_QUEUE.

Examples

execute ctx_adm.update_queue_status('ctx_adm.dml_queue','ctx_adm.enable_queue')

Notes

A queue with a status of DISABLED will remain inactive until it is enabled using UPDATE_QUEUE_STATUS; however, the queue will continue to accept requests. The ConText administrator should regularly monitor the status of the queues and pipes to prevent accumulation of requests in disabled queues.

Both qname and qstatus must be fully qualified with the PL/SQL package name (CTX_ADM) as shown in the examples.

CTX_SVC: Services Queue Administration

The CTX_SVC PL/SQL package is used to query requests in the Services Queue and to manage the queue.

CTX_SVC contains the following stored procedures and functions:

Name	Description
CANCEL	Removes a pending request from the Services Queue
CANCEL_ALL	Removes all pending requests from the Services Queue
CANCEL_USER	Removes a pending request from the Services Queue for the current user
CLEAR_ALL_ERRORS	Removes all requests with an error status from the Services Queue
CLEAR_ERROR	Removes a request with an error status from the Services Queue
CLEAR_INDEX_ERRORS	Removes errored indexing requests from the Services Queue
CLEAR_LING_ERRORS	Removes errored requests for Linguistic Services from the Services Queue
REQUEST_STATUS	Returns the status of a request in the Services Queue

CANCEL

The CANCEL procedure removes a request with a status of PENDING from the Services Queue.

Syntax

CTX_SVC.CANCEL(request_handle IN NUMBER);
request_handle

Specify the handle, returned by CTX_LING.SUBMIT, of the service request to remove.

Examples

execute ctx_svc.cancel(3321)

Notes

Requests with a status other than pending in the Services Queue cannot be removed using CTX_SVC.CANCEL. To cancel requests that ConText has not yet entered into the Services Queue, use CTX_LING.CANCEL.

See Also:

For more information about the CTX_LING PL/SQL package, see Oracle8 ConText Cartridge Application Developer's Guide.

CANCEL_ALL

The CANCEL_ALL procedure removes all requests with a status of PENDING from the Services Queue.

Syntax

CTX_SVC.CANCEL_ALL;

Examples

execute ctx_svc.cancel_all

CANCEL_USER

The CANCEL_USER procedure removes all requests with a status of PENDING for the current user from the Services Queue.

Syntax

CTX_SVC.CANCEL_USER;

Examples

execute cancel

CLEAR_ALL_ERRORS

The CLEAR_ALL_ERRORS procedure removes all requests (text indexing, theme indexing, and linguistics) that have a status of ERROR in the Services Queue.

Syntax

CTX_SVC.CLEAR_ALL_ERROR;

Examples

execute ctx_svc.clear_all_errors

CLEAR_ERROR

The CLEAR_ERROR procedure can be used to remove a request with a status of ERROR from the Services Queue.

Syntax

CTX_SVC.CLEAR_ERROR(request_handle IN NUMBER);
request_handle

Specify the handle, returned by CTX_LING.SUBMIT, of the errored service request to remove.

See Also:

For more information about SUBMIT and the CTX_LING PL/SQL package, see Oracle8 ConText Cartridge Application Developer's Guide.

Examples

execute clear_error(214)

Notes

If you call CLEAR_ERROR with a 0 (zero) value for request_handle, all requests with a status of ERROR in the Services Queue are removed.

Use the CTX_SVC.REQUEST_STATUS function to return the status of a request in the Services Queue.

CLEAR_INDEX_ERRORS

The CLEAR_INDEX_ERRORS procedure removes all indexing requests that have a status of ERROR in the Services Queue.

Syntax

CTX_SVC.CLEAR_INDEX_ERROR;

Examples

execute ctx_svc.clear_index_errors

CLEAR_LING_ERRORS

The CLEAR_LING_ERRORS procedure removes all Linguistic Services requests that have a status of ERROR in the Services Queue.

Syntax

CTX_SVC.CLEAR_LING_ERROR;

Examples

execute ctx_svc.clear_ling_errors

REQUEST_STATUS

The REQUEST_STATUS function returns the status of a request in the Services Queue.

Syntax

CTX_SVC.REQUEST_STATUS(request_handle  IN  NUMBER,
                      timestamp       OUT DATE,
                      errors          OUT VARCHAR2)
RETURN VARCHAR2;
request_handle

Specify the handle of the service request, as returned by CTX_LING.SUBMIT.

timestamp

Returns the time at which request was submitted.

errors

Returns the error message stack for the request. The message stack is returned only if the status of the request is ERROR.

See Also:

For more information about SUBMIT and the CTX_LING PL/SQL package, see Oracle8 ConText Cartridge Application Developer's Guide.

Returns

Status of the request, which is one of the following:

PENDING

The request has not yet been picked up by a ConText server.

RUNNING

The request is being processed by a ConText server.

ERROR

The request encountered an error (see errors argument).

SUCCESS

The request completed successfully.

Examples

declare status varchar2(10);
declare timestamp date;
declare errors varchar2(60);
begin
   status := ctx_svc.request_status(3461,timestamp,errors);
   dbms_output.put_line(status,timestamp,substr(errors,1,20);
end;

Notes

Specifying an invalid value for request_handle causes CTX_SVC.REQUEST_STATUS to return a status of SUCCESS.

CTX_DDL: Text Setup and Management

The CTX_DDL PL/SQL package is used to create preferences and policies for ConText and to perform DDL actions such as index creation and optimization.

CTX_DDL contains the following stored procedures and functions:

Name	DESCRIPTION
CLEAR_ATTRIBUTES	Clears the buffer for any attributes that have been set
CREATE_INDEX	Creates an index for the text column using the specified policy
CREATE_POLICY	Creates a policy in the ConText data dictionary
CREATE_PREFERENCE	Creates a preference in the ConText data dictionary
CREATE_SOURCE	Creates a text loading source in the ConText data dictionary
CREATE_TEMPLATE_POLICY	Creates a policy that has no text column defined
DROP_INDEX	Deletes an index
DROP_POLICY	Deletes a policy from the ConText data dictionary
DROP_PREFERENCE	Deletes a preference from the ConText data dictionary
DROP_SOURCE	Deletes a text loading source from the ConText data dictionary
OPTIMIZE_INDEX	Combines index fragments into complete strings and updates index strings for deleted documents
RESUME_FAILED_INDEX	Resumes creation/optimization of a failed ConText index
SET_ATTRIBUTE	Specifies the Tile attribute and corresponding value for a preference
UPDATE_POLICY	Changes the description and/or the preferences in a policy
UPDATE_SOURCE	Changes the description and/or the preferences in a source

CLEAR_ATTRIBUTES

The CLEAR_ATTRIBUTES procedure clears the buffer of all attributes that have been set using CTX_DDL.SET_ATTRIBUTE.

Syntax

CTX_DDL.CLEAR_ATTRIBUTES;

Examples

execute ctx_ddl.clear_attributes

CREATE_INDEX

The CREATE_INDEX procedure creates an index for the column defined in the specified policy.

Syntax

CTX_DDL.CREATE_INDEX(policy_name IN VARCHAR2,
                    parallel    IN VARCHAR2 DEFAULT 1);
policy_name

Specify the name of the policy for which the index is created.

parallel

Specify the number of ConText servers to be used in parallel to create the index for a column.

The default is 1.

Examples

execute ctx_ddl.create_index('MY_POLICY', 2)

CREATE_POLICY

The CREATE_POLICY procedure creates a policy for a column.

Syntax

CTX_DDL.CREATE_POLICY(
                    policy_name     IN VARCHAR2,
                    colspec         IN VARCHAR2 DEFAULT NULL,
                    source_policy   IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_POLICY',
                    description     IN VARCHAR2 DEFAULT NULL,
                    textkey         IN VARCHAR2 DEFAULT NULL,
                    lineno          IN VARCHAR2 DEFAULT NULL,
                    dstore_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_DIRECT_DATASTORE',
                    compressor_pref IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_COMPRESSOR',
                    filter_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_FILTER',
                    lexer_pref      IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_LEXER',
                    wordlist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.NO_SOUNDEX',
                    stoplist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_STOPLIST',
                    engine_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_INDEX');
policy_name

Specify the name of the policy to be created.

colspec

Specify the column (and table) to which the policy is assigned. If not value is specified for colspec, a template policy is created.

source_policy

Specify the name of a template policy on which the column policy to be created is based.

The default is DEFAULT_POLICY.

description

Specify the description of the policy.

textkey

Specify the column or columns (up to sixteen) that represent the unique identifier (textkey) for each document.

lineno

Specify the column that stores the unique ID for each document section in a master-detail table.

dstore_pref

Specify the name of the Data Store preference assigned to the policy.

compressor_pref

Specify the name of the Compressor preference assigned to the policy (Compressors are not currently provided or supported by ConText).

filter_pref

Specify the name of the Filter preference assigned to the policy.

lexer_pref

Specify the name of the Lexer preference assigned to the policy.

wordlist_pref

Specify the name of the Wordlist preference assigned to the policy.

stoplist_pref

Specify the name of the Stoplist preference assigned to the policy.

engine_pref

Specify the name of the Engine preference assigned to the policy.

Examples

begin
  ctx_ddl.create_policy(policy_name  => 'MY_POLICY',
                        colspec      => 'DOCS.TEXT',
                        desrcription => 'This is my policy',
                        textkey      => 'AUTH,TITLE'
                        dstore_pref  => 'INTERNAL_STORE',
                        filter_pref  => 'ASCII_TXT',
                        lexer_pref   => 'ENGLISH_BASIC',
                        wordlist_pref => 'CTXSYS.NO_SOUNDEX',
                        stoplist_pref => 'MY_LIST'
                        engine_pref   => 'BASIC_INDEX',);
end;

In this example, the textkey for docs.text is a composite textkey consisting of two columns in docs named auth and title.

Notes

All of the arguments are optional, except for policy_name. If you do not specify a preference for one of the categories, the default preference for the category is automatically used.

For a composite textkey, each column name specified in textkey must be separated by a comma from the other column names. In addition, the string of column names is limited to 256 characters, including the comma.

If a preference belonging to another user is specified in a policy, the fully-qualified name of the preference must be used. For example, if you want to include the NO_SOUNDEX predefined preference in a policy, the syntax would be:

(...,
wordlist_pref => CTXSYS.NO_SOUNDEX,
...)

CREATE_PREFERENCE

The CREATE_PREFERENCE procedure creates a preference in the ConText data dictionary for a Tile. All Tile attributes and their values that have been set using CTX_DDL.SET_ATTRIBUTE are applied to the preference created by CREATE_PREFERENCE.

The preference can then be used in a policy (indexing/linguistic generation) or a source (text loading).

Syntax

CTX_DDL.CREATE_PREFERENCE(preference_name IN VARCHAR2,
                         description     IN VARCHAR2,
                         object_name     IN VARCHAR2);
preference_name

Specify the name of the preference to be created.

description

Specify the description for the preference.

object_name

Specify the Tile for the preference.

Examples

begin
  ctx_ddl.create_preference('NO_JOIN',
                            'Text engine that does not use any printjoins',
                            'GENERIC ENGINE');
end;

Notes

CREATE_PREFERENCE must always be preceded by one or more SET_ATTRIBUTE calls, which set the attribute values for the specified Tile.

Once CREATE_PREFERENCE is called, the buffer used to store the attributes that were set for the preference is cleared. If the preference creation failed, all of the attributes must be entered again before calling CREATE_PREFERENCE.

CREATE_SOURCE

The CREATE_SOURCE procedure creates a text loading source for a column.

Syntax

CTX_DDL.CREATE_SOURCE(name            IN VARCHAR2,
                      colspec         IN VARCHAR2 DEFAULT NULL,
                      description     IN VARCHAR2 DEFAULT NULL,
                      refresh         IN NUMBER   DEFAULT NULL,
                      engine_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_LOADER',
                      translator_pref IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_TRANSLATOR',
                      reader_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_READER');
name

Specify the name of the source to be created.

colspec

Specify the column (and table) to which the source is assigned.

description

Specify the description of the source.

refresh

Specify the elapsed time, in minutes, before a ConText server checks the specified directory for new files to be loaded.

engine_pref

Specify the name of the Loader Engine preference assigned to the source.

translator_pref

Specify the name of the Translator preference assigned to the policy.

reader_pref

Specify the name of the Reader preference assigned to the source.

Examples

begin
  ctx_ddl.create_source(name         => 'MY_SOURCE',
                        colspec      => 'DOCS.TEXT',
                        desrcription => 'Source for loading',
                        reader_pref  => 'DOCS_DIRECTORY');
end;

In this example, the default Loader Engine and Translator preferences are used.

Notes

colspec must be a LONG or LONG RAW column, because load servers only support loading text into LONG or LONG RAW columns.

If a Loader Engine, Reader, or Translator preference belonging to another user is used to create a source, the fully-qualified name of the preference must be used.

The first time the source directory is scanned for files to load is SYSDATE (of source creation) + refresh. Subsequent scans occur at regular intervals specified by refresh.

CREATE_TEMPLATE_POLICY

The CREATE_TEMPLATE_POLICY procedure creates a policy that does not have a reference to a text column. It is identical to CTX_DDL.CREATE_POLICY, except the colspec argument is not included.

The template policy can be used as a source policy for other policies in the user's schema. If CTXSYS creates a template policy, the policy is available to all ConText users.

Syntax

CTX_DDL.CREATE_TEMPLATE_POLICY(
                    policy_name     IN VARCHAR2,
                    source_policy   IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_POLICY',
                    description     IN VARCHAR2 DEFAULT NULL,
                    textkey         IN VARCHAR2 DEFAULT NULL,
                    lineno          IN VARCHAR2 DEFAULT NULL,
                    dstore_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_DIRECT_DATASTORE',
                    compressor_pref IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_COMPRESSOR',
                    filter_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_FILTER',
                    lexer_pref      IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_LEXER',
                    wordlist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.NO_SOUNDEX',
                    stoplist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_STOPLIST',
                    engine_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_INDEX');
policy_name

Specify the name of the template policy to be created.

source_policy

Specify the name of another template policy on which the template policy to be created is based.

The default is DEFAULT_POLICY.

description

Specify the description of the template policy.

textkey

Specify the column or columns (up to sixteen) that represent the unique identifier (textkey) for each document.

lineno

Specify the column that stores the unique ID for each document section in a master-detail table.

dstore_pref

Specify the name of the Data Store preference assigned to the template policy.

compressor_pref

Specify the name of the Compressor preference assigned to the template policy (Compressors are not currently provided or supported by ConText).

filter_pref

Specify the name of the Filter preference assigned to the template policy.

lexer_pref

Specify the name of the Lexer preference assigned to the template policy.

wordlist_pref

Specify the name of the Wordlist preference assigned to the template policy.

stoplist_pref

Specify the name of the Stoplist preference assigned to the template policy.

engine_pref

Specify the name of the Engine preference assigned to the template policy.

Examples

See CTX_DDL.CREATE_POLICY

DROP_INDEX

The DROP_INDEX procedure deletes the index for the column defined in the specified policy.

Syntax

CTX_DDL.DROP_INDEX(policy_name IN VARCHAR2);
policy_name

Specify the name of the policy for which the index is deleted.

Examples

execute ctx_ddl.drop_index('MY_POLICY')

DROP_INTTRIG

The DROP_INTTRIG procedure deletes the DML trigger for a specified table. A DML trigger is created automatically on a table when a ConText index is created for a text column in the table.

Syntax

CTX_DDL.DROP_INTTRIG(tablename IN VARCHAR2);
tablename

Specify the name of the table for which the DML trigger is dropped.

Examples

execute ctx_ddl.drop_inttrig('DOCS')

DROP_POLICY

The DROP_POLICY procedure deletes the specified policy from the ConText data dictionary.

Syntax

CTX_DDL.DROP_POLICY(policy_name IN VARCHAR2);
policy_name

Specify the name of the policy to be dropped.

Examples

execute ctx_ddl.drop_policy('MY_POLICY')

Notes

If the specified policy has an existing index, the index must be dropped using CTX_DDL.DROP_INDEX before the policy can be dropped.

DROP_PREFERENCE

The DROP_PREFERENCE procedure deletes the specified preference from the ConText data dictionary.

Syntax

CTX_DDL.DROP_PREFERENCE(preference_name IN VARCHAR2);
preference_name

Specify the name of the preference to be dropped.

Examples

execute ctx_ddl.drop_preference('MY_ENGINE')

Notes

If the specified preference is currently used in a policy, the policy must be dropped using CTX_DDL.DROP_POLICY before the policy can be dropped.

DROP_SOURCE

The DROP_SOURCE procedure deletes the specified text loading source from the ConText data dictionary. A source can be dropped at any time.

Syntax

CTX_DDL.DROP_SOURCE(source_name IN VARCHAR2);
source_name

Specify the name of the source to be dropped.

Examples

execute ctx_ddl.drop_source('MY_LOADER')

OPTIMIZE_INDEX

The OPTIMIZE_INDEX procedure optimizes the index for the column defined in the specified policy.

Syntax

CTX_DDL.OPTIMIZE_INDEX(policy_name IN VARCHAR2,
                       opttyp      IN NUMBER  DEFAULT NULL,
                       threshold   IN NUMBER  DEFAULT 50,
                       parallel    IN NUMBER  DEFAULT 1,
                       switch_new  IN BOOLEAN DEFAULT TRUE,
                       drop_old    IN BOOLEAN DEFAULT TRUE);
policy_name

Specify the name of the policy for the index to be optimized.

opttyp

Specify the type of optimization performed for the index:

1 (DR_OPTIMIZE_LAZY_DELETES) - use original token table to remove references for deleted/modified documents
2 (DR_OPTIMIZE_COMPACT_INDEXES) - use original token table to compact index fragments
3 (DR_OPTIMIZE_COMPACT_NEW) - use mirror token table to compact index fragments
4 (DEFRAGMENT_TO_NEW_TABLE) - use mirror token table to compact index fragments and remove deleted/modified document references
5 (DEFRAGMENT_IN_PLACE) - use original token table to compact index fragments and remove deleted document references

The default depends on the value set for the DEFAULT_OPTIMIZE attribute in the BASIC ENGINE Tile (see "Notes" for OPTIMIZE_INDEX).

threshold

Specify the threshold, as a percentage, under which a term's index strings are not compacted during in-place compaction.

The default is 50.

parallel

Specify the number of ConText servers to be used in parallel to perform two-table optimization.

The default is 1.

switch_new

For internal use only.

drop_old

For internal use only.

Examples

begin
  ctx_ddl.optimize_index('MY_POLICY',
                         opttyp => ctx_ddl.defragment_in_place,
                         parallel => 2);
end;

Notes

Optimization cannot be performed for an index while any other operation (i.e. creation, updating, deletion) is being performed on the index.

opttyp must be fully qualified with the PL/SQL package name (CTX_DDL) as shown in the examples.

The default for opttyp is the value specified for the DEFAULT_OPTIMIZE attribute (BASIC ENGINE Tile) in the Engine preference of the policy for the text column to be optimized. If no value was specified for DEFAULT_OPTIMIZE when the Engine preference for the policy was created, the default is DEFRAGMENT_TO_NEW_TABLE.

DEFRAGMENT_IN_PLACE does not use threshold. If opttyp is DEFRAGMENT_IN_PLACE, OPTIMIZE_INDEX ignores any value specified for threshold.

parallel is used only for two-table compaction and two-table combined reference deletion and compaction.

threshold is used only for in-place compaction. It specifies the percentage under which ConText compacts a term's index fragments (rows) if the compaction will result in the number of fragments for the term being reduced to more than or equal to the percentage specified.

For example, a value of 60 for threshold indicates the number of fragments for a given term must be reduced to 60% or more of the total number of pre-optimization fragments for in-place compaction to take place.

RESUME_FAILED_INDEX

The RESUME_FAILED_INDEX procedure resumes an unsuccessful text DDL operation (index creation/optimization).

Note:

RESUME_FAILED_INDEX should be called only after the problem that caused the failure has been corrected or removed.

Syntax

CTX_DDL.RESUME_FAILED_INDEX(policy_name IN VARCHAR2,
                           operation   IN NUMBER  DEFAULT 1,
                           parallel    IN NUMBER  DEFAULT 1,
                           opttyp      IN NUMBER  DEFAULT 3,
                           switch_new  IN BOOLEAN DEFAULT TRUE,
                           drop_old    IN BOOLEAN DEFAULT TRUE);
policy_name

Specify the index (through the policy) that requires an Oracle index.

operation

Specify the operation that was being performed on the index at the time of failure:

1 (OPERATION_CREATE)
2 (OPERATION_OPTIMIZE)

The default is 1.

parallel

If operation is 1 (index creation), then use this argument to specify the degree of parallelism used for creating the index.

The default is 1.

opttyp

If operation is 2 (OPERATION_OPTIMIZE), use this argument to specify the method of two-table optimization to use:

3 (DR_OPTIMIZE_COMPACT_NEW) - use mirror index table to compact index fragments)
4 (DEFRAGMENT_TO_NEW_TABLE) - use mirror token table to compact index fragments and remove deleted/modified document references)

The default depends on the value set for the default_optimize attribute in the BASIC ENGINE Tile (see notes).

Examples

begin
  ctx_ddl.resume_failed_index('MY_POLICY',
                              operation => 2,
                              parallel  => 2,
                              opttyp    => ddl.defragment_to_new_table);
end;

In this example, optimization (operation => 2) is resumed with a parallelism level of 2 for the index for my_policy. The type of optimization performed is compaction and garbage collection combined.

Notes

Only the owner of the policy or CTXSYS can resume creation of a ConText index.

RESUME_FAILED_INDEX uses the ConText index log to determine the point of failure for the index and the point from which to proceed with indexing/optimization.

Depending on the stage at which the text DDL operation failed, RESUME_FAILED_INDEX may start the operation from the beginning, in which case, CREATE_INDEX or OPTIMIZE_INDEX serves the same purpose as RESUME_FAILED_INDEX and can be called in its place.

Because RESUME_FAILED_INDEX automatically determines where to resume a failed DDL operation, the user should consult the index log before calling RESUME_FAILED_INDEX to decide whether to call CREATE_INDEX/OPTIMIZE_INDEX instead.

opttyp must be fully qualified with the PL/SQL package name (CTX_DDL) as shown in the examples.

The default for opttyp is the value specified for the default_optimize attribute (BASIC ENGINE Tile) in the Engine preference of the policy for the text column to be optimized. If no value was specified for default_optimize when the Engine preference for the policy was created, the default is 3 (DR_OPTIMIZE_COMPACT_NEW).

SET_ATTRIBUTE

The SET_ATTRIBUTE procedure assigns values to Tile attributes used in the CTX_DDL.CREATE_PREFERENCE procedure.

Syntax

CTX_DDL.SET_ATTRIBUTE(name  IN VARCHAR2,
                     value  IN VARCHAR2,
                     seq    IN NUMBER DEFAULT 1);

CTX_DDL.SET_ATTRIBUTE(name  IN VARCHAR2,
                     value1 IN VARCHAR2,
                     value2 IN VARCHAR2,
                     seq    IN NUMBER);
name

Specify the attribute to which a value is assigned.

value

Specify the value assigned to the attribute. This argument is not used when value1 and value2 are used.

value1

Specify the first value assigned to the attribute (used only for the executable attribute for the BLASTER Tile).

value2

Specify the second value assigned to attribute (used only for the executable attribute for the BLASTER Tile).

seq

Specify the sequence number assigned to the attribute (only required for creating preferences that use Tiles which support multiple values for the same attribute)

The default is 1.

Examples

execute ctx_ddl.set_attribute('INDEX_MEMORY', '3000000')

In example 1, the index_memory attribute is assigned approximately 3 megabytes of memory. The index_memory attribute belongs to the GENERIC ENGINE Tile and is used for allocating indexing memory.


execute ctx_ddl.set_attribute('STOP_WORD', 'of', 1)
execute ctx_ddl.set_attribute('STOP_WORD', 'and', 2)

In example 2, the stop_word attribute (GENERIC STOP LIST Tile) is set twice, once for the stopword of and once for the stopword and. The stopwords are assigned sequences of 1 and 2 respectively in the stoplist.


execute ctx_ddl.set_attribute('EXECUTABLE', 19, 'amipro.sh', 1)
execute ctx_ddl.set_attribute('EXECUTABLE', 57, 'acrobat.sh', 2)

In example 3, the executable attribute (BLASTER FILTER Tile) is set twice to register external filter executables (amipro.sh and acrobat.sh) for AmiPro and Adobe Acrobat (PDF) documents. AmiPro has a format code of 19 and Acrobat has a format code of 57. The executables are assigned sequences of 1 and 2 respectively.

Notes

SET_ATTRIBUTE writes the specified attribute values to an internal buffer. Once all of the attributes for a particular Tile have been set, CTX_DDL.CREATE_PREFERENCE is called to create a preference for the Tile.

Any errors that may occur from entering incorrect values for SET_ATTRIBUTE are not reported until CREATE_PREFERENCE is called.

When CREATE_PREFERENCE is called, the buffer used to store the attributes for the preference is cleared. If the preference creation failed, all of the attributes must be entered again before calling CREATE_PREFERENCE.

CTX_DDL.CLEAR_ATTRIBUTES can be used to manaully clear all attributes in the buffer.

seq is only used with the Tiles that support multiple values for the same attribute (i.e. BLASTER FILTER and GENERIC STOP LIST). For all the other Tiles, seq is not required and should not be set.

A call to SET_ATTRIBUTE that uses the same seq value as a previous call to SET_ATTRIBUTE overrides the previously attribute that was set in the buffer.

UPDATE_POLICY

The UPDATE_POLICY procedure updates the description and/or the preferences for an existing column or template policy. For column policies, it can only be used to update a column policy if ConText has not yet generated a ConText index for the policy.

Syntax

CTX_DDL.UPDATE_POLICY(
                    policy_name     IN VARCHAR2,
                    description     IN VARCHAR2 DEFAULT NULL,
                    dstore_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_DIRECT_DATASTORE',
                    compressor_pref IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_COMPRESSOR',
                    filter_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_NULL_FILTER',
                    lexer_pref      IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_LEXER',
                    wordlist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.NO_SOUNDEX',
                    stoplist_pref   IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_STOPLIST',
                    engine_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_INDEX');
policy_name

Specify the name of the policy to be updated.

description

Specify the new description of the policy.

dstore_pref

Specify the name of the new Data Store preference for the policy.

compressor_pref

Specify the name of the new Compressor preference (Compressors are not currently provided or supported by ConText).

filter_pref

Specify the name of the new Filter preference for the policy.

lexer_pref

Specify the name of the new Lexer preference for the policy.

wordlist_pref

Specify the name of the new Wordlist preference for the policy.

stoplist_pref

Specify the name of the new Stoplist preference for the policy.

engine_pref

Specify the name of the new Engine preference for the policy.

Examples

begin
  ctx_ddl.update_policy(policy_name   => 'MY_POLICY',
                        dstore_pref   => 'CTXSYS.MD_BINARY');
end;

Notes

If a preference belonging to another user is used to update a policy, the fully-qualified name of the preference must be used.

UPDATE_SOURCE

The UPDATE_SOURCE procedure updates the description, text column, refresh rate, and preferences for the text loading source specified in the argument string. UPDATE_SOURCE can be called at any time for any existing source.

Syntax

CTX_DDL.CREATE_SOURCE(name            IN VARCHAR2,
                      colspec         IN VARCHAR2 DEFAULT NULL,
                      description     IN VARCHAR2 DEFAULT NULL,
                      refresh         IN NUMBER   DEFAULT NULL,
                      next            IN DATE     DEFAULT NULL
                      engine_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_LOADER',
                      translator_pref IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_TRANSLATOR',
                      reader_pref     IN VARCHAR2 DEFAULT 'CTXSYS.DEFAULT_READER');
name

Specify the name of the source to be updated.

colspec

Specify the new text column (and table) to which the source is assigned.

description

Specify the new description for the source.

refresh

Specify the new elapsed time, in minutes, before a ConText server checks the directory (specified in the Reader preference) for new files to be loaded.

Specify the date and time for the initial scan of the updated source by available Loader servers.

engine_pref

Specify the name of the new Loader Engine preference assigned to the source.

translator_pref

Specify the name of the new Translator preference assigned to the source.

reader_pref

Specify the name of the new Reader preference assigned to the source.

Examples

begin
  ctx_ddl.update_policy(policy_name   => 'MY_POLICY',
                        dstore_pref   => 'CTX.MD_BINARY');
end;

Notes

If a Loader Engine, Reader, or Translator preference belonging to another user is used to update a source, the fully-qualified name of the preference must be used.

next specifies the date and time that an updated source is initially scanned by ConText servers running with the Loader (R) personality.

The next scan of the source occurs at next + refresh, then all subsequent scans occur at regular intervals specified by refresh.

CTX_DML: ConText Index Update and Management

The CTX_DML PL/SQL package is used to manage DML Operations.

CTX_DML contains the following stored procedures and functions:

Name	Description
REINDEX	Specify reindexing for a document
SYNC	Batches all pending requests in DML Queue and enables ConText servers with DDL personality to process the batches
SYNC_QUERY	Returns a time-stamp in the form of a date for the batches generated by SYNC

REINDEX

The REINDEX procedure is used to write a row to the DML Queue for a specified document. The index for the document is then created/updated according to the DML method being used (immediate or batch).

REINDEX can be used to reindex documents that have errored during DDL or DML. It can also be used to provide DML processing on a view. Views cannot have a trigger assigned, meaning that DML operations on a view cannot be sent to the DML Queue by way of the trigger that is automatically created when a table is indexed.

Finally, it can be used to notify the system of updates to documents stored externally. If a document uses the OSFILE Data Store, REINDEX can be called when the document is updated to ensure that the update is recorded in the DML Queue.

Syntax

CTX_DML.REINDEX(policy IN VARCHAR2,
                pk     IN VARCHAR2);

CTX_DML.REINDEX(cid IN NUMBER,
                pk  IN VARCHAR2);
policy

Specify name of policy for text column where document to be reindexed is stored. If policy is used, cid is not used.

cid

Specify the identifier for the text column where document to be reindexed is stored. If cid is used, policy is not used.

pk

Specify the identifier for the document to be reindexed.

Examples

execute ctx_dml.reindex('MY_POLICY', '1')

execute ctx_dml.reindex(3451, '1')

Notes

REINDEX uses either the policy name or the column ID to identify the column where the document to be reindexed is stored.

REINDEX does not perform a COMMIT. After REINDEX is called for a document, COMMIT must be performed to save the request in the DML Queue.

SYNC

The SYNC procedure bundles all pending rows in the DML Queue at the time it is called and enables ConText servers with the DDL personality to process the rows as a single batch (if parallelism is not specified) or as a group of batches (if parallelism is specified).

Syntax

CTX_DML.SYNC(timestamp IN DATE     DEFAULT NULL,
             pol       IN VARCHAR2 DEFAULT NULL,
             parallel  IN NUMBER   DEFAULT 1,
             testing   IN NUMBER   DEFAULT 0,
             timeout   IN NUMBER   DEFAULT 0);
timestamp

Specify the time at which you want the batch DML to start.

The default is SYSDATE.

pol

Specify the policy for the text column for which SYNC is performed.

parallel

Specify the number of ConText servers used to process the operation.

The default is 1.

testing

For internal use only.

timeout

For internal use only.

Examples

execute ctx_dml.sync(PARALLEL=>2)

Notes

timestamp limits the rows in the batch to those rows with a date equal to or less than the date specified. pol limits SYNC to a particular text column. If a value is not specified for pol, SYNC is performed for every text column in the database.

SYNC_QUERY

The SYNC_QUERY function returns a DATE which is the lower bound to which rows in the DML Queue have been indexed.

Syntax

CTX_DML.SYNC_QUERY(cid      IN NUMBER DEFAULT NULL,
                  cur_date  IN DATE   DEFAULT SYSDATE)
RETURN DATE;
cid

Specify the text column for which SYNC_QUERY is called.

cur_date

Specify the date from which to perform the query synchronization.

Returns

The timestamp (date and time) for the reindexed rows.

Examples

select ctx_dml.sync_query(3) from dual;

Notes

cid can be used to limit SYNC_QUERY to a particular text column. Otherwise, SYNC_QUERY returns the DATE value for all text columns.

CTX_THES: Thesaurus Management

The CTX_THES PL/SQL package is used to manage thesauri in the ConText thesaurus tables.

CTX_THES contains the following stored procedures and functions:

Name	Description
CREATE_PHRASE	Adds a phrase to the specified thesaurus or modifies the information about the phrase in the thesaurus and returns the ID for the phrase
CREATE_THESAURUS	Creates the specified thesaurus and returns the ID for the thesaurus
DROP_THESAURUS	Drops the specified thesaurus from the thesaurus tables

Note:

The remaining procedures and functions in CTX_THES are used to enable the thesaurus operators in query expressions.

For more information about the thesaurus operators, see Oracle8 ConText Cartridge Application Developer's Guide.

CREATE_PHRASE

The CREATE_PHRASE function adds a new phrase to the specified thesaurus or creates a new relationship between two existing phrases.

Syntax

CTX_THES.CREATE_PHRASE(tname   IN VARCHAR2,
                       phrase  IN VARCHAR2,
                       rel     IN VARCHAR2 DEFAULT NULL,
                       relname IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
tname

Specify the name of the thesaurus in which the new phrase is added or the existing phrase is located.

phrase

Specify the text of the phrase to be added/updated.

rel

Specify the relationship between the phrase and another existing phrase:

SYN, BT, NT, BTG, NTG, BTP, NTP, RT, or TT

See Also:

For more information about the relationships you can define for thesaurus entries, see "Thesauri" in Chapter 4, "Text Concepts".

relname

Specify the text of the existing phrase that is related to the new/updated phrase.

Returns

The ID for the entry.

Examples

declare phraseid number;
begin
   phraseid := ctx_thes.create_phrase('tech_thes','os');
   phraseid := ctx_thes.create_phrase('tech_thes','operating system');
end;

In example 1, two new phrases (os and operating system) are created in a thesaurus named tech_thes.

declare phraseid number;
begin
   phraseid := ctx_thes.create_phrase('tech_thes','os','syn','oprating system);
end;

In example 2, the two phrases (os and operating system) in tech_thes are recorded as synonyms (syn).

Notes

rel and relname can only be used in CREATE_PHRASE if the phrases specified for both phrase and relname already exist in the thesaurus.

CREATE_PHRASE cannot be used to update the relationship between two existing phrases. It can only be used to create a new relationship between two existing phrases.

CREATE_THESAURUS

The CREATE_THESAURUS function creates an empty thesaurus with the specified name in the thesaurus tables.

Syntax

CTX_THES.CREATE_THESAURUS(name IN VARCHAR2)
RETURN NUMBER;
name

Specify the name of the thesaurus to be created.

Returns

The ID for the thesaurus.

Examples

declare thesid number;
begin
   thesid := ctx_thes.create_phrase('tech_thes');
end;

Notes

The name of the thesaurus must be unique. If a thesaurus with the specified name already exists, CREATE_THESAURUS returns an error and does not create the thesaurus.

To enter phrases in the thesaurus, use CTX_THES.CREATE_PHRASE or use the Thesaurus Maintenance screen in the ConText System Administration tool.

DROP_THESAURUS

The DROP_THESAURUS procedure deletes the specified thesaurus and all of its entries from the thesaurus tables.

Syntax

CTX_THES.DROP_THESAURUS(name IN VARCHAR2);
name

Specify the name of the thesaurus to be dropped.

Examples

execute ctx_ths.drop_thesaurus('tech_thes');

CTX_INFO: Product Information

The CTX_INFO PL/SQL package is used to obtain information about the installed version of ConText.

CTX_INFO contains the following stored procedures and functions:

Name	Description
GET_INFO	Returns the status and version number for the installed ConText
GET_STATUS	Returns the status of ConText
GET_VERSION	Returns the version number for the installed ConText

GET_INFO

The GET_INFO procedure calls the GET_VERSION and GET_STATUS functions in CTX_INFO to return version and status information for ConText.

Syntax

CTX_INFO.GET_INFO(product IN  VARCHAR2,
                  version OUT VARCHAR2,
                  status  OUT VARCHAR2);
product

Specify the product code for which information is returned. Currently, the only valid value for product is OCO.

version

Specify the version of the product.

status

Specify the status of the product.

Examples

declare
version varchar2(20);
status varchar2(20);
begin
  ctx_info.get_info('CTX_INFO.OCO', :version, :status);
  dbms_output.put_line ('OCO version is '||version||');
  dbms_output.put_line ('OCO status is '||status||');
end;

Notes

product must be fully qualified with the PL/SQL package name (CTX_INFO) as shown in the examples.

GET_STATUS

The GET_STATUS function returns the product status for ConText.

Syntax

CTX_INFO.GET_STATUS(product IN VARCHAR2)
RETURN VARCHAR2;
product

Specify the product for which a status returned. Currently, the only valid value for product is OCO.

Returns

The product status for ConText.

Examples

declare
status varchar2(60);
begin
  status := ctx_info.get_status('CTX_INFO.OCO');
  dbms_output.put_line ('OCO status is '||status||');
end;

Notes

product must be fully qualified with the PL/SQL package name (CTX_INFO) as shown in the example.

GET_VERSION

The GET_VERSION function returns the version number for the version of ConText.

Syntax

CTX_INFO.GET_VERSION(product IN VARCHAR2)
RETURN NUMBER;
product

Specify the product for which a version number is returned. Currently, the only valid value for product is OCO.

Returns

The version number for ConText.

Examples

declare
version number;
begin
  version := ctx_info.get_version('CTX_INFO.OCO');
  dbms_output.put_line ('OCO version is '||version||');
end;

Notes

product must be fully qualified with the PL/SQL package name (CTX_INFO) as shown in the example.

11 PL/SQL Packages

CTX_ADM: ConText Administration

CHANGE_MASK

Syntax

personality_mask

Examples

Notes

GET_QUEUE_STATUS

Syntax

Returns

ENABLED

DISABLED

Examples

Notes

RECOVER

Syntax

Examples

Notes

SET_QUERY_BUFFER_SIZE

Syntax

Examples

Notes

SHUTDOWN

Syntax

sdmode

Examples

Notes

UPDATE_QUEUE_STATUS

Syntax

Examples

Notes

CTX_SVC: Services Queue Administration

CANCEL

Syntax

Examples

Notes

CANCEL_ALL

Syntax

Examples

CANCEL_USER

Syntax

Examples

CLEAR_ALL_ERRORS

Syntax

Examples

CLEAR_ERROR

Syntax

Examples

Notes

CLEAR_INDEX_ERRORS

Syntax

Examples

CLEAR_LING_ERRORS

Syntax

Examples

REQUEST_STATUS

Syntax

timestamp

errors

Returns

PENDING

RUNNING

ERROR

SUCCESS

Examples

Notes

CTX_DDL: Text Setup and Management

CLEAR_ATTRIBUTES

Syntax

Examples

CREATE_INDEX

Syntax

parallel

Examples

CREATE_POLICY

Syntax

colspec

source_policy

description

textkey

11
PL/SQL Packages