Re: Problems with Oracle Text (in Oracle 10g)

From: BicycleRepairman <engel.kevin_at_gmail.com>
Date: Fri, 27 Jun 2008 08:27:50 -0700 (PDT)
Message-ID: <8c3ade72-5189-4fff-9d7a-920bdc314334@l64g2000hse.googlegroups.com>


On Jun 27, 8:31 am, jasondugd..._at_hotmail.co.uk wrote:
> Hi,
> I'm working on a project using Oracle Text, and wondering if it is
> possible display "relevant" parts of any documents returned by a
> search (in a similar fashion to Google).
>
> Heres a summary of what I have so far:
>
> CREATE TABLE news(
> news_id NUMBER, --Primary key: starts from "1"
> name VARCHAR2(80), --Name of document
> document CLOB --Document itself
> );
>
> INSERT INTO News(name, document)
> VALUES('Councils warned over spying laws', 'Councils in England have
> been urged to review the way they use surveillance powers to
> investigate suspected crime. Under laws brought in to help fight
> terrorism, councils can...');
>
> If searching for "terrorism" for example, I can happily return the
> name of the document, and using the SUBSTR and INSTR operators, can
> return a substring from the document (either a substring starting at
> the start of the document, or ending at the end of the document).
> I would like to be able to return the part of the document relevant to
> the "terrorism" search criteria (Under laws......help fight terrorism)
> and was wondering if this was possible, as I could not find anything
> in the Oracle 10g documentation about how to do it.
>
> I have set up a text index to index all the entries in the "News"
> table, which seems to work fine:
>
> create index news_index
> on news (document)
> indextype is ctxsys.context;
>
> I have also written a package containing the following procedure used
> for entering the search keyword (p_string), and displaying the search
> results:
>
> procedure proc1
> (p_string in varchar2 default null)
> is
>
> i number;
> op varchar2(200);
> pos number;
>
> mklob clob;
> v_amt number := 30000;
> v_doc long;
>
> begin
>
> icp.p('Search within document');
>
> icp.hr;
> icp.formopen('jason.proc1','get');
> icp.formtext('p_string',cvalue=>p_string);
> icp.formsubmit;
> icp.formclose;
>
> begin
>
> icp.hr;
> i := 0;
> for newsrec in (select name, news_id from news
> where contains (document, p_string) > 0)
>
> loop
>
> i := i + 1;
> icp.bold(newsrec.name);
> icp.br;
> pos := 1;
>
> select substr(document, 1, instr(document, ' ', pos, 20)-1) into
> op
> from news
> where contains (document, p_string) > 0;
>
> icp.p(op || ' ...');
>
> icp.br;
>
> select count(news_id) into i from news;
>
> icp.p(i || ' result(s) returned from search');
>
> end;
>
> exception
> when others then
> icp.p(sqlcode || ': ' || sqlerrm(sqlcode));
>
> end proc1;
>
> Any help would be greatly appreciated,
> Thanks,
> dugjason

Have you looked at ctx_doc.snippet()? That seems much more like what you are asking for than ctx_doc.highlight(). The params are quite similar, so you may have problems like you're having with Highlight below.
Keep it simple to start with -- don't call SET_KEY_TYPE; pass in only the params that are required (index name, textkey, and query) and see what happens.
In my case, I have a query like:
select icon, title, ctx_doc.snippet('doctext', :docid, :searchterms) from mydocs where docid = :docid; Received on Fri Jun 27 2008 - 10:27:50 CDT

Original text of this message