Problems with Oracle Text (in Oracle 10g)
Date: Fri, 27 Jun 2008 05:31:34 -0700 (PDT)
Message-ID: <b92608d9-e780-49b6-81dc-3b08b86259fa@m44g2000hsc.googlegroups.com>
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