Home » Server Options » Text & interMedia » CTXSYS.MARKUP on package problem
CTXSYS.MARKUP on package problem [message #148228] Wed, 23 November 2005 03:37 Go to next message
herry.roby
Messages: 10
Registered: September 2005
Junior Member
Dear all..

I try to create stored procedure that can highlight document stored at ORDDOC

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

create table markuptab (query_id number,
document ordsys.orddoc);

CREATE OR REPLACE PROCEDURE HIGHLIGHT ( textkey IN VARCHAR2, textquery IN VARCHAR2 )
AS
localObject ORDSYS.ORDDOC;
localBlob BLOB;
localBfile BFILE;
httpStatus NUMBER;
lastModDate VARCHAR2(256);
BEGIN
BEGIN
ctx_doc.markup(index_name => 'idx_tab',
textkey => textkey,
text_query => textquery,
restab => 'markuptab',
query_id => 1,
tagset => 'HTML_DEFAULT');
END;

BEGIN
SELECT mt.DOCUMENT INTO localObject FROM markuptab mt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ordplsgwyutil.resource_not_found( 'textkey', textkey );
RETURN;
END;

IF localObject.isLocal() THEN
localBlob := localObject.getContent();
owa_util.mime_header( localObject.getMimeType(), FALSE );
ordplsgwyutil.set_last_modified( localObject.getUpdateTime() );
owa_util.http_header_close();
IF owa_util.get_cgi_env( 'REQUEST_METHOD' ) <> 'HEAD' THEN
wpg_docload.download_file( localBlob );
END IF;
ELSE
NULL;
END IF;
END HIGHLIGHT;
-----------------------------------------------------------------------------------------------------------------------

Exec highlight(105, 'oracle');

Select token_text, token_count from dr$idx_tab$i where token_text='ORACLE';
Result:
TOKEN_TEXT | TOKEN_COUNT
-----------------------------------------------------
ORACLE | 6


Select query_id from markuptab;
Result:
no rows selected

Anyone got an ideas?

Many thanks

Robby

Re: CTXSYS.MARKUP on package problem [message #148347 is a reply to message #148228] Wed, 23 November 2005 14:33 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I believe Oracle expects the document column of the markuptab table to be a clob, as shown below. Also, just because you have 6 values of "oracle" in the index does not mean that any of them are in the row whose primary key value is 105.

scott@ORA92> CREATE TABLE your_table
  2    (id	    NUMBER PRIMARY KEY,
  3  	your_column VARCHAR2(30))
  4  /

Table created.

scott@ORA92> INSERT INTO your_table
  2  VALUES (105, 'Oracle test record')
  3  /

1 row created.

scott@ORA92> CREATE INDEX idx_tab
  2  ON your_table (your_column)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

scott@ORA92> CREATE TABLE markuptab
  2    (query_id NUMBER,
  3  	document CLOB)
  4  /

Table created.

scott@ORA92> CREATE OR REPLACE PROCEDURE HIGHLIGHT
  2    (textkey   IN VARCHAR2,
  3  	textquery IN VARCHAR2)
  4  AS
  5  BEGIN
  6    BEGIN
  7  	 ctx_doc.markup
  8  	   (index_name => 'idx_tab',
  9  	    textkey    => textkey,
 10  	    text_query => textquery,
 11  	    restab     => 'markuptab',
 12  	    query_id   => 1,
 13  	    tagset     => 'HTML_DEFAULT');
 14    END;
 15  END HIGHLIGHT;
 16  /

Procedure created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> Exec highlight(105, 'oracle')

PL/SQL procedure successfully completed.

scott@ORA92> Select token_text, token_count
  2  from   dr$idx_tab$i
  3  where  token_text='ORACLE'
  4  /

TOKEN_TEXT                                                       TOKEN_COUNT
---------------------------------------------------------------- -----------
ORACLE                                                                     1

scott@ORA92> Select *
  2  from   markuptab
  3  /

  QUERY_ID DOCUMENT
---------- --------------------------------------------------------------------------------
         1 <B>Oracle</B> test record

scott@ORA92>


Previous Topic: Error using ctxload from 8.1.6 to 9.2.0
Next Topic: CLOB Retrive Problem ... Pls help [Urgent]
Goto Forum:
  


Current Time: Thu Mar 28 04:22:22 CDT 2024