Re: Searching CLOBS case insensitive

Basically, you create an index with indextype ctxsys.context specifying necessary parameters to it, something like this:

create index ctx_mytable on mytable (clob_col)  INDEXTYPE IS CTXSYS.CONTEXT [PARAMETERS '...'] /

and when it's online (may take quite some time if created on populated table since DR will need to filter each CLOB and break it down into tokens, record these tokens in its own tables and do some other things, like theme extraction, if requested in index parameters), you query the table like this:

select whatever from mytable
 where CONTAINS(CLOB_COL,'my search term',1) > 0

(the third parameter, which is 1 here but may be any number) allows you to identify this particular operator for result scoring:

select whatever from mytable
 where ( CONTAINS(CLOB_COL,'my search term',1) > 0

            and SCORE(1) > 35 )
       or (CONTAINS(CLOB_COL,'ABOUT(some theme)',2) > 0
            and SCORE(2) > 50 )

To find out more, please refer to interMedia Text (8i)/Oracle Text (9i) documentation.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Petri" <Petri33_at_hotmail.com> wrote in message
news:b4803f2d.0212190612.26153ce4_at_posting.google.com...


> Pablo Sanchez <pablo_at_dev.null> wrote in message

news:<Xns92E892FD6AB37pingottpingottbah_at_209.242.64.107>...


> > Petri33_at_hotmail.com (Petri) wrote in

> > news:b4803f2d.0212181312.2dcc4fde_at_posting.google.com:

> >

> > > I am trying to search a CLOB field case insensitively.  I am

> > > currently using dbms_lob.instr() but this function is case

> > > sensitive.  Does anyone know how to search case insensitively?

> >

> > Unless you plan on having very few CLOB searches, you might want to

> > instead use the Intermedia/Context cartridge to do this activity.

>


> How would I use the Intermedia/Context cartridge?  Do you know where I

> could find an example?