OraFAQ Forum: Text & interMedia » ORA-29900: operator binding does not exist

Home » Server Options » Text & interMedia » ORA-29900: operator binding does not exist (Oracle 10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-29900: operator binding does not exist [message #472060]

Wed, 18 August 2010 11:08

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

Report message to a moderator

Re: ORA-29900: operator binding does not exist [message #472094 is a reply to message #472060]

Wed, 18 August 2010 13:42

Barbara Boehmer
Messages: 9101
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>

Report message to a moderator

Re: ORA-29900: operator binding does not exist [message #472302 is a reply to message #472060]

Thu, 19 August 2010 15:04

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

Thanks a lot.

Report message to a moderator

Previous Topic:	Optimization taking more and more time each day
Next Topic:	Will "Contain clause" degrade the performance?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 23:18:17 CST 2024