Problems with Oracle Text (in Oracle 10g)

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 Received on Fri Jun 27 2008 - 07:31:34 CDT