Unanswered: CATSEARCH Oracle Text index not returning data
We have a basic Oracle Text index setup on a field.
create index search_landusex on PARCEL.address_search(land_use_zoning) indextype is CTXSYS.CTXCAT
ADDRESS_SUFFIX VARCHAR2(50 BYTE)
The distinct values in this field are:
A,ABC,B,C,D,E,F,G,H,I,J,L,M,N,Q,S and an empty value ''
Two questions/problems I am encountering.
1) From what I have researched, CATSEARCH does not support NULLS, which is fine. The columns have empty values which should be able to be queried with a ''. However, I can't get them to return and throw an error.
select objectID, address_suffix from PARCEL.address_search where CATSEARCH(address_suffix,'',NULL) > 0
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 1
How can I return the fields that are empty?
2) The following queries returns all values:
select address_suffix from address_search where Address_Suffix = 'A'
However, the CATSEARCH returns no values. Niether does including trailing wildcard (e.g. 'A*')
select objectID, address_suffix from PARCEL.address_search where CATSEARCH(address_suffix,'A',NULL) > 0
Finally, on a hopefully not totally stupid question, I cannot find anything on what the trailing >0 does on a CATSEARCH, or why the final NULL is required in it.
CATSEARCH(address_suffix,'A',NULL) > 0
It will not work without the NULL in bold above (there are no parameters, or index sets defined) and changing the >0 to > 100000, = 0, etc. etc. do not make a difference.