17 Oracle Text Utilities

Oracle Text provides utilities for managing and operating on Text indexes. For example, you can load a specific thesaurus into the index, and you can create your own knowledge base to be associated with the index, among other things. This chapter discusses the utilities shipped with Oracle Text.

The following topics are included:


The APIs in the utilities shipped with Oracle Text do not support identifiers that are prefixed with the schema or the owner name.

17.1 Thesaurus Loader (ctxload)

Use ctxload to import a thesaurus file into the Oracle Text thesaurus tables.

An import file is an ASCII flat file that contains entries for synonyms, broader terms, narrower terms, or related terms, which can be used to expand queries.

This section contains the following topics.

See Also:

For examples of import files for thesaurus importing, see "Structure of ctxload Thesaurus Import File" in Text Loading Examples for Oracle Text

17.1.1 ctxload Text Loading

The ctxload program no longer supports the loading of text columns. To load files to a text column in batch mode, Oracle recommends that you use SQL*Loader.

17.1.2 ctxload Syntax

ctxload -user username[/password][@sqlnet_address]
        -name object_name 
        -file file_name 
       [-thescase y|n]
       [-log file_name]

ctxload Mandatory Arguments


Specify the user name and password of the user running ctxload.

The user name and password can be followed immediately by @sqlnet_address to permit logging on to remote databases. The value for sqlnet_address is a database connect string. If the TWO_TASK environment variable is set to a remote database, then you do not need to specify a value for sqlnet_address to connect to the database.

-name object_name

When you use ctxload to import a thesaurus, use object_name to specify the name of the thesaurus to be imported.

Use object_name to identify the thesaurus in queries that use thesaurus operators.


Thesaurus name must be unique. If the name specified for the thesaurus is identical to an existing thesaurus, then ctxload returns an error and does not overwrite the existing thesaurus.

-file file_name

When ctxload is used to import a thesaurus, use file_name to specify the name of the import file that contains the thesaurus entries.

When ctxload is used to export a thesaurus, use file_name to specify the name of the export file created by ctxload.


If the name specified for the thesaurus dump file is identical to an existing file, then ctxload overwrites the existing file.

ctxload Optional Arguments


Import a thesaurus. Specify the source file with the -file argument. Specify the name of the thesaurus to be imported with -name.

-thescase y | n

Specify y to create a case-sensitive thesaurus with the name specified by -name and populate the thesaurus with entries from the thesaurus import file specified by -file. If -thescase is y (the thesaurus is case-sensitive), ctxload enters the terms in the thesaurus exactly as they appear in the import file.

The default for -thescase is n (case-insensitive thesaurus).


-thescase is valid for use only with the -thes argument.


Export a thesaurus. Specify the name of the thesaurus to be exported with the -name argument. Specify the destination file with the -file argument.


Specify the name of the log file to which ctxload writes any national-language supported (Globalization Support) messages generated during processing. If you do not specify a log file name, the messages appear on the standard output. The logs generated by ctxload will be present in $ORACLE_HOME/ctx/log directory.


Enables SQL statement tracing using ALTER SESSION SET SQL_TRACE TRUE. This command captures all processed SQL statements in a trace file, which can be used for debugging. The location of the trace file is operating-system dependent and can be modified using the DIAGNOSTIC_DEST initialization parameter.

See Also:

For more information about SQL trace and the DIAGNOSTIC_DEST initialization parameter, see Oracle Database Administrator's Guide

17.1.3 ctxload Examples

This section provides examples for some of the operations that ctxload can perform.

See Also:

For more document loading examples, see Text Loading Examples for Oracle Text

The following example imports a thesaurus named tech_doc from an import file named tech_thesaurus.txt:

ctxload -user jsmith/password -thes -name tech_doc -file tech_thesaurus.txt 

The following example exports the contents of a thesaurus named tech_doc into a file named tech_thesaurus.out:

ctxload -user jsmith/password -thesdump -name tech_doc -file tech_thesaurus.out 

17.2 Entity Extraction User Dictionary Loader (ctxload)

Use ctxload to import an entity extraction user dictionary into Oracle Text tables.

An import file is an XML flat file containing entries for entities, with their associated types and alternate forms.

This section contains the following topics.

17.2.1 ctxload Syntax

ctxload -user username[/password][@sqlnet_address]
        -name entity extraction policy name 
        -file user-dictionary file name 
        [-drop] to drop a user-dictionary from a policy

ctxload Mandatory Arguments


Specify the user name and password of the user running ctxload.

The user name and password can be followed immediately by @sqlnet_address to permit logging on to remote databases. The value for sqlnet_address is a database connect string. If the TWO_TASK environment variable is set to a remote database, then you do not need to specify a value for sqlnet_address to connect to the database.

-name entity extraction policy name

When you use ctxload to import an entity extraction dictionary, use object_name to specify the entity extraction policy to associate the dictionary with. An entity extraction policy can have only one user dictionary.

-file user-dictionary file name

Use file to specify the name of the XML file containing the user dictionary.


Drop the user dictionary currently associated with an entity extraction policy.

17.2.2 Considerations When Creating a User Dictionary

The following are some considerations for when creating a user dictionary:

  • Entity mentions are case-sensitive. They cannot contain any null characters.

  • Entity type names are case-insensitive. They cannot contain any null or comma characters.

  • Customers will be able to assign two or more entity types to a single entity mention. For example, the entity "Washington" could be assigned the type "CITY" and also the type "STATE".

  • The content of a user's dictionary is invisible to other users.

  • The maximum byte length of an entity mention is 512 bytes by the server-side database character set.

  • The maximum byte length of an entity type name is 30 bytes by the server-side database character set.

17.2.3 XML Schema

The entity extraction dictionary follows this XML schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="dictionary">
      <xsd:element name="entities" type="entityType" maxOccurs="unbounded"/>

<xsd:complexType name="entityType">
    <xsd:element name="entity" type="entType" maxOccurs="unbounded"/>
  </xsd:attribute name="language" type="xsd:string"/>

<xsd:complexType name="entType">
    <xsd:element name="value" type="xsd:string"/>
    <xsd:element name="type" type="xsd:string" minOccurs="1" maxOccurs="unbounded"/>
    <xsd:element name="alternate" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>

The following tables illustrate some aspects of the XML schema for the entity extraction dictionary.

Element Name Description


Collection of entities


Collection of entities per language


Each entity


Entity mention


Entity type


Alternate form of entity

Attribute Name Element Name Description



Language name of each entity in entities

17.2.4 ctxload Example

The following is an example of an entity extraction user dictionary file that can be loaded using ctxload:

<?xml version="1.0" encoding="utf-8"?>
      <value>New York</value>
  <entities language="german">
  <entities language="english">
      <value>George W. Bush<value>
      <alternate>G. W. Bush<alternate>
      <alternate>G. Bush<alternate>

17.3 Knowledge Base Extension Compiler (ctxkbtc)

The knowledge base is the information source that Oracle Text uses to perform theme analysis, such as theme indexing, processing ABOUT queries, and to document theme extraction with the CTX_DOC package. A knowledge base is supplied for English and French and is installed by default.

With the ctxkbtc compiler, you can:

17.3.1 Knowledge Base Character Set

Knowledge bases can be in any single-byte character set. Supplied knowledge bases are in WE8ISO8859P1. You can store an extended knowledge base in another character set such as US7ASCII.

17.3.2 ctxkbtc Syntax

ctxkbtc -user uname/passwd
[-name thesname1 [thesname2 ... thesname16]]
[-stoplist stoplistname]
[-log filename]

Specify the user name and password for the administrator creating an extended knowledge base. This user must have write permission to the ORACLE_HOME directory.

-name thesname1 [thesname2 ... thesname16]

Specify the names of the thesauri (up to 16) to be compiled with the knowledge base to create the extended knowledge base. The thesauri you specify must already be loaded with ctxload with the "-thescase Y" option.


Reverts the extended knowledge base to the default knowledge base provided by Oracle Text.

-stoplist stoplistname

Specify the name of the stoplist. Stopwords in the stoplist are added to the knowledge base as useless words that are prevented from becoming themes or contributing to themes. Add stopthemes after running this command using CTX_DLL.ADD_STOPTHEME.


Displays all warnings and messages, including non-Globalization Support messages, to the standard output.


Specify the log file for storing all messages. When you specify a log file, no messages are reported to standard out. The logs generated by ctxkbtc will be present in $ORACLE_HOME/ctx/log directory.

17.3.3 ctxkbtc Usage Notes

  • Before running ctxkbtc, you must set the NLS_LANG environment variable to match the database character set.

  • The user issuing ctxkbtc must have write permission to the ORACLE_HOME, because the program writes files to this directory.

  • Before being compiled, each thesaurus must be loaded into Oracle Text case sensitive with the "-thescase Y" option in ctxload.

  • Running ctxkbtc twice removes the previous extension.

17.3.4 ctxkbtc Limitations

The ctxkbtc program has the following limitations:

  • When upgrading or downgrading your database to a different release, for theme indexing and related features to work correctly, Oracle recommends that you recompile your extended knowledge base in the new environment.

  • Before extending the knowledge base, you must terminate all server processes that have invoked any knowledge base-related Text functions during their lifetime.

  • There can be only one user extension for each language for each installation. Because a user extension affects all users at the installation, only the CTXSYS user can extend the knowledge base.

  • In an Oracle RAC environment, the ORACLE_HOME can either be shared between multiple nodes, or each node can have its own ORACLE_HOME. The following requirements apply:

    • Before using any knowledge base-dependent functionality in any of the Oracle RAC nodes, ctxkbtc must be run in every ORACLE_HOME in the Oracle RAC environment.

    • When using ctxkbtc, the exact same input thesaurus content must be used in every ORACLE_HOME in the Oracle RAC environment.

17.3.5 ctxkbtc Constraints on Thesaurus Terms

Terms are case sensitive. If a thesaurus has a term in uppercase, for example, the same term present in lowercase form in a document will not be recognized.

The maximum length of a term is 80 characters.

Disambiguated homographs are not supported.

17.3.6 ctxkbtc Constraints on Thesaurus Relations

The following constraints apply to thesaurus relations:

  • BTG and BTP are the same as BT. NTG and NTP are the same as NT.

  • Only preferred terms can have a BT, NTs or RTs.

  • If a term has no USE relation, it will be treated as its own preferred term.

  • If a set of terms are related by SYN relations, only one of them may be a preferred term.

  • An existing category cannot be made a top term.

  • There can be no cycles in BT and NT relations.

  • A term can have at most one preferred term and at most one BT. A term may have any number of NTs.

  • An RT of a term cannot be an ancestor or descendent of the term. A preferred term may have any number of RTs up to a maximum of 32.

  • The maximum height of a tree is 16 including the top term level.

  • When multiple thesauri are being compiled, a top term in one thesaurus should not have a broader term in another thesaurus.


    The thesaurus compiler tolerates some violations of the preceding rules. For example, if a term has multiple BTs, then the compiler ignores all but the last one it encounters.

    Similarly, BTs between existing knowledge base categories result only in a warning message.

    Oracle recommends that you do not set up extended storage bases with violations. Using extended storage bases containing violations can produce undesired results.

17.3.7 Extending the Knowledge Base

Extend the supplied knowledge base by compiling one or more thesauri with the Oracle Text knowledge base. The extended information can be application-specific terms and relationships. During theme analysis, the extended portion of the knowledge base overrides any terms and relationships in the knowledge base where there is overlap.

When extending the knowledge base, Oracle recommends that new terms be linked to one of the categories in the knowledge base for best results in theme proving when appropriate.

If new terms are kept completely disjoint from existing categories, fewer themes from new terms will be proven. The result of this is poorer precision and recall with ABOUT queries as well poor quality of gists and theme highlighting.

Link new terms to existing terms by making an existing term the broader term for the new terms.

17.3.8 Example for Extending the Knowledge Base

You purchase a medical thesaurus medthes containing a hierarchy of medical terms. The four top terms in the thesaurus are the following:

  • Anesthesia and Analgesia

  • Anti-Allergic and Respiratory System Agents

  • Anti-Inflammatory Agents, Antirheumatic Agents, and Inflammation Mediators

  • Antineoplastic and Immunosuppressive Agents

To link these terms to the existing knowledge base, add the following entries to the medical thesaurus to map the new terms to the existing health and medicine branch:

health and medicine
 NT Anesthesia and Analgesia
 NT Anti-Allergic and Respiratory System Agents
 NT Anti-Inflamammatory Agents, Antirheumatic Agents, and Inflamation Mediators
 NT Antineoplastic and Immunosuppressive Agents

Set your globalization support language environment variable to match the database character set. For example, if your database character set is WE8ISO8859P1 and you are using American English, set your NLS_LANG as follows:


Assuming the medical thesaurus is in a file called med.thes, load the thesaurus as medthes with ctxload as follows:

ctxload -thes -thescase y -name medthes -file med.thes -user ctxsys/ctxsys

To link the loaded thesaurus medthes to the knowledge base, use ctxkbtc as follows:

ctxkbtc -user ctxsys/ctxsys -name medthes 

17.3.9 Adding a Language-Specific Knowledge Base

Extend theme functionality to languages other than English or French by loading your own knowledge base for any single-byte whitespace delimited language, including Spanish.

Theme functionality includes theme indexing, ABOUT queries, theme highlighting, and the generation of themes, gists, and theme summaries with the CTX_DOC PL/SQL package.

Extend theme functionality by adding a user-defined knowledge base. For example, you can create a Spanish knowledge base from a Spanish thesaurus.

To load your language-specific knowledge base, follow these steps:

  1. Load your custom thesaurus using ctxload.

  2. Set NLS_LANG so that the language portion is the target language. The charset portion must be a single-byte character set.

  3. Compile the loaded thesaurus using ctxkbtc:

ctxkbtc -user ctxsys/ctxsys -name my_lang_thes

This command compiles your language-specific knowledge base from the loaded thesaurus. To use this knowledge base for theme analysis during indexing and ABOUT queries, specify the NLS_LANG language as the THEME_LANGUAGE attribute value for the BASIC_LEXER preference.

17.3.10 Limitations for Adding a Knowledge Base

The following limitations hold for adding knowledge bases:

  • Oracle Text supplies knowledge bases in English and French only. You must provide your own thesaurus for any other language.

  • You can only add knowledge bases for languages with single-byte character sets. You cannot create a knowledge base for languages which can be expressed only in multibyte character sets. If the database is a multibyte universal character set, such as UTF-8, the NLS_LANG parameter must still be set to a compatible single-byte character set when compiling the thesaurus.

  • Adding a knowledge base works best for whitespace delimited languages.

  • You can have at most one knowledge base for each globalization support language.

  • Obtaining hierarchical query feedback information such as broader terms, narrower terms and related terms does not work in languages other than English and French. In other languages, the knowledge bases are derived entirely from your thesauri. In such cases, Oracle recommends that you obtain hierarchical information directly from your thesauri.

17.3.11 Order of Precedence for Multiple Thesauri

When multiple thesauri are to be compiled, precedence is determined by the order in which thesauri are listed in the arguments to the compiler, assumed to be most preferred first. A user-defined thesaurus always has precedence over the built-in knowledge base.

17.3.12 Size Limits for Extended Knowledge Base

The following table lists the size limits associated with creating and compiling an extended knowledge base.

Table 17-1 Size Limit for the Extended Knowledge Base

Description of Parameter Limit

Number of RTs (from + to) for each term


Number of terms for each single hierarchy (for example, all narrower terms for a given top term)


Number of new terms in an extended knowledge base

1 million

Number of separate thesauri that can be compiled into a user extension to the KB


17.4 Lexical Compiler (ctxlc)

The Lexical Compiler (ctxlc) is a command-line utility that enables you to create your own Chinese and Japanese lexicons (dictionaries). Such a lexicon may either be generated from a user-supplied word list or from the merging of a word list with the system lexicon for that language.

ctxlc creates the new lexicon in your current directory. The new lexicon consists of three files, drold.dat, drolk.dat, and droli.dat. To change your system lexicon for Japanese or Chinese, overwrite the system lexicon with these files.

The Lexical Compiler can also generate wordlists from the system lexicons for Japanese and Chinese, enabling you to see their contents. These word lists go to the standard output and thus can be redirected into a file of your choice.

After overwriting the system lexicon, you need to re-create your indexes before querying them.

This section contains the following topics.

17.4.1 Syntax of ctxlc

ctxlc has the following syntax:

ctxlc -ja | -zht [ -n ] -ics character_set -i input_file

ctxlc -ja | -zht -ocs character_set [ > output_file ]

ctxload Mandatory Arguments

-ja | -zht

Specify the language of the lexicon to modify or create. -ja indicates the Japanese lexicon; -zht indicates the Chinese lexicon, the same for either traditional or simplified Chinese.

-ics character_set

Specify the character set of the input file denoted by -i input_file. input_file is the list of words, one word to a line, to use in creating the new lexicon.

-i input_file

Specify the file containing words to use in creating a new lexicon.

-ocs character_set

Specify the character set of the text file to be output.

ctxload Optional Arguments


Specify -n to create a new lexicon that consists only of user-supplied words taken from input_file. If -n is not specified, then the new lexicon consists of a merge of the system lexicon with input_file. Also, when -n is not selected, a text file called drolt.dat, is created in the current directory to enable you to inspect the contents of the merged lexicon without having to enter another ctxlc command.

17.4.2 ctxlc Performance Considerations

You can add up to 1,000,000 new words to a lexicon. However, creating a very large lexicon can reduce performance in indexing and querying. Performance is best when the lexicon character set is UTF-8. There is no performance impact on the Chinese or Japanese V-gram lexers, as they do not use lexicons.

17.4.3 ctxlc Usage Notes

Oracle recommends the following practices with regard to ctxlc:

  • Save your plain text dictionary file in your environment for emergency use.

  • When upgrading or downgrading your database to a different release, recompile your plain text dictionary file in the new environment so that the user lexicon will work correctly.

17.4.4 ctxlc Example

In this example, you create a new Japanese lexicon from the file jadict.txt, a word list that uses the JA16EUC character set. Because you are not specifying -n, the new lexicon is the result of merging jadict.txt with the system Japanese lexicon. Then replace the existing Japanese lexicon with the new, merged one.

% ctxlc -ja -ics JA16EUC -i jadict.txt

This creates new files in the current directory:

% ls

The system lexicon files for Japanese and Chinese are named droldxx.dat drolkxx.dat, and drolixx.dat, where xx is either JA (for Japanese) or ZH (for Chinese). Rename the three new files and copy them to the directory containing the system Japanese lexicon.

% mv drold.dat droldJA.dat
% mv drolk.dat drolkJA.dat
% mv droli.dat droliJA.dat
% cp *dat $ORACLE_HOME/ctx/data/jalx

This replaces the system Japanese lexicon with one that is a merge of the old system lexicon and your wordlist from jadict.txt.

You can also use ctxlc to get a dump of a system lexicon. This example dumps the Chinese lexicon to a file called new_chinese_dict.txt in the current directory:

% ctxlc -zh -ocs UTF8 > new_chinese_dict.txt

This creates a file, new_japanese.dict.txt, using the UTF8 character set, in the current directory.