Problems with Oracle Text (in Oracle 10g)
- From: jasondugdale@xxxxxxxxxxxxx
- Date: Fri, 27 Jun 2008 05:31:34 -0700 (PDT)
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
.
- Follow-Ups:
- Re: Problems with Oracle Text (in Oracle 10g)
- From: BicycleRepairman
- Re: Problems with Oracle Text (in Oracle 10g)
- From: Dan Blum
- Re: Problems with Oracle Text (in Oracle 10g)
- Prev by Date: Re: ORA-27086: skgfglk: unable to lock file - already in use -> Instance "BOCHUM", status BLOCKED
- Next by Date: Re: ORA-27086: skgfglk: unable to lock file - already in use -> Instance "BOCHUM", status BLOCKED
- Previous by thread: ORA-27086: skgfglk: unable to lock file - already in use -> Instance "BOCHUM", status BLOCKED
- Next by thread: Re: Problems with Oracle Text (in Oracle 10g)
- Index(es):
Relevant Pages
|
