Dear All,

I am trying to learn how to use “Oracle Text” for Context indexing on word & text documents stores as a blob. I got the following code from one the web sites. It worked on my home computer (Oracle 9.0) without any problem. But I did not get the expected result on my office computer (Oracle 9.2). On my office computer I can create the Context index without any errors, but when I execute a select statement with Contains clause, I do not get any rows returned.

Please give me some advice.

CREATE TABLE my_docs (
id NUMBER(10) NOT NULL,
name VARCHAR2(200) NOT NULL,
doc BLOB NOT NULL
)
/

ALTER TABLE my_docs ADD (
CONSTRAINT my_docs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE my_docs_seq;

CREATE OR REPLACE DIRECTORY documents AS 'C:\work\';
Next we load several files as follows:
CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN my_docs.name%TYPE) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO my_docs (id, name, doc)
VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
RETURN doc INTO v_blob;

v_bfile := BFILENAME('DOCUMENTS', p_file_name);
Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
Dbms_Lob.Fileclose(v_bfile);

COMMIT;
END;
/

EXEC load_file_to_my_docs('FullTextIndexingUsingOracleText9i.doc');
EXEC load_file_to_my_docs('FullTextIndexingUsingOracleText9i.asp');
EXEC load_file_to_my_docs('XMLOverHTTP9i.asp');
EXEC load_file_to_my_docs('UNIXForDBAs.asp');
EXEC load_file_to_my_docs('emp_ws_access.sql');
EXEC load_file_to_my_docs('emp_ws_test.html');
EXEC load_file_to_my_docs('9ivsSS2000forPerformanceV22.pdf');
Next we create a CONTEXT type index on the doc column and gather table statistics:
CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;

EXEC DBMS_STATS.GATHER_TABLE_STATS('DEV', 'MY_DOCS', cascade=>TRUE);
Finally we query table looking for documents with specific content:
SELECT SCORE(1) score, id, name
FROM my_docs
WHERE CONTAINS(doc, 'SQL Server', 1) > 0
ORDER BY SCORE(1) DESC;

SCORE ID NAME
---------- ---------- ------------------------------------------------
100 127 9ivsSS2000forPerformanceV22.pdf