The error on using to_date and contains Together。very Strange。thanks

[chinahoufei]

First of all,my English is very poor,so somewhere may be i white it unCourtesy or unclearlly.I'm sorry.

Two sql,
sql1:

Select count(*) as cnt From position A,companyinfo B Where A.companyid=B.companyid  And B.servicetype<>'1' And A.LASTREFRESHDATE>=to_date('2010-05-13','yyyy-mm-dd')  And A.CurState='1'  And contains(B.companyname,'aaaa') > 0

sql2:

Select count(*) as cnt From position A,companyinfo B Where A.companyid=B.companyid  And B.servicetype<>'1' And A.LASTREFRESHDATE>=to_date('2010-05-07','yyyy-mm-dd')  And A.CurState='1'  And contains(B.companyname,'aaaa') > 0

Almost the same,The only difference is date .But sql1 is All right,sql2 have a ora-20000 and org-10599 error.

I've no idea of that.Help you see where the problem may be!

PS: if i delete --And contains(B.comp anyname,'aaaa') > 0-- this part,the two sql is all right.

[rski]

Is column B.companyname indexed. If so whit kind of index it is, can you put here sql code creating this index.

Oracle: DRG-10599: column is not indexed

[debasisdas]

The "contains" clause is used for finding specific strings within an Oracle*Text index.

But i am not sure how your first query works on the same table in the same environment (i assume).

Check if you have text index and then re-execute and check.

Please read more here and here.

[chinahoufei]

Thank you, my problem is after I have created an index, but according to different time, sometimes will get an error.

The end result: I made a Mistake with the index created, it is useless. When the query result is not empty,the function contains usesd, and there's an error. When the query result is empty, for the function contains is not used, it will Seems to be correct.

Thank you again for Reply my question.