Re: how to: oracle full text search over multiple columns

From: Vadim Grepan <_RMV_IT_kezal_at_mail.ru>
Date: Fri, 26 Dec 2003 10:16:50 +0300
Message-ID: <bsgn7v$o9p$1@n6.co.ru>

Hello!

It's complicated task in fact. You should refer the Oracle's doc

1. Create iMedia preferences
2. Creating procedure to concatenate all indexing fields in one (C)LOB area
3. Creating ctxsys procedure
4. Setting preference's attribute

--# 1. Create iMedia preferences ##############################

ctx_ddl.create_preference('IMI_DATASTORE_AUTHOR_FULLNAME', 'USER_DATASTORE');

--# 2. Creating procedure ##############################

CREATE OR REPLACE PROCEDURE indexing_author_fullname (rid in rowid, tlob in out clob) IS

        offset number := 1;
BEGIN

	FOR c1 IN (SELECT NVL(author_fname, ' ') author_fname,
       		NVL(author_mname, ' ') author_mname,
       		NVL(author_lname, ' ') author_lname
        	FROM tbl_author
         WHERE rowid = rid)
       LOOP
	dbms_lob.write (tlob, length(c1.author_fname)+1, 1, c1.author_fname||' ');
	dbms_lob.writeappend (tlob, length(c1.author_mname)+1, 
c1.author_mname||' ');
	dbms_lob.writeappend (tlob, length(c1.author_lname), c1.author_lname);
       END LOOP;
     END;

--# 3. Creating procedure for ctxsys shema ##############################

connect ctxsys/xxxxxx
CREATE OR REPLACE PROCEDURE ctx_indexing_author_fullname (rid in rowid, lob in out clob) IS
BEGIN
           myproj.indexing_author_fullname(rid, tlob); END;
GRANT EXECUTE ON ctx_indexing_author_fullname TO myproj;

--# 4. Setting attribute ##############################

ctx_ddl.set_attribute('IMI_DATASTORE_AUTHOR_FULLNAME', 'procedure', 'ctx_indexing_author_fullname');

Rgds, Vadim Grepan

--
Moscow, Russia


joes wrote:



 > Hello there

 >

 > I am looking for a way to  realize a full text search on a whole table

 > in oracle 8.1.7 . I found "catsearch()" and "contains()" features. But

 > it seems that these only supports the lookup for a single column. So

 > exists there way to realize a full text search over multiple columns ?

 > How ?

 >

 > second Question I have is about, does Oracle supports an automatic

 > translation of special characters suring the search ? for instance

 > german "umlaute" so that the a umlaut (ä) is automatic trnsalted

 > into 'ae' or 'a'. Or that the french characters are translated into

 > 'e' ?

 >

 > thanks xou for any answers on this

 > regards

 > Mark

Received on Fri Dec 26 2003 - 01:16:50 CST