Home » Server Options » Text & interMedia » Merge columns from dif tables in one index and markup the result (Oracle 10)
icon9.gif  Merge columns from dif tables in one index and markup the result [message #332970] Thu, 10 July 2008 03:55 Go to next message
kruschinski
Messages: 2
Registered: July 2008
Location: Germany
Junior Member
I want to build up an text index containing columns from different tables. That’s not the problem - I tried successfully user_datastore. Now I'm searching for a solution to get back the content of the Index after querying marked up. Our Client Server application should be able to point all hits after search. We dreamed Smile of an XML based text index where the table and column names are used as tags (what not will be the problem) but then we hoped to get back the marked up xml so we can point the application user the table name and field name including the position where the hits occur. I read the oracle text reference and found some about ctx_doc.markup but understand that this functionality gives back the content of an column and not of my combined index. Have someone experiences in this area?
Thanks in advance!
Joerg
Re: Merge columns from dif tables in one index and markup the result [message #333124 is a reply to message #332970] Thu, 10 July 2008 09:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see the example below.


SCOTT@orcl_11g> CREATE TABLE master
  2    (id	     NUMBER,
  3  	data	     CLOB)
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO master VALUES (1, 'parent test')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO master VALUES (2, 'another parent test')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE TABLE detail
  2    (id	     NUMBER,
  3  	data	     CLOB)
  4  /

Table created.

SCOTT@orcl_11g> INSERT INTO detail VALUES (1, 'first child test')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO detail VALUES (1, 'second child test')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO detail VALUES (2, 'another child test')
  2  /

1 row created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE test_proc
  2    (p_rowid IN ROWID,
  3  	p_clob	IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR m IN (SELECT * FROM master WHERE ROWID = p_rowid) LOOP
  7  	 DBMS_LOB.WRITEAPPEND (p_clob, 13, '<master_data>');
  8  	 DBMS_LOB.APPEND (p_clob, m.data);
  9  	 DBMS_LOB.WRITEAPPEND (p_clob, 14, '</master_data>');
 10  	 FOR d IN (SELECT * FROM detail WHERE id = m.id) LOOP
 11  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, CHR(10) || '<detail_data>');
 12  	   DBMS_LOB.APPEND (p_clob, d.data);
 13  	   DBMS_LOB.WRITEAPPEND (p_clob, 14, '</detail_data>');
 14  	 END LOOP;
 15    END LOOP;
 16  END test_proc;
 17  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'test_proc');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX text_index ON master (data)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('DATASTORE	test_datastore
  5  	 SECTION GROUP	CTXSYS.AUTO_SECTION_GROUP')
  6  /

Index created.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_markup
  2    (p_rowid IN ROWID,
  3  	p_text	IN VARCHAR2)
  4    RETURN CLOB
  5  AS
  6    v_markup CLOB;
  7  BEGIN
  8    CTX_DOC.SET_KEY_TYPE ('ROWID');
  9    CTX_DOC.MARKUP
 10  	 (index_name	     => 'text_index',
 11  	  textkey	     =>  p_rowid,
 12  	  text_query	     => p_text,
 13  	  restab	     => v_markup,
 14  	  starttag	     => '[B]',
 15  	  endtag	     => '[/B]');
 16    RETURN v_markup;
 17  END get_markup;
 18  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE search_words VARCHAR2 (2000)


SCOTT@orcl_11g> EXEC :search_words := 'test'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT id, get_markup (ROWID, :search_words)
2 FROM master
3 WHERE CONTAINS (data, :search_words) > 0
4 /

ID
----------
GET_MARKUP(ROWID,:SEARCH_WORDS)
--------------------------------------------------------------------------------
1
<master_data>parent test</master_data>
<detail_data>first child test</detail_data>
<detail_data>second child test</detail_data>

2
<master_data>another parent test</master_data>
<detail_data>another child test</detail_data>


SCOTT@orcl_11g> EXEC :search_words := 'parent test'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /

ID
----------
GET_MARKUP(ROWID,:SEARCH_WORDS)
--------------------------------------------------------------------------------
1
<master_data>parent test</master_data>
<detail_data>first child test</detail_data>
<detail_data>second child test</detail_data>

2
<master_data>another parent test</master_data>
<detail_data>another child test</detail_data>


SCOTT@orcl_11g> EXEC :search_words := 'child test'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /

ID
----------
GET_MARKUP(ROWID,:SEARCH_WORDS)
--------------------------------------------------------------------------------
1
<master_data>parent test</master_data>
<detail_data>first child test</detail_data>
<detail_data>second child test</detail_data>

2
<master_data>another parent test</master_data>
<detail_data>another child test</detail_data>


SCOTT@orcl_11g> EXEC :search_words := 'test WITHIN master_data'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /

ID
----------
GET_MARKUP(ROWID,:SEARCH_WORDS)
--------------------------------------------------------------------------------
1
<master_data>parent test</master_data>
<detail_data>first child test</detail_data>
<detail_data>second child test</detail_data>

2
<master_data>another parent test</master_data>
<detail_data>another child test</detail_data>


SCOTT@orcl_11g> EXEC :search_words := 'test WITHIN detail_data'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> /

ID
----------
GET_MARKUP(ROWID,:SEARCH_WORDS)
--------------------------------------------------------------------------------
1
<master_data>parent test</master_data>
<detail_data>first child test</detail_data>
<detail_data>second child test</detail_data>

2
<master_data>another parent test</master_data>
<detail_data>another child test</detail_data>


SCOTT@orcl_11g>

icon14.gif  Re: Merge columns from dif tables in one index and markup the result [message #333325 is a reply to message #333124] Fri, 11 July 2008 04:38 Go to previous message
kruschinski
Messages: 2
Registered: July 2008
Location: Germany
Junior Member
Thanks, thats the solution!
Previous Topic: speed up 'like' queries - Oracle Text API?
Next Topic: Oracle Text using Contains
Goto Forum:
  


Current Time: Thu Mar 28 06:22:09 CDT 2024