Creating an interMedia Text Index

The following example illustrates how to create an interMedia Text index. The example is presented in tutorial fashion. Creating an interMedia Text index involves the following steps:

• 1 Determine the Role you Need and GRANT ctxapp Privilege

• 2 Set up Data, if Not Already Available

• 3 Creating an interMedia Text Index in Order to use CONTAINS

• 4 Creating a Preference: You Need to Express the Parameterization with a "Preference"

• 5 Parameterizing the Preference

1 Determine the Role you Need and GRANT ctxapp Privilege

Determine the role you need. See "Listing the Required Roles for Each CTX Package" and grant the appropriate privilege.

CONNECT system/manager
GRANT ctxapp to scott;
CONNECT scott/tiger

2 Set up Data, if Not Already Available

The following example creates a table, my_table, and inserts data into the table.

-- Set up some data

CREATE TABLE my_table (id number(5) primary key, my_column clob );

INSERT INTO my_table VALUES ( 1,
  '<author>Fred Smith</author>' || 
  '<document>I had a nice weekend in the mountains.</document>' );
INSERT INTO my_table VALUES ( 2,
  '<author>Jack Jones</author>' || 
  '<document>My month at the coast was relaxing.</document>' );
INSERT INTO my_table VALUES ( 3,
  '<publisher>Dog House</publisher>' || 
  '<document>His year in Provence was fulfilling.</document>' ||
  '<junk>banana</junk>' );
COMMIT;

3 Creating an interMedia Text Index in Order to use CONTAINS

The following example shows that you need a Text index in order to use CONTAINS.

3.1 You Need an interMedia Text Index in Order to Use CONTAINS

This example shows that you need an interMedia Text index in order to use "CONTAINS":

SELECT my_column FROM my_table
  WHERE CONTAINS
    ( my_column, 'smith WITHIN author'
    ) > 0;

The following error message is obtained:

DRG-10599: column is not indexed

3.2 Creating a Default Parameterized interMedia Text Index

This example shows that a default parameterized interMedia Text index does not support XML functionality:

CREATE INDEX my_index ON my_table ( my_column )
  INDEXTYPE IS Ctxsys.Context /* implies defaults */;

SELECT my_column FROM my_table
  WHERE CONTAINS
    ( my_column, 'smith WITHIN author'
    ) > 0;

The following error message is obtained:

DRG-10837: section author does not exist

See "5 Parameterizing the Preference".

4 Creating a Preference: You Need to Express the Parameterization with a "Preference"

Consider a preference, "my_section_group". It must be created before we can refer to it! This example shows that you need to express parameterization with a preference:

DROP INDEX my_index;

CREATE INDEX my_index ON my_table ( my_column )
  INDEXTYPE IS Ctxsys.Context
  PARAMETERS ( 'SECTION GROUP my_section_group' );

The following error message is obtained:

DRG-12203: section group my_section_group does not exist

5 Parameterizing the Preference

You need to do more than just CREATE 'my_section_group', you need to parameterize it too. Use CTX_DDL.Create_Section_Group

5.1 Parametrizing the Preference, my_section_group

This example shows the need to parameterize the my_section_group preference:

DROP INDEX my_index;

BEGIN
  Ctx_Ddl.Create_Section_Group
    ( group_name => 'my_section_group',
       group_type => 'xml_section_group'
    );
end;
/
CREATE INDEX my_index ON my_table ( my_column )
  INDEXTYPE IS Ctxsys.Context
  PARAMETERS ( 'SECTION GROUP my_section_group' );

SELECT my_column FROM my_table
  WHERE CONTAINS
    (my_column, 'smith WITHIN author'
    ) > 0;

The following error message is obtained:

DRG-10837: section author does not exist

5.2 Parameterize the Preference, 'my_section_group', Correctly Using CTX_DDL.Create_Section_Group and CTX_DDL.Add_Field_Section

This example shows that after creating the interMedia Text index and the preference, my_section_group, you need to parameterize the preference correctly.

• First use the CTX_DDL.create_section_group to create the section group.

• Then use CTX_DDL.Add_Field_Section for every tag used after WITHIN in your query statement.

  See Also: Using Table CTX_OBJECTS and CTX_OBJECT_ATTRIBUTES View for a brief description of the CTX_DDL package, and Oracle8i interMedia Text Reference for detailed notes on CTX_DDL.:

interMedia Text Example 1: Creating an Index -- Creating a Preference and Correctly Parameterizing it

DROP INDEX my_index;
BEGIN
Ctx_Ddl.Drop_Section_Group
  ( group_name => 'my_section_group'
  );
END;
/

BEGIN
Ctx_Ddl.Create_Section_Group /* We're dealing here with XML, not say HTML */
  (  group_name => 'my_section_group',
     group_type => 'xml_section_group'
      );

Ctx_Ddl.Add_Field_Section /* THIS IS KEY */
  (  group_name   =>'my_section_group',
     section_name =>'author',/* do this for EVERY tag used after "WITHIN" */
     tag          =>'author'
  );

 Ctx_Ddl.Add_Field_Section /* THIS IS KEY */
      (  group_name   =>'my_section_group',
         section_name =>'document',/*do this for EVERY tag after "WITHIN" */
         tag          =>'document'
      );

  ...
END;
/

CREATE INDEX my_index ON my_table ( my_column )
  INDEXTYPE IS Ctxsys.Context
  PARAMETERS ( 'SECTION GROUP my_section_group' );

SELECT my_column FROM my_table
  WHERE CONTAINS
    ( my_column, 'smith WITHIN author'
    ) > 0;

The last example is correct.

interMedia Text Example 2: Creating a Section Group with AUTO_SECTION_GROUP

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

BEGIN
ctx_ddl.create_section_group('autogroup', 'AUTO_SECTION_GROUP');
END;

To index your documents you can use the following statement:

CREATE INDEX myindex ON docs(htmlfile) INDEXTYPE IS ctxsys.context 
   parameters('filter ctxsys.null_filter section group autogroup');

Note: You can add attribute sections only to XML section groups. When you use AUTO_SECTION_GROUP, attribute sections are created automatically. Attribute sections created automatically are named in the form tag@attribute.

interMedia Text Example 3: Creating a Section Group with XML_SECTION_GROUP

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

BEGINE
ctx_ddl.create_section_group('xmlgroup', 'XML_SECTION_GROUP');
END;

You can add sections to this group using CTX_DDL.ADD_SECTION.

To index your documents, you can use the following statement:

CREATE INDEX myindex ON docs(htmlfile) INDEXTYPE IS ctxsys.context 
     parameters('filter ctxsys.null_filter section group xmlgroup');

Further Examples for Creating Section Group Indexes?

Further examples for creating section group indexes are at the following site:

http:// technet.oracle.com

Then select the following:

• Internet Servers. This is under Products on the side navigation bar.

• interMedia. This is under Oracle8i towards the top of the page.

• Training. This is on the top navigation bar.

• Oracle8i interMedia Text 8.1.5/8.1.6 - Training or similar.