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

Vadim Grepan wrote:
>
> 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

A similar theme (no pun intended) is to "concatenate" the fields into a XML structure and index that. Either way, I'm currently facing the same challenges (the user wants a "find string X anywhere in the database" functionality) - we started with the procedural option Vadim mentioned - we've since moved to a model where we store the clob in its own right as opposed to "within" the index

hth
connor Received on Mon Dec 29 2003 - 04:41:11 CST