Testing Intermedia

Even if you follow the instructions in the installation notes, content searches may inexplicably fail to work. This document describes how to create a simple test case independent of the content repository to verify that Intermedia is indeed functioning properly.

Create a document table

Create a simple table to hold some test documents:

create table cr_test_documents ( 
  doc_id    integer primary key, 
  author    varchar2(30), 
  format    varchar2(30), 
  title     varchar2(256), 
  doc       blob 
);

Create an Intermedia preference to specify INSO filtering:

begin
  ctx_ddl.create_preference
  (
    preference_name => 'CONTENT_FILTER_PREF',
    object_name     => 'INSO_FILTER'
  );

If this preference has already been created, this step will cause an error that you can ignore.

Create an Intermedia index on the test table with INSO filtering:

create index cr_test_documents_idx on cr_test_documents ( doc )
  indextype is ctxsys.context
  parameters ('FILTER content_filter_pref' );

Load test documents

You can use SQL*Loader to load some documents into the test table. First create a control file named cr-test-docs.ctl:

load data
INFILE 'cr-test-docs.data'
INTO TABLE cr_test_documents
APPEND
FIELDS TERMINATED BY ','
(doc_id SEQUENCE (MAX,1),
 format,
 title,
 ext_fname FILLER CHAR(80),
 doc LOBFILE(ext_fname) TERMINATED BY EOF)

Copy any number of documents (Microsoft Word, PDF, text, HTML, etc.) to the filesystem of your database server. Create a data file with an entry for each document you would like to load. This is simply a comma-separated text file:

word, Simple Story,sample-docs/simple.doc,
excel, Simple Spreadsheet,sample-docs/simple.xls

Load the documents from the command line:

$ sqlldr userid=cms/cms control=cr-test-docs.ctl log=cr-test-docs.log

SQL*Loader: Release 8.1.6.2.0 - Production on Thu Nov 9 13:36:56 2000

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 2

Test search

Once the documents have been loaded, rebuild the index and run some test queries:

SQL> alter index cr_test_documents_index rebuild online parameters ('sync');
SQL> select score(1), doc_id from cr_test_documents 
       where contains(doc, 'cars', 1) > 0;

  SCORE(1)     DOC_ID
---------- ----------
	 4	    1