Oracle8 ConText Cartridge Administrator's Guide Release 2.3 A58165-01 |
Library | Product | Contents | Index

# 11
PL/SQL Packages - Text Management

This chapter provides reference information for using the PL/SQL packages provided with ConText to manage text.

The topics covered in this chapter are:

- CTX_DDL: Text Setup and Management
- CTX_DML: ConText Index Update
- CTX_THES: Thesaurus Management

## 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
--- | ---
ADD_SECTION | Creates a section and assigns the section to the specified section group
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_SECTION_GROUP | Creates a section group 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 the ConText index for the specified policy
DROP_INTTRIG | Deletes the DML trigger for the specified table
DROP_POLICY | Deletes a policy from the ConText data dictionary
DROP_PREFERENCE | Deletes a preference from the ConText data dictionary
DROP_SECTION_GROUP | Deletes a section group 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
REMOVE_SECTION | Deletes a section from a section group
RESUME_FAILED_INDEX | Resumes creation/optimization of a failed ConText index
SET_ATTRIBUTE | Specifies the Tile attribute and corresponding value for a preference
UPGRADE_INDEX | Converts ConText indexes from Release 2.0 or earlier to the current release
UPDATE_POLICY | Changes the description and/or the preferences in a policy
UPDATE_SOURCE | Changes the description and/or the preferences in a source

## ADD_SECTION

The ADD_SECTION procedure creates a section and adds the section to an existing section group.

### Syntax

    CTX_DDL.ADD_SECTION(group_name   IN VARCHAR2,
                        section_name IN VARCHAR2,
                        start_tag    IN VARCHAR2,
                        end_tag      IN VARCHAR2,
                        top_level    IN BOOLEAN DEFAULT FALSE,
                        enclose_self IN BOOLEAN DEFAULT FALSE);

##### group_name

Specify the name of the section group to which ConText adds the section.

##### section_name

Specify the name of the section ConText adds to the section group.

##### start_tag

Specify the token, including any characters that appear at the beginning or end or the token, which marks the start of a section. For example: <HTML>

##### end_tag

specify the token, including any characters that appear at the beginning or end or the token, which marks the end of a section. For example: </HTML>

##### top_level

Specify that the section implicitly closes non-top-level sections and is implicitly closed by the start of other top-level sections.

##### enclose_self

Specify that the section can enclose itself. If this parameter is not set, the section is implicitly closed when the next start tag is encountered.

If enclose_self is TRUE, the end of the section is identified by either:

1. the end tag for the section
2. the end of the document

If enclose_self is FALSE, the end of the section is identified by either:

1. the end tag (if any) for the section
2. the next start or end tag encountered (if top_level is FALSE)
3. the end of the document

   See Also: For more information about top-level sections and self-enclosing sections, see "Sections" in Chapter 6, "Text Concepts".

### Examples

Examples are provided for four different types of sections you can create.

#### Example 1: Non-enclosed, repeating sections

    Title: Guide to Oracle
    Author: Joseph Smith
    Review: Very well written
    Review: Interesting and exciting

    Section Name

    Start Tag

    End Tag

    Top Level

    Enclose Self

    TITLE

    Title:

    Y

    N

    AUTHOR

    Author:

    Y

    N

    REVIEW

    Review:

    Y

    N

    exec ctx_ddl.add_section(`doc_section','title','Title:', top_level=>TRUE)
    exec ctx_ddl.add_section(`doc_section','author','Author:', top_level=>TRUE)
    exec ctx_ddl.add_section(`doc_section','review','Review:', top_level=>TRUE)

#### Example 2: Enclosed and non-enclosed repeating sections

    <BODY>
    <P> This is the first <B>paragraph</B>
    <P> This is the second paragraph
    </BODY>

    Section Name

    Start Tag

    End Tag

    Top Level

    Enclose Self

    BODY

    <BODY>

    </BODY>

    Y

    N

    PARA

    <P>

    </P>

    N

    N

    BOLD

    <B>

    </B>

    N

    N

    exec ctx_ddl.add_section(`html_section','BODY','<BODY>', `</BODY>', top_level=>TRUE)
    exec ctx_ddl.add_section(`html_section','PARA','<P>','</P>')
    exec ctx_ddl.add_section(`html_section','BOLD','<B>,'</B>')

#### Example 3: Enclosed, overlapping sections

    <CODE>
    <OLD>
    a := 9;
    <NEW>
    c := 14;
    </OLD>
    d := 15;
    </NEW>
    </CODE>

    Section Name

    Start Tag

    End Tag

    Top Level

    Enclose Self

    CODE

    <CODE>

    </CODE>

    Y

    N

    OLD

    <OLD>

    </OLD>

    N

    N

    NEW

    <NEW>

    </NEW>

    N

    N

    exec ctx_ddl.add_section(`html_sections','CODE','<CODE>', `</CODE>', top_level=>TRUE)
    exec ctx_ddl.add_section(`html_sections','OLD','<OLD>','</OLD>')
    exec ctx_ddl.add_section(`html_sections','NEW','<NEW>,'</NEW>')

#### Example 4: Enclosed, self enclosing, repeating sections

    <TABLE>
    <TR>
    <TD>March</TD>
    <TD>
    <TABLE>
    <TR>
    <TD>14</TD>
    </TR>
    </TABLE>
    </TD>
    </TR>
    </TABLE>

    Section Name

    Start Tag

    End Tag

    Top Level

    Enclose Self

    TABLE

    <TABLE>

    </TABLE>

    N

    Y

    ROW

    <TR>

    </TR>

    N

    Y

    DATA

    <TD>

    </TD>

    N

    Y

    exec ctx_ddl.add_section(`html_sections','TABLE','<TABLE>',`</TABLE>', enclose_self=>TRUE)
    exec ctx_ddl.add_section(`html_sections','ROW','<TR>','</TR>', enclose_self=>TRUE)
    exec ctx_ddl.add_section(`html_sections','DATA','<TD>,'</TD>', enclose_self=>TRUE)

### Notes

If the section group specified in group_name is currently used in a preference, the preference must be dropped using CTX_DDL.DROP_PREFERENCE before sections can be added to the section group.

## 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
                         create_trig IN BOOLEAN  DEFAULT TRUE
                         pop_index   IN BOLLEAN  DEFAULT TRUE);

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

##### create_trig

Specify whether to create a DML trigger for the table or update the existing trigger to include the text column for the specified policy:

- TRUE (create/update trigger)
- FALSE (do not create/update trigger)

The default is TRUE.

##### pop_index

Specify whether to populate the ConText index tables with index entries during ConText indexing:

- TRUE (create and populate ConText index tables)
- FALSE (create ConText index tables, but do not populate tables)

The default is TRUE.

### Examples

Examples are provided for parallel indexing, DML trigger control, and table population during indexing.

#### Example 1: Parallel Indexing

In the following example, a ConText index is created with a parallelism level of 2 for the text column in my_policy.

    execute ctx_ddl.create_index('MY_POLICY', 2)

#### Example 2: DML Trigger and Index Population Control

In the following example, a table has policies pol1, pol2, pol3 for text columns text1, text2, text3 respectively. ConText indexes are created for each policy:

    ctx_ddl.create_index(`P1', create_trig=>FALSE, pop_index=>FALSE);
    ctx_ddl.create_index(\QP2', create_trig=>TRUE, pop_index=>TRUE);
    ctx_ddl.create_index(\QP3', create_trig=>FALSE, pop_index=>FALSE);

The DML trigger is created for the table; however, only the text column (text2) for policy pol2 is included in the trigger. As a result, only an update to the textkey or text column for policy pol2 will cause a request to be inserted into the DML Queue.

In addition, during ConText indexing, only the ConText index tables for policy pol2 are populated. To populate the ConText index tables for pol1 and pol3, CTX_DML.REINDEX must be called for each document in text columns text1 and text3.

#### Example 3: DML Trigger Control

In the following example, the same three policies and tables are used from before. The create_trig parameter is set to FALSE for all three, so no DML trigger is created for the table. The pop_index parameter is set to TRUE for all three, so the ConText index tables for all three policies are populated.

    ctx_ddl.create_index(\QP1', create_trig=>FALSE, pop_index=>TRUE);
    ctx_ddl.create_index(\QP2', create_trig=>FALSE, pop_index=>TRUE);
    ctx_ddl.create_index(\QP3', create_trig=>FALSE, pop_index=>TRUE);

### Notes

If a DML trigger is not created for a table during ConText indexing, changes to the table will not result in the ConText index being updated. Changes to a document in the table can be recorded in the DML Queue using the CTX_DML.REINDEX procedure; however, REINDEX must be called each time a document changes.

Automated DML notification can be enabled for the table by creating a trigger that calls CTX_DML.REINDEX.

For example:

    create or replace trigger resume_update
    before delete or insert or update of empno,resume on ctxdev.emp
    for each row
      declare
        newkey varchar2(1000) := :new.empno;
        oldkey varchar2(1000) := :old.empno;
      begin
        if inserting then
          ctx_dml.reindex('resume_pol',newkey);
        else if updating then
          ctx_dml.reindex('resume_pol',oldkey);
          ctx_dml.reindex('resume_pol',newkey);
        else
          ctx_dml.reindex('resume_pol',oldkey);
        end if;
      end;

In this example, a trigger named resume_update is created on a table named emp in the database schema for user ctxdev. empno is the primary key (and textkey) and resume is the text column for emp. A policy named resume_pol has been created for the text column and an index created for the policy.

Each time a row is inserted or deleted from emp, or empno or resume is updated for an existing row, resume_update places a DML request for the row (document) in the DML queue.

## 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. This is the column that contains the text to be indexed. If no 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. This is usually the primary key(s) for the table, but can also be any column(s) for which a UNIQUE constraint has been defined.

Note: If no value is specified for textkey in CREATE_POLICY, ConText does not, by default, always select the primary key column for the table identified in colspec. ConText selects the first primary key or unique column encountered in the table. To ensure that the desired column(s) are defined as the textkey for a text column, always specify a textkey value when creating a policy for the column.

lineno

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

Note: This attribute is used only if the Data Store preference for the policy calls the MASTER DETAIL Tile. If the Data Store preference calls the MASTER DETAIL NEW Tile, the line number column name is specified in the preference.

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 named auth and title in docs.

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

The values for colspec and textkey cannot be the same. In other words, a column that serves as a text column cannot also be the (only) column that uniquely identifies rows in the table.

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:

    exec ctx_ddl.create_policy(...,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',
                                'Lexer that does not use any printjoins',
                                'BASIC LEXER');
    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_SECTION_GROUP

The CREATE_SECTION_GROUP procedure creates a section group for defining sections for a text column.

### Syntax

    CTX_DDL.CREATE_SECTION_GROUP(group_name IN VARCHAR2);

##### group_name

Specify the name of the section group to create.

### Examples

The following example creates a section group named html_sections:

exec ctx_ddl.create_section_group('html_sections')

## 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, predefined 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/updated automatically for 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')

### Notes

DROP_INTTRIG deletes the trigger for the table; it cannot be used to selectively disable automatic DML for a text column in a table. If the table contains more than one text column with existing ConText indexes, automatic DML is disabled for all the text columns.

## 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 preference can be dropped.

## DROP_SECTION_GROUP

The DROP_SECTION_GROUP deletes the specified section group, as well as all the sections in the group, from the ConText data dictionary.

### Syntax

    CTX_DDL.DROP_SECTION_GROUP(group_name IN VARCHAR2);

##### group_name

Specify the name of the section group to delete.

### Examples

    exec ctx_ddl.drop_section_group('html_sections')

### Notes

If the specified section group is used in an existing Wordlist preference, the preference must be dropped, using CTX_DDL.DROP_PREFERENCE, before the section can be dropped from the section group.

## 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 this procedure).

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

## REMOVE_SECTION

The REMOVE_SECTION procedure removes the specified section from the specified section group.

### Syntax

    CTX_DDL.REMOVE_SECTION(group_name   IN VARCHAR2,
                           section_name IN VARCHAR2);

##### group_name

Specify the name of the section group from which ConText deletes the section.

##### section_name

Specify the name of the section ConText deletes from the section group.

### Examples

    exec ctx_ddl.remove_section('html_sections', 'H1')

### Notes

If the specified section is part of a section group used in an existing Wordlist preference, the preference must be dropped, using CTX_DDL.DROP_PREFERENCE, before the section can be dropped from the section group.

## RESUME_FAILED_INDEX

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

### 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

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

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 with the executable attribute for the BLASTER FILTER Tile).

##### value2

Specify the second value assigned to attribute (used only with the executable attribute for the BLASTER FILTER 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

Examples are provided for setting attributes for Engine, Stoplist, and Filter Tiles.

#### Example 1: Engine Tile Attribute

In this example, 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('INDEX_MEMORY', '3000000')

#### Example 2: Stoplist Tile Attributes

In this example, the stop_word attribute (GENERIC STOP LIST Tile) is set twice, once for the stop word of and once for the stop word and. The stop words are assigned sequences of 1 and 2 respectively.

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

#### Example 3: Filter Tile Attributes

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.

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

### 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 automatically cleared. As a result, 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 manually clear all attributes in the buffer.

seq is only used with the Tiles that have attributes that support multiple values for the same attribute (i.e. BLASTER FILTER, GENERIC STOP LIST, and GENERIC WORD 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.

## UPGRADE_INDEX

The UPGRADE_INDEX procedure upgrades the ConText index for a policy from the format used in ConText, Release 2.0 and earlier, to the current format.

### Syntax

    CTX_DDL.UPGRADE_INDEX(policy_name IN VARCHAR2);

##### policy_name

The name of the policy for which the index is upgraded.

### Examples

In the following example, UPGRADE_INDEX is called for a policy named doc_pol1 that is owned by ctxdemo.

    connect ctxdemo/passwd
    SQL> exec ctx_ddl.upgrade_index(`doc_pol1')

In the following example, UPGRADE_INDEX is called by CTXSYS for a policy named doc_pol2 that is owned by ctxdemo.

    connect ctxsys/passwd
    SQL> exec ctx_ddl.upgrade_index(`ctxdemo.doc_pol2')

### Notes

You only need to run UPGRADE_INDEX for ConText indexes that were created in Release 2.0 or earlier. In addition, you only need to run UPGRADE_INDEX once for each ConText index

If CTXSYS is used to upgrade the indexes for other users, the policy name specified for UPGRADE_INDEX must be fully qualified with the username for the user.

The CTX_INDEX_LOG view can be used by users with the CTXADMIN role to view the status of all ConText indexes.

The CTX_USER_INDEX_LOG view can be used by users with the CTXAPP role to view the status of all ConText indexes for the user.

## 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.UPDATE_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.

##### next

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

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 automatic 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. Instead, a trigger that calls REINDEX must be created on the base table containing the text column for the view.

Finally, it can be used to notify the system of updates to documents stored externally. If a document uses the OSFILE Tile, 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 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 phrase to be added to a thesaurus or the phrase for which a new relationship is created.

##### rel

Specify the new relationship between phrase and relname:

- SYN
- BT
- NT
- BTG
- NTG
- BTP
- NTP
- RT
- TT

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

  relname

Specify the existing phrase that is related to phrase.

### Returns

The ID for the entry.

### Examples

Examples are provided for creating entries for two phrases and defining a relationship between the phrases.

#### Example 1: Creating Entries for Phrases

In this example, 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');
       phraseid := ctx_thes.create_phrase('tech_thes','operating system');
    end;

#### Example 2: Creating a Relationship

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

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

### 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(thes_name      IN VARCHAR2
                              case_sensitive IN BOOLEAN DEFAULT FALSE)
    RETURN NUMBER;

##### thes_name

Specify the name of the thesaurus to be created.

##### case_sensitive

Specify whether the thesaurus to be created is case-sensitive. If case_sensitive is TRUE, ConText retains the cases of all terms entered in the specified thesaurus. As a result, queries that use the thesaurus are case-sensitive.

### 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_thes.drop_thesaurus('tech_thes');

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index