Home » Server Options » Text & interMedia » ORA-29900: operator binding does not exist (Oracle 10.2.0.4)
ORA-29900: operator binding does not exist [message #472060] Wed, 18 August 2010 11:08 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Hi,

I am running a query using CATSEARH operator and it is keep giving me following error message. CTXCAT index is already created:



CREATE INDEX INDX_BS_ORGNL_SBMSN_SHRT
  ON BS_ORGNL_SBMSN(SHRT_NM)
   INDEXTYPE IS CTXSYS.CTXCAT PARALLEL


 select  * FROM BS_ORGNL_SBMSN
 Where catsearch(SHRT_NM,'ROYAL',1)>0 


ORA-29900: operator binding does not exist
ORA-06553: PLS-307: too many declarations of 'CATSEARCH' match this call
Re: ORA-29900: operator binding does not exist [message #472094 is a reply to message #472060] Wed, 18 August 2010 13:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
The third parameter of the catsearch operator cannot be a number as there is no score functionality with catsearch. It must either be null or a structured query. Please see the demo below that demonstrates invalid and valid syntax.


SCOTT@orcl_11gR2> CREATE TABLE bs_orgnl_sbmsn
  2    (shrt_nm  VARCHAR2 (30))
  3  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO bs_orgnl_sbmsn VALUES
  2    ('royal')
  3  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX INDX_BS_ORGNL_SBMSN_SHRT
  2    ON BS_ORGNL_SBMSN(SHRT_NM)
  3  	INDEXTYPE IS CTXSYS.CTXCAT PARALLEL
  4  /

Index created.

SCOTT@orcl_11gR2> -- invalid syntax:
SCOTT@orcl_11gR2> select * FROM BS_ORGNL_SBMSN
  2  Where  catsearch (SHRT_NM,'ROYAL', 1 ) > 0
  3  /
Where  catsearch (SHRT_NM,'ROYAL', 1 ) > 0
       *
ERROR at line 2:
ORA-29900: operator binding does not exist
ORA-06553: PLS-307: too many declarations of 'CATSEARCH' match this call


SCOTT@orcl_11gR2> -- valid syntaxes:
SCOTT@orcl_11gR2> select * FROM BS_ORGNL_SBMSN
  2  Where  catsearch (SHRT_NM,'ROYAL', NULL ) > 0
  3  /

SHRT_NM
------------------------------
royal

1 row selected.

SCOTT@orcl_11gR2> select * FROM BS_ORGNL_SBMSN
  2  Where  catsearch (SHRT_NM,'ROYAL', '' ) > 0
  3  /

SHRT_NM
------------------------------
royal

1 row selected.

SCOTT@orcl_11gR2> 

Re: ORA-29900: operator binding does not exist [message #472302 is a reply to message #472060] Thu, 19 August 2010 15:04 Go to previous message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Thanks a lot.
Previous Topic: Optimization taking more and more time each day
Next Topic: Will "Contain clause" degrade the performance?
Goto Forum:
  


Current Time: Thu Apr 17 07:47:35 CDT 2014

Total time taken to generate the page: 0.16643 seconds