SEO

Hello,
we encountered a very strange problem with searching in our XMLType
database.

Our configuration is:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Below you will find a very simple example that demonstrates the problem.
It seems that a diacritic in an attribute confuses Oracle, and makes
some record "invisable". We expect the query to find both records in the
table.

Please try this and confirm that the problem is real, or that we have
some misconception about Oracle. (and if so: what do we do wrong?)

Also we are very much interested to know how Oracle 10 handles this.

The input is UTF8 ,and NSL_LANG is set to American_america.AL32UTF8

Here is the example script:
================================================== ==
drop table my_table;

create table my_table ( doc_id NUMBER, data_char xmltype );

insert into my_table values( 1, XMLType(
'<document>
<au first="Rüdiger" id="1">Rüdiger</au>
</document>' ) );

insert into my_table values( 2, XMLType(
'<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>' ) );

create index my_table_idx on my_table(data_char) indextype is
ctxsys.context;

select data_char from my_table where contains(data_char,'1 inpath(
//au/@id )' ) > 0;

Ko van der Sloot wrote:
> Hello,
> we encountered a very strange problem with searching in our XMLType
> database.
>
> Our configuration is:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.4.0 - Production
>
> Below you will find a very simple example that demonstrates the problem.
> It seems that a diacritic in an attribute confuses Oracle, and makes
> some record "invisable". We expect the query to find both records in the
> table.
>
> Please try this and confirm that the problem is real, or that we have
> some misconception about Oracle. (and if so: what do we do wrong?)
>
> Also we are very much interested to know how Oracle 10 handles this.
>
> The input is UTF8 ,and NSL_LANG is set to American_america.AL32UTF8
>
> Here is the example script:
> ================================================== ==
> drop table my_table;
>
> create table my_table ( doc_id NUMBER, data_char xmltype );
>
> insert into my_table values( 1, XMLType(
> '<document>
> <au first="Rüdiger" id="1">Rüdiger</au>
> </document>' ) );
>
> insert into my_table values( 2, XMLType(
> '<document>
> <au first="Ruediger" id="1">Rüdiger</au>
> </document>' ) );
>
> create index my_table_idx on my_table(data_char) indextype is
> ctxsys.context;
>
> select data_char from my_table where contains(data_char,'1 inpath(
> //au/@id )' ) > 0;
On my box:

Connected to Oracle Database 10g Release 10.1.0.3.0
Connected as hardya


SQL>

Table created

1 row inserted

1 row inserted

Index created

DATA_CHAR
--------------------------------------------------------------------------------
<document>
<au first="R?diger" id="1">R?diger</au>
</document>

<document>
<au first="Ruediger" id="1">R?diger</au>
</document>


SQL>

--
Andy

Opinions are mine and do not necessarily reflect those of Advantica
Technologies

Andrew Hardy wrote:
> Ko van der Sloot wrote:
>
>> Hello,
>> we encountered a very strange problem with searching in our XMLType
>> database.
.....
insert into my_table values( 1, XMLType(
'<document>
<au first="Rüdiger" id="1">Rüdiger</au>
</document>' ) );

insert into my_table values( 2, XMLType(
'<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>' ) );
....
> On my box:
....
> <document>
> <au first="R?diger" id="1">R?diger</au>
> </document>
>
> <document>
> <au first="Ruediger" id="1">R?diger</au>
> </document>

In your reply, ü (small caps u with diaeresis) was replaced by ?
If this already happened at the moment the records were created,
nothing is proved yet.
So now I'm still not sure if it realy worked well in Oracle 10
can you please confirm that the output contains real UTF8

sorry for the inconvenience

Ko vd Sloot

Ko van der Sloot wrote:
This has little or nothing to do with Oracle, and everything with:
a) the tool you use to display the results
b) the platform you use

I am guessing (which I shouldn't): SQL*Plus and Windows.

Check code page (chcp), change to 1252.

--
Regards,
Frank van Bortel

Andrew Hardy schrieb:
> Ko van der Sloot wrote:
>
>> Hello,
>> we encountered a very strange problem with searching in our XMLType
>> database.
>>
>> Our configuration is:
>> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
>> With the Partitioning, OLAP and Oracle Data Mining options
>> JServer Release 9.2.0.4.0 - Production
>>
>> Below you will find a very simple example that demonstrates the problem.
>> It seems that a diacritic in an attribute confuses Oracle, and makes
>> some record "invisable". We expect the query to find both records in the
>> table.
>>
>> Please try this and confirm that the problem is real, or that we have
>> some misconception about Oracle. (and if so: what do we do wrong?)
>>
>> Also we are very much interested to know how Oracle 10 handles this.
>>
>> The input is UTF8 ,and NSL_LANG is set to American_america.AL32UTF8
>>
>> Here is the example script:
>> ================================================== ==
>> drop table my_table;
>>
>> create table my_table ( doc_id NUMBER, data_char xmltype );
>>
>> insert into my_table values( 1, XMLType(
>> '<document>
>> <au first="Rüdiger" id="1">Rüdiger</au>
>> </document>' ) );
>>
>> insert into my_table values( 2, XMLType(
>> '<document>
>> <au first="Ruediger" id="1">Rüdiger</au>
>> </document>' ) );
>>
>> create index my_table_idx on my_table(data_char) indextype is
>> ctxsys.context;
>>
>> select data_char from my_table where contains(data_char,'1 inpath(
>> //au/@id )' ) > 0;
>
> On my box:
>
> Connected to Oracle Database 10g Release 10.1.0.3.0
> Connected as hardya
>
>
> SQL>
>
> Table created
>
> 1 row inserted
>
> 1 row inserted
>
> Index created
>
> DATA_CHAR
> --------------------------------------------------------------------------------
>
> <document>
> <au first="R?diger" id="1">R?diger</au>
> </document>
>
> <document>
> <au first="Ruediger" id="1">R?diger</au>
> </document>
>
>
> SQL>
>

On my box:

scott@ORA92>

Table created.


1 row created.


1 row created.


Index created.

scott@ORA92> select data_char from my_table where contains(data_char,'1
inpath(
//au/@id )' ) > 0; 2

DATA_CHAR
--------------------------------------------------------------------------------
--------------------
<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>

<document>

Verify pls the character set of your database. If your character set
does contain 'ü', you should be able to see proper results.

Best regards

Maxim

Maxim Demenko wrote:
> On my box:
>
> scott@ORA92>
>
> Table created.
>
>
> 1 row created.
>
>
> 1 row created.
>
>
> Index created.
>
> scott@ORA92> select data_char from my_table where contains(data_char,'1
> inpath(
> //au/@id )' ) > 0; 2
>
> DATA_CHAR
> --------------------------------------------------------------------------------
> --------------------
> <document>
> <au first="Ruediger" id="1">Rüdiger</au>
> </document>
>
> <document>

So you can reproduce the Error. Thanx

> Verify pls the character set of your database. If your character set
> does contain 'ü', you should be able to see proper results.

But you can't....
anyway: i thinks yout output proves already that ü is part of the
charset. Just look at the name: Rüdiger, which is displayed correctly!

> Best regards
>
> Maxim

Thanks for helping me further.

Ko

Frank van Bortel wrote:
> Ko van der Sloot wrote:
> This has little or nothing to do with Oracle, and everything with:
> a) the tool you use to display the results
> b) the platform you use

please reread the discussion:

First: I posted an example containing ü characters.
Some of them were correctly retrieved. Others not.
So our Oracle DB is capable of storing and retrieving ü, yes?
Ans so are my tools, because I used cut-and-paste to create my mail.

Then: Andrew Hardy replied that he got the correct result.
BUT: In his mail all ü characters were mangled. so it was unclear to me
if he realy entered ü characters in hit DB or that they were already
mangled beforehand.

Then: I asked to clearify this. But he didn't upto now.

> I am guessing (which I shouldn't): SQL*Plus and Windows.

Well: I am using the gqlplus tool on a RedHat Linux system.
Oracle also runs there.

> Check code page (chcp), change to 1252.

Which is EVIL of course. We want full UTF8

regards
Ko vd Sloot

Ko van der Sloot wrote:
> Frank van Bortel wrote:
>
>>Ko van der Sloot wrote:
>>This has little or nothing to do with Oracle, and everything with:
>>a) the tool you use to display the results
>>b) the platform you use
>
>
> please reread the discussion:
>
> First: I posted an example containing ü characters.
> Some of them were correctly retrieved. Others not.
> So our Oracle DB is capable of storing and retrieving ü, yes?

I must have missed something, because your original post as I received
it, didn't contain data retrieving.

> Ans so are my tools, because I used cut-and-paste to create my mail.
>
> Then: Andrew Hardy replied that he got the correct result.
> BUT: In his mail all ü characters were mangled. so it was unclear to me
> if he realy entered ü characters in hit DB or that they were already
> mangled beforehand.

Tested on SLES8/10gR1: Same result as Andrew. You put in 'ü' but (possibly
in the xmltype constructor) they got mangled.

>
> Then: I asked to clearify this. But he didn't upto now.

I'll do on his behalf: They seem to get mangled. But then tested in a
plain varchar2 field the 'ü' gets mangled too.

>
>
>>I am guessing (which I shouldn't): SQL*Plus and Windows.
>
>
> Well: I am using the gqlplus tool on a RedHat Linux system.
> Oracle also runs there.

Important information that, if supplied in the first place wouldn't have
led Frank (and indeed me, too) on the wrong path.

>
>
>>Check code page (chcp), change to 1252.
>
>
> Which is EVIL of course. We want full UTF8

But would have been the right advice of course for the situation Frank describes.
SQL*Plus on Windows (from the command line) using code page 1252 would come as
near to UTF-8 as it can get.

Now that all the confusion is hopefully resolved, could you please post *your*
results when you execute the query.

Sofar I for one don't even know what this thread is *really* about.

Regards,
Holger

Holger Baer wrote:
> Ko van der Sloot wrote:
>
>> Frank van Bortel wrote:
>>
>>> Ko van der Sloot wrote:
>>> This has little or nothing to do with Oracle, and everything with:
>>> a) the tool you use to display the results
>>> b) the platform you use
>>
>>
>>
>> please reread the discussion:
>>
>> First: I posted an example containing ü characters.
>> Some of them were correctly retrieved. Others not.
>> So our Oracle DB is capable of storing and retrieving ü, yes?
>
>
> I must have missed something, because your original post as I received
> it, didn't contain data retrieving.

oeps: I didn't include it, because it was so obvious (to me, that is) sorry.
I said I expected te retrieve both record. implying that only one was
retrieved.
At the end I will include the whole session.

>> BUT: In his mail all ü characters were mangled. so it was unclear to me
>> if he realy entered ü characters in hit DB or that they were already
>> mangled beforehand.
>
>
> Tested on SLES8/10gR1: Same result as Andrew. You put in 'ü' but (possibly
> in the xmltype constructor) they got mangled.
>
>>
>> Then: I asked to clearify this. But he didn't upto now.

>
> I'll do on his behalf: They seem to get mangled. But then tested in a
> plain varchar2 field the 'ü' gets mangled too.

Then your Oracle isn't UTF8 aware I guess.
It isn't mangled here. But Oracle just can't find one of the records

> Important information that, if supplied in the first place wouldn't have
> led Frank (and indeed me, too) on the wrong path.

Sorry about that. There are so many variables....
As said: it was clear to me that this isn't a simple 'codepage' problem,
because we are able to retreive numerous UTF8 character strings.
Only not if there are UTF characters in an XML attribute (which is NOT
used at all, in the example)

> Now that all the confusion is hopefully resolved, could you please post
> *your*
> results when you execute the query.

Commin up....

> Sofar I for one don't even know what this thread is *really* about.

About Oracle XML text search failing when some attribute contains ü.

> Regards,
> Holger

Thanks for your patience...

Ko vd Sloot
The whole session:
================================================== =======================================
SQL> drop table my_table;

Table dropped.

SQL> create table my_table ( doc_id NUMBER, data_char xmltype );

Table created.

SQL> insert into my_table values( 1, XMLType(
2 '<document>
3 <au first="Rüdiger" id="1">Rüdiger</au>
4 </document>' ) );

1 row created.

SQL> insert into my_table values( 2, XMLType(
2 '<document>
3 <au first="Ruediger" id="1">Rüdiger</au>
4 </document>' ) );

1 row created.

SQL> create index my_table_idx on my_table(data_char) indextype is
ctxsys.context;

Index created.

SQL> select data_char from my_table where contains(data_char,'1
inpath(//au/@id)' ) > 0;

DATA_CHAR
--------------------------------------------------------------------------------
<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>

SQL> select * from my_table;

DOC_ID
----------
DATA_CHAR
--------------------------------------------------------------------------------
1
<document>
<au first="Rüdiger" id="1">Rüdiger</au>
</document>

2
<document>
<au first="Ruediger" id="1">Rüdiger</au>
</document>

DOC_ID
----------
DATA_CHAR
-------
SQL>

Ko van der Sloot wrote:
> Holger Baer wrote:
>
>>Ko van der Sloot wrote:
>>
>>
>>>Frank van Bortel wrote:
>>>
>>>
>>>>Ko van der Sloot wrote:
>>>>This has little or nothing to do with Oracle, and everything with:
>>>>a) the tool you use to display the results
>>>>b) the platform you use
>>>
>>>
>>>
>>>please reread the discussion:
>>>
>>>First: I posted an example containing ü characters.
>>>Some of them were correctly retrieved. Others not.
>>>So our Oracle DB is capable of storing and retrieving ü, yes?
>>
>>
>>I must have missed something, because your original post as I received
>>it, didn't contain data retrieving.
>
>
> oeps: I didn't include it, because it was so obvious (to me, that is) sorry.
> I said I expected te retrieve both record. implying that only one was
> retrieved.
> At the end I will include the whole session.
>
>
>>>BUT: In his mail all ü characters were mangled. so it was unclear to me
>>>if he realy entered ü characters in hit DB or that they were already
>>>mangled beforehand.
>>
>>
>>Tested on SLES8/10gR1: Same result as Andrew. You put in 'ü' but (possibly
>>in the xmltype constructor) they got mangled.
>>
>>
>>>Then: I asked to clearify this. But he didn't upto now.
>
>
>>I'll do on his behalf: They seem to get mangled. But then tested in a
>>plain varchar2 field the 'ü' gets mangled too.
>
>
> Then your Oracle isn't UTF8 aware I guess.
> It isn't mangled here. But Oracle just can't find one of the records

Or it's my linux...

>
>
>>Important information that, if supplied in the first place wouldn't have
>>led Frank (and indeed me, too) on the wrong path.
>
>
> Sorry about that. There are so many variables....
> As said: it was clear to me that this isn't a simple 'codepage' problem,
> because we are able to retreive numerous UTF8 character strings.
> Only not if there are UTF characters in an XML attribute (which is NOT
> used at all, in the example)
>
>
>>Now that all the confusion is hopefully resolved, could you please post
>>*your*
>>results when you execute the query.
>
>
> Commin up....
>
>
>>Sofar I for one don't even know what this thread is *really* about.
>
>
> About Oracle XML text search failing when some attribute contains ü.
>

Ok, so now we might be getting somewhere.

[...]

>
> SQL> select data_char from my_table where contains(data_char,'1
> inpath(//au/@id)' ) > 0;
>
> DATA_CHAR
> --------------------------------------------------------------------------------
> <document>
> <au first="Ruediger" id="1">Rüdiger</au>
> </document>
>
> SQL> select * from my_table;
>
> DOC_ID
> ----------
> DATA_CHAR
> --------------------------------------------------------------------------------
> 1
> <document>
> <au first="Rüdiger" id="1">Rüdiger</au>
> </document>
>
> 2
> <document>
> <au first="Ruediger" id="1">Rüdiger</au>
> </document>
>
> DOC_ID
> ----------
> DATA_CHAR
> -------
> SQL>

Sorry, I'm out of my depths here, but what does

select extract (data_char, '/document') from my_table

return?

Regards,
Holger