Introduction to Oracle Text , 3 of 8

Loading Your Text Table

Figure 1-1

Text description of the illustration ccapp002.gif

The basic prerequisite for an Oracle Text query application is to have a populated text table. The text table is where you store information about your document collection and is required for indexing.

You can populate rows in your text table with one of the following elements:

text information (can be documents or text fragments)
path names of documents in your file system
URLs that specify world-wide web documents

Figure 1-1 illustrates these different methods.

By default, the indexing operation expects your document text to be directly loaded in your text table, which is the first method above.

However, you can specify the other ways of identifying your documents such as with filenames or with URLs using the corresponding data storage indexing preference.

Storing Text in the Text Table

You can store documents in your text table in different ways.

You can store documents in one column using the DIRECT_DATASTORE data storage type or over a number of columns using the MULTI_COLUMN_DATASTORE type. When your text is stored over a number of columns, Oracle concatenates the columns into a virtual document for indexing.

You can also create master-detail relationships for your documents, where one document can be stored across a number of rows. To create master-detail index, use the DETAIL_DATASTORE data storage type.

In your text table, you can also store short text fragments such as names, descriptions, and addresses over a number of columns to create a catalog index.

You can also store your text in a nested table using the NESTED_DATASTORE type.

Oracle Text supports the indexing of the XMLType which you can use to store XML documents.

Storing File Path Names

In your text table, you can store path names to files stored in your file system. When you do so, use the FILE_DATASTORE preference type during indexing.

Storing URLs

You can store URL names to index web-sites. When you do so, use the URL_DATASTORE preference type during indexing.

Storing Associated Document Information

In your text table, you can create additional columns to store structured information that your query application might need, such as primary key, date, description, or author.

Format and Character Set Columns

If your documents are of mixed formats or of mixed character sets, you can create the following additional columns:

Format column to record format (TEXT or BINARY) to help filtering during indexing. You can also use to format column to ignore rows for indexing by setting the format column to IGNORE. This is useful for bypassing rows that contain data incompatible with text indexing such as images.
Character set column to record document character set on a per row basis.

When you create your index, you must specify the name of the format or character set column in the parameter clause of CREATE INDEX.

Supported Column Types

With Oracle Text, you can create a CONTEXT index with columns of type VARCHAR2, CLOB, BLOB, CHAR, BFILE, and XMLType.

Note:

The column types NCLOB, DATE and NUMBER cannot be indexed.

Supported Document Formats

Because the system can index most document formats including HTML, PDF, Microsoft Word, and plain text, you can load any supported type into the text column.

When you have mixed formats in your text column, you can optionally include a format column to help filtering during indexing. With the format column you can specify whether a document is binary (formatted) or text (non-formatted such as HTML).

See Also:

Oracle Text Reference for more information about the supported document formats.

Loading Methods

The following sections describe different methods of loading information into a text column.

INSERT Statement

You can use the SQL INSERT statement to load text to a table.

The following example creates a table with two columns, id and text, using CREATE TABLE. The example populates the table with the INSERT statement. This example makes the id column the primary key, which is the required constraint for a Text table. The text column is VARCHAR2:

CREATE TABLE docs (id number primary key, text varchar2(80));

To populate this table, use the INSERT statement as follows:

INSERT into docs values(1, 'this is the text of the first document');
INSERT into docs values(12, 'this is the text of the second document');

Loading Text from File-System

In addition to the INSERT statement, Oracle enables you to load text data (this includes documents, pointers to documents, and URLs) into a table from your file-system using other automated methods, including