Home » Server Options » Text & interMedia » package and oracle text search AND SOS,SOS,Search,Search,SOS (merged by bb)
package and oracle text search AND SOS,SOS,Search,Search,SOS (merged by bb) [message #322002] Thu, 22 May 2008 02:26 Go to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
Hi,everyone

i want to bulid a package.
use ctx_doc.markup to <u></u> search word.
the package returns cursor,
then to read, to show.

help me, please.
many many thanks
Jim
Re: package and oracle text search [message #322006 is a reply to message #322002] Thu, 22 May 2008 02:28 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
My code:

CREATE OR REPLACE package body PKG_SHOP as
function P_SEARCH_SHOP (p_query in varchar2) return cur_emp as
cursor1 cur_emp;
begin
open cursor1 for select CDM,S_NAME,S_GOOD,S_MAIN,S_PLACE,S_FOR,S_INFO from T_SHOP where contains(S_NAME,p_query,1)>0;
--ctx_doc.markup ('T_SHOP_INDEX','1',p_query,STARTTAG => '<i><font color=red>', ENDTAG => '</font></i>');
return cursor1;
end P_SEARCH_SHOP;
end PKG_SHOP;


But error
Re: package and oracle text search [message #322149 is a reply to message #322006] Thu, 22 May 2008 11:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> CREATE TABLE t_shop
  2    (cdm	NUMBER,
  3  	s_name	CLOB,
  4  	s_good	NUMBER,
  5  	s_main	NUMBER,
  6  	s_place NUMBER,
  7  	s_for	NUMBER,
  8  	s_info	NUMBER)
  9  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO t_shop VALUES (1, '... testing once', 2, 3, 4, 5, 6)
  3  INTO t_shop VALUES (7, 'and testing twice', 8, 9, 10, 11, 12)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> CREATE INDEX t_shop_index ON t_shop (s_name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pkg_shop
  2  AS
  3    TYPE cur_emp IS REF CURSOR;
  4    FUNCTION p_markup
  5  	 (p_rowid IN VARCHAR2,
  6  	  p_query IN VARCHAR2)
  7  	 RETURN CLOB;
  8    FUNCTION p_search_shop
  9  	 (p_query IN VARCHAR2)
 10  	 RETURN cur_emp;
 11  END pkg_shop;
 12  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pkg_shop
  2  AS
  3    FUNCTION p_markup
  4  	 (p_rowid IN VARCHAR2,
  5  	  p_query IN VARCHAR2)
  6  	 RETURN CLOB
  7    IS
  8  	 v_clob 	  CLOB;
  9    BEGIN
 10  	 CTX_DOC.MARKUP
 11  	   (index_name => 'T_SHOP_INDEX',
 12  	    textkey    => p_rowid,
 13  	    text_query => p_query,
 14  	    restab     => v_clob,
 15  	    starttag   => '<i><font color=red>',
 16  	    endtag     => '</font></i>');
 17  	 RETURN v_clob;
 18    END p_markup;
 19    FUNCTION p_search_shop
 20  	 (p_query IN VARCHAR2)
 21  	 RETURN cur_emp
 22    IS
 23  	 cursor1 cur_emp;
 24    BEGIN
 25  	 CTX_DOC.SET_KEY_TYPE ('ROWID');
 26  	 OPEN	cursor1 FOR
 27  	 SELECT cdm,
 28  		p_markup (ROWID, p_query) AS s_name,
 29  		s_good, s_main, s_place, s_for, s_info
 30  	 FROM	t_shop
 31  	 WHERE	CONTAINS (s_name, p_query, 1) > 0;
 32  	 RETURN cursor1;
 33    END p_search_shop;
 34  END pkg_shop;
 35  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := pkg_shop.p_search_shop ('testing')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN s_name FORMAT A55
SCOTT@orcl_11g> COLUMN s_good NEWLINE
SCOTT@orcl_11g> PRINT g_ref

       CDM S_NAME
---------- -------------------------------------------------------
    S_GOOD     S_MAIN    S_PLACE      S_FOR     S_INFO
---------- ---------- ---------- ---------- ----------
         1 ... <i><font color=red>testing</font></i> once
         2          3          4          5          6

         7 and <i><font color=red>testing</font></i> twice
         8          9         10         11         12


SCOTT@orcl_11g>

[Updated on: Thu, 22 May 2008 11:07]

Report message to a moderator

Re: package and oracle text search [message #322237 is a reply to message #322149] Thu, 22 May 2008 20:58 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
good idea, thanks, Barbara Boehmer
my oracle is 10g
now, i bulid a MULTI_COLUMN_DATASTORE index
like following:

ctx_ddl.create_preference('shop_column', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('shop_column', 'columns', 'S_NAME, S_PLACE, S_MAIN, S_GOOD, S_FOR');

CREATE INDEX T_SHOP_INDEX ON T_SHOP(S_NAME) INDEXTYPE IS ctxsys.CONTEXT
PARAMETERS('datastore shop_column LEXER shop_lexer');

i want to markup SEARCH WORD in any column.
return CURSOR

use many FUNCIONS like p_markup ???

Thanks

Re: package and oracle text search [message #322239 is a reply to message #322006] Thu, 22 May 2008 21:02 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
COLUMNS of T_SHOP TABLE:
CDM CHAR(20 BYTE) NOT NULL PK,
S_NAME VARCHAR2(30 BYTE),
S_LOGO VARCHAR2(30 BYTE),
S_INFO LONG,
S_PLACE VARCHAR2(100 BYTE),
S_TEL VARCHAR2(50 BYTE),
C_ID CHAR(10 BYTE),
C_SID CHAR(10 BYTE),
S_MAIN VARCHAR2(100 BYTE),
S_GOOD VARCHAR2(100 BYTE),
S_FOR VARCHAR2(100 BYTE),
S_GO VARCHAR2(100 BYTE),
Re: package and oracle text search [message #322242 is a reply to message #322149] Thu, 22 May 2008 21:25 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
USE CTX_DOC.MARKUP to HIGHLIGHT many columns in CURSOR, then return CURSOR.
every column is separate, not in one CLOB VARIABLE.

I think the final effect should look like Google, but not search web page content.
thanks
Re: package and oracle text search [message #322243 is a reply to message #322242] Thu, 22 May 2008 21:28 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
can we use CTX_DOC.MARKUP to HIGHLIGHT one no-index-column?
Re: package and oracle text search [message #322249 is a reply to message #322237] Thu, 22 May 2008 22:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It might be better to use a user_datastore with a procedure to concatenate the columns into one clob, as demonstrated below.

SCOTT@orcl_11g> CREATE TABLE t_shop
  2    (cdm	NUMBER,
  3  	s_name	VARCHAR2 (30 BYTE),
  4  	s_good	VARCHAR2 (100 BYTE),
  5  	s_main	VARCHAR2 (100 BYTE),
  6  	s_place VARCHAR2 (100 BYTE),
  7  	s_for	VARCHAR2 (100 BYTE),
  8  	s_info	LONG)
  9  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO t_shop VALUES (1, 'I want to', 'markup search word ', ' in any column', 'return', 'cursor', 'whatever')
  3  INTO t_shop VALUES (2, 'Another', 'line', 'with', ' search word ', ' in it ', ' something')
  4  INTO t_shop VALUES (3, 'This one has ', 'search', 'in one', 'column and word', ' in another ', ' something')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE shop_proc
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    SELECT s_name || ' ' || s_good || ' ' || s_main || ' ' || s_place || ' ' || s_for
  7    INTO   p_clob
  8    FROM   t_shop
  9    WHERE  ROWID = p_rowid;
 10  END shop_proc;
 11  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    ctx_ddl.create_preference ('shop_column', 'USER_DATASTORE');
  3    ctx_ddl.set_attribute ('shop_column', 'PROCEDURE', 'shop_proc');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX T_SHOP_INDEX ON T_SHOP (S_NAME) INDEXTYPE IS ctxsys.CONTEXT
  2  PARAMETERS('datastore shop_column')
  3  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$t_shop_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
ANOTHER
COLUMN
CURSOR
LINE
MARKUP
RETURN
SEARCH
WANT
WORD

9 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pkg_shop
  2  AS
  3    TYPE cur_emp IS REF CURSOR;
  4    FUNCTION p_markup
  5  	 (p_rowid IN VARCHAR2,
  6  	  p_query IN VARCHAR2)
  7  	 RETURN CLOB;
  8    FUNCTION p_search_shop
  9  	 (p_query IN VARCHAR2)
 10  	 RETURN cur_emp;
 11  END pkg_shop;
 12  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pkg_shop
  2  AS
  3    FUNCTION p_markup
  4  	 (p_rowid IN VARCHAR2,
  5  	  p_query IN VARCHAR2)
  6  	 RETURN CLOB
  7    IS
  8  	 v_clob 	  CLOB;
  9    BEGIN
 10  	 CTX_DOC.MARKUP
 11  	   (index_name => 'T_SHOP_INDEX',
 12  	    textkey    => p_rowid,
 13  	    text_query => p_query,
 14  	    restab     => v_clob,
 15  	    plaintext  => TRUE,
 16  	    tagset     => 'TEXT_DEFAULT',
 17  	    starttag   => '<i><font color=red>',
 18  	    endtag     => '</font></i>');
 19  	 RETURN v_clob;
 20    END p_markup;
 21    FUNCTION p_search_shop
 22  	 (p_query IN VARCHAR2)
 23  	 RETURN cur_emp
 24    IS
 25  	 cursor1 cur_emp;
 26    BEGIN
 27  	 CTX_DOC.SET_KEY_TYPE ('ROWID');
 28  	 OPEN	cursor1 FOR
 29  	 SELECT p_markup (ROWID, p_query) AS shop_column
 30  	 FROM	t_shop
 31  	 WHERE	CONTAINS (s_name, p_query, 1) > 0;
 32  	 RETURN cursor1;
 33    END p_search_shop;
 34  END pkg_shop;
 35  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := pkg_shop.p_search_shop ('search AND word')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN shop_column FORMAT A150
SCOTT@orcl_11g> PRINT g_ref

SHOP_COLUMN
------------------------------------------------------------------------------------------------------------------------------------------------------
I want to markup <i><font color=red>search</font></i> <i><font color=red>word</font></i>   in any column return cursor
Another line with  <i><font color=red>search</font></i> <i><font color=red>word</font></i>   in it
This one has  <i><font color=red>search</font></i> in one column and <i><font color=red>word</font></i>  in another

SCOTT@orcl_11g> 

Re: package and oracle text search [message #322250 is a reply to message #322243] Thu, 22 May 2008 22:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
jimsang wrote on Thu, 22 May 2008 19:28
can we use CTX_DOC.MARKUP to HIGHLIGHT one no-index-column?


CTX_DOC.POLICY_MARKUP can do that without an index:

http://download.oracle.com/docs/cd/B28359_01/text.111/b28304/cdocpkg.htm#i999514
Re: package and oracle text search [message #322276 is a reply to message #322249] Thu, 22 May 2008 23:55 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
Thanks
Your idea is very good!
icon8.gif  SOS,SOS,Search,Search,SOS [message #348757 is a reply to message #322002] Wed, 17 September 2008 21:12 Go to previous messageGo to next message
jimsang
Messages: 13
Registered: May 2008
Junior Member
i will to be a madman.........help...........help me(~!~)...........

create a table: T_SHOP
create some columns: S_ID, S_NAME, S_HOST, S_ADDRESS, S_INTRO
fill the table with some data:
1, 'wine bar', 'jim', 'New York Road', '<i>wine bar</i> is very <center>famous</center>....'
2, 'shopping center', 'rose', 'london street', '<p>shopping center</p> is very <b>good</b>'

create MULTI_COLUMN_DATASTORE index : (S_NAME, S_ADDRESS, S_INTRO)

search keyword 'center', return secord record, looks like :
2, 'shopping <font color=red>center</font>', 'orse', 'lodon street', '<p>shopping <font color=red>center</font></p> is very

<b>good</b>'


create a package to encapsule complicated process: PKG_SEARCH_SHOP(keyword), the package return a REF CURSOR.
we can use java, php or else development languages to call it, to search.

many thanks.

i wander:
if i use USER_DATASTORE and PROCEDURE, or MULTI_COLUMN_DATASTORE and columns to do MULTI_COLUMN INDEX,
what's the difference between them?

if i have 1 million records, how to optimize the table index to heighten search efficiency? thank you.

..................................................SOS.................................................
Re: SOS,SOS,Search,Search,SOS [message #348767 is a reply to message #348757] Wed, 17 September 2008 22:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
jimsang wrote on Wed, 17 September 2008 19:12

if i use USER_DATASTORE and PROCEDURE, or MULTI_COLUMN_DATASTORE and columns to do MULTI_COLUMN INDEX, what's the difference between them?



The multi_column_datastore creates a virtual clob document, just like the procedure with the user_datastore. By default, the multi_column_datastore creates an xml document that would cause your search to return something like this:

SHOP_COLUMN
------------------------------------------------------------------------------------------------------------------------------------------------------
<S_NAME>
I want to
</S_NAME>
<S_GOOD>
markup <i><font color=red>search</font></i> <i><font color=red>word</font></i>
</S_GOOD>
<S_MAIN>
 in any column
</S_MAIN>
<S_PLACE>
return
</S_PLACE>
<S_FOR>
cursor
</S_FOR>

<S_NAME>
Another
</S_NAME>
<S_GOOD>
line
</S_GOOD>
<S_MAIN>
with
</S_MAIN>
<S_PLACE>
 <i><font color=red>search</font></i> <i><font color=red>word</font></i>
</S_PLACE>
<S_FOR>
 in it
</S_FOR>

<S_NAME>
This one has
</S_NAME>
<S_GOOD>
<i><font color=red>search</font></i>
</S_GOOD>
<S_MAIN>
in one
</S_MAIN>
<S_PLACE>
column and <i><font color=red>word</font></i>
</S_PLACE>
<S_FOR>
 in another
</S_FOR>


When I previously said that the user_datastore and procedure might be better, I believe that is what I was thinking. However, you can use the newline delimeter and replace that with a space in order to get the same output as with a user_datastore and procedure, so you might find that more convenient. Please see the demonstration below.

SCOTT@orcl_11g> CREATE TABLE t_shop
  2    (cdm	NUMBER,
  3  	s_name	VARCHAR2 (30 BYTE),
  4  	s_good	VARCHAR2 (100 BYTE),
  5  	s_main	VARCHAR2 (100 BYTE),
  6  	s_place VARCHAR2 (100 BYTE),
  7  	s_for	VARCHAR2 (100 BYTE),
  8  	s_info	LONG)
  9  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO t_shop VALUES (1, 'I want to', 'markup search word ', ' in any column', 'return', 'cursor', 'whatever')
  3  INTO t_shop VALUES (2, 'Another', 'line', 'with', ' search word ', ' in it ', ' something')
  4  INTO t_shop VALUES (3, 'This one has ', 'search', 'in one', 'column and word', ' in another ', ' something')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> BEGIN
  2    ctx_ddl.create_preference ('shop_column', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute ('shop_column', 'COLUMNS', 's_name, s_good, s_main, s_place, s_for');
  4    ctx_ddl.set_attribute ('shop_column', 'delimiter', 'NEWLINE');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX T_SHOP_INDEX ON T_SHOP (S_NAME) INDEXTYPE IS ctxsys.CONTEXT
  2  PARAMETERS('datastore shop_column')
  3  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$t_shop_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
ANOTHER
COLUMN
CURSOR
LINE
MARKUP
RETURN
SEARCH
WANT
WORD

9 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pkg_shop
  2  AS
  3    TYPE cur_emp IS REF CURSOR;
  4    FUNCTION p_markup
  5  	 (p_rowid IN VARCHAR2,
  6  	  p_query IN VARCHAR2)
  7  	 RETURN CLOB;
  8    FUNCTION p_search_shop
  9  	 (p_query IN VARCHAR2)
 10  	 RETURN cur_emp;
 11  END pkg_shop;
 12  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pkg_shop
  2  AS
  3    FUNCTION p_markup
  4  	 (p_rowid IN VARCHAR2,
  5  	  p_query IN VARCHAR2)
  6  	 RETURN CLOB
  7    IS
  8  	 v_clob 	  CLOB;
  9    BEGIN
 10  	 CTX_DOC.MARKUP
 11  	   (index_name => 'T_SHOP_INDEX',
 12  	    textkey    => p_rowid,
 13  	    text_query => p_query,
 14  	    restab     => v_clob,
 15  	    plaintext  => TRUE,
 16  	    tagset     => 'TEXT_DEFAULT',
 17  	    starttag   => '<i><font color=red>',
 18  	    endtag     => '</font></i>');
 19  	 RETURN v_clob;
 20    END p_markup;
 21    FUNCTION p_search_shop
 22  	 (p_query IN VARCHAR2)
 23  	 RETURN cur_emp
 24    IS
 25  	 cursor1 cur_emp;
 26    BEGIN
 27  	 CTX_DOC.SET_KEY_TYPE ('ROWID');
 28  	 OPEN	cursor1 FOR
 29  	 SELECT REPLACE (p_markup (ROWID, p_query), CHR(10), ' ') AS shop_column
 30  	 FROM	t_shop
 31  	 WHERE	CONTAINS (s_name, p_query, 1) > 0;
 32  	 RETURN cursor1;
 33    END p_search_shop;
 34  END pkg_shop;
 35  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := pkg_shop.p_search_shop ('search AND word')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN shop_column FORMAT A150
SCOTT@orcl_11g> PRINT g_ref

SHOP_COLUMN
------------------------------------------------------------------------------------------------------------------------------------------------------
 I want to  markup <i><font color=red>search</font></i> <i><font color=red>word</font></i>    in any column  return  cursor
 Another  line  with   <i><font color=red>search</font></i> <i><font color=red>word</font></i>    in it
 This one has   <i><font color=red>search</font></i>  in one  column and <i><font color=red>word</font></i>   in another

SCOTT@orcl_11g>

Re: SOS,SOS,Search,Search,SOS [message #348772 is a reply to message #348757] Wed, 17 September 2008 23:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
jimsang wrote on Wed, 17 September 2008 19:12

fill the table with some data:
1, 'wine bar', 'jim', 'New York Road', '<i>wine bar</i> is very <center>famous</center>....'
2, 'shopping center', 'rose', 'london street', '<p>shopping center</p> is very <b>good</b>'
...
search keyword 'center', return secord record, looks like :
2, 'shopping <font color=red>center</font>', 'orse', 'lodon street', '<p>shopping <font color=red>center</font></p> is very

<b>good</b>'



If you want to search for "center" without searching for "<center>", you can specify "<" as startjoins and ">" as endjoins in a basic_lexer and specify that lexer in your parameters when creating your index. Please see the revised demonstration below.

SCOTT@orcl_11g> CREATE TABLE t_shop
  2    (s_id	  NUMBER,
  3  	s_name	  VARCHAR2 (30 BYTE),
  4  	s_host	  VARCHAR2 (100 BYTE),
  5  	s_address VARCHAR2 (100 BYTE),
  6  	s_info	  CLOB)
  7  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO t_shop VALUES (1, 'wine bar', 'Jim', 'New York Road', '<i>wine bar</i> is very <center>famous</center>....')
  3  INTO t_shop VALUES (2, 'shopping center', 'rose', 'london street', '<p>shopping center</p> is very <b>good</b>')
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> BEGIN
  2    ctx_ddl.create_preference ('shop_column', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute ('shop_column', 'COLUMNS', 's_name, s_address, s_info');
  4    ctx_ddl.set_attribute ('shop_column', 'delimiter', 'NEWLINE');
  5    CTX_DDL.CREATE_PREFERENCE ('shop_lexer', 'BASIC_LEXER');
  6    CTX_DDL.SET_ATTRIBUTE ('shop_lexer', 'STARTJOINS', '<');
  7    CTX_DDL.SET_ATTRIBUTE ('shop_lexer', 'ENDJOINS', '>');
  8  END;
  9  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX T_SHOP_INDEX ON T_SHOP (S_NAME) INDEXTYPE IS ctxsys.CONTEXT
  2  PARAMETERS
  3    ('datastore shop_column
  4  	 LEXER	   shop_lexer')
  5  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$t_shop_index$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
<B>
<CENTER>
<I>
<P>
B>
BAR
CENTER
CENTER>
FAMOUS
GOOD
I>
LONDON
NEW
P>
ROAD
SHOPPING
STREET
WINE
YORK

19 rows selected.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pkg_shop
  2  AS
  3    TYPE cur_emp IS REF CURSOR;
  4    FUNCTION p_markup
  5  	 (p_rowid IN VARCHAR2,
  6  	  p_query IN VARCHAR2)
  7  	 RETURN CLOB;
  8    FUNCTION p_search_shop
  9  	 (p_query IN VARCHAR2)
 10  	 RETURN cur_emp;
 11  END pkg_shop;
 12  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pkg_shop
  2  AS
  3    FUNCTION p_markup
  4  	 (p_rowid IN VARCHAR2,
  5  	  p_query IN VARCHAR2)
  6  	 RETURN CLOB
  7    IS
  8  	 v_clob 	  CLOB;
  9    BEGIN
 10  	 CTX_DOC.MARKUP
 11  	   (index_name => 'T_SHOP_INDEX',
 12  	    textkey    => p_rowid,
 13  	    text_query => p_query,
 14  	    restab     => v_clob,
 15  	    plaintext  => TRUE,
 16  	    tagset     => 'TEXT_DEFAULT',
 17  	    starttag   => '<font color=red>',
 18  	    endtag     => '</font>');
 19  	 RETURN v_clob;
 20    END p_markup;
 21    FUNCTION p_search_shop
 22  	 (p_query IN VARCHAR2)
 23  	 RETURN cur_emp
 24    IS
 25  	 cursor1 cur_emp;
 26    BEGIN
 27  	 CTX_DOC.SET_KEY_TYPE ('ROWID');
 28  	 OPEN	cursor1 FOR
 29  	 SELECT REPLACE (p_markup (ROWID, p_query), CHR(10), ' ') AS shop_column
 30  	 FROM	t_shop
 31  	 WHERE	CONTAINS (s_name, p_query, 1) > 0;
 32  	 RETURN cursor1;
 33    END p_search_shop;
 34  END pkg_shop;
 35  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> EXEC :g_ref := pkg_shop.p_search_shop ('center')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> COLUMN shop_column FORMAT A150
SCOTT@orcl_11g> PRINT g_ref

SHOP_COLUMN
------------------------------------------------------------------------------------------------------------------------------------------------------
 shopping <font color=red>center</font>  london street  <p>shopping <font color=red>center</font></p> is very <b>good</b>

SCOTT@orcl_11g>


Re: SOS,SOS,Search,Search,SOS [message #348777 is a reply to message #348757] Wed, 17 September 2008 23:09 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
jimsang wrote on Wed, 17 September 2008 19:12

if i have 1 million records, how to optimize the table index to heighten search efficiency?



http://download.oracle.com/docs/cd/B28359_01/text.111/b28303/aoptim.htm#i1007227

Previous Topic: index error
Next Topic: CONTEXT INDEX and Tune LIKE clause (merged by bb and mc)
Goto Forum:
  


Current Time: Thu Mar 28 13:44:00 CDT 2024