Re: Problems with Oracle Text (in Oracle 10g)
Date: Fri, 27 Jun 2008 08:27:50 -0700 (PDT)
On Jun 27, 8:31 am, jasondugd..._at_hotmail.co.uk wrote:
> 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
> procedure proc1
> (p_string in varchar2 default null)
> i number;
> op varchar2(200);
> pos number;
> mklob clob;
> v_amt number := 30000;
> v_doc long;
> icp.p('Search within document');
> i := 0;
> for newsrec in (select name, news_id from news
> where contains (document, p_string) > 0)
> i := i + 1;
> pos := 1;
> select substr(document, 1, instr(document, ' ', pos, 20)-1) into
> from news
> where contains (document, p_string) > 0;
> icp.p(op || ' ...');
> select count(news_id) into i from news;
> icp.p(i || ' result(s) returned from search');
> when others then
> icp.p(sqlcode || ': ' || sqlerrm(sqlcode));
> end proc1;
> Any help would be greatly appreciated,
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