2 Oracle Text Indexing Elements

2.2.1.1 DIRECT_DATASTORE CLOB Example

The following example creates a table with a CLOB column to store text data. It then populates two rows with text data and indexes the table using the system-defined preference CTXSYS.DEFAULT_DATASTORE.

create table mytable(id number primary key, docs clob); 

insert into mytable values(111555,'this text will be indexed');
insert into mytable values(111556,'this is a direct_datastore example');
commit;

create index myindex on mytable(docs) 
  indextype is ctxsys.context 
  parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

2.2.2.1 Indexing and DML

To index, you must create a dummy column to specify in the CREATE INDEX statement. This column's contents are not made part of the virtual document, unless its name is specified in the columns attribute.

The index is synchronized only when the dummy column is updated. You can create triggers to propagate changes if needed.

2.2.2.2 MULTI_COLUMN_DATASTORE Restriction

You cannot create a multi-column datastore with XMLType columns. MULTI_COLUMN_DATA_STORE does not support XMLType. You can create a CONTEXT index with an XMLType column, as described in Chapter 1, "Oracle Text SQL Statements and Operators".

2.2.2.3 MULTI_COLUMN_DATASTORE Example

The following example creates a multi-column datastore preference called my_multi with three text columns:

begin

ctx_ddl.create_preference('my_multi', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('my_multi', 'columns', 'column1, column2, column3');

end;

2.2.2.4 MULTI_COLUMN_DATASTORE Filter Example

The following example creates a multi-column datastore preference and denotes that the bar column is to be filtered with the AUTO_FILTER.

ctx_ddl.create_preference('MY_MULTI','MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('MY_MULTI', 'COLUMNS','foo,bar');
ctx_ddl.set_attribute('MY_MULTI','FILTER','N,Y');

The multi-column datastore fetches the content of the foo and bar columns, filters bar, then composes the compound document as:

<FOO>
foo contents
</FOO>
<BAR>
bar filtered contents (probably originally HTML)
</BAR>

The N's need not be specified, and there need not be a flag for every column. Only the Y's need to be specified, with commas to denote which column they apply to. For instance:

ctx_ddl.create_preference('MY_MULTI','MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('MY_MULTI', 'COLUMNS','foo,bar,zoo,jar');
ctx_ddl.set_attribute('MY_MULTI','FILTER',',,Y');

This filters only the column zoo.

2.2.2.5 Tagging Behavior

During indexing, the system creates a virtual document for each row. The virtual document is composed of the contents of the columns concatenated in the listing order with column name tags automatically added. For example:

create table mc(id number primary key, name varchar2(10), address varchar2(80));
insert into mc values(1, 'John Smith', '123 Main Street');

exec ctx_ddl.create_preference('mymds', 'MULTI_COLUMN_DATASTORE');
exec ctx_ddl.set_attibute('mymds', 'columns', 'name, address');

This produces the following virtual text for indexing:

<NAME>
John Smith
</NAME>
<ADDRESS>
123 Main Street
</ADDRESS>

The system indexes the text between the tags, ignoring the tags themselves.

2.2.2.6 Indexing Columns as Sections

To index these tags as sections, you can optionally create field sections with the BASIC_SECTION_GROUP.

When you use expressions or functions, the tag is composed of the first 30 characters of the expression unless a column alias is used.

For example, if your expression is as follows:

exec ctx_ddl.set_attibute('mymds', 'columns', '4 + 17');

then it produces the following virtual text:

<4 + 17>
21
</4 + 17>

If your expression is as follows:

exec ctx_ddl.set_attibute('mymds', 'columns', '4 + 17 col1');

then it produces the following virtual text:

<col1>
21
<col1>

The tags are in uppercase unless the column name or column alias is in lowercase and surrounded by double quotes. For example:

exec ctx_ddl.set_attibute('mymds', 'COLUMNS', 'foo');

produces the following virtual text:

<FOO>
content of foo
</FOO>

For lowercase tags, use the following:

exec ctx_ddl.set_attibute('mymds', 'COLUMNS', 'foo "foo"');

This expression produces:

<foo>
content of foo
</foo>

2.2.3.1 Synchronizing Master/Detail Indexes

Changes to the detail table do not trigger re-indexing when you synchronize the index. Only changes to the indexed column in the master table triggers a re-index when you synchronize the index.

You can create triggers on the detail table to propagate changes to the indexed column in the master table row.

2.2.3.2 Example Master/Detail Tables

This example illustrates how master and detail tables are related to each other.

begin

ctx_ddl.create_preference('my_detail_pref', 'DETAIL_DATASTORE');
ctx_ddl.set_attribute('my_detail_pref', 'binary', 'true');
ctx_ddl.set_attribute('my_detail_pref', 'detail_table', 'my_detail');
ctx_ddl.set_attribute('my_detail_pref', 'detail_key', 'article_id');
ctx_ddl.set_attribute('my_detail_pref', 'detail_lineno', 'seq');
ctx_ddl.set_attribute('my_detail_pref', 'detail_text', 'text');

end;

CREATE INDEX myindex on my_master(body) indextype is ctxsys.context
parameters('datastore my_detail_pref');

2.2.4.1 PATH Attribute Limitations

The PATH attribute has the following limitations:

• If you specify a PATH attribute, then you can only use a simple filename in the indexed column. You cannot combine the PATH attribute with a path as part of the filename. If the files exist in multiple folders or directories, you must leave the PATH attribute unset, and include the full file name, with PATH, in the indexed column.

• On Windows systems, the files must be located on a local drive. They cannot be on a remote drive, whether the remote drive is mapped to a local drive letter.

2.2.4.2 FILE_DATASTORE and Security

File and URL datastores enable access to files on the actual database disk. This may be undesirable when security is an issue since any user can browse the file system that is accessible to the Oracle user. The FILE_ACCESS_ROLE system parameter can be used to set the name of a database role that is authorized to create an index using FILE or URL datastores. If set, any user attempting to create an index using FILE or URL datastores must have this role, or the index creation will fail. Only SYS can set FILE_ACCESS_ROLE, and an error will be raised if any other user tries to modify it. If FILE_ACCESS_ROLE is left at the default of NULL, access is disallowed. Thus, by default, users are not able to create indexes that use the file or URL datastores. Users can, if desired, set FILE_ACCESS_ROLE to PUBLIC if they want to preserve the behavior from earlier releases.

For example, the following statement sets the name of the database role:

ctx_adm.set_parameter('FILE_ACCESS_ROLE','TOPCAT');

where TOPCAT is the role that is authorized to create an index on a file or URL datastore. The CREATE INDEX operation will fail when a user that does not have an authorized role tries to create an index on a file or URL datastore. For example:

CREATE INDEX myindex ON mydocument(TEXT) INDEXTYPE IS ctxsys.context  PARAMETERS('DATASTORE ctxsys.file_datastore')

In this case, if the user does not have the role TOPCAT, then index creation will fail and return an error. For users who have the TOPCAT role, the index creation will proceed normally.

The authorized role name is checked any time the datastore is accessed. This includes index creation, index sync, and calls to document services, such as CTX_DOC.HIGHLIGHT.

2.2.4.3 FILE_DATASTORE Example

This example creates a file datastore preference called COMMON_DIR that has a path of /mydocs:

begin
 ctx_ddl.create_preference('COMMON_DIR','FILE_DATASTORE');
 ctx_ddl.set_attribute('COMMON_DIR','PATH','/mydocs');
end;

When you populate the table mytable, you need only insert filenames. The path attribute tells the system where to look during the indexing operation.

create table mytable(id number primary key, docs varchar2(2000)); 
insert into mytable values(111555,'first.txt');
insert into mytable values(111556,'second.txt');
commit;

Create the index as follows:

create index myindex on mytable(docs)
  indextype is ctxsys.context
  parameters ('datastore COMMON_DIR'); 

2.2.5.1 URL Syntax

The syntax of a URL you store in a text field is as follows (with brackets indicating optional parameters):

[URL:]<access_scheme>://<host_name>[:<port_number>]/[<url_path>]

The access_scheme string can be either ftp, http, or file. For example:

http://mymachine.us.oracle.com/home.html

Because this syntax is partially compliant with the RFC 1738 specification, the following restriction holds for the URL syntax: The URL must contain only printable ASCII characters. Non-printable ASCII characters and multibyte characters must be escaped with the %xx notation, where xx is the hexadecimal representation of the special character.

2.2.5.2 URL_DATASTORE Attributes

URL_DATASTORE has the following attributes:

timeout

The value of this attribute is ignored. This is provided for backward compatibility.

maxthreads

The value of this attribute is ignored. URL_DATASTORE is single-threaded. This is provided for backward compatibility.

urlsize

The value of this attribute is ignored. This is provided for backward compatibility.

maxdocsize

The value of this attribute is ignored. This is provided for backward compatibility.

maxurls

The value of this attribute is ignored. This is provided for backward compatibility.

http_proxy

Specify the fully qualified name of the host machine that serves as the HTTP proxy (gateway) for the machine on which Oracle Text is installed. You can optionally specify port number with a colon in the form hostname:port.

You must set this attribute if the machine is in an intranet that requires authentication through a proxy server to access Web files located outside the firewall.

ftp_proxy

Specify the fully qualified name of the host machine that serves as the FTP proxy (gateway) for the server on which Oracle Text is installed. You can optionally specify a port number with a colon in the form hostname:port.

This attribute must be set if the machine is in an intranet that requires authentication through a proxy server to access Web files located outside the firewall.

no_proxy

Specify a string of domains (up to sixteen, separated by commas) that are found in most, if not all, of the machines in your intranet. When one of the domains is encountered in a host name, no request is sent to the server(s) specified for ftp_proxy and http_proxy. Instead, the request is processed directly by the host machine identified in the URL.

For example, if the string us.example.com, uk.example.com is entered for no_proxy, any URL requests to machines that contain either of these domains in their host names are not processed by your proxy server(s).

2.2.5.3 URL_DATASTORE and Security

For a discussion of how to control file access security for file and URL datastores, refer to "FILE_DATASTORE and Security".

2.2.5.4 URL_DATASTORE Example

This example creates a URL_DATASTORE preference called URL_PREF for which the http_proxy, no_proxy, and timeout attributes are set. The defaults are used for the attributes that are not set.

begin
 ctx_ddl.create_preference('URL_PREF','URL_DATASTORE');
 ctx_ddl.set_attribute('URL_PREF','HTTP_PROXY','www-proxy.us.oracle.com');
 ctx_ddl.set_attribute('URL_PREF','NO_PROXY','us.oracle.com');
 ctx_ddl.set_attribute('URL_PREF','Timeout','300');
end;

Create the table and insert values into it:

create table urls(id number primary key, docs varchar2(2000));
insert into urls values(111555,'http://context.us.oracle.com');
insert into urls values(111556,'http://www.sun.com');
commit;
 

To create the index, specify URL_PREF as the datastore:

create index datastores_text on urls ( docs ) 
  indextype is ctxsys.context 
  parameters ( 'Datastore URL_PREF' ); 

2.2.6.1 Constraints

The following constraints apply to procedure:

• It can be owned by any user, but the user must have database permissions to execute procedure correctly

• It must be executable by the index owner

• It must not enter DDL or transaction control statements, like COMMIT

2.2.6.2 Editing Procedure after Indexing

When you change or edit the stored procedure, indexes based on it will not be notified, so you must manually re-create such indexes. So if the stored procedure makes use of other columns, and those column values change, the row will not be re-indexed. The row is re-indexed only when the indexed column changes.

output_type

Specify the datatype of the second argument to procedure. You can use either CLOB, BLOB, CLOB_LOC, BLOB_LOC, or VARCHAR2.

2.2.6.3 USER_DATASTORE with CLOB Example

Consider a table in which the author, title, and text fields are separate, as in the articles table defined as follows:

create table articles( 
    id       number, 
    author   varchar2(80), 
    title    varchar2(120), 
    text     clob );

The author and title fields are to be part of the indexed document text. Assume user appowner writes a stored procedure with the user datastore interface that synthesizes a document from the text, author, and title fields:

create procedure myproc(rid in rowid, tlob in out clob nocopy) is 
  begin 
      for c1 in (select author, title, text from articles 
                  where rowid = rid) 
      loop 

dbms_lob.writeappend(tlob, length(c1.title), c1.title);
   dbms_lob.writeappend(tlob, length(c1.author), c1.author);
   dbms_lob.writeappend(tlob, length(c1.text), c1.text);

end loop; 
    end; 
 

This procedure takes in a rowid and a temporary CLOB locator, and concatenates all the article's columns into the temporary CLOB. The for loop executes only once.

The user appowner creates the preference as follows:

begin

ctx_ddl.create_preference('myud', 'user_datastore'); 
ctx_ddl.set_attribute('myud', 'procedure', 'myproc'); 
ctx_ddl.set_attribute('myud', 'output_type', 'CLOB'); 

end;

When appowner creates the index on articles(text) using this preference, the indexing operation sees author and title in the document text.

2.2.6.4 USER_DATASTORE with BLOB_LOC Example

The following procedure might be used with OUTPUT_TYPE BLOB_LOC:

procedure myds(rid in rowid, dataout in out nocopy blob)
is
  l_dtype varchar2(10);
  l_pk    number;
begin
  select dtype, pk into l_dtype, l_pk from mytable where rowid = rid;
  if (l_dtype = 'MOVIE') then
    select movie_data into dataout from movietab where fk = l_pk;
  elsif (l_dtype = 'SOUND') then
    select sound_data into dataout from soundtab where fk = l_pk;
  end if;
end;

The user appowner creates the preference as follows:

begin

ctx_ddl.create_preference('myud', 'user_datastore'); 
ctx_ddl.set_attribute('myud', 'procedure', 'myproc'); 
ctx_ddl.set_attribute('myud', 'output_type', 'blob_loc'); 

end;

2.2.7.1 NESTED_DATASTORE Example

This section shows an example of using the NESTED_DATASTORE type to index documents stored as rows in a nested table.

create type nt_rec as object (
  lno number, -- line number
  ltxt varchar2(80) -- text of line
);

create type nt_tab as table of nt_rec;
create table mytab (
   id number primary key, -- primary key
   dummy char(1), -- dummy column for indexing
   doc nt_tab -- nested table
)
nested table doc store as myntab;

insert into mytab values (1, null, nt_tab());
insert into table(select doc from mytab where id=1) values (1, 'the dog');
insert into table(select doc from mytab where id=1) values (2, 'sat on mat ');
commit;

begin
-- create nested datastore pref
ctx_ddl.create_preference('ntds','nested_datastore'); 

-- nest tab column in main table
ctx_ddl.set_attribute('ntds','nested_column', 'doc'); 

-- nested table type
ctx_ddl.set_attribute('ntds','nested_type', 'scott.nt_tab');

-- lineno column in nested table
ctx_ddl.set_attribute('ntds','nested_lineno','lno');

--text column in nested table
ctx_ddl.set_attribute('ntds','nested_text', 'ltxt');
end;

create index myidx on mytab(dummy) -- index dummy column, not nest table
indextype is ctxsys.context parameters ('datastore ntds');

select * from mytab where contains(dummy, 'dog and mat')>0;
-- returns document 1, because it has dog in line 1 and mat in line 2.

2.3.1.1 UTF-16 Big- and Little-Endian Detection

If your character set is UTF-16, then you can specify UTF16AUTO to automatically detect big- or little-endian data. Oracle Text does so by examining the first two bytes of the document row.

If the first two bytes are 0xFE, 0xFF, the document is recognized as big-endian and the remainder of the document minus those two bytes is passed on for indexing.

If the first two bytes are 0xFF, 0xFE, the document is recognized as little-endian and the remainder of the document minus those two bytes is passed on for indexing.

If the first two bytes are anything else, the document is assumed to be big-endian and the whole document including the first two bytes is passed on for indexing.

2.3.1.2 Indexing Mixed-Character Set Columns

A mixed character set column is one that stores documents of different character sets. For example, a text table might store some documents in WE8ISO8859P1 and others in UTF8.

To index a table of documents in different character sets, you must create your base table with a character set column. In this column, specify the document character set on a per-row basis. To index the documents, Oracle Text converts the documents into the database character set.

Character set conversion works with the CHARSET_FILTER. When the charset column is NULL or not recognized, Oracle Text assumes the source character set is the one specified in the charset attribute.

create table hdocs (
     id number primary key,
     fmt varchar2(10),
     cset varchar2(20),
     text varchar2(80)
);

begin
cxt_ddl.create.preference('cs_filter', 'CHARSET_FILTER');
ctx_ddl.set_attribute('cs_filter', 'charset', 'UTF8');end

insert into hdocs values(1, 'text', 'WE8ISO8859P1', '/docs/iso.txt');
insert into hdocs values (2, 'text', 'UTF8', '/docs/utf8.txt');
commit;

create index hdocsx on hdocs(text) indextype is ctxsys.context
  parameters ('datastore ctxsys.file_datastore 
  filter cs_filter 
  format column fmt
  charset column cset');

2.3.2.1 Indexing Formatted Documents

To index a text column containing formatted documents such as Microsoft Word, use the AUTO_FILTER. This filter automatically detects the document format. Use the CTXSYS.AUTO_FILTER system-defined preference in the parameter clause as follows:

create index hdocsx on hdocs(text) indextype is ctxsys.context
  parameters ('datastore ctxsys.file_datastore 
  filter ctxsys.auto_filter');

2.3.2.2 Explicitly Bypassing Plain Text or HTML in Mixed Format Columns

A mixed-format column is a text column containing more than one document format, such as a column that contains Microsoft Word, PDF, plain text, and HTML documents.

The AUTO_FILTER can index mixed-format columns, automatically bypassing plain text, HTML, and XML documents. However, if you prefer not to depend on the built-in bypass mechanism, you can explicitly tag your rows as text and cause the AUTO_FILTER to ignore the row and not process the document in any way.

The format column in the base table enables you to specify the type of document contained in the text column. You can specify the following document types: TEXT, BINARY, and IGNORE. During indexing, the AUTO_FILTER ignores any document typed TEXT, assuming the charset column is not specified. (The difference between a document with a TEXT format column type and one with an IGNORE type is that the TEXT document is indexed, but ignored by the filter, while the IGNORE document is not indexed at all. Use IGNORE to overlook documents such as image files, or documents in a language that you do not want to index. IGNORE can be used with any filter type.)

To set up the AUTO_FILTER bypass mechanism, you must create a format column in your base table.

For example:

create table hdocs (
     id number primary key,
     fmt varchar2(10),
     text varchar2(80)
);

Assuming you are indexing mostly Word documents, you specify BINARY in the format column to filter the Word documents. Alternatively, to have the AUTO_FILTER ignore an HTML document, specify TEXT in the format column.

For example, the following statements add two documents to the text table, assigning one format as BINARY and the other TEXT:

insert into hdocs values(1, 'binary', '/docs/myword.doc');
insert in hdocs values (2, 'text', '/docs/index.html');
commit;

To create the index, use CREATE INDEX and specify the format column name in the parameter string:

create index hdocsx on hdocs(text) indextype is ctxsys.context
  parameters ('datastore ctxsys.file_datastore 
  filter ctxsys.auto_filter 
  format column fmt');

If you do not specify TEXT or BINARY for the format column, BINARY is used.

2.3.2.3 Character Set Conversion With AUTO_FILTER

The AUTO_FILTER converts documents to the database character set when the document format column is set to TEXT. In this case, the AUTO_FILTER looks at the charset column to determine the document character set.

If the charset column value is not an Oracle Text character set name, the document is passed through without any character set conversion.

If you do specify the charset column and do not specify the format column, the AUTO_FILTER works like the CHARSET_FILTER, except that in this case there is no Japanese character set auto-detection.

2.3.3.1 Indexing HTML Documents

If your document set is entirely HTML, Oracle recommends that you use the NULL_FILTER in your filter preference.

For example, to index an HTML document set, specify the system-defined preferences for NULL_FILTER and HTML_SECTION_GROUP as follows:

create index myindex on docs(htmlfile) indextype is ctxsys.context 
  parameters('filter ctxsys.null_filter
  section group ctxsys.html_section_group');

2.3.4.1 Filter Behavior

This filter behaves in the following way for each document:

• Read and remove header fields

• Decode message body if needed, depending on Content-transfer-encoding field

• Take action depending on the Content-Type field value and the user-specified behavior specified in a mail filter configuration file. (See "About the Mail Filter Configuration File".) The possible actions are:

  • produce the body in the output text (INCLUDE). If no character set is encountered in the INCLUDE parts in the Content-Type header field, then Oracle defaults to the value specified in the character set column in the base table. Name your populated character set column in the parameter string of the CREATE INDEX command.

  • AUTO_FILTER the body contents (AUTO_FILTER directive).

  • remove the body contents from the output text (IGNORE)

• If no behavior is specified for the type in the configuration file, then the defaults are as follows:

  • text/*: produce body in the output text

  • application/*: AUTO_FILTER the body contents

  • image/*, audio/*, video/*, model/*: ignore

• Multipart messages are parsed, and the mail filter applied recursively to each part. Each part is appended to the output.

• All text produced will be charset-converted to the database character set, if needed.

2.3.4.2 About the Mail Filter Configuration File

The MAIL_FILTER filter makes use of a mail filter configuration file, which contains directives specifying how a mail document should be filtered. The mail filter configuration file is a editable text file. Here you can override default behavior for each Content-Type. The configuration file also contains IANA-to-Oracle Globalization Support character set name mappings.

The location of the file must be in ORACLE_HOME/ctx/config. The name of the file to use is stored in the new system parameter MAIL_FILTER_CONFIG_FILE. On install, this is set to drmailfl.txt, which has useful default contents.

Oracle recommends that you create your own mail filter configuration files to avoid overwrite by the installation of a new version or patch set. The mail filter configuration file should be in the database character set.

[behavior]

application/zip     IGNORE
application/msword  AUTO_FILTER
model               IGNORE

[charsets]

US-ASCII     US7ASCI
ISO-8859-1   WE8ISO8859P1

2.3.4.3 Mail_Filter Example

Suppose there is an e-mail with the following form, in which other e-mails with different subject lines are attached to this e-mail:

To:  somebody@someplace
Subject:  mainheader
Content-Type:  multipart/mixed
. . .
Content-Type: text/plain
X-Ref:  some_value
Subject:  subheader 1
. . .
Content-Type:  text/plain
X-Control:  blah blah blah 
Subject:  subheader 2
. . .

Set INDEX_FIELDS to be "Subject" and, initially, PART_FIELD_STYLE to IGNORE.

CTX_DDL.CREATE_PREFERENCE('my_mail_filt', 'mail_filter');
CTX_DDL_SET_ATTRIBUTE(my_mail_filt', 'INDEX_FILES', 'subject');
CTX_DDL.SET ATTRIBUTE ('my_mail_filt', 'PART_FIELD_STYLE', 'ignore');

Now when the index is created, the file will be indexed as follows:

<SUBJECT>mainheader</SUBJECT>

If PART_FIELD_STYLE is instead set to TAG, this becomes:

<SUBJECT>mainheader</SUBJECT>
<SUBJECT>subheader1</SUBJECT>
<SUBJECT>subheader2</SUBJECT>

If PART_FIELD_STYLE is set to FIELD instead, this is the result:

<SUBJECT>mainheader<SUBJECT>
SUBJECT:subheader1
SUBJECT:subheader2

Finally, if PART_FIELD_STYLE is instead set to TEXT, then the result is:

<SUBJECT>mainheader</SUBJECT>
subheader1
subheader2

2.3.5.1 Using USER_FILTER with Charset and Format Columns

USER_FILTER bypasses documents that do not need to be filtered. Its behavior is sensitive to the values of the format and charset columns. In addition, USER_FILTER performs character set conversion according to the charset column values.

2.3.5.2 Explicitly Bypassing Plain Text or HTML in Mixed Format Columns

A mixed-format column is a text column containing more than one document format, such as a column that contains Microsoft Word, PDF, plain text, and HTML documents.

The USER_FILTER executable can index mixed-format columns, automatically bypassing textual documents. However, if you prefer not to depend on the built-in bypass mechanism, you can explicitly tag your rows as text and cause the USER_FILTER executable to ignore the row and not process the document in any way.

The format column in the base table enables you to specify the type of document contained in the text column. You can specify the following document types: TEXT, BINARY, and IGNORE. During indexing, the USER_FILTER executable ignores any document typed TEXT, assuming the charset column is not specified. (The difference between a document with a TEXT format column type and one with an IGNORE type is that the TEXT document is indexed, but ignored by the filter, while the IGNORE document is not indexed at all. Use IGNORE to overlook documents such as image files, or documents in a language that you do not want to index. IGNORE can be used with any filter type.

To set up the USER_FILTER bypass mechanism, you must create a format column in your base table. For example:

create table hdocs (
   id number primary key,
   fmt varchar2(10),
   text varchar2(80)
);

Assuming you are indexing mostly Word documents, you specify BINARY in the format column to filter the Word documents. Alternatively, to have the USER_FILTER executable ignore an HTML document, specify TEXT in the format column.

For example, the following statements add two documents to the text table, assigning one format as BINARY and the other TEXT:

insert into hdocs values(1, 'binary', '/docs/myword.doc');
insert into hdocs values(2, 'text', '/docs/index.html');
commit;

Assuming that this file is named upcase.pl, create the filter preference as follows:

ctx_ddl.create_preference
   (
    preference_name => 'USER_FILTER_PREF',
    object_name     => 'USER_FILTER'
    );

ctx_ddl.set_attribute ('USER_FILTER_PREF', 'COMMAND', 'upcase.pl');

To create the index, use CREATE INDEX and specify the format column name in the parameter string:

create index hdocsx on hdocs(text) indextype is ctxsys.context
   parameters ('datastore ctxsys.file_datastore
   filter 'USER_FILTER_PREF'
   format column fmt');

If you do not specify TEXT or BINARY for the format column, BINARY is used.

2.3.5.3 Character Set Conversion with USER_FILTER

The USER_FILTER executable converts documents to the database character set when the document format column is set to TEXT. In this case, the USER_FILTER executable looks at the charset column to determine the document character set.

If the charset column value is not an Oracle Text character set name, the document is passed through without any character set conversion.

If you do specify the charset column and do not specify the format column, the USER_FILTER executable works like the CHARSET_FILTER, except that in this case, there is no Japanese character set auto-detection. See "CHARSET_FILTER" for more information regarding CHARSET_FILTER.

2.3.5.4 User Filter Example

The following example shows a Perl script to be used as the user filter. This script converts the input text file specified in the first argument to uppercase and writes the output to the location specified in the second argument.

#!/usr/local/bin/perl

open(IN, $ARGV[0]);
open(OUT, ">".$ARGV[1]);

while (<IN>)
{
  tr/a-z/A-Z/;
  print OUT;
}

close (IN);
close (OUT);

Assuming that this file is named upcase.pl, create the filter preference as follows:

begin 
  ctx_ddl.create_preference 
    ( 
      preference_name => 'USER_FILTER_PREF', 
      object_name     => 'USER_FILTER' 
    ); 
  ctx_ddl.set_attribute
    ('USER_FILTER_PREF','COMMAND','upcase.pl');
end; 

Create the index in SQL*Plus as follows:

create index user_filter_idx on user_filter ( docs ) 
  indextype is ctxsys.context 
  parameters ('FILTER USER_FILTER_PREF'); 

2.3.6.1 Parameter Order

ROWID_PARAMETER, FORMAT_PARAMETER, and CHARSET_PARAMETERare all independent. The order is rowid, the format, then charset. However, the filter procedure is passed only the minimum parameters required.

For example, assume that INPUT_TYPE is BLOB and OUTPUT_TYPE is CLOB. If your filter procedure requires all parameters, then the procedure signature must be:

(id IN ROWID, format IN VARCHAR2, charset IN VARCHAR2, input IN BLOB, output IN
OUT NOCOPY CLOB)

If your procedure requires only the ROWID, then the procedure signature must be:

(id IN ROWID,input IN BLOB, ouput IN OUT NOCOPY CLOB)

2.3.6.2 Procedure Filter Execute Requirements

To create an index using a PROCEDURE_FILTER preference, the index owner must have execute permission on the procedure.

2.3.6.3 Error Handling

The filter procedure can raise any errors needed through the normal PL/SQL raise_application_error facility. These errors are propagated to the CTX_USER_INDEX_ERRORS view or reported to the user, depending on how the filter is invoked.

2.3.6.4 Procedure Filter Preference Example

Consider a filter procedure CTXSYS.NORMALIZE that you define with the following signature:

PROCEDURE NORMALIZE(id IN ROWID, charset IN VARCHAR2, input IN CLOB, 
output IN OUT NOCOPY VARCHAR2);

To use this procedure as your filter, set up your filter preference as follows:

begin
ctx_ddl.create_preference('myfilt', 'procedure_filter');
ctx_ddl.set_attribute('myfilt', 'procedure', 'normalize');
ctx_ddl.set_attribute('myfilt', 'input_type', 'clob');
ctx_ddl.set_attribute('myfilt', 'output_type', 'varchar2');
ctx_ddl.set_attribute('myfilt', 'rowid_parameter', 'TRUE');
ctx_ddl.set_attribute('myfilt', 'charset_parameter', 'TRUE');
end;

2.4.1.1 AUTO_LEXER Attributes Inherited from BASIC_LEXER

The following attributes are used in the same way and have the same effect on the AUTO_LEXER as their corresponding attributes in BASIC_LEXER:

• base_letter

• base_letter_type

• override_base_letter

• mixed_case

• alternate_spelling

2.4.1.2 AUTO_LEXER Language-Independent Attributes

Table 2-17 lists the language-independent attributes available in the AUTO_LEXER

2.4.1.3 AUTO_LEXER Language-Dependent Attributes

Table 2-18 lists the language-dependent attributes available in the AUTO_LEXER. The <language> variable in the attribute name refers to any of the supported language names that are listed in Table 2-10, "Character Sets Supported for CHARSET_FILTER Auto-detection". Examples are provided in "Examples for AUTO_LEXER User-Supplied Dictionary Attributes".

Table 2-19 Default Values for AUTO_LEXER Language-Dependent Attributes

Attribute	Language	Default Value
<language>_prefix_morphemes	All languages	None
<language>_suffix_morphemes	English	s es er
	Spanish	ba n s es
	Portuguese	s es
	German	in innen
	French	ne e
	All other languages	None
<language>_punctuations	English	. ? !
	Arabic, Catalan, Croatian, Czech, English, Greek, Hebrew, Hungarian, Polish, Romanian, Russian, Serbian, Slovak, Slovenian, Turkish	. ? ! - --
	Bokmal, Danish, Finnish, French, German, Italian, Korean, Nynorsk, Portuguese, Spanish, Swedish	, ? !
	Japanese	Description of the illustration imga.gif
	Persian	Description of the illustration imgb.gif
	Simplified Chinese	Description of the illustration imgd.gif
	Thai	Description of the illustration imge.gif
	Traditional Chinese	Description of the illustration imgf.gif
<language>_sentence_starts	Arabic, catalan, Croatian, Czech, English, Finish, French, Greek, Hebrew, Hungarian, Italian, Japanese, Korean, Persian, Polish, Portuguese, Romanian, Russian, Serbian, Simplified Chinese, Slovak, Slovenian, Thai, Traditional Chinese, Turkish	"
	Bokmal, Danish, Nynorsk, Swedish	" –
	German	" ,, „
	Spanish	" ¿ ¡
<language>_non_sentence_ending_abrev	Arabic, Polish, Romanian, Russian, Serbian, Slovak, Slovenian, Turkish	e.g. i.e. viz. a.k.a.
	Bokmal	f.eks. f. eks. inkl. 1.kons. 1.sekr. 1.aman. vit.ass. vit. ass. sekr. stortingsrep." stip. prof. kons. hr. gen.sekr. gen. sekr. førstekons. førstesekr. førsteaman. fullm. frk. d.e. d.y. dr. dir. aman. adm.dir. adm. dir.
	Catalan	R.D. pp.
	Croatian, Czech, Greek, Hebrew, Hungarian	e.g. i.e. viz. a.k.a.
	Danish	f.eks. f. eks. inkl. sr. skuesp. sekr. prof. mus. lrs. logr. kgl. insp. hr. hrs. gdr. frk. fr. forst. forf. fm. fmd. esq. d.æ d.æ. d.y. dr. dir. dept.chef civiling. bibl. ass. admn. adj. Skt. H.K.H.
	English, Japanese, Simplified Chinese, Thai, Traditional Chinese	e.g. i.e. viz. a.k.a. Adm. Br. Capt. Cdr. Cmdr. Col. Comdr. Comdt. Dr. Drs. Fr. Gen. Gov. Hon. Ins. Lieut. Lt. Maj. Messrs. Mdm. Mlle. Mlles. Mme. Mmes. Mr. Mrs. Ms. Pres. Prof. Profs. Pvt. Rep. Rev. Revd. Secy. Sen. Sgt. Sra. Srta. St. Ste.
	Finish	inkl. dipl. prof. hrr. hr. Hrr. Hr. dr. Dr.
	French	c.-à-d. cf. e.g. ex. i.e. Pr. Prof. M. Mr. Mrs. Mme Mmes Mlle Mlles Mgr. MM. Lieut. Gén. Dr. Col.
	German	ca. bzw. e.g. i.e. inkl. Fr. Frl. Mme. Mile. Mag. Stud. Tel. Hr. Hrn. apl.Prof. Prof.
	Italian	e.g. i.e. pag. pagg. tel. T.V. N.H. N.D. comm. col. cav. cap. geom. gen. ing. jr. mr. mons. mar. magg. prof. prof.ssa prof.sse proff. pres. perito ind. p. p.i. sr. s.ten. sottoten. sig. serg. sen. segr. sac. ten. uff. vicepres. vesc. S.S. S.E. avv. app. amm. arch. on. dir. dott. dott.ssa dr. rag.
	Korean	e.g. i.e. a.k.a. Dr. Mr. Mrs. Ms. Prof.
	Nynorsk	f.eks. f. eks. inkl. 1.kons. 1.sekr. 1.aman. vit.ass. vit. ass. sekr. stortingsrep. stip. prof. kons. hr. gen.sekr. gen. sekr. førstekons. førstesekr. førsteaman. fullm. frk. d.e. d.y. dr. dir. aman. adm.dir. adm. dir. fyrstesekr. fyrstekons. fyrsteaman. hr
	Persian	Description of the illustration imgc.gif Dr. Mr. Mrs. Ms. Prof. e.g. i.e. viz. a.k.a.
	Portuguese	cf. Cf. e.g. E.g. i.é. I.é. p.ex. P.ex. pág. pag. Pág. Pag. tel. telef. Tel. Telef. sr. srs. sra. mr. eng. dr. dra. Dr. Dra. V.Ex. V.Exa. S. N. S. Mrs. Eng. Ex. Exa.
	Spanish	e.g. i.e. ej. p.ej. pág. págs. tel. tfno. Fr. Ldo. Lda. Lic. Pbro. D. Dña. Dr. Dres. Dra. Dras. Dn. Mons. Rvdo. Sto. Sta. Sr. Srs. Srta. Srtas. Sres. Sra. Sras. Excmo. Excma. Ilmo. Ilma. Sto. Sta.
	Swedish	inkl. prof. hrr. hr. Hrr. Hr. dr. Dr.

Examples for AUTO_LEXER Language-Dependent Attributes

ctx_ddl.set_attribute(
      'a_lex', 'english_prefix_morphemes', 're'
);

ctx_ddl.set_attribute(
      'a_lex', 'english_suffix_morphemes', 's es'
);

ctx_ddl.set_attribute(
      'a_lex', 'english_punctuations', '. ? !'
);

ctx_ddl.set_attribute(
      'a_lex', 'english_sentence_starts', '" '
);

ctx_ddl.set_attribute(
      'a_lex', 'english_sentence_starts', 'e.g. a.k.a. Dr.'
);

2.4.1.4 AUTO_LEXER User-Defined Dictionary Attributes

The attributes in this section are language-specific and are used to set the name of user-supplied dictionary files. The attributes share the following behavior:

• The value of the attribute specifies only the filename (excluding the file path) of the dictionary. The file should be placed at the following location: $ORACLE_HOME/ctx/data/user.

• The set_attribute method does not load the file; it only records the filename. Therefore, the file must be at the specified location when the dictionary is needed. Otherwise, an error will be raised.

• The client dictionaries specify the character encoding of the file in the <?xml ...?> markup (for example, <?xml encoding="cp-1252" ?>). If no <?xml ... ?> specification exists and no special identification is given (for example, as for UCS-4), then the system assumes that the encoding is cp-1252, instead of UTF-8 as specified in the XML standard.

The user-supplied dictionary attributes are listed in Table 2-20, and examples are given for each attribute under "Examples for AUTO_LEXER User-Supplied Dictionary Attributes". The values inside the brackets in Table 2-20 refer to parts-of-speech tags.

Examples for AUTO_LEXER User-Supplied Dictionary Attributes

exec Ctx_ddl.set_attribute (
     'a_lex', 
     'english_abbrev_dictionary', 
     'english_abbrev_dictionary.xml'
);

Dictionary example:

<explicit-pair-list>
     <item key="inc." analysis="[Abbrev]"/>
     <item key="Inc." analysis="[Abbrev]"/>
</explicit-pair-list>

exec Ctx_ddl.set_attribute
            ('a_lex', 'english_tagging_dictionary', 'english_tagging_dictionary.xml');

<explicit-pair-list>
     <item key="Inxight" analysis = "[Prop]"></item>
     <item key="furby" analysis = "[Nn-Indef-Sg]"></item>
     <item key="furbys"
          analysis = "[Nn-Indef-Sg-Gen]"></item>
     <item key="furbyer" analysis = "[Nn-Indef-Pl]"></item>
     <item key="furbyen" analysis = "[Nn-Def-Sg]"></item>
     <item key="furbyene" analysis = "[Nn-Def-Pl]"></item>
</explicit-pair-list>

The values inside the brackets are part-of-speech tags.

exec Ctx_ddl.set_attribute (
    'a_lex', 
    'english_tagging_dictionary',
    'english_tagged_stem_dictionary.xml'
);

Dictionary example:

<tag-stem-list>
     <item key = "running[V-PrPart]" stem = "run" > </item>
</tag-stem-list>

The values inside the brackets are part-of-speech tags.

exec Ctx_ddl.set_attribute (
    'a_lex', 
    'english_tagging_dictionary', 
    'english_stemming_dictionary.xml'
);

Dictionary example:

<explicit-pair-list>
     <item key="running" stem="run"></item>
     <item key="flying" stem="fly"></item>
</explicit-pair-list>

exec Ctx_ddl.set_attribute (
    'a_lex', 
    'thai_ccjt_dictionary', 
    'thai_ccjt_dictionary.xml'
);

CCJT dictionary example:

2.4.2.1 Stemming User-Dictionaries

You can create a user-dictionary for your own language to customize how words are decomposed. These dictionaries are shown in Table 2-23.

Stemming user-dictionaries are not supported for languages other than those listed in Table 2-23.

The format for the user dictionary is as follows:

output term <tab> input term

The individual parts of the decomposed word must be separated by the # character. The following example entries are for the German word Hauptbahnhof:

Hauptbahnhof<tab>Haupt#Bahnhof
Hauptbahnhofes<tab>Haupt#Bahnhof
Hauptbahnhof<tab>Haupt#Bahnhof
Hauptbahnhoefe<tab>Haupt#Bahnhof

index_themes

Specify YES to index theme information in English or French. This makes ABOUT queries more precise. The index_themes and index_text attributes cannot both be NO.

If you use the BASIC_LEXER and specify no value for index_themes, this attribute defaults to NO.

You can set this parameter to TRUE for any index type, including CTXCAT. To enter an ABOUT query with CATSEARCH, use the query template with CONTEXT grammar.

Note:

index_themes requires an installed knowledge base. A knowledge base may or may not have been installed with Oracle Text. For more information on knowledge bases, see Oracle Text Application Developer's Guide.

prove_themes

Specify YES to prove themes. Theme proving attempts to find related themes in a document. When no related themes are found, parent themes are eliminated from the document.

While theme proving is acceptable for large documents, short text descriptions with a few words rarely prove parent themes, resulting in poor recall performance with ABOUT queries.

Theme proving results in higher precision and less recall (less rows returned) for ABOUT queries. For higher recall in ABOUT queries and possibly less precision, you can disable theme proving. Default is YES.

The prove_themes attribute is supported for CONTEXT and CTXRULE indexes.

theme_language

Specify which knowledge base to use for theme generation when index_themes is set to YES. When index_themes is NO, setting this parameter has no effect on anything.

Specify any globalization support language or AUTO. You must have a knowledge base for the language you specify. This release provides a knowledge base in only English and French. In other languages, you can create your own knowledge base.

See Also:

"Adding a Language-Specific Knowledge Base" in Chapter 14, "Oracle Text Utilities".

The default is AUTO, which instructs the system to set this parameter according to the language of the environment.

index_stems

Specify the stemmer to use for stem indexing. Choose one of the following stemmers:

NONE ARABIC CATALAN CROATIAN CZECH DANISH DERIVATIONAL DUTCH ENGLISH FINNISH FRENCH GERMAN

HEBREW HUNGARIAN ITALIAN NORWEGIAN POLISH PORTUGUESE ROMANIAN SLOVAK SLOVENIAN SPANISH SWEDISH

Tokens are stemmed to a single base form at index time in addition to the normal forms. Indexing stems enables better query performance for stem ($) queries, such as $computed.

index_text

Specify YES to index word information. The index_themes and index_text attributes cannot both be NO.

The default is NO.

alternate_spelling

Specify either GERMAN, DANISH, or SWEDISH to enable the alternate spelling in one of these languages. Enabling alternate spelling enables you to query a word in any of its alternate forms.

Alternate spelling is off by default; however, in the language-specific scripts that Oracle provides in admin/defaults (drdefd.sql for German, drdefdk.sql for Danish, and drdefs.sql for Swedish), alternate spelling is turned on. If your installation uses these scripts, then alternate spelling is on. However, you can specify NONE for no alternate spelling. For more information about the alternate spelling conventions Oracle Text uses, see Alternate Spelling.

new_german_spelling

Specify whether the queries using the BASIC_LEXER return both traditional and reformed (new) spellings of German words. If new_german_spelling is set to YES, then both traditional and new forms of words are indexed. If it is set to NO, then the word will be indexed only as it as provided in the query. The default is NO.

See Also:

"New German Spelling"

2.4.2.2 BASIC_LEXER Example

The following example sets printjoin characters and disables theme indexing with the BASIC_LEXER:

begin
ctx_ddl.create_preference('mylex', 'BASIC_LEXER');
ctx_ddl.set_attribute('mylex', 'printjoins', '_-');
ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO');
ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); 
end;

To create the index with no theme indexing and with printjoin characters set as described, enter the following statement:

create index myindex on mytable ( docs ) 
  indextype is ctxsys.context 
  parameters ( 'LEXER mylex' ); 

2.4.3.1 Multi-language Stoplists

When you use the MULTI_LEXER, you can also use a multi-language stoplist for indexing.

2.4.3.2 MULTI_LEXER Example

Create the multi-language table with a primary key, a text column, and a language column as follows:

create table globaldoc (
   doc_id number primary key,
   lang varchar2(3),
   text clob
);

Assume that the table holds mostly English documents, with the occasional German or Japanese document. To handle the three languages, you must create three sub-lexers, one for English, one for German, and one for Japanese:

ctx_ddl.create_preference('english_lexer','basic_lexer');
ctx_ddl.set_attribute('english_lexer','index_themes','yes');
ctx_ddl.set_attribute('english_lexer','theme_language','english');

ctx_ddl.create_preference('german_lexer','basic_lexer');
ctx_ddl.set_attribute('german_lexer','composite','german');
ctx_ddl.set_attribute('german_lexer','mixed_case','yes');
ctx_ddl.set_attribute('german_lexer','alternate_spelling','german');

ctx_ddl.create_preference('japanese_lexer','japanese_vgram_lexer');

Create the multi-lexer preference:

ctx_ddl.create_preference('global_lexer', 'multi_lexer');

Because the stored documents are mostly English, make the English lexer the default using CTX_DDL.ADD_SUB_LEXER:

ctx_ddl.add_sub_lexer('global_lexer','default','english_lexer');

Now add the German and Japanese lexers in their respective languages with CTX_DDL.ADD_SUB_LEXER procedure. Also assume that the language column is expressed in the standard ISO 639-2 language codes, so add those as alternative values.

ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','ger');
ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer','jpn');

Now create the index globalx, specifying the multi-lexer preference and the language column in the parameter clause as follows:

create index globalx on globaldoc(text) indextype is ctxsys.context
parameters ('lexer global_lexer language column lang');

2.4.3.3 Querying Multi-Language Tables

At query time, the multi-lexer examines the language setting and uses the sub-lexer preference for that language to parse the query.

If the language is not set, then the default lexer is used. Otherwise, the query is parsed and run as usual. The index contains tokens from multiple languages, so such a query can return documents in several languages. To limit your query to a given language, use a structured clause on the language column.

If the language column is set to AUTO, then the multi-lexer detects the language of the document for the supported languages shown in Table 2-24.

2.4.4.1 CHINESE_VGRAM_LEXER Attribute

The CHINESE_VGRAM_LEXER has the following attribute:

2.4.4.2 Character Sets

You can use this lexer if your database uses one of the following character sets:

• AL32UTF8

• ZHS16CGB231280

• ZHS16GBK

• ZHS32GB18030

• ZHT32EUC

• ZHT16BIG5

• ZHT32TRIS

• ZHT16HKSCS

• ZHT16MSWIN950

• UTF8

2.4.5.1 CHINESE_LEXER Attribute

The CHINESE_LEXER has the following attribute:

2.4.5.2 Customizing the Chinese Lexicon

You can modify the existing lexicon (dictionary) used by the Chinese lexer, or create your own Chinese lexicon, with the ctxlc command.

2.4.6.1 JAPANESE_VGRAM_LEXER Attributes

This lexer has the following attributes:

2.4.6.2 JAPANESE_VGRAM_LEXER Character Sets

You can use this lexer if your database uses one of the following character sets:

• JA16SJIS

• JA16EUC

• UTF8

• AL32UTF8

• JA16EUCTILDE

• JA16EUCYEN

• JA16SJISTILDE

• JA16SJISYEN

2.4.7.1 Customizing the Japanese Lexicon

You can modify the existing lexicon (dictionary) used by the Japanese lexer, or create your own Japanese lexicon, with the ctxlc command.

2.4.7.2 JAPANESE_LEXER Attributes

This lexer has the following attributes:

2.4.7.3 JAPANESE LEXER Character Sets

The JAPANESE_LEXER supports the following character sets:

• JA16SJIS

• JA16EUC

• UTF8

• AL32UTF8

• JA16EUCTILDE

• JA16EUCYEN

• JA16SJISTILDE

• JA16SJISYEN

2.4.7.4 Japanese Lexer Example

When you specify JAPANESE_LEXER for creating text index, the JAPANESE_LEXER resolves a sentence into words.

For example, the following compound word (natural language institute)

is indexed as three tokens:

To resolve a sentence into words, the internal dictionary is referenced. When a word cannot be found in the internal dictionary, Oracle Text uses the JAPANESE_VGRAM_LEXER to resolve it.

2.4.8.1 Supplied Dictionaries

The KOREAN_MORPH_LEXER uses four dictionaries:

The grammar, user-defined, and stopword dictionaries should be written using the KSC 5601 or MSWIN949 character sets. You can modify these dictionaries using the defined rules. The system dictionary must not be modified.

You can add unregistered words to the user-defined dictionary file. The rules for specifying new words are in the file.

2.4.8.2 Supported Character Sets

You can use KOREAN_MORPH_LEXER if your database uses one of the following character sets:

• KO16KSC5601

• KO16MSWIN949

• UTF8

• AL32UTF8

The KOREAN_MORPH_LEXER enables mixed-case searches.

2.4.8.3 Unicode Support

The KOREAN_MORPH_LEXER supports:

• Words in non-KSC5601 Korean characters defined in Unicode

• Supplementary characters

Some Korean documents may have non-KSC5601 characters in them. As the KOREAN_MORPH_LEXER can recognize all possible 11,172 Korean (Hangul) characters, such documents can also be interpreted by using the UTF8 or AL32UTF8 character sets.

Use the AL32UTF8 character set for your database to extract surrogate characters. By default, the KOREAN_MORPH_LEXER extracts all series of surrogate characters in a document as one token for each series.

2.4.8.4 KOREAN_MORPH_LEXER Attributes

When you use the KOREAN_MORPH_LEXER, you can specify the following attributes:

2.4.8.5 Limitations

Sentence and paragraph sections are not supported with the KOREAN_MORPH_LEXER.

2.4.8.6 KOREAN_MORPH_LEXER Example: Setting Composite Attribute

Use the composite attribute to control how composite nouns are indexed.

2.4.8.6.1 NGRAM Example

When you specify NGRAM for the composite attribute, composite nouns are indexed with all possible component tokens. For example, the following composite noun (information processing institute)

Description of the illustration a1.jpg

is indexed as six tokens:

Description of the illustration a2.jpg


Description of the illustration a3.jpg

Specify NGRAM indexing as follows:

begin
ctx_ddl.create_preference('my_lexer','KOREAN_MORPH_LEXER');
ctx_ddl.set_attribute('my_lexer','COMPOSITE','NGRAM');
end

To create the index:

create index koreanx on korean(text) indextype is ctxsys.context
parameters ('lexer my_lexer');

2.4.8.6.2 COMPONENT_WORD Example

When you specify COMPONENT_WORD for the composite attribute, composite nouns and their components are indexed. For example, the following composite noun (information processing institute)

Description of the illustration a1.jpg

is indexed as four tokens:

Description of the illustration a1.jpg


Description of the illustration comp.jpg

Specify COMPONENT_WORD indexing as follows:

begin
ctx_ddl.create_preference('my_lexer','KOREAN_MORPH_LEXER');
ctx_ddl.set_attribute('my_lexer','COMPOSITE','COMPONENT_WORD');
end

To create the index:

create index koreanx on korean(text) indextype is ctxsys.context
parameters ('lexer my_lexer');

2.4.9.1 Limitations

The following features are not supported with the USER_LEXER:

• CTX_DOC.GIST and CTX_DOC.THEMES

• CTX_QUERY.HFEEDBACK

• ABOUT query operator

• CTXRULE index type

• VGRAM indexing algorithm

2.4.9.2 USER_LEXER Attributes

USER_LEXER has the following attributes:

2.4.9.3 INDEX_PROCEDURE

This callback stored procedure is called by Oracle Text as needed to tokenize a document or a stop word found in the stoplist object.

2.4.9.4 INPUT_TYPE

Two different interfaces are supported for the User-defined lexer indexing procedure. One interface enables the document or stop word and the corresponding tokens encoded as XML to be passed as VARCHAR2 datatype whereas the other interface uses the CLOB datatype. This attribute indicates the interface implemented by the stored procedure specified by the INDEX_PROCEDURE attribute.

2.4.9.5 QUERY_PROCEDURE

This callback stored procedure is called by Oracle Text as needed to tokenize words in the query. A space-delimited group of characters (excluding the query operators) in the query will be identified by Oracle Text as a word.

2.4.9.6 Encoding Tokens as XML

The sequence of tokens returned by your stored procedure must be represented as an XML 1.0 document. The XML document must be valid with respect to the XML Schemas given in the following sections.

• XML Schema for No-Location, User-defined Indexing Procedure

• XML Schema for User-defined Indexing Procedure with Location

• XML Schema for User-defined Lexer Query Procedure

2.4.9.7 XML Schema for No-Location, User-defined Indexing Procedure

This section describes additional constraints imposed on the XML document returned by the user-defined lexer indexing procedure when the third parameter is FALSE. The XML document returned must be valid with respect to the following XML Schema:

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

  <xsd:element name="tokens">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:choice minOccurs="0" maxOccurs="unbounded"> 
          <xsd:element name="eos" type="EmptyTokenType"/>
          <xsd:element name="eop" type="EmptyTokenType"/>
          <xsd:element name="num" type="xsd:token"/> 
          <xsd:group ref="IndexCompositeGroup"/>
        </xsd:choice>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

  <!-- 
  Enforce constraint that compMem element must be preceeded by word element
  or compMem element for indexing 
  -->
  <xsd:group name="IndexCompositeGroup">
    <xsd:sequence>
      <xsd:element name="word" type="xsd:token"/>
      <xsd:element name="compMem" type="xsd:token" minOccurs="0"
maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:group>

  <!-- EmptyTokenType defines an empty element without attributes -->
  <xsd:complexType name="EmptyTokenType"/>

</xsd:schema>

Here are some of the constraints imposed by this XML Schema:

• The root element is tokens. This is mandatory. It has no attributes.

• The root element can have zero or more child elements. The child elements can be one of the following elements: eos, eop, num, word, and compMem. Each of these represent a specific type of token.

• The compMem element must be preceded by a word element or a compMem element.

• The eos and eop elements have no attributes and must be empty elements.

• The num, word, and compMem elements have no attributes. Oracle Text will normalize the content of these elements as follows: convert whitespace characters to space characters, collapse adjacent space characters to a single space character, remove leading and trailing spaces, perform entity reference replacement, and truncate to 64 bytes.

Table 2-36 describes the element names defined in the preceding XML Schema.

<tokens>
  <word> VOM </word>
  <word> NORDHAUPTBAHNHOF </word>
  <compMem>NORD</compMem>
  <compMem>HAUPT </compMem>
  <compMem>BAHNHOF </compMem>
  <compMem>HAUPTBAHNHOF </compMem>
  <word> UND </word>
  <word> AUS </word>
  <word> DER </word>
  <word> INNENSTADT </word>
  <word> ZUM </word>
  <word> MESSEGELÄNDE </word>
  <eos/>
</tokens>

<tokens>
  <word> ORACLE11G</word>
  <word> RELEASE </word>
  <num> 1 </num>
</tokens>

<tokens>
  <word> WHERE </word>
  <word> salary&lt;2500.00 </word>
  <word> AND </word>
  <word> job </word>
  <word> F&amp;B </word>
  <word> Manager </word>
</tokens>

2.4.9.8 XML Schema for User-defined Indexing Procedure with Location

This section describes additional constraints imposed on the XML document returned by the user-defined lexer indexing procedure when the third parameter is TRUE. The XML document returned must be valid according to the following XML schema:

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

  <xsd:element name="tokens">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:choice minOccurs="0" maxOccurs="unbounded">
          <xsd:element name="eos" type="EmptyTokenType"/>
          <xsd:element name="eop" type="EmptyTokenType"/>
          <xsd:element name="num" type="DocServiceTokenType"/>
          <xsd:group ref="DocServiceCompositeGroup"/>
        </xsd:choice>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

  <!-- 
  Enforce constraint that compMem element must be preceeded by word element
  or compMem element for document service
  -->
  <xsd:group name="DocServiceCompositeGroup">
    <xsd:sequence>
      <xsd:element name="word" type="DocServiceTokenType"/>
      <xsd:element name="compMem" type="DocServiceTokenType" minOccurs="0"
           maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:group>

  <!-- EmptyTokenType defines an empty element without attributes -->
  <xsd:complexType name="EmptyTokenType"/>

  <!-- 
  DocServiceTokenType defines an element with content and mandatory attributes 
  -->
  <xsd:complexType name="DocServiceTokenType">
    <xsd:simpleContent>
      <xsd:extension base="xsd:token">
        <xsd:attribute name="off" type="OffsetType" use="required"/>
        <xsd:attribute name="len" type="xsd:unsignedShort" use="required"/>
      </xsd:extension>
    </xsd:simpleContent>
  </xsd:complexType>

  <xsd:simpleType name="OffsetType">
    <xsd:restriction base="xsd:unsignedInt">
      <xsd:maxInclusive value="2147483647"/>
    </xsd:restriction>
  </xsd:simpleType>

</xsd:schema>

Some of the constraints imposed by this XML Schema are as follows:

• The root element is tokens. This is mandatory. It has no attributes.

• The root element can have zero or more child elements. The child elements can be one of the following elements: eos, eop, num, word, and compMem. Each of these represent a specific type of token.

• The compMem element must be preceded by a word element or a compMem element.

• The eos and eop elements have no attributes and must be empty elements.

• The num, word, and compMem elements have two mandatory attributes: off and len. Oracle Text will normalize the content of these elements as follows: convert whitespace characters to space characters, collapse adjacent space characters to a single space character, remove leading and trailing spaces, perform entity reference replacement, and truncate to 64 bytes.

• The off attribute value must be an integer between 0 and 2147483647 inclusive.

• The len attribute value must be an integer between 0 and 65535 inclusive.

Table 2-36 describes the element types defined in the preceding XML Schema.

Table 2-37 describes the attributes defined in the preceding XML Schema.

Sum of off attribute value and len attribute value must be less than or equal to the total number of characters in the document being tokenized. This is to ensure that the document offset and characters being referenced are within the document boundary.

<tokens>
  <word off="0" len="4"> USE </word>
  <word off="5" len="7"> DEF </word>
  <word off="13" len="5"> LEX </word>
  <eos/>
</tokens>

2.4.9.9 XML Schema for User-defined Lexer Query Procedure

This section describes additional constraints imposed on the XML document returned by the user-defined lexer query procedure. The XML document returned must be valid with respect to the following XML Schema:

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

  <xsd:element name="tokens">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:choice minOccurs="0" maxOccurs="unbounded">
          <xsd:element name="num" type="QueryTokenType"/>
          <xsd:group ref="QueryCompositeGroup"/>
        </xsd:choice>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

<!--
Enforce constraint that compMem element must be preceeded by word element
or compMem element for query
-->
  <xsd:group name="QueryCompositeGroup">
    <xsd:sequence>
      <xsd:element name="word" type="QueryTokenType"/>
      <xsd:element name="compMem" type="QueryTokenType" minOccurs="0"
                                              maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:group>

  <!-- 
  QueryTokenType defines an element with content and with an optional attribute
  -->
  <xsd:complexType name="QueryTokenType">
    <xsd:simpleContent>
      <xsd:extension base="xsd:token">
        <xsd:attribute name="wildcard" type="WildcardType" use="optional"/>
      </xsd:extension>
    </xsd:simpleContent>
  </xsd:complexType>

  <xsd:simpleType name="WildcardType">
    <xsd:restriction base="WildcardBaseType">
      <xsd:minLength value="1"/>
      <xsd:maxLength value="64"/>
    </xsd:restriction>     
  </xsd:simpleType>

  <xsd:simpleType name="WildcardBaseType">
    <xsd:list>
      <xsd:simpleType>
        <xsd:restriction base="xsd:unsignedShort">
          <xsd:maxInclusive value="378"/>
        </xsd:restriction>
      </xsd:simpleType>
    </xsd:list>
  </xsd:simpleType>

</xsd:schema>

Here are some of the constraints imposed by this XML Schema:

• The root element is tokens. This is mandatory. It has no attributes.

• The root element can have zero or more child elements. The child elements can be one of the following elements: num and word. Each of these represent a specific type of token.

• The compMem element must be preceded by a word element or a compMem element.

  The purpose of compMem is to enable USER_LEXER queries to return multiple forms for a single query. For example, if a user-defined lexer indexes the word bank as BANK(FINANCIAL) and BANK(RIVER), the query procedure can return the first term as a word and the second as a compMem element:

  <tokens>
    <word>BANK(RIVER)</word>
    <compMem>BANK(FINANCIAL)</compMem>
  </tokens>
  

  See Table 2-38, "User-defined Lexer Query Procedure XML Schema Attributes" for more on the compMem element.

• The num and word elements have a single optional attribute: wildcard. Oracle Text will normalize the content of these elements as follows: convert whitespace characters to space characters, collapse adjacent space characters to a single space character, remove leading and trailing spaces, perform entity reference replacement, and truncate to 64 bytes.

• The wildcard attribute value is a white-space separated list of integers. The minimum number of integers is 1 and the maximum number of integers is 64. The value of the integers must be between 0 and 378 inclusive. The intriguers in the list can be in any order.

Table 2-36 describes the element types defined in the preceding XML Schema.

Table 2-38 describes the attribute defined in the preceding XML Schema.

<tokens>
  <word> PSEUDO </word>
  <word wildcard="1 7"> %MORPH% </word>
</tokens>

Query word: <%>
Tokens:
<tokens>
  <word wildcard="5"> &lt;%&gt; </word>
</tokens>

2.4.10.1 WORLD_LEXER Attribute

The WORLD_VGRAM_LEXER has the following attribute:

2.4.10.2 WORLD_LEXER Example

Here is an example of creating an index using WORLD_LEXER.

exec ctx_ddl.create_preference('MYLEXER', 'world_lexer');
create index doc_idx on doc(data)
  indextype is CONTEXT
  parameters ('lexer MYLEXER
               stoplist CTXSYS.EMPTY_STOPLIST');

2.5.2.1 Enabling Fuzzy Matching and Stemming

The following example enables stemming and fuzzy matching for English. The preference STEM_FUZZY_PREF sets the number of expansions to the maximum allowed. This preference also instructs the system to create a substring index to improve the performance of double-truncated searches.

begin 
  ctx_ddl.create_preference('STEM_FUZZY_PREF', 'BASIC_WORDLIST'); 
  ctx_ddl.set_attribute('STEM_FUZZY_PREF','FUZZY_MATCH','ENGLISH');
  ctx_ddl.set_attribute('STEM_FUZZY_PREF','FUZZY_SCORE','0');
  ctx_ddl.set_attribute('STEM_FUZZY_PREF','FUZZY_NUMRESULTS','5000');
  ctx_ddl.set_attribute('STEM_FUZZY_PREF','SUBSTRING_INDEX','TRUE');
  ctx_ddl.set_attribute('STEM_FUZZY_PREF','STEMMER','ENGLISH');
end; 

To create the index in SQL, enter the following statement:

create index fuzzy_stem_subst_idx on mytable ( docs ) 
  indextype is ctxsys.context parameters ('Wordlist STEM_FUZZY_PREF');

2.5.2.2 Enabling Sub-string and Prefix Indexing

The following example sets the wordlist preference for prefix and sub-string indexing. For prefix indexing, it specifies that Oracle Text create token prefixes between 3 and 4 characters long:

begin 

ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST'); 
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',3);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 4);
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');

end

2.5.2.3 Setting Wildcard Expansion Limit

Use the wildcard_maxterms attribute to set the maximum allowed terms in a wildcard expansion.

--- create a sample table
drop table quick ;
create table quick 
  ( 
    quick_id number primary key, 
    text      varchar(80) 
  ); 

--- insert a row with 10 expansions for 'tire%'
insert into quick ( quick_id, text ) 
  values ( 1, 'tire tirea tireb tirec tired tiree tiref tireg tireh tirei tirej');
commit;

--- create an index using wildcard_maxterms=100
begin 
    Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST'); 
    ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 100) ;
end; 
/
create index wildcard_idx on quick(text)
    indextype is ctxsys.context 
    parameters ('Wordlist wildcard_pref') ;

--- query on 'tire%' - should work fine
select quick_id from quick
  where contains ( text, 'tire%' ) > 0;

--- now re-create the index with wildcard_maxterms=5

drop index wildcard_idx ;

begin 
    Ctx_Ddl.Drop_Preference('wildcard_pref'); 
    Ctx_Ddl.Create_Preference('wildcard_pref', 'BASIC_WORDLIST'); 
    ctx_ddl.set_attribute('wildcard_pref', 'wildcard_maxterms', 5) ;
end; 
/

create index wildcard_idx on quick(text)
    indextype is ctxsys.context 
    parameters ('Wordlist wildcard_pref') ;

--- query on 'tire%' gives "wildcard query expansion resulted in too many terms"
select quick_id from quick
  where contains ( text, 'tire%' ) > 0;

2.6.1.1 Storage Default Behavior

By default, BASIC_STORAGE attributes are not set. In such cases, the Text index tables are created in the index owner's default tablespace. Consider the following statement, entered by user IUSER, with no BASIC_STORAGE attributes set:

create index IOWNER.idx on TOWNER.tab(b) indextype is ctxsys.context;

In this example, the text index is created in IOWNER's default tablespace.

2.6.1.2 Storage Examples

The following examples specify that the index tables are to be created in the foo tablespace with an initial extent of 1K:

begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)'); 
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)'); 
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
                        'tablespace users storage (initial 1K) lob
                         (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)'); 
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
                        'tablespace foo storage (initial 1K) compress 2');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)'); 
ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
end;

2.7.1.1 Creating Section Groups in HTML Documents

The following statement creates a section group called htmgroup with the HTML group type.

begin
ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP');
end;

You can optionally add sections to this group using the procedures in the CTX_DDL package, such as CTX_DDL.ADD_SPECIAL_SECTION or CTX_DDL.ADD_ZONE_SECTION. To index your documents, enter a statement such as:

create index myindex on docs(htmlfile) indextype is ctxsys.context 
parameters('filter ctxsys.null_filter section group htmgroup');

2.7.1.2 Creating Sections Groups in XML Documents

The following statement creates a section group called xmlgroup with the XML_SECTION_GROUP group type.

begin
ctx_ddl.create_section_group('xmlgroup', 'XML_SECTION_GROUP');
end;

You can optionally add sections to this group using the procedures in the CTX_DDL package, such as CTX_DDL.ADD_ATTR_SECTION or CTX_DDL.ADD_STOP_SECTION. To index your documents, enter a statement such as:

create index myindex on docs(htmlfile) indextype is ctxsys.context 
parameters('filter ctxsys.null_filter section group xmlgroup');

2.7.1.3 Automatic Sectioning in XML Documents

The following statement creates a section group called auto with the AUTO_SECTION_GROUP group type. This section group automatically creates sections from tags in XML documents.

begin

ctx_ddl.create_section_group('auto', 'AUTO_SECTION_GROUP');

end;

CREATE INDEX myindex on docs(htmlfile) INDEXTYPE IS ctxsys.context 
PARAMETERS('filter ctxsys.null_filter section group auto');

2.10.3.1 Dynamic Addition of Stopwords

You can add stopwords dynamically to a default or custom stoplist with ALTER INDEX. When you add a stopword dynamically, you need not re-index, because the word immediately becomes a stopword and is removed from the index.

2.11.1.1 CTXSYS.DEFAULT_DATASTORE

This preference uses the DIRECT_DATASTORE type. Use this preference to create indexes for text columns in which the text is stored directly in the column.

2.11.1.2 CTXSYS.FILE_DATASTORE

This preference uses the FILE_DATASTORE type.

2.11.1.3 CTXSYS.URL_DATASTORE

This preference uses the URL_DATASTORE type.

2.11.2.1 CTXSYS.NULL_FILTER

This preference uses the NULL_FILTER type.

2.11.2.2 CTXSYS.AUTO_FILTER

This preference uses the AUTO_FILTER type.

2.11.3.1 CTXSYS.DEFAULT_LEXER

The default lexer depends on the language used at install time. The following sections describe the default settings for CTXSYS.DEFAULT_LEXER for each language.

2.11.3.2 CTXSYS.BASIC_LEXER

This preference uses the BASIC_LEXER.

2.11.4.1 CTXSYS.NULL_SECTION_GROUP

This preference uses the NULL_SECTION_GROUP type.

2.11.4.2 CTXSYS.HTML_SECTION_GROUP

This preference uses the HTML_SECTION_GROUP type.

2.11.4.3 CTXSYS.AUTO_SECTION_GROUP

This preference uses the AUTO_SECTION_GROUP type.

2.11.4.4 CTXSYS.PATH_SECTION_GROUP

This preference uses the PATH_SECTION_GROUP type.

2.11.5.1 CTXSYS.DEFAULT_STOPLIST

This stoplist preference defaults to the stoplist of your database language.

2.11.5.2 CTXSYS.EMPTY_STOPLIST

This stoplist has no words.

2.11.6.1 CTXSYS.DEFAULT_STORAGE

This storage preference uses the BASIC_STORAGE type.

2.11.7.1 CTXSYS.DEFAULT_WORDLIST

This preference uses the language stemmer for your database language. If your language is not listed in Table 2-40, then this preference defaults to the NULL stemmer and the GENERIC fuzzy matching attribute.

2.12.2.1 CONTEXT Index Parameters

The following default parameters are used when you create a CONTEXT index and do not specify preferences in the parameter clause of CREATE INDEX. Each default parameter names a system-defined preference to use for data storage, filtering, lexing, and so on.

2.12.2.2 CTXCAT Index Parameters

The following default parameters are used when you create a CTXCAT index with CREATE INDEX and do not specify any parameters in the parameter string. The CTXCAT index supports only the index set, lexer, storage, stoplist, and wordlist parameters. Each default parameter names a system-defined preference.

2.12.2.3 CTXRULE Index Parameters

Table 2-50 lists the default parameters that are used when you create a CTXRULE index with CREATE INDEX and do not specify any parameters in the parameter string. The CTXRULE index supports only the lexer, storage, stoplist, and wordlist parameters. Each default parameter names a system-defined preference.

CTXRULE Index Limitations

The CTXRULE index does not support the following query operators:

• Fuzzy

• Soundex

It also does not support the following BASIC_WORDLIST attributes:

• SUBSTRING_INDEX

• PREFIX_INDEX

2.12.2.4 Viewing Default Values

View system defaults by querying the CTX_PARAMETERS view. For example, to see all parameters and values, enter the following statement:

SQL> SELECT par_name, par_value from ctx_parameters;

2.12.2.5 Changing Default Values

Change a default value using the CTX_ADM.SET_PARAMETER procedure to name another custom or system-defined preference to use as default.