Home » Server Options » Text & interMedia » Need help on CATSEARCH (ORACLE 10G)
Need help on CATSEARCH [message #527519] Tue, 18 October 2011 08:57 Go to next message
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
Junior Member
Hi,

I am trying to implement catserach in my application. I am struggling to use this cat search. Can any one help on this.

first query working fine.


SELECT A.*
FROM Xtable A,
Ytable B,
WHERE A.ID = B.ID
AND CATSEARCH (A.COD, '**XYZ*', null) > 0 -- Hard coded

Second query : giving Oracle text error ORA : 20000

Here I am searching with second tables value

SELECT A.*
FROM Xtable A,
Ytable B,
WHERE A.ID = B.ID
AND CATSEARCH (A.TCOD, '**'||b.cod||'*', null) > 0


Thanks,
Venkataraman .L

Re: Need help on CATSEARCH [message #527520 is a reply to message #527519] Tue, 18 October 2011 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Need help on CATSEARCH [message #527524 is a reply to message #527520] Tue, 18 October 2011 09:23 Go to previous messageGo to next message
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
Junior Member
Ignore my previous queries.


first query working fine. Here I am using only one table


SELECT A.*
  FROM Xtable A,
 WHERE CATSEARCH(A.COD, '**XYZ*', null) > 0 -- Hard coded


Second query : giving Oracle text error ORA : 20000

If I join with another table I am getting the error


SELECT A.*
  FROM Xtable A, Ytable B,
 WHERE A.ID = B.ID
   AND CATSEARCH(A.TCOD, '**' || b.cod || '*', null) > 0


Please help on this

[Updated on: Tue, 18 October 2011 10:23]

Report message to a moderator

Re: Need help on CATSEARCH [message #527527 is a reply to message #527524] Tue, 18 October 2011 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59403
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session, the WHOLE session.

Regards
Michel
Re: Need help on CATSEARCH [message #527555 is a reply to message #527527] Tue, 18 October 2011 15:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7994
Registered: November 2002
Location: California, USA
Senior Member
You are using column cod in one query and column tcod in another query. You also have an extra comma after the table names. I will assume that both of these are typing errors. This is why you should copy and paste, instead of typing.

We also expect you to provide create table and insert statements to reproduce the problem. You also need to provide the complete stack of error message, not just one error number, as there can be different error messages with the same number and we need to see all of the messages.

Catsearch queries with ctxcat indexes have what I consider a major bug. Any time that the optimizer chooses functional invocation, the query will fail, since catsearch queries do not support functional invocation. It looks like that is what you have encountered. This may happen at any time with any catsearch query, depending on the data, statistics, and the execution plan that the optimizer chooses. Because of this, I recommend that ctxcat indexes and catsearch not be used in a production environment.

Ctxcat indexes and catsearch queries have a lot of limitations that context indexes and contains queries do not have. In the past, the major benefit to ctxcat indexes was that they are transactional. However, context indexes can now be created using the transactional parameter. So, there does not appear to be any benefit to ctxcat, but lots of drawbacks.

I have provided a demonstration below that includes the create table and insert statements and index creation statements that you should have provided, a reproduction of the problem, and a better method of obtaining the same results and performance using context and contains instead of ctxcat and catsearch.

-- create table, insert, and index creation statements that you should have provided:
SCOTT@orcl_11gR2> CREATE TABLE Xtable
  2    (id   NUMBER,
  3  	cod  VARCHAR2 (10))
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO Xtable VALUES (1, 'XYZ')
  3  INTO Xtable VALUES (1, 'WXYZ')
  4  INTO Xtable VALUES (1, 'XYZA')
  5  INTO Xtable VALUES (1, 'ABC')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11gR2> CREATE TABLE Ytable
  2    (id   NUMBER,
  3  	cod  VARCHAR2 (10))
  4  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO Ytable VALUES (1, 'XYZ')
  3  SELECT * FROM DUAL
  4  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX Xtable_cod_idx
  2  ON Xtable (cod)
  3  INDEXTYPE IS CTXSYS.CTXCAT
  4  /

Index created.


-- reproduction of problem:
SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A
  3  WHERE  CATSEARCH (A.COD, '**XYZ*', null) > 0
  4  /

        ID COD
---------- ----------
         1 XYZ
         1 WXYZ
         1 XYZA

3 rows selected.

SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A, Ytable B
  3  WHERE  A.ID = B.ID
  4  AND    CATSEARCH (A.COD, '**XYZ*', null) > 0
  5  /

        ID COD
---------- ----------
         1 XYZA
         1 WXYZ
         1 XYZ

3 rows selected.

SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A, Ytable B
  3  WHERE  A.ID = B.ID
  4  AND    CATSEARCH (A.COD, '**' || b.cod || '*', null) > 0
  5  /
SELECT A.*
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation


-- better method:
SCOTT@orcl_11gR2> DROP INDEX Xtable_cod_idx
  2  /

Index dropped.

SCOTT@orcl_11gR2> CREATE INDEX Xtable_cod_idx
  2  ON Xtable (cod)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('TRANSACTIONAL')
  5  /

Index created.

SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A
  3  WHERE  CONTAINS (A.COD, '%XYZ%') > 0
  4  /

        ID COD
---------- ----------
         1 XYZ
         1 WXYZ
         1 XYZA

3 rows selected.

SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A, Ytable B
  3  WHERE  A.ID = B.ID
  4  AND    CONTAINS (A.COD, '%XYZ%') > 0
  5  /

        ID COD
---------- ----------
         1 XYZA
         1 WXYZ
         1 XYZ

3 rows selected.

SCOTT@orcl_11gR2> SELECT A.*
  2  FROM   Xtable A, Ytable B
  3  WHERE  A.ID = B.ID
  4  AND    CONTAINS (A.COD, '%' || b.cod || '%') > 0
  5  /

        ID COD
---------- ----------
         1 XYZ
         1 WXYZ
         1 XYZA

3 rows selected.

SCOTT@orcl_11gR2>



[Updated on: Tue, 18 October 2011 15:06]

Report message to a moderator

Re: Need help on CATSEARCH [message #527571 is a reply to message #527555] Tue, 18 October 2011 23:39 Go to previous message
venkatlvr
Messages: 5
Registered: October 2011
Location: DUBAI
Junior Member
Thank you very much ... Barbara.

Its really helped me. Here after I will post with all details.
Previous Topic: Full-Text Search [intermedia]
Next Topic: SYS Context index is not working for new records
Goto Forum:
  


Current Time: Mon Oct 20 13:53:52 CDT 2014

Total time taken to generate the page: 0.12876 seconds