Thank you Jonathan,
In the meantime I opened a SR at Oracle (SR 3-25336436801 - in case
someone wants to give the poor support analyst a hand and show him how to
use a testcase on liveql).
You are right, the "alter session set default_collocation" is not required.
It's an artefact from some other tests I did with a table
CREATE TABLE "A"."T_OBJECT2"
( "NAME" VARCHAR2(12)
*COLLATE "GERMAN_AI"* )
this requires max_string_size = extended.
As it did not help in my tests, I removed the table and it's tests, just
forgot about the default_collocation.
Bug 10224414 and some others looks similar. But all those I found are
"fixed in" 12. or older. That's the reason I did not dig into them deeper.
I did not mention it yet, my sandbox is NLS_CHARACTERSET AL32UTF8. So
nothing special here.
alter session set nls_sort=binary_ai ;
does not change anything.
If I get substantial updates from Oracle I'll post it here.
regards,
Martin
Am Mi., 3. März 2021 um 11:36 Uhr schrieb Jonathan Lewis <
jlewisoracle@xxxxxxxxx>:
A couple of follow-ups.
a) I've only just realised that the "alter session set default_collation"
was complaining that I didn't have max_string_size = extended. (Didn't make
any difference, though, but it's an interesting point, and one to beware
of, that it is set on LiveSQL).
b) This looke remarkably like bug 10224414 (which is reported fixed in
12.0). Workaround "Avoid using the ESCAPE clause". (You could take a leaf
from Oracle's book on that one - "select * from t_object where
translate(name,'_','#') like '#%' order by d1; " - which they've used in
the definition of gv$parameter since ca. Oracle 6.
c) This may depend on your character set, but do you get the results
you're expecting if you specify binary_ai rather than german_ai ?
Regards
Jonathan Lewis
On Fri, 26 Feb 2021 at 19:35, Martin Berger <martin.a.berger@xxxxxxxxx>
wrote:
Hi List,
Yesterday I was confronted with an interesting question they faced.
They have some entries in a varchar2 column such as
_A, _B, _Ä, _, A
The LIKE pattern is '\_%' ESCAPE '\'
when NLS_COMP is LINGUISTIC and NLS_SORT is set to GERMAN_AI
this LIKE doesn't return any rows.
My suggested workaround is to use
('\_%' COLLATE GERMAN_AI ) escape '\'
But I can't explain, why this is required to make the % work as I expect.
Why % is sensitive of the collation?
Can anyone give me a hint, please?
A simple testcase can be found at
https://livesql.oracle.com/apex/livesql/s/lfcf58k7cj18t22jeth3rz6cu
thank you,
Martin
--
Martin Berger Oracle ♠
martin.a.berger@xxxxxxxxx @martinberx <https://twitter.com/martinberx>
^∆x http://berxblog.blogspot.com
