Home » Server Options » Text & interMedia » Displaying search results from table and CTX_DOC.SNIPPET - Returning error messages (merged by bb)
Displaying search results from table and CTX_DOC.SNIPPET - Returning error messages (merged by bb) [message #330560] Mon, 30 June 2008 07:56 Go to next message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
Hi,
This may seem like a fairly simple one, but I'm having no end of problems with it:

I am trying to display the title and snippet of data stored in the related document for each document that is returned from my search. I can easily return a single title & snippet, but if the search returns more than 1 result (in this case: searching for "concerns", then i receive the following error:
"-1422: ORA-01422: exact fetch returns more than requested number of rows"

Here is my source code so far:

drop table news;
create table news(
news_id number,
name varchar2(80),
document clob
);

--------------------------------------------

set define off

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. Concerns have been raised...');

insert into News(name, document)
values('Pill on the web scheme concerns', 'Experts have raised concerns about a UK-based online medical service which offers the contraceptive pill without the need to see a doctor...');

alter table news add (
constraint news_pk primary key (news_id)
);
set define on

------------------------------------------

commit;

--------------------------------------------

create index news_index
on news (document)
indextype is ctxsys.context;

----------------------------------------------

create or replace package wd.jason
is
procedure proc1
(p_string in varchar2 default null);
procedure proc1_db
(p_string in varchar2 default null);
end jason;
/
show errors

---------------------------------------------

set serveroutput on size 999999

create or replace package body wd.jason
is

procedure proc1
(p_string in varchar2 default null)
is

i number;
op varchar2(200);

begin

htp.p('Search within document');
htp.hr;
htp.formopen('jason.proc1','get');
htp.formtext('p_string',cvalue=>p_string);
htp.formsubmit;
htp.formclose;

begin

htp.hr;
i := 0;
--for newsrec in (select name, news_id from news
for newsrec in (select * from news
where contains (document, p_string) > 0)

loop

i := i + 1;
htp.bold(newsrec.name);
htp.br;

select substr(document, 1, instr(document, ' ', 1, 20)-1) into op
from news
where contains (document, p_string) > 0;

htp.p(op || ' ...');

end loop;

htp.hr;

htp.br;

htp.p(i || ' result(s) returned from search');

end;

exception
when others then
htp.p(sqlcode || ': ' || sqlerrm(sqlcode));

end proc1;
end jason;
/
show errors


Any help would be greatly appreciated!
Re: Displaying search results from table [message #330561 is a reply to message #330560] Mon, 30 June 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01422: exact fetch returns more than requested number of rows
 *Cause: The number specified in exact fetch is less than the rows returned.
 *Action: Rewrite the query or change number of rows requested


Check if
select substr(document, 1, instr(document, ' ', 1, 20)-1) into op
from news
where contains (document, p_string) > 0;
returns more than one row.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Mon, 30 June 2008 07:59]

Report message to a moderator

Re: Displaying search results from table [message #330564 is a reply to message #330560] Mon, 30 June 2008 08:11 Go to previous messageGo to next message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
Hi,
Sorry about the lack of formatting

When the "p_string" value is a word that appears in more than one "document",

select substr(document, 1, instr(document, ' ', 1, 20)-1) into op
from news
where contains (document, p_string) > 0;

will return more than one row, although I think this is where the problem lies, as if i comment it out (so the procedure only returns a name) it seems to work fine and return all results.
When using "select substr....." it will only return the first result
Re: Displaying search results from table [message #330565 is a reply to message #330564] Mon, 30 June 2008 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When using "select substr....." it will only return the first result

substr does not restrict the result set just the result value.
If you just want to check if there is one value or only want the first value add "and rownum=1".
If you want to display all values loop on the cursor.
If you know that all values are the same ones, use DISTINCT to only get one row.

Maybe you should add an exception block to handle the case, if you know how to handle it.

Regards
Michel

[Updated on: Mon, 30 June 2008 08:21]

Report message to a moderator

Re: Displaying search results from table [message #330566 is a reply to message #330565] Mon, 30 June 2008 08:24 Go to previous messageGo to next message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
Adding "and rownum = 1" worked perfectly. Thanks!
Re: Displaying search results from table [message #330567 is a reply to message #330566] Mon, 30 June 2008 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care that adding this may hide useful results or some inconsistencies in your data: you artificially enforce one row.

Regards
Michel

[Edit: fix typo]

[Updated on: Mon, 30 June 2008 08:38]

Report message to a moderator

Re: Displaying search results from table [message #330570 is a reply to message #330560] Mon, 30 June 2008 08:34 Go to previous messageGo to next message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
Ah yes, I see what you mean. It is bringing up the same snippet of information where it should be bringing up different snippets related to each unique search result
CTX_DOC.SNIPPET - Returning error messages [message #330579 is a reply to message #330560] Mon, 30 June 2008 09:09 Go to previous messageGo to next message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
Hi,
I am fairly new at this. I want to return a "snippet" of relevant information from a document (or number of documents) that are returned from a search. So far I have only managed to return the name of the document, followed by these error messages:

-20000: ORA-20000: Oracle Text error: DRG-11445: rowid value is invalid: 2 ORA-06512: at "CTXSYS.DRUE", line 160 ORA-06512: at "CTXSYS.CTX_DOC", line 1297 ORA-06512: at line 1


Here is my code so far:

DROP TABLE  News;
CREATE TABLE News (
  News_Id  NUMBER,
  NAME     VARCHAR2(80),
  Document CLOB);
-----------------------------
set define off

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. Concerns have been raised...');

INSERT INTO News
           (NAME,
            Document)
VALUES     ('Pill on the web scheme concerns',
            'Experts have raised concerns about a UK-based online medical service which offers the...');

ALTER TABLE News ADD(
                 CONSTRAINT News_pk PRIMARY KEY( News_Id ));
set define on

------------------------------

commit;

-------------------------------

UPDATE News SET news_id = rownum;

-------------------------------

CREATE INDEX news_index
on News (Document)
indextype is ctxsys.context;

-------------------------------

CREATE OR REPLACE PACKAGE wd.jason
IS
  PROCEDURE Proc1(p_String  IN VARCHAR2 DEFAULT NULL);
END jason;
/
SHOW ERRORS

-------------------------------

set serveroutput on size 999999

CREATE OR REPLACE PACKAGE BODY wd.jason
IS
  PROCEDURE Proc1
       (p_String  IN VARCHAR2 DEFAULT NULL)
  IS
    i   NUMBER;
    op  VARCHAR2(200);
  BEGIN
    htp.hr;
    
    htp.FormOpen('jason.proc1','get');
    
    htp.FormText('p_string',cValue => p_String);
    
    htp.FormSubmit;
    
    htp.FormClose;
    
    BEGIN
      htp.hr;
      
      i := 0;
      
      FOR Newsrec IN (SELECT *
                      FROM   News
                      WHERE  Contains(Document,p_String) > 0)
      LOOP
        i := i + 1;
        
        htp.Bold(Newsrec.NAME);
        
        htp.br;
        
        ctx_Doc.Set_Key_Type('PRIMARY_KEY');
        
        SELECT ctx_Doc.Snippet('news_index',To_char(Newsrec.News_Id),p_String)
        INTO   mkLob
        FROM   News;
      END LOOP;
      
      icp.br;
      
      htp.p(i
            ||' result(s) returned from search');
    END;
  EXCEPTION
    WHEN OTHERS THEN
      htp.p(SQLCODE
            ||': '
            ||SQLERRM(SQLCODE));
  END Proc1;
END jason;
/
show errors



Any help will be greatly appreciated



[mod-edity by bb: added code tags; next time please add them yourself-see the forum guidelines]

[Updated on: Mon, 30 June 2008 10:53] by Moderator

Report message to a moderator

Re: CTX_DOC.SNIPPET - Returning error messages [message #330632 is a reply to message #330579] Mon, 30 June 2008 11:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have a few things out of sequence, such as trying to add the primary key while the news_id is still null. Also, you are trying to select into a variable, mklob, that has not been declared. You need to add a where condition to your select statement in order to make sure you get only one row. Also, you should take your ctx_doc.set_key_type out of the loop, since it only needs to be executed once, and put it before the loop. Please see the demonstration below.

SCOTT@orcl_11g> CREATE TABLE News (
  2    News_Id	NUMBER,
  3    NAME	VARCHAR2(80),
  4    Document CLOB)
  5  /

Table created.

SCOTT@orcl_11g> set define off
SCOTT@orcl_11g> INSERT INTO News
  2  		(NAME,
  3  		 Document)
  4  VALUES	('Councils warned over spying laws',
  5  		 'Councils in England have been urged to review the way they use surveillance powers to investigate suspected crime. Concerns have been raised...')
  6  /

1 row created.

SCOTT@orcl_11g> INSERT INTO News
  2  		(NAME,
  3  		 Document)
  4  VALUES	('Pill on the web scheme concerns',
  5  		 'Experts have raised concerns about a UK-based online medical service which offers the...')
  6  /

1 row created.

SCOTT@orcl_11g> UPDATE News SET news_id = rownum
  2  /

2 rows updated.

SCOTT@orcl_11g> ALTER TABLE News ADD(
  2  		      CONSTRAINT News_pk PRIMARY KEY( News_Id ))
  3  /

Table altered.

SCOTT@orcl_11g> CREATE INDEX news_index
  2  on News (Document)
  3  indextype is ctxsys.context
  4  /

Index created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE jason
  2  IS
  3    PROCEDURE Proc1(p_String  IN VARCHAR2 DEFAULT NULL);
  4  END jason;
  5  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY jason
  2  IS
  3    PROCEDURE Proc1(p_String  IN VARCHAR2 DEFAULT NULL)
  4    IS
  5  	 i   NUMBER;
  6  	 op  VARCHAR2(200);
  7    BEGIN
  8  	 BEGIN
  9  	   ctx_Doc.Set_Key_Type('PRIMARY_KEY');
 10  	   i := 0;
 11  	   FOR Newsrec IN (SELECT *
 12  			   FROM   News
 13  			   WHERE  Contains(Document,p_String) > 0)
 14  	   LOOP
 15  	     i := i + 1;
 16  	     SELECT ctx_Doc.Snippet('news_index',To_char(Newsrec.News_Id),p_String, '[B]', '[/B]')
 17  	     INTO   op
 18  	     FROM   News
 19  	     WHERE  news.news_id = newsrec.news_id;
 20  	     DBMS_OUTPUT.PUT_LINE (op);
 21  	   END LOOP;
 22  	   DBMS_OUTPUT.PUT_LINE (i || ' result(s) returned from search');
 23  	 END;
 24    END Proc1;
 25  END jason;
 26  /

Package body created.

SCOTT@orcl_11g> show errors
No errors.


SCOTT@orcl_11g> set serveroutput on size 999999
SCOTT@orcl_11g> EXEC jason.proc1 ('concerns')
powers to investigate suspected crime. Concerns have been raised...
Experts have raised concerns about a UK-based online medical service which
2 result(s) returned from search

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>
Re: CTX_DOC.SNIPPET - Returning error messages [message #330785 is a reply to message #330632] Tue, 01 July 2008 04:35 Go to previous message
dugjason
Messages: 13
Registered: June 2008
Location: UK
Junior Member
That is fantastic thank you.
I realise now that I had made some silly mistakes using the primary key, but I think most of my problems were coming from the SNIPPET function, so adding the WHERE clause certainly helped that. Thanks again,
Jason
Previous Topic: Synchronizing
Next Topic: speed up 'like' queries - Oracle Text API?
Goto Forum:
  


Current Time: Thu Mar 28 15:41:21 CDT 2024