Forum Stats

  • 3,751,469 Users
  • 2,250,366 Discussions


en inpath finds record, es inpath does not

949946 Member Posts: 2
edited Jul 17, 2012 11:06AM in SQL & PL/SQL
I have an Oracle 11g database (11.2.0), and a table T with a column A of type CLOB. The CLOB contains XML data, which includes - among others - an element with a language code. The XML looks like this:

<tags><tag name="Status"><int>5</int></tag><tag name="Language"><string>en</string></tag></tags>

I want to find all records of T where the language is "es" (Spanish):
select * from T where contains(A, '{es} INPATH(//tag[@name="Language"]/string)')>0;

Now the funny thing is, this works with all language codes except es. "{en} INPATH" gives me the English record, "{pt} INPATH" gives me the Portuguese record, but "{es} INPATH" returns 0 results. Why?

The following SQL works absolutely fine, returning a record with lang=es:
select extractvalue(xmltype(A), '//tag[@name="Language"]/string') as lang from T where uuid='B4qgMkx_DKwAAAEwcVp5ztFY';

I performed both a length and an asciistr check on the extractvalue results: "es" is 2 ASCII characters, nothing more, nothing less, and by no means different from "en" or "pt". I'm clueless. Can you help?

Edited by: 946943 on Jul 17, 2012 10:20 AM


  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
    Hi and welcome,

    There is a dedicated forum for Oracle Text. It may not be as active as this one, but still you might have more luck in there.

    Personally I am not familar with that technology, but searching through mentioned forum, I found
    Which could look like what you are experiencing.

    Peter Gjelstrup
  • 949946
    949946 Member Posts: 2
    Thanks for the hint, Peter. You're the second person who tells me to check my index for the column A.

    In the meantime I figured out what was wrong: Oracle filters stop words when creating an index. As my installation is in German, German prepositions and pronouns (ich, du, er, sie, es, ...) are in Oracle's BASIC_STOPLIST.

    Here's the query I used to find out:
    select * from ctx_user_index_values where ixv_index_name = '<name of my index>' order by ixv_class, ixv_object , ixv_value;

    With an English installation, I would have had problems with language codes like he, it, or no.
This discussion has been closed.