Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    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


    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.

    Thanks very much for any help you can provide.

  2. #2
    Join Date
    Feb 2005
    Provided Answers: 1
    check out CATSEARCH although it doesn't explain the >0!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts