Home » Server Options » Text & interMedia » Using PROCEDURE Filters for Indexing data from Multi-Table (Oracle Full Text, Oracle 11g)
Using PROCEDURE Filters for Indexing data from Multi-Table [message #469461] Wed, 04 August 2010 14:23 Go to next message
raimredy
Messages: 3
Registered: August 2010
Junior Member
Indexing Data from Multiple Tables using PROCEDURE Filters

Problem: I have data in to two table one table being the table where ID of the entity is stored and the in the other table the localized (Internationalized) value of the entity title is being stored. And based on the user language preference I need to perform the search using the user entered keyword phrase.

2. So I got two tables
CREATE TABLE app_tbl (
app_id VARCHAR(80),
app_title VARCHAR(80), --Column where we would store local label ID (foreign key to locale_tbl.label_id)
dummy VARCHAR2(1)
);

INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID1', 'label.id.1', 'Y');
INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID2', 'label.id.2', 'Y');
INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID3', 'label.id.3', 'Y');
INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID4', 'label.id.4', 'Y');
INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID5', 'label.id.5', 'Y');
INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID6', 'label.id.6', 'Y');

CREATE TABLE locale_tbl (
locale_id VARCHAR(80),
label_id VARCHAR(80),
label_value VARCHAR(120)
);

--For label.id.1 the value is being translated into three different languages
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.1', 'Trust is Worth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('German', 'label.id.1', 'GERTrust GERis GERWorth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('Polish', 'label.id.1', 'POLTrust POLis POLWorth'); --Just some label value

--For label.id.2 the value is being translated into three different languages
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.2', 'Wealth is Health');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('German', 'label.id.2', 'GERWealth GERis GERHealth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('Polish', 'label.id.2', 'POLWealth POLis POLHealth'); --Just some label value

--And for all the remaining labels only english transalation is available
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.3', 'Trust is Worth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.4', 'Trust is Worth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.5', 'Health is Wealth');
INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.6', 'Health is Wealth');


3. And then here is the procedure filter indexing which will give CLOB to oracle for indexing the row
So for example I am expecting following XML clob from this stored procedure to be returned.
Quote:
For app_id = ID1
CLOB = <app_id>ID1</app_id>
<English_app_title>Trust is Worth</English_app_title>
<German_app_title>GERTrust GERis GERWorth</German_app_title>
<Polish_app_title>POLTrust POLis POLWorth</Polish_app_title>

For app_id = ID3
CLOB = <app_id>ID3</app_id>
<English_app_title>Trust is Worth</English_app_title>
<German_app_title></German_app_title> --Empty value since there is no translation is avaialable.
<Polish_app_title></Polish_app_title> --Empty value since there is no translation is avaialable.


PROCEDURE prepare_app_index_xml(rid IN ROWID, tlob IN OUT NOCOPY CLOB) AS
  v_clob CLOB;
  locale_length number default 0;
  BEGIN
   FOR c1 IN (SELECT app_id FROM app_tbl WHERE rowid = rid)
    LOOP
     dbms_lob.writeappend(tlob, 8, '<app_id>');
     dbms_lob.writeappend(tlob, LENGTH(c1.app_id), c1.app_id);
     dbms_lob.writeappend(tlob, 9, '</app_id>');
     FOR c2 IN (select locale_id, label_value1 from (
       SELECT c.locale_id, c.label_value1 from locale_tbl c
          WHERE c.label_id=c1.app_title
       UNION
       SELECT b.locale_id, null as label_value1 from locale_tbl b
          WHERE b.locale_id not in
         (SELECT c.locale_id from locale_tbl c WHERE c.label_id=c1.app_title)
          )
       )
     LOOP
       locale_length := length(c2.locale_id);
       dbms_lob.writeappend(tlob, 11+locale_length, '<'_app_title');
       dbms_lob.writeappend(tlob, LENGTH(c2.label_value1),  c2.label_value1);
       dbms_lob.writeappend(tlob, 12+locale_length, '</' ||  c2.locale_id || '_app_title');
       locale_length := 0;
     END LOOP;
   END LOOP;
END prepare_app_index_xml;


4. Then here are my preferences and Index creation
--With out auto section Group
execute CTX_DDL.CREATE_PREFERENCE ('APP_TBL_PREF', 'USER_DATASTORE');
execute CTX_DDL.SET_ATTRIBUTE ('APP_TBL_PREF', 'PROCEDURE','pkg_test.prepare_app_index_xml');
CREATE INDEX IXFTS_APP_TBL ON APP_TBL(app_id) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (' DATASTORE APP_TBL_PREF SYNC (ON COMMIT)' );

--With Auto Section Grouping
execute CTX_DDL.CREATE_PREFERENCE ('APP_TBL_PREF', 'USER_DATASTORE');
execute CTX_DDL.SET_ATTRIBUTE ('APP_TBL_PREF', 'PROCEDURE','pkg_test.prepare_app_index_xml');
execute CTX_DDL.CREATE_SECTION_GROUP ('APP_TBL_SECGRP', 'AUTO_SECTION_GROUP');
CREATE INDEX IXFTS_APP_TBL ON APP_TBL(app_id) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (' DATASTORE APP_TBL_PREF SECTION GROUP APP_TBL_SECGRP SYNC (ON COMMIT)' );


5. Here is the query, so I preparing this query dynamically based on the user locale query that particluar locale column
For example it could be English_app_title or Polish_app_title etc..

select APP_ID, APP_TITLE, SCORE(1) as RELEVANCE from APP_TBL where (CONTAINS (APP_ID, '(((wealth)) WITHIN app_id) * 10 OR (((wealth)) WITHIN English_app_title)', 1) > 0) order by SCORE(1) desc;


Result: I am not getting any results.

Can you please help me and let me know if there is any flaw in the approach/design I took and if I mis-understood any feature.

Thanks in advance
Rakesh
Re: Using PROCEDURE Filters for Indexing data from Multi-Table [message #469472 is a reply to message #469461] Wed, 04 August 2010 16:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have multiple errors in your procedure, so it does not compile. In the below, I have corrected your procedure and shown how to test that it compiles and how to test that it returns the clob as expected. You need some sort of section group if you are going to search within, so I used the auto_section_group in the example below. I have also shown how to check what is indexed.


SCOTT@orcl_11gR2> -- your supplied tables and insert statements:
SCOTT@orcl_11gR2> CREATE TABLE app_tbl (
  2  app_id VARCHAR(80),
  3  app_title VARCHAR(80),
  4  dummy VARCHAR2(1)
  5  )
  6  /

Table created.

SCOTT@orcl_11gR2> CREATE TABLE locale_tbl (
  2  locale_id VARCHAR(80),
  3  label_id VARCHAR(80),
  4  label_value VARCHAR(120)
  5  )
  6  /

Table created.

SCOTT@orcl_11gR2> BEGIN
  2    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID1', 'label.id.1', 'Y');
  3    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID2', 'label.id.2', 'Y');
  4    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID3', 'label.id.3', 'Y');
  5    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID4', 'label.id.4', 'Y');
  6    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID5', 'label.id.5', 'Y');
  7    INSERT INTO app_tbl (app_id, app_title, dummy) VALUES ('ID6', 'label.id.6', 'Y');
  8    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.1', 'Trust is Worth');
  9    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('German', 'label.id.1', 'GERTrust GERis GERWorth');
 10    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('Polish', 'label.id.1', 'POLTrust POLis POLWorth'); --Just some label value
 11    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.2', 'Wealth is Health');
 12    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('German', 'label.id.2', 'GERWealth GERis GERHealth');
 13    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('Polish', 'label.id.2', 'POLWealth POLis POLHealth'); --Just some label value
 14    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.3', 'Trust is Worth');
 15    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.4', 'Trust is Worth');
 16    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.5', 'Health is Wealth');
 17    INSERT INTO locale_tbl (locale_id, label_id, label_value) VALUES ('English', 'label.id.6', 'Health is Wealth');
 18  END;
 19  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- procedure with errors fixed:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE prepare_app_index_xml
  2    (rid IN ROWID, tlob IN OUT NOCOPY CLOB) AS
  3    v_clob CLOB;
  4    locale_length number default 0;
  5    BEGIN
  6  	FOR c1 IN (SELECT app_id, app_title FROM app_tbl WHERE rowid = rid)
  7  	 LOOP
  8  	  dbms_lob.writeappend(tlob, 8, '<app_id>');
  9  	  dbms_lob.writeappend(tlob, LENGTH(c1.app_id), c1.app_id);
 10  	  dbms_lob.writeappend(tlob, 10, '</app_id>' || chr(10));
 11  	  FOR c2 IN (select locale_id, label_value from (
 12  	    SELECT c.locale_id, c.label_value from locale_tbl c
 13  	       WHERE c.label_id=c1.app_title
 14  	    UNION
 15  	    SELECT b.locale_id, null as label_value from locale_tbl b
 16  	       WHERE b.locale_id not in
 17  	      (SELECT c.locale_id from locale_tbl c WHERE c.label_id=c1.app_title)
 18  	       )
 19  	    )
 20  	  LOOP
 21  	    locale_length := length(c2.locale_id);
 22  	    if locale_length > 0 then
 23  	      dbms_lob.writeappend(tlob, 12+locale_length, '<' || c2.locale_id || '_app_title>');
 24  	      if length (c2.label_value) > 0 then
 25  		dbms_lob.writeappend(tlob, LENGTH(c2.label_value),  c2.label_value);
 26  		dbms_lob.writeappend(tlob, 14+locale_length, '</' ||  c2.locale_id || '_app_title>' || chr(10));
 27  	      end if;
 28  	    end if;
 29  	    locale_length := 0;
 30  	  END LOOP;
 31  	END LOOP;
 32  END prepare_app_index_xml;
 33  /

Procedure created.

SCOTT@orcl_11gR2> -- test that the procedure compiles without errors:
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> -- test that the procedure returns the clob as expected:
SCOTT@orcl_11gR2> declare
  2    v_rid  rowid;
  3    v_clob clob;
  4  begin
  5    select rowid
  6    into   v_rid
  7    from   app_tbl
  8    where  app_id = 'ID1';
  9    dbms_lob.createtemporary (v_clob, true, dbms_lob.session);
 10    prepare_app_index_xml (v_rid, v_clob);
 11    dbms_output.put_line (v_clob);
 12    dbms_lob.freetemporary (v_clob);
 13  end;
 14  /
<app_id>ID1</app_id>
<English_app_title>Trust is
Worth</English_app_title>
<German_app_title>GERTrust GERis
GERWorth</German_app_title>
<Polish_app_title>POLTrust POLis
POLWorth</Polish_app_title>


PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> declare
  2    v_rid  rowid;
  3    v_clob clob;
  4  begin
  5    select rowid
  6    into   v_rid
  7    from   app_tbl
  8    where  app_id = 'ID3';
  9    dbms_lob.createtemporary (v_clob, true, dbms_lob.session);
 10    prepare_app_index_xml (v_rid, v_clob);
 11    dbms_output.put_line (v_clob);
 12    dbms_lob.freetemporary (v_clob);
 13  end;
 14  /
<app_id>ID3</app_id>
<English_app_title>Trust is
Worth</English_app_title>
<German_app_title><Polish_app_title>

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> -- use auto_section_group:
SCOTT@orcl_11gR2> execute CTX_DDL.CREATE_PREFERENCE ('APP_TBL_PREF', 'USER_DATASTORE');

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> execute CTX_DDL.SET_ATTRIBUTE ('APP_TBL_PREF', 'PROCEDURE','prepare_app_index_xml');

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> execute CTX_DDL.CREATE_SECTION_GROUP ('APP_TBL_SECGRP', 'AUTO_SECTION_GROUP');

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> CREATE INDEX IXFTS_APP_TBL ON APP_TBL(app_id)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    (' DATASTORE	APP_TBL_PREF
  5  	  SECTION GROUP APP_TBL_SECGRP
  6  	  SYNC		(ON COMMIT)' )
  7  /

Index created.

SCOTT@orcl_11gR2> -- check what is indexed:
SCOTT@orcl_11gR2> select token_text from dr$ixfts_app_tbl$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
APP_ID
ENGLISH_APP_TITLE
GERHEALTH
GERIS
GERMAN_APP_TITLE
GERTRUST
GERWEALTH
GERWORTH
HEALTH
ID1
ID2
ID3
ID4
ID5
ID6
POLHEALTH
POLIS
POLISH_APP_TITLE
POLTRUST
POLWEALTH
POLWORTH
TRUST
WEALTH
WORTH

24 rows selected.

SCOTT@orcl_11gR2> -- test query:
SCOTT@orcl_11gR2> column app_id    format a10
SCOTT@orcl_11gR2> column app_title format a10
SCOTT@orcl_11gR2> select APP_ID, APP_TITLE, SCORE(1) as RELEVANCE
  2  from   APP_TBL
  3  where  (CONTAINS (APP_ID, '(((wealth)) WITHIN app_id) * 10
  4  	     OR (((wealth)) WITHIN English_app_title)', 1) > 0)
  5  order  by SCORE(1) desc
  6  /

APP_ID     APP_TITLE   RELEVANCE
---------- ---------- ----------
ID2        label.id.2          4
ID6        label.id.6          4
ID5        label.id.5          4

3 rows selected.

SCOTT@orcl_11gR2> 

Re: Using PROCEDURE Filters for Indexing data from Multi-Table [message #469794 is a reply to message #469472] Thu, 05 August 2010 16:45 Go to previous messageGo to next message
raimredy
Messages: 3
Registered: August 2010
Junior Member
Thank you very much Barbara this really helped.
One more question I have app_id's with "underscore" in it. for example app_id1, app_id2, app_id3 etc..
But when search:
select APP_ID, APP_TITLE, SCORE(1) as RELEVANCE
    from   APP_TBL
    where  (CONTAINS (APP_ID, '(((app_id1)) WITHIN app_id) * 10
    	     OR (((app_id1)) WITHIN English_app_title)', 1) > 0)
    order  by SCORE(1) desc;

Does not yeild any results. When I looked at the dr$ixfts_app_tbl$i table the I don't see any token with "app_id1" or "app_id2" but this got broken down to "app" and "id1" etc..
Do I have to use any special lexer/parser for to handle these.

Thanks again for your help
Rakesh
Re: Using PROCEDURE Filters for Indexing data from Multi-Table [message #469802 is a reply to message #469794] Thu, 05 August 2010 18:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You will need to set the underscore as a printjoin:

EXEC CTX_DDL.CREATE_PREFERENCE ('APP_TBL_PREF', 'USER_DATASTORE');
EXEC CTX_DDL.SET_ATTRIBUTE ('APP_TBL_PREF', 'PROCEDURE','prepare_app_index_xml');
EXEC CTX_DDL.CREATE_SECTION_GROUP ('APP_TBL_SECGRP', 'AUTO_SECTION_GROUP');
EXEC CTX_DDL.CREATE_PREFERENCE ('app_tbl_lex', 'BASIC_LEXER')
EXEC CTX_DDL.SET_ATTRIBUTE ('app_tbl_lex', 'PRINTJOINS', '_')
CREATE INDEX IXFTS_APP_TBL ON APP_TBL(app_id)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS
(' DATASTORE APP_TBL_PREF
SECTION GROUP APP_TBL_SECGRP
LEXER app_tbl_lex
SYNC (ON COMMIT)' )
/
Re: Using PROCEDURE Filters for Indexing data from Multi-Table [message #470724 is a reply to message #469802] Tue, 10 August 2010 09:42 Go to previous message
raimredy
Messages: 3
Registered: August 2010
Junior Member
Thanks Barbara you are very helpful.

I got a one more question arround printjoins:
Assume if one of the column in the table APP_TBL(app_id) contains a word "WEB-SITE" right.
How could I force the oracle to tokenize this to
"WEB-SITE"
"WEb"
"SITE"

Basically when I do the search for "WEB" the documents which contains "WEB-SITE" should show up.
And at the same time if I search for "WEB-SITE" then the document contains word "WEB-SITE" should show up to.

Is this possible in Oracle full text can we have a combination of not defining printjoin and defining prinjoin for lexer.

Thanks
Rakesh

[Updated on: Tue, 10 August 2010 14:29]

Report message to a moderator

Previous Topic: CONTAINS EQUAL
Next Topic: PDF documents not indexed by OracleText using auto-filter and format_column
Goto Forum:
  


Current Time: Thu Mar 28 10:33:59 CDT 2024