I love examples. I can see real structures, real code, and try them myself.
Several aspects of context searching and lexers illustrated here.

Tom,
I have tried the following:
create table t1 (id number, data varchar2(100));
insert into t1 values (1,'web-site test');
insert into t1 values (2,'web_site test');
commit;

begin
ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('my_lexer', 'printjoins', '-_');
end;
/

Create index idx_lexer on t1 (data) indextype is ctxsys.context parameters('LEXER my_lexer');

select * from t1 where contains(data,'web_site')>0

ID DATA
---------- --------------------
2 web_site test
1 web-site test

select * from t1 where contains(data,'web-site')>0;

no rows selected

It seems to that the intermedia text treat '_' as a single-character wildcard and ignored the printjoins. What did I do wrong?

Thank you for your help.

there are various constructs to achieve this but what I do is use {} to "quote" the strings.  If you search on asktom for 

how now brown cow

not only does it surprisingly find quite a few hits but it prints out the search string I use:

            
{how} and {now} and {brown} and {cow}




ops$tkyte@ORA920> select * from t1 where contains(data,'{web_site}')>0;

        ID DATA
---------- ----------------------------------------------------------------------------------------------------
         2 web_site test

ops$tkyte@ORA920> select * from t1 where contains(data,'{web-site}')>0;

        ID DATA
---------- ----------------------------------------------------------------------------------------------------
         1 web-site test

 

Tom,
The problem with using {} is that the wildcard symbol cannot be used.
select * from t1 where contains(data,'{web%}')>0;
or
select * from t1 where contains(data,'{web-%}')>0;
will not return any rows.
When people do a search, they can stop at anywhere by using a wildcard.
How should we handle this?
Thank you again for your help.

as i said, many ways to do this, you can of course escape individual characters as well:

ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t1 where contains(data,'web\_site')>0;

        ID DATA
---------- ----------------------------------------------------------------------------------------------------
         2 web_site test

ops$tkyte@ORA920> select * from t1 where contains(data,'web\-site')>0;

        ID DATA
---------- ----------------------------------------------------------------------------------------------------
         1 web-site test

is all about escaping special characters...
 

Tom,

I just created the table and insert the values as above only... I added 6th record, but which i query i am not able to get it.. but when i drop the index and recreate it - i am able to get the search.

Can you please tell me what i am missing here.  Since because we need a simillar requirement, going with this kind of search, does it require drop-recreate/rebuild of index. If this is how it works , how can we handle this in online application.

13:49:09 SQL>select * from  xxx ;

        X1 X2
---------- ----------------------------------------------------------------------------------------------
         1 Hello
         2 Hello 2
         3 Hello 3
         4 Hello 4
         5 Hello 5

13:49:19 SQL>create index xxx_ind_ctx on xxx(x2) indextype is ctxsys.context ;

Index created.

13:49:29 SQL>select * from xxx;

        X1 X2
---------- ----------------------------------------------------------------------------------------------
         1 Hello
         2 Hello 2
         3 Hello 3
         4 Hello 4
         5 Hello 5

13:49:56 SQL>select * from xxx where contains(x2,'Hello 5') >0;

        X1 X2
---------- ----------------------------------------------------------------------------------------------
         5 Hello 5

13:50:13 SQL>insert into xxx values (6,'Hello 6');

1 row created.

13:50:26 SQL>commit;

Commit complete.

13:50:28 SQL>select * from xxx where contains(x2,'Hello 6') >0;

no rows selected

13:50:39 SQL>drop index xxx_ind_ctx;

Index dropped.

13:51:04 SQL>create index xxx_ind_ctx on xxx(x2) indextype is ctxsys.context ;

Index created.

13:51:08 SQL>select * from xxx where contains(x2,'Hello 6') >0;

        X1 X2
---------- ----------------------------------------------------------------------------------------------
         6 Hello 6 

Tom,

I missed to see the below article before asking the previous message ...

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:238814521769, <code>


thats a fantastic message. Thanks a lot.

Hello Tom,

Thanks again for having this site, it is awesome!

I am having some issues with the "-" character and Intermedia.  Originally I used the escape character, "/" to replace the "-" in my intermedia searches.  It was working fine... However if the user entered "-" in combination with a wildcard "%"  (example: ECV-%) the following error returned: 

ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: interMedia Text error: DRG-51030: wildcard query expansion resulted in too many terms ORA-06512:  "LIMSDB.OI_LIMS_REPORT_SEARCH_PKG", line 154 ORA-06512: at line 1

If the user entered in something like ECV-A% then the query would return results.

I then set up the following:

SQL> begin
  2  ctx_ddl.create_preference ('oi_lims_report_search_lexer', 'BASIC_LEXER');
  3  ctx_ddl.set_attribute ('oi_lims_report_search_lexer', 'printjoins', '-');
  4  end;
  5  /

Dropped the index and recreated.
The ORA error 29902 still occurs. 
The escape character no longer works 
When searching using the "-" character the results are not always correct, meaning it returns back files that do not contain the text searched on.

Any help you can provide is greatly appreciated!





 

Hi Tom,
How can i find typos using oracle text search? I am trying the following query to find all the matched address_1 rows along with the typos. In this case the last 2 record with the address_1 column value of "'28 E BLVD' and '28 E BVLD'. Please help me to correct my query.
Thanks a lot.
Leo.
--*
CREATE TABLE CFI
(
CFI_ID NUMBER(10),
LAST_NAME VARCHAR2(40 BYTE),
FIRST_NAME VARCHAR2(40 BYTE),
MIDDLE_NAME VARCHAR2(40 BYTE),
ADDRESS_1 VARCHAR2(50 BYTE),
ADDRESS_2 VARCHAR2(50 BYTE),
CITY VARCHAR2(30 BYTE),
COUNTY VARCHAR2(3 BYTE),
STATES VARCHAR2(2 BYTE),
POSTAL_CODE VARCHAR2(9 BYTE))
LOGGING
NOCACHE
NOPARALLEL;
--*
INSERT INTO CFI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71927, 'CARVER', 'JEFFERAY', 'W', 'P.O. BOX 85', NULL, 'SCITUATE', '23', '25', '02066');
INSERT INTO CfI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71928, 'CARVER', 'JEFFERAY', 'W', 'P.O. BOX 85', NULL, 'SCITUATE', '23', '25', '02066');
INSERT INTO Cfi ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71932, 'DESMOND', 'MICHAEL', 'J', '105 MAPLEWOOD RD.', NULL, 'LYNN', '9', '25', '01904');
INSERT INTO CFI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71933, NULL, NULL, NULL, '114 MAC ARTHUR DRIVE', NULL, 'NEW BEDFORD', '5', '25', '02740');
INSERT INTO CFI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71934, NULL, NULL, NULL, '35 BLOSSOM ST.', NULL, 'FAIRHAVEN', '5', '25', '02719');
INSERT INTO CFI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71935, 'GARCIA', 'MANUEL', 'J', '28 E BLVD', NULL, 'ONSET', '23', '25', '02558');
INSERT INTO CFI ( CFI_ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, ADDRESS_1,
ADDRESS_2, CITY, COUNTY, STATES, POSTAL_CODE ) VALUES (
71936, 'GARCIA', 'MANUEL', 'J', '28 E BLVD', NULL, 'ONSET', '23', '25', '02558');
--*
CREATE INDEX CFI_ADDR1_CTX_I ON CFI
(ADDRESS_1)
INDEXTYPE IS CTXSYS.CONTEXT;
--*
select a.last_name, a.cfi_id, b.last_name lname, b.cfi_id, a.address_1, b.address_1
from cfi a, cfi b
where a.last_name = b.last_name
and a.city = b.city
and a.cfi_id <> b.cfi_id
and a.states = b.states
and a.postal_code = b.postal_code
and a.last_name is not null
and b.last_name is not null
and contains(a.address_1, '?'||b.address_1) > 0

Hi Tom,
  Sorry about the incorrect previous test data. I did change the last two insert statements address_1 column values and did the following. Here is my typo data and not getting cfi_id = 71935 and 71936 in the result. Thanks for your immediate response.
Leo.


SQL> column address_1 format a20
SQL> column last_name format a10
SQL> column lname format a10
SQL> select cfi_id, last_name, address_1 from cfi;

    CFI_ID LAST_NAME  ADDRESS_1                                                 
---------- ---------- --------------------                                      
     71927 CARVER     P.O. BOX 85                                               
     71928 CARVER     P.O. BOX 85                                               
     71932 DESMOND    105 MAPLEWOOD RD.                                         
     71933            114 MAC ARTHUR DRIVE                                      
     71934            35 BLOSSOM ST.                                            
     71935 GARCIA     28 E BVLD                                                 
     71936 GARCIA     28 E BLVD                                                 

7 rows selected.

SQL> column address_1 format a14
SQL> r
  1  select a.last_name, a.cfi_id, b.last_name lname,
  2         b.cfi_id, a.address_1, b.address_1
  3    from cfi a, cfi b
  4   where a.last_name = b.last_name
  5     and a.city = b.city
  6     and a.cfi_id <> b.cfi_id
  7     and a.states = b.states
  8     and a.postal_code = b.postal_code
  9     and a.last_name is not null
 10     and b.last_name is not null
 11*    and contains(a.address_1, '?'||b.address_1) > 0

LAST_NAME      CFI_ID LNAME          CFI_ID ADDRESS_1      ADDRESS_1            
---------- ---------- ---------- ---------- -------------- --------------       
CARVER          71928 CARVER          71927 P.O. BOX 85    P.O. BOX 85          
CARVER          71927 CARVER          71928 P.O. BOX 85    P.O. BOX 85          

SQL> spo off
 

Thanks.
Leo.

Thanks again for the great followup!

I went back and used the {} characters and it works great, no problems!

FYI... I did try recreating the preference with the Lexer and I still could not retrieve the correct results. When performing the test on one file, in a new table, it worked, but when I applied the preference and re-indexed my main table that has over 8000 records with different file formats, I still had issues with the content searching.

I can go back and provide SQL... but if the {} characters work well (which appears so), then I will just code my package to include them in the final cursor sql statement

Thanks Again!

Deanna

HI Tom

I have been having issues with brackets {} and the wildcard % sign.

For example..
Say you have three independant documents

Content
Document 1 : SF
Document 2: SF-CA
Document 3: SFCoast

Query 1
select * from table where contains (url, 'SF%')>0
Returns all 3 documents (great!)

Query 2
select * from table where contains (url, 'SF-%')>0
DRG-51030:wildcard query expansion resulted in too many terms


So, use the {}
select * from table where contains (url, '{SF-%}')>0
Document 1 and 2 return, not document 3
This query is problematic because SF- and SF are not the same word to the end user

select * from table where contains (url, '{SF%}')>0
Document 1 and 2 retuen, but not Document 3 (No SFCoast)
This query is problematic because all 3 documents should return with the wildcard

Any help you can provide is appreciated

Thanks!

ops$tkyte@ORA9IR2> select * from t where contains (url, 'SF\-%')>0 ;
 
URL
-------------------------
SF-CA
 


where you looking to just escape the - (which is a special character to text)? 

Hi Tom

Thanks again for the prompt reply!

When I try...
select * from t where contains (url, 'SF\-%')>0;
where t= my table oi_lims_report_search I get the same error
wildcard query expansion resulted in too many terms

SQL> select * from oi_lims_report_search where contains (url, 'SF\-%')>0;
select * from oi_lims_report_search where contains (url, 'SF\-%')>0
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: interMedia Text error:
DRG-51030: wildcard query expansion resulted in too many terms


Oracle version: 8.1.7.4.0
Index Create command used inso filter

create index INSO_INDEX on OI_LIMS_REPORT_SEARCH ( url )
    indextype is ctxsys.context
    parameters ( 'Datastore OI_PREF filter ctxsys.inso_filter' )

I would like to set up the contains string such that when  searching by SF-% the query returns SF-CA, SF-MA, SF-WA, etc...

And when searching by SF% all results return: SF-CA, SFCoast, SF, SF-MA, SF-WA etc...

 

normally, - isn't "part of the index", so it is having to sort of try to glue things together.  consider:

ops$tkyte@ORA9IR2> create table t ( data varchar2(50) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'How-Now Brown-Cow' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 'How Now Brown Cow' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec ctx_ddl.drop_preference('my_lexer' )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2      ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
  3      ctx_ddl.set_attribute('my_lexer','printjoins','-');
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create index t_ind_ctx on t(data) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2> select token_text from       DR$T_IND_CTX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
BROWN
COW
HOW
NOW
 
<b>Normally, the HOW-NOW is not a "word", HOW and NOW are -- we'd have to put it all back together and so extra work to find HOW-NOW.  So, we can:</b>


ops$tkyte@ORA9IR2> drop index t_ind_ctx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> create index t_ind_ctx on t(data) indextype is ctxsys.context <b>parameters('lexer my_lexer');</b>
 
Index created.
 
ops$tkyte@ORA9IR2> select token_text from       DR$T_IND_CTX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
BROWN
BROWN-COW
COW
HOW
HOW-NOW
NOW
 
6 rows selected.
 
<b>and now - is considered "part of a word"</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where contains( data, 'How\-%' ) > 0;
 
DATA
--------------------------------------------------
How-Now Brown-Cow


<b>that should limit the number of terms returned with the SF\-%</b> 

Hi Tom

Thanks again for the detailed example, the print joins are now working correctly on my end.

I didn't realize that even after using the printjoins and defining a lexer that you still had to escape the - character.

'SF\-%'

I thought that once you defined the lexor you could then run 'SF-%' within the contains function.

Again thanks for providing examples! Its greatly appreciated!

Hi Tom,
In your page, "Tom's latest Followup", If we click this link it jumps to your latest followup-again i have to move up to see reviewer question- instead at question.
I feel it has to be jumped to the latest answered question is right place.

I would like to trap the following error. However, the Oracle JDBC error throws an SQLException and the error code is ORA-29902. I am able to get the 29902 error code, but the message of the exception also includes:

ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

How would I parse through the SQLException and trap the DRG-51030 error (without performing a search on the message text for this occurance). Also, is there a better way to trap the oracle text error?

Sorry if i did not phrase my questions accurately.

I was wondering if there was a way to cast the SQLException into an Oracle Specific exception and then get an actual handle on the Oracle Text error.

Thanks Tom.

Hi Tom,

your articles are good.
Have a query...
The follow is part of an XML stored in a ORACLE CLOB data.

<ServiceOrder TrackingId="ORD568" UpdateVersion="4" WFInstanceId="135343" WFMetricsTranx="id=12210;startTime=1047437553674;subscriberOid=4002413;userOid=14937;" action="U" isEditable="Y" version="1.0">

This is the root node of the XML
I want to search the table for all XML that contains tracking_id="ORD586"

How do i do that?

Regards,
Vivek R

Hi Tom,

Thanks for the Extraordinarly quick feed back. Frankly never expected it :-)

The link which u sent gives the syntax of the INPATH and WITHIN clauses. But it does not tell me how to use them within a SELECT clause. Will be very very grateful if u do that...

Hi Tom,

We have a table where one of the columns is a varchar2(9). This column contains any character imaginable. So, for instance, we may have a value of ".22354554" in this column for one of the rows. Let's say we have a text index of the ctxsys.context type for this column, and we want to do searches using numbers only. So in the example above, we would enter SELECT * FROM MYTABLE WHERE CONTAINS(acol, '22354554') > 0. What we get is no rows returned, because the period (.) is treated as part of the whole token. We can see this is the case in the token_text column of the DR$USR_ACOL_IDX$I. My question is: Is there a way to create a custom token separator for the period (.) character?

Thanks.

Fred

the fact is, it is seeing that the "." is relevant in the context of the string.

Text considers tokens and tokens are white space separated words, number, acronyms and the like.  It uses the NLS settings to determine what they are by default -- we can override that.

But on a varchar2(9), why are you using context anyway?  Doesn't seem like any of the values could be tokenized?

anyway, 3 examples -- one shows the issue the other two ways around it

ops$tkyte@ORA9IR2> create table t ( x varchar2(9) );
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( '.22354554' );
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( '.nyet' );
1 row created.
<b> 
ops$tkyte@ORA9IR2> create index t_idx on t(x) indextype is ctxsys.context;
Index created.
 
ops$tkyte@ORA9IR2> SELECT * FROM T WHERE CONTAINS(x, '22354554') > 0;
no rows selected

Because, by default .22354554 is a NUMBER (as would be 1,001,032.235) </b>

ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
.22354554
NYET
 
ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.

<b>so, lets tell text to use '@' instead of ., to see numbers for example:</b>
 
ops$tkyte@ORA9IR2> exec ctx_ddl.create_preference( 'mylex', 'BASIC_LEXER' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec ctx_ddl.set_attribute( 'mylex', 'numjoin', '@' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec ctx_ddl.set_attribute( 'mylex', 'numgroup', '@' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x)
  2  indextype is ctxsys.context
  3  parameters( 'LEXER mylex' );
Index created.
 
ops$tkyte@ORA9IR2> SELECT * FROM T WHERE CONTAINS(x, '22354554') > 0;
 
X
---------
.22354554
 
ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
22354554
NYET

<b>or, if we need to do wildcard searches alot, this might make even more sense:</b>
 
ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.
 
ops$tkyte@ORA9IR2> exec ctx_ddl.drop_preference( 'mylex' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec ctx_ddl.create_preference( 'SUBSTRING_PREF', 'BASIC_WORDLIST' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec ctx_ddl.set_attribute( 'SUBSTRING_PREF', 'SUBSTRING_INDEX', 'TRUE' );
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x)
  2  indextype is ctxsys.context
  3  parameters( 'wordlist SUBSTRING_PREF' );
Index created.
 
ops$tkyte@ORA9IR2> SELECT * FROM T WHERE CONTAINS(x, '%22354554') > 0;
 
X
---------
.22354554
 
ops$tkyte@ORA9IR2> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
.22354554
NYET
 
ops$tkyte@ORA9IR2> select * from dr$t_idx$p;
 
PAT_PART1 PAT_PART2
--------- ---------
          .22354554
.         22354554           <<<<<====== it used this.
.2        2354554
.22       354554
.2235     4554
.223      54554
.22354    554
          NYET
N         YET
 
9 rows selected.

 

Just what I need. Thanks again!

Fred

Hi,

I've got the following:
exec ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
exec ctx_ddl.set_attribute('my_lexer','printjoins','-_~!@#$%^&*(){}[],=?\;|><');

CREATE INDEX txtidx_rep_ttl
ON rep(rep_ttl)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('LEXER my_lexer SYNC(EVERY "SYSDATE+5/1440") TRANSACTIONAL');

My query:

SELECT r.*
FROM rep r
where
contains(r.rep_ttl, '%\_%, %\%%') > 0

returns me a funny result.

I've got a value in rep_ttl like this:
~!@#$%^&*()_+{}|:"<>?/.,';\][=-

We want to replace a wild card search implemented with LIKE '%...%' approach (which is slow) with the Oracle Text solution. I can't use {} operator with wild card search, so I have to escape them with backslash character.

I've got it working with -, _ characters, but it does not work properly with the rest of them, such as ~!@#$%^&*(){}[],=?\;|><.

BTW, do I have to specify all escapable characters in one statement (as I did above), or is it possible to make it with separate statements:

I appreciate for your help in advance.

ops$tkyte@ORA10G> exec ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec ctx_ddl.set_attribute('my_lexer','printjoins','-_~!@#$%^&*(){}[],=?\;|><');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> create table t ( x varchar2(30) );
 
Table created.
 
ops$tkyte@ORA10G> insert into t values ( 'Hello () there' );
 
1 row created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> CREATE INDEX t_idx ON t(x) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER my_lexer' );
 
Index created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where contains( x, '%\(\)%' ) > 0;
 
X
------------------------------
Hello () there
 


<b>define "does not work properly"?</b> 

Hi Tom,

Found another interesting thread !

Should I  use intermediate context for searching large number of rows for specified string – in 1-2 million rows of clinical notes ?

Sample code would be :-

spool text_search;

REM DROP INDEX intermedia_clinwp;
REM CREATE INDEX intermedia_clinwp ON CLINWP(TEXT) indextype REM is ctxsys.context;

REM cant create index in prs tablespace - invalid for Domain index !!

column text heading 'Text' format a40 word_wrapped;
column wp_id_no heading 'WP no' format 99999999;
column cno_int_patid heading 'Crn' format a8;
column cno_dt heading 'Dated';

define Search_for =  &search_for;

select cno_int_patid,cno_dt,text,wp_id_no from clinwp,clinote
       where contains(text,'&search_for') > 0  and
       wp_id_no = cno_wp_id_no
       order by cno_int_patid,wp_id_no,line;

Is working fine on my test database.

Also, if I stupidly search for and    or  or  
Query fails with

Enter value for search_for: and
old   2:        where contains(text,'&search_for') > 0  and
new   2:        where contains(text,'and') > 0  and
select cno_int_patid,cno_dt,text,wp_id_no from clinwp,clinote
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine 
ORA-20000: interMedia Text error: 
DRG-50901: text query parser syntax error on line 1, column 1   


SQL> exit

As usual, comments would be appreciated 

Thanks
Ian
 

for how to specify the tablespace for the MANY components of the text index (many tables are created for this index).  You can create it in any tablespace you want.

Hi Tom,

Thanks for the prompt response! Your commitment to this forum is outstanding!

In addition to the column's value I specied before (~!@#$%^&*()_+{}|:"<>?/.,';][=-), I've inserted the second row into the table:

test& for fa%ncy chara.ct*e%rs an-th_er~ is! & , a l@o#t of $ sp%e c^eia&l ch*a(r a)c{ }t[e r]s ,h e=r ?e so le; t us| te>s t< all of them

Try to use query like this, it did not work for me:
select * from t where contains( x, '%&%' ) > 0
(I'm getting tons of records where an ampersand character does not appear in the column)
or

select * from t where contains( x, '%.%' ) > 0
(result set is empty)

However it works well for all other special characters, so somehow it does not like the ampersand and dot.

Thanks,

onsh76

select token_text from DR$T_IDX$I;

look at your tokens- not everything is in there.

you have somethings as printjoins and by default as puncuation.  That means sometimes the character won't make it into the string, the token.  In particular your puncuation of 

ops$tkyte@ORA10G> exec ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec ctx_ddl.set_attribute('my_lexer','printjoins','-_~!@#$%^&*(){}[],=?\;|><.');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec ctx_ddl.set_attribute('my_lexer','punctuations',' ' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> create table t ( x varchar2(4000) );
 
Table created.
 
ops$tkyte@ORA10G> insert into t values ( 'Hello () there' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'Hello!There' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'Hello.There' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'Hello&There' );
 
1 row created.
 
ops$tkyte@ORA10G> insert into t values ( 'test& for fa%ncy chara.ct*e%rs an-th_er~ is! & , a l@o#t of $ sp%e c^eia&l ch*a(r a)c{ }t[e r]s ,h e=r ?e so le; t us| te>s t< all of them' );
 
1 row created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G> CREATE INDEX t_idx ON t(x) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER my_lexer' );
 
Index created.
 
ops$tkyte@ORA10G> select token_text from DR$T_IDX$I;
 
TOKEN_TEXT
----------------------------------------------------------------
$
&
()
,
,H
?E
A)C{
AN-TH_ER~
CH*A(R
CHARA.CT*E%RS
C^EIA&L
E=R
FA%NCY
HELLO
HELLO!THERE
HELLO&THERE
HELLO.THERE
IS!
L@O#T
LE;
R]S
SP%E
T
T<
TE>S
TEST&
THEM
US|
}T[E
 
29 rows selected.
 
ops$tkyte@ORA10G> select * from t where contains( x, '%\(\)%' ) > 0;
 
X
-------------------------------------------------------------------------------
Hello () there
 
ops$tkyte@ORA10G> select * from t where contains( x, '%\!%' ) > 0;
 
X
-------------------------------------------------------------------------------
Hello!There
test& for fa%ncy chara.ct*e%rs an-th_er~ is! & , a l@o#t of $ sp%e c^eia&l ch*a
(r a)c{ }t[e r]s ,h e=r ?e so le; t us| te>s t< all of them
 
 
ops$tkyte@ORA10G> set define off
ops$tkyte@ORA10G> select * from t where contains( x, '%\&%' ) > 0;
 
X
-------------------------------------------------------------------------------
Hello&There
test& for fa%ncy chara.ct*e%rs an-th_er~ is! & , a l@o#t of $ sp%e c^eia&l ch*a
(r a)c{ }t[e r]s ,h e=r ?e so le; t us| te>s t< all of them
 
 
ops$tkyte@ORA10G> select * from t where contains( x, '%\.%' ) > 0;
 
X
-------------------------------------------------------------------------------
Hello.There
test& for fa%ncy chara.ct*e%rs an-th_er~ is! & , a l@o#t of $ sp%e c^eia&l ch*a
(r a)c{ }t[e r]s ,h e=r ?e so le; t us| te>s t< all of them
 
 
 

Tom,
I appologies for the typo in the previous posting:
it did not work well for &(ampersand) and !(exclamation sign).

Regards,

onsh76

Hi Tom,
I appreciate for your help!
Yes, the problem was in punctuation. Additionally I found that several tools I'm using behaive differently (sqlplus, toad, golden32) with Oracle Text query. For example, the query with ampersand was not manageble by toad and golden32, however it worked well with the reference tool (sqlplust).

BTW, is there any constraints we have to know related to using Oracle Text with JDBC Type 4 driver? Sorry, you may find as a silly question, but I know that the sql-99 usage of joins in FROM clause was not supported by JDBC Type 4 driver (8.1.7 and 9i for sure).

Oh, yeah... Unfortunately new sql sintax was not available in 8.1.7:
Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)
Part Number A83724-01
Outer Joins

Oracle's JDBC drivers do not support outer join syntax: {oj outer-join}. The workaround is to use Oracle outer join syntax:

Instead of:

Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
("SELECT ename, dname
FROM {OJ dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno}
ORDER BY ename");

Use Oracle SQL syntax:

Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery
("SELECT ename, dname
FROM emp a, dept b WHERE a.deptno = b.deptno(+)
ORDER BY ename");

The same deal in Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)
Part Number A96654-01.

Again, is there any constraints related to Oracle Text in version 10G when interaction is performed over JDBC Type 4 connection.

Thanks,

onsh76

Hi Tom,
I appreciate for your great assistance!
I've got additional questions...

Here is a definition for printjoins:
exec ctx_ddl.set_attribute('my_lexer','printjoins','-_~!@#$%^&*(){}[],=?\;|><:/"`.+');

1.I'd like to have words with single quotes to be indexed as is. Will you please provide a correction to the above definition.

2. Will you please provide an exampe for wild card search where the only search criteria is a single quote. Is an example below the right one?

contains(r.title, '%\''%') > 0

3. I've got around 30 Oracle Text-indexed columns VARCHAR2(2000) in the query. Only 7 of them are located in the same table. All indices are independent: there is no multi-column indices there. We implemented two searching solutions: Oracle Text and the one with general "LIKE statements". We loaded around 50, 000 records in the tables. The Oracle Text query is damn slow, I have no idea why the LIKE-based query executes within a second or two and the Oracle Text one runs at least 2 minutes or more with the same parameters.

3.1 Will a multi-column index give me a better performance rather then a 7 separate ones? What is the rule of thumb?

3.2 Why is Oracle Text-based query slower then the LIKE-based one? Will you provide me with some ideas on how to fix this issue? Any suggestions regarding to execution plan? What to check and what to tweak...

Thanks in advance.

Found the solutions for questions #1 and 2:

1. exec ctx_ddl.set_attribute('my_lexer','printjoins','-_~!@'||''''||'#$%^&*(){}[],=?\;|><:/"`.+');

2. Do not escape the single quote at all:
contains(r.my_column, '%''%') > 0

Will you please be so kind to review my question #3.

Thanks,

onsh76

Hi Tom,
After creating printjoint index on 8million records, i have started optimizing this index by using the following statement
begin
ctx_ddl.optimize_index
( idx_name => 'quick_text', optlevel => ctx_ddl.optlevel_fast );
end;

It has been running since more than 5 hours, Can you please suggest me to run this optimization fast.

Advance Thanks...
Regards,
A.Anil Kumar

Hi Tom,

I have 2 questions on oracle text(Intermedia context):
1) When we are trying to search a string more than 4000 characters in single quotes(' ') oracle is throwing error "ORA-1704:string literal too long".
Is the oracle's restriction or is there any alternate way to do this?
2) when iam running the following query it is taking too long time for 100 records out of 8 million records.
SELECT * FROM gt_view WHERE contains (
batch_id, 'C__1111111_10 OR
C__15555532_10 OR
C__15556691_10 OR
C__17726138_10 OR
C__17726139_10 OR
C__17726141_10 OR
C__17726213_10 OR
C__17726218_10 OR
C__17726239_10'
) > 0;
Is there anyway we can optimize this to reduce the time. To create the oracle text index i have used the following options :
begin
ctx_ddl.create_preference(’mylex’, ’BASIC_LEXER’);
ctx_ddl.set_attribute(’mylex’, ’printjoins’, ’_-’);
ctx_ddl.set_attribute ( ’mylex’, ’index_themes’, ’NO’);
ctx_ddl.set_attribute ( ’mylex’, ’index_text’, ’YES’);
end;
By changing the above options can we optimize.

Thanks in Advance...
Regards,
A.Anil Kumar

I ran the tkprof, the tkprof output is
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 74 (recursive depth: 1)
********************************************************************************

SELECT * FROM gt_view WHERE contains (
batch_id, 'C__10054931_10') > 0

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 9.71 9.88 37755 44165 0 0
Execute 1 9.77 9.94 37752 43913 0 0
Fetch 2 0.00 0.00 1 16 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 19.48 19.83 75508 88094 0 2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 74

Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID GT_VIEW
2 DOMAIN INDEX GT_VIEW_BATCH_ID_TEXT


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net more data to client 1 0.00 0.00
SQL*Net message from client 2 12.86 13.25
db file sequential read 1 0.00 0.00
********************************************************************************

BEGIN :p := CTX_QUERY.PREFERENCE;END;

My search query is going to be like this
SELECT * FROM gt_view WHERE contains (
batch_id, 'A2BP1 OR A2BP1 OR hCG1811870 OR A2BP1 OR hCG1982595 OR AAA1 OR hCG2010049 OR AACSL OR hCG2041540 OR AACS') >0;

When i try to run the above query i am getting very fast the results. But whatever the query i have given in tkprof it is taking to long. Why?

Thanks in Advance...
Regards,
A.Anil Kumar

GT_VIEW is a table name.
We have started debugging why it is taking so long time.

Thanks in Advance..
Regards,
A.Anil Kumar

Hi Tom,

Is printjoin going to degrade the performance? Is the load will matter alot when we are searching printjoin in oracle text. When iam trying to search the following query on 100,000 rows it is giving the results very fast, but on 40,00,000 rows it is taking so long time. I have take the sql trace also , it is using the same explain plan. Only the difference is it is having two much physical read. What is the problem? Or do we need to take any precautions while creating printjoin index?

EX:select * from gt_view_text where contains(batch_id, 'C__26130941_10 OR C__29017342_10 OR C__29308966_10 OR C__29367895_10 ORC__30672104_10 OR C__31677339_10 OR C__33209002_10 OR C__34803592_10 OR C___2795664_10 OR C___3050181_10')>0;

gt_view_text is a table name.

select *
from
gt_view_text where contains(batch_id, 'C__26130941_10 OR C__29017342_10 OR
C__29308966_10 OR C__29367895_10 ORC__30672104_10 OR C__31677339_10 OR
C__33209002_10 OR C__34803592_10 OR C___2795664_10 OR C___3050181_10')>0

TKPROF OUTPUT:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 171 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 2 0.01 0.00 0 13 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.03 0 184 0 8

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 52

Rows Row Source Operation
------- ---------------------------------------------------
8 TABLE ACCESS BY INDEX ROWID GT_VIEW_TEXT
8 DOMAIN INDEX GTVW_TEXT_BATCH_ID_TEXT

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net more data to client 2 0.00 0.00
SQL*Net message from client 2 23.59 24.03
********************************************************************************

autotrace output :

Statistics
----------------------------------------------------------
217 recursive calls
0 db block gets
272881 consistent gets
2 physical reads
0 redo size
6523 bytes sent via SQL*Net to client
277 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed


Thanks in Advance,
Regards,
A.Anil Kumar

Hi Tom,

could you please help me with how to search for a text string with special characters in a context indexed column?

set escape '\'

drop table mfg;
create table mfg (manuf varchar2(25));

insert into mfg values('Oracle');
insert into mfg values('ORAND Systems');
insert into mfg values('Orad Hi-Tec Systems');
insert into mfg values('Orative');

insert into mfg values('ATT');
insert into mfg values('AT\&T');
insert into mfg values('AT Systems');

commit;

CREATE OR REPLACE PROCEDURE mfg_proc
(p_rowid IN ROWID,
p_clob IN OUT NOCOPY CLOB)
AS
BEGIN
FOR r IN
(SELECT manuf
FROM mfg
WHERE ROWID = p_rowid)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 8, 'XXSTART ');
DBMS_LOB.APPEND (p_clob, r.manuf);
DBMS_LOB.WRITEAPPEND (p_clob, 6, ' XXEND');
END LOOP;
END mfg_proc;
/

BEGIN
CTX_DDL.CREATE_PREFERENCE ('mfg_datastore', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('mfg_datastore', 'PROCEDURE', 'MFG_PROC');
CTX_DDL.SET_ATTRIBUTE ('mfg_datastore', 'OUTPUT_TYPE', 'CLOB');
END;
/

drop index search_mfg;


begin
ctx_ddl.create_preference('ravi_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('ravi_lexer','printjoins','&');
end;

create index search_mfg on mfg (manuf)
indextype is ctxsys.context
parameters('lexer ravi_lexer WORDLIST my_wordlist DATASTORE mfg_datastore')

exec dbms_stats.gather_table_stats (USER, 'mfg');

commit;

select SCORE(1) SCORE, manuf as manufacturer_name
from mfg where contains (manuf, 'XXSTART ORA%', 1) > 0
order by SCORE(1) DESC;

How do I search for AT&T?

select SCORE(1) SCORE, manuf as manufacturer_name
from mfg where contains (manuf, 'XXSTART AT&%', 1) > 0
order by SCORE(1) DESC;

ops$tkyte%ORA11GR2> set define off
ops$tkyte%ORA11GR2> select SCORE(1) SCORE, manuf as manufacturer_name
  2    from mfg where contains (manuf, 'XXSTART AT\&%', 1) > 0
  3  order by SCORE(1) DESC;

     SCORE MANUFACTURER_NAME
---------- -------------------------
         6 AT&T

Thanks!

Hi Tom,

Could you please help me with this.

we have a table A with all manufacturers names.
we have a table B with manufacturers names and its keywords.

In the following example, companies like Bind view, Guardian Edge come under Symantec Corporation.

I wanted to find out all the companies in table A which come under Symantec Corporation based on keyword search.

This is a very simple example but in real time there could be hundred of keywords with makes impractical to use like operator.

Keywords are entered by data entry operators.

My problem is how to deal with % (wildcard) in the keyword string. I cannot escape it with printjoins as it is meant to be a wildcard.

DROP TABLE A;

CREATE TABLE A (manuf VARCHAR2(100));

INSERT INTO A VALUES('Symantec Corporation');
INSERT INTO A VALUES('BindView Development Corporation');
INSERT INTO A VALUES('Bind View');
INSERT INTO A VALUES('Bind Software View');
INSERT INTO A VALUES('Bind*View');
INSERT INTO A VALUES('guardian edge');
INSERT INTO A VALUES('guardianEdge');
commit;
select * from A;

DROP TABLE B;

CREATE TABLE B (manuf VARCHAR2(100), KEYWORDS VARCHAR2(100));

INSERT INTO B VALUES('Symantec Corporation','symantec, bindview, bind%view, guardian%edge');
commit;
SELECT * FROM B;

drop procedure proc_A;

CREATE OR REPLACE PROCEDURE proc_A
(p_rowid IN ROWID,
p_clob IN OUT NOCOPY CLOB)
AS
BEGIN
FOR r IN
(SELECT MANUF
FROM A
WHERE ROWID = p_rowid)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 8, 'XXSTART ');
DBMS_LOB.APPEND (p_clob, r.MANUF);
DBMS_LOB.WRITEAPPEND (p_clob, 6, ' XXEND');
END LOOP;
END proc_A;

begin
ctx_ddl.drop_preference ( 'test_datastore' );
end;

BEGIN
CTX_DDL.CREATE_PREFERENCE ('test_datastore', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'PROCEDURE', 'proc_a');
CTX_DDL.SET_ATTRIBUTE ('test_datastore', 'OUTPUT_TYPE', 'CLOB');
COMMIT;
END;

drop index search_manuf_idx;

begin
ctx_ddl.drop_preference ( 'manuf_lexer' );
end;

begin
ctx_ddl.create_preference('manuf_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('manuf_lexer','printjoins','_');
ctx_ddl.set_attribute('manuf_lexer','printjoins','*');
ctx_ddl.set_attribute('manuf_lexer','printjoins','-');
ctx_ddl.set_attribute('manuf_lexer','printjoins','(');
ctx_ddl.set_attribute('manuf_lexer','printjoins',')');
ctx_ddl.set_attribute('manuf_lexer','printjoins',',');
ctx_ddl.set_attribute('manuf_lexer','printjoins','&');
ctx_ddl.set_attribute('manuf_lexer','printjoins','"');
end;


create index search_manuf_idx on A(MANUF)
indextype is ctxsys.context
PARAMETERS ('DATASTORE test_datastore LEXER manuf_lexer');

exec dbms_stats.gather_table_stats (USER, 'A');

Following select should give all the rows from table A. But only returns 3 of them.

select * from A where contains(MANUF, (select keywords from B where manuf='Symantec Corporation')||'%')>0;

Thanks!

Hi Tom,

Yes % in the string is a a wild card search.
What the operator means is "bind<what ever word or space>view" in table A represents Symantec Corporation in table B.

So the requirement is:

To find all the manufacturers (table A) and link to Parent manufacturer (table B) based on keyword search.
For example, for Microsoft/Cisco or oracle there could be hundreds of companies which have merged. Meaning hundreds of key words.

What do you think is a best way to do this?

Tom,
I tried making it master/detail but still dint work. may be i am missing something. could you please help me out with this.

Thanks!

CREATE TABLE A1 (manuf VARCHAR2(100));
INSERT INTO A1 VALUES('Symantec Corporation');
INSERT INTO A1 VALUES('BindView Development Corporation');
INSERT INTO A1 VALUES('Bind View');
INSERT INTO A1 VALUES('Bind Software View');
INSERT INTO A1 VALUES('Bind*View');
INSERT INTO A1 VALUES('guardian edge');
INSERT INTO A1 VALUES('guardianEdge');
commit;
select * from A1;

DROP TABLE B1;
CREATE TABLE B1 (manuf VARCHAR2(100), KEYWORDS VARCHAR2(100));
INSERT INTO B1 VALUES('Symantec Corporation','symantec');
INSERT INTO B1 VALUES('Symantec Corporation','bindview');
INSERT INTO B1 VALUES('Symantec Corporation','bind%view');
INSERT INTO B1 VALUES('Symantec Corporation','guardianedge');
INSERT INTO B1 VALUES('Symantec Corporation','guardian%edge');
commit;
SELECT * FROM B1;

CREATE OR REPLACE PROCEDURE proc_A1
(p_rowid IN ROWID,
p_clob IN OUT NOCOPY CLOB)
AS
BEGIN
FOR r IN
(SELECT MANUF
FROM A1
WHERE ROWID = p_rowid)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 8, 'XXSTART ');
DBMS_LOB.APPEND (p_clob, r.MANUF);
DBMS_LOB.WRITEAPPEND (p_clob, 6, ' XXEND');
END LOOP;
END proc_A1;

begin
ctx_ddl.drop_preference ( 'test_datastore1' );
end;

BEGIN
CTX_DDL.CREATE_PREFERENCE ('test_datastore1', 'USER_DATASTORE');
CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'PROCEDURE', 'proc_a1');
CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'OUTPUT_TYPE', 'CLOB');
COMMIT;
END;

begin
ctx_ddl.drop_preference ( 'manuf_lexer1' );
end;


begin
ctx_ddl.create_preference('manuf_lexer1','BASIC_LEXER');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','_');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','*');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','-');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','(');
ctx_ddl.set_attribute('manuf_lexer1','printjoins',')');
ctx_ddl.set_attribute('manuf_lexer1','printjoins',',');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','&');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','"');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','.');
ctx_ddl.set_attribute('manuf_lexer1','printjoins','!');
end;

drop index search_manuf_idx1;

create index search_manuf_idx1 on A1(MANUF)
indextype is ctxsys.context
PARAMETERS ('DATASTORE test_datastore1 LEXER manuf_lexer1');

exec dbms_stats.gather_table_stats (USER, 'A1');

Tom,

I am not tied to a particular way of doing this search. I am trying to put together a working model for this problem.

When operator enters guardian%edge which means he wants all the manufacturers with name starting with 'guardian' and ending with 'edge' tied to Semantec Corp. What i put in there is some sample test data. Currently i get comma delimited keywords to be searched against a particular company which i load into a table. The implementation part is up to me how I translate/interpret that % sign.

I am not sure if I can simplify it further in words but let me try and state it in another way.

Here is the problem statement.

Input:

1) Two column spread sheet col1: Manufacturer name col2: comma separated keywords with simple words as well as words with % symbol which means 'do a wild card search'.

2) Table with list of Manufacturer names

Output:

Search the table in 2) with the given keywords in 1) and associate Manufacturer name in 2) with Manufacturer in 1)

I am open to any other way to do this.

Thanks for your time.

I think I understand Ravi B's problem. What is shown as table b1 is actually user-inputted search criteria that he has put into a table. I believe he wants to use those keywords to search for matching manufacturer names in table a1. Instead of a1 and b1, I have used mfg and operator_entry below. I replaced the xxstart and xxend in the procedure with <name> and </name> tags, then created a section group and added an ndata section that uses the section created by the name tags, then used that section group in the index parameters. I did not use the lexer with the printjoins. The new ndata section allows searching of similar spellings beyond what is normally tokenized. For example, you can search for "bind view" and find "bindview" and vice vera. I have provided a revised script, followed by execution of it below. Your readers can also post Oracle Text questions on the OTN Text forum.

-- script:

CREATE TABLE mfg 
  (manuf VARCHAR2(100))
/
INSERT ALL
INTO mfg VALUES ('Symantec Corporation')
INTO mfg VALUES ('BindView Development Corporation')
INTO mfg VALUES ('Bind View')
INTO mfg VALUES ('Bind Software View')
INTO mfg VALUES ('Bind*View')
INTO mfg VALUES ('guardian edge')
INTO mfg VALUES ('guardianEdge')
SELECT * FROM DUAL
/
CREATE TABLE operator_entry 
  (manuf    VARCHAR2(100), 
   KEYWORDS VARCHAR2(100))
/
INSERT ALL
INTO operator_entry VALUES ('Symantec Corporation', 'symantec')
INTO operator_entry VALUES ('Symantec Corporation', 'bindview')
INTO operator_entry VALUES ('Symantec Corporation', 'bind%view')
INTO operator_entry VALUES ('Symantec Corporation', 'guardianedge')
INTO operator_entry VALUES ('Symantec Corporation', 'guardian%edge')
SELECT * FROM DUAL
/
CREATE OR REPLACE PROCEDURE proc_mfg
  (p_rowid IN            ROWID,
   p_clob  IN OUT NOCOPY CLOB)
AS
BEGIN
  FOR r IN
    (SELECT MANUF
     FROM   mfg
     WHERE  ROWID = p_rowid)
  LOOP
    DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
    DBMS_LOB.APPEND (p_clob, r.MANUF);
    DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
  END LOOP;
END proc_mfg;
/  
BEGIN
  CTX_DDL.CREATE_PREFERENCE ('test_datastore1', 'USER_DATASTORE');
  CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'PROCEDURE', 'proc_mfg');
  CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'OUTPUT_TYPE', 'CLOB');
  COMMIT;
END;
/
begin
  ctx_ddl.create_section_group ('ndata_group', 'basic_section_group');
  ctx_ddl.add_ndata_section    ('ndata_group', 'name', 'name');
end;
/
create index search_manuf_idx1 on mfg (MANUF)
indextype is ctxsys.context
PARAMETERS 
  ('DATASTORE      test_datastore1 
    section group  ndata_group')
/
exec dbms_stats.gather_table_stats (USER, 'MFG')
column mfg_manuf format a32
column oe_manuf  format a32
column keywords  format a13
select mfg.manuf mfg_manuf, 
       operator_entry.manuf oe_manuf, 
       operator_entry.keywords
from   mfg, operator_entry
where  contains 
         (mfg.manuf, 
          'ndata (name,' || operator_entry.keywords || ')') > 0
order  by mfg_manuf, oe_manuf, keywords
/
-- cleanup:
exec ctx_ddl.drop_section_group ('ndata_group')
exec ctx_ddl.drop_preference ( 'test_datastore1' )
drop table operator_entry
/
drop table mfg
/

-- execution:

SCOTT@orcl_11gR2> set define off
SCOTT@orcl_11gR2> CREATE TABLE mfg
  2    (manuf VARCHAR2(100))
  3  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO mfg VALUES ('Symantec Corporation')
  3  INTO mfg VALUES ('BindView Development Corporation')
  4  INTO mfg VALUES ('Bind View')
  5  INTO mfg VALUES ('Bind Software View')
  6  INTO mfg VALUES ('Bind*View')
  7  INTO mfg VALUES ('guardian edge')
  8  INTO mfg VALUES ('guardianEdge')
  9  SELECT * FROM DUAL
 10  /

7 rows created.

SCOTT@orcl_11gR2> CREATE TABLE operator_entry
  2    (manuf  VARCHAR2(100),
  3   KEYWORDS VARCHAR2(100))
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO operator_entry VALUES ('Symantec Corporation', 'symantec')
  3  INTO operator_entry VALUES ('Symantec Corporation', 'bindview')
  4  INTO operator_entry VALUES ('Symantec Corporation', 'bind%view')
  5  INTO operator_entry VALUES ('Symantec Corporation', 'guardianedge')
  6  INTO operator_entry VALUES ('Symantec Corporation', 'guardian%edge')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE proc_mfg
  2    (p_rowid IN       ROWID,
  3   p_clob IN OUT NOCOPY CLOB)
  4  AS
  5  BEGIN
  6    FOR r IN
  7    (SELECT MANUF
  8     FROM  mfg
  9     WHERE  ROWID = p_rowid)
 10    LOOP
 11    DBMS_LOB.WRITEAPPEND (p_clob, 6, '<name>');
 12    DBMS_LOB.APPEND (p_clob, r.MANUF);
 13    DBMS_LOB.WRITEAPPEND (p_clob, 7, '</name>');
 14    END LOOP;
 15  END proc_mfg;
 16  /

Procedure created.

SCOTT@orcl_11gR2> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_datastore1', 'USER_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'PROCEDURE', 'proc_mfg');
  4    CTX_DDL.SET_ATTRIBUTE ('test_datastore1', 'OUTPUT_TYPE', 'CLOB');
  5    COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> begin
  2    ctx_ddl.create_section_group ('ndata_group', 'basic_section_group');
  3    ctx_ddl.add_ndata_section    ('ndata_group', 'name', 'name');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> create index search_manuf_idx1 on mfg (MANUF)
  2  indextype is ctxsys.context
  3  PARAMETERS
  4    ('DATASTORE test_datastore1
  5    section group ndata_group')
  6  /

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (USER, 'MFG')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> column mfg_manuf format a32
SCOTT@orcl_11gR2> column oe_manuf  format a32
SCOTT@orcl_11gR2> column keywords  format a13
SCOTT@orcl_11gR2> select mfg.manuf mfg_manuf,
  2       operator_entry.manuf oe_manuf,
  3       operator_entry.keywords
  4  from   mfg, operator_entry
  5  where  contains
  6         (mfg.manuf,
  7          'ndata (name,' || operator_entry.keywords || ')') > 0
  8  order  by mfg_manuf, oe_manuf, keywords
  9  /

MFG_MANUF                        OE_MANUF                         KEYWORDS
-------------------------------- -------------------------------- -------------
Bind Software View               Symantec Corporation             bind%view
Bind Software View               Symantec Corporation             bindview
Bind View                        Symantec Corporation             bind%view
Bind View                        Symantec Corporation             bindview
Bind*View                        Symantec Corporation             bind%view
Bind*View                        Symantec Corporation             bindview
BindView Development Corporation Symantec Corporation             bind%view
BindView Development Corporation Symantec Corporation             bindview
Symantec Corporation             Symantec Corporation             symantec
guardian edge                    Symantec Corporation             guardian%edge
guardian edge                    Symantec Corporation             guardianedge
guardianEdge                     Symantec Corporation             guardian%edge
guardianEdge                     Symantec Corporation             guardianedge

13 rows selected.

SCOTT@orcl_11gR2> -- cleanup:
SCOTT@orcl_11gR2> exec ctx_ddl.drop_section_group ('ndata_group')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> exec ctx_ddl.drop_preference ( 'test_datastore1' )

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> drop table operator_entry
  2  /

Table dropped.

SCOTT@orcl_11gR2> drop table mfg
  2  /

Table dropped.

SCOTT@orcl_11gR2>

I presume that the search criteria is not actually to be stored. Therefore, rather than enter it into a table, it would be better to use Tom Kyte's str2tbl or in_list function or some similar method of converting a comma-separated list of values from a string to a virtual table, as shown in the revised query below.

SCOTT@orcl_11gR2> variable comma_separated_keywords varchar2(200)
SCOTT@orcl_11gR2> exec :comma_separated_keywords := 'symantec,bindview,bind%view,guardianedge,guardian%edge'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select mfg.manuf mfg_manuf,
  2       oe.column_value keywords
  3  from   mfg,
  4       table (cast (str2tbl (:comma_separated_keywords) as mytabletype)) oe
  5  where  contains
  6         (mfg.manuf,
  7          'ndata (name,' || oe.column_value || ')') > 0
  8  order  by mfg_manuf, keywords
  9  /

MFG_MANUF                        KEYWORDS
-------------------------------- -------------
Bind Software View               bind%view
Bind Software View               bindview
Bind View                        bind%view
Bind View                        bindview
Bind*View                        bind%view
Bind*View                        bindview
BindView Development Corporation bind%view
BindView Development Corporation bindview
Symantec Corporation             symantec
guardian edge                    guardian%edge
guardian edge                    guardianedge
guardianEdge                     guardian%edge
guardianEdge                     guardianedge

13 rows selected.

SCOTT@orcl_11gR2>

Many thanks Barbara for your valuable inputs. It certainly helped me a lot! Man! not a day goes by learning new things on asktom! Wonderful host and wonderful readers!!