Oracle: неправильно работает ORDER BY
Вроде все он различает
Тока тебе похоже сортировка другая нужна.
select *
from (select 'a' as F
from DUAL
union
select 'A' as F
from DUAL
union
select '.A' as F
from DUAL
union
select 'A.' as F
from DUAL
union
select 'B' as F
from DUAL
union
select 'b' as F from DUAL)
order by F
Тока тебе похоже сортировка другая нужна.
У меня нормальный порядок - вроде бы такой, как тебе надо. Что я делаю не так? 
P.S. Oracle 9.2.0.5, везде UTF8
SQL> DESC TEST
TABLE TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
S NOT NULL VARCHAR2(100)
SQL> SELECT S FROM TEST ORDER BY S;
S
--------------------------------------------------------------------------------
"Justice"
"Liberty"
"Liberty" Organisation
, avocat, Bordeaux
, lawyer, Kyiv
, lawyer, Ostrava
A
A. MARCHETTI, avocat à l'Aquila
A. Me.
A. Q.C. and
A. and CHRISTIE
A. and GONI
A. and LOIZIDES
A. and VAN EMPEL
A. et ANGELOPOULOS
A. et B. agissent pour C.
A. et CENGIZ
A. et CENSI
A. et PORRAS
A.-E.
A.A.
Aachen
a non-governmental organisation
a solicitor practising in Newcastle
24 rows selected.
P.S. Oracle 9.2.0.5, везде UTF8
так в том-то и вопрос: почему разным клиентам он возвращает разные результаты? 
какое значение у NLS_LANG? да и вообще, покажи все NLS_*
какой тип у поля, по которому сортируешь?
какой тип у поля, по которому сортируешь?
а где это смотреть? в смысле NLS?
поле типа VARCHAR2(200)
поле типа VARCHAR2(200)
SHOW PARAMETER NLS;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_timestamp_format string
nls_timestamp_tz_format string
nls_time_tz_format string
SQL> exit
А можешь показать результат такого селекта:
SELECT ASCIISTR(name) FROM table ORDER BY NLSSORT(name, 'NLS_SORT = BINARY');
SELECT ASCIISTR(name) FROM table ORDER BY NLSSORT(name, 'NLS_SORT = BINARY');
Ага, так сортировка работает.
Фрагмент:
тогда вопрос - как изменять такие настройки ГЛОБАЛЬНО? Кого пинать за неправильно настроенную локаль (я правильно понимаю?)
Настраивается ли это при инсталляции или нужно руками донастраивать?
Фрагмент:
ASCIISTR(HDVD_REPR)
---------------------
A
A-F.
A-L.
A.
A. A.
A. E.
A. MARCHETTI, avocat à l"Aquila
A. Me.
A. Q.C. and
A. and CHRISTIE
A. and GONI
A. and LOIZIDES
A. and VAN EMPEL
A. et ANGELOPOULOS
A. et B. agissent pour C.
A. et CENGIZ
A. et CENSI
A. et PORRAS
A.-E.
A.-F.
A.-M.
A.-V.
A.A.
A.B.
A.C.T.
A.D.
A.D. (sister)
A.D. ABRAHAMSON and Co.
A.E.
A.G.
A.H.
A.H.M.M.
A.J.
A.J.L.J.
A.J.W.
A.K.
A.L.
A.M.
A.P.
A.P.L.
A.R.W.
A.W.M.
AAE
AARONSON
тогда вопрос - как изменять такие настройки ГЛОБАЛЬНО? Кого пинать за неправильно настроенную локаль (я правильно понимаю?)
Настраивается ли это при инсталляции или нужно руками донастраивать?
Изменить походу никак. Есть параметр NLS_SORT, но в его описании сказано что он уровня сесси.
С локалью есть два подхода:
1. Честно настроить её на сервере, правда я не знаю что при этом станет с существующими данными.
2. Выставлять на клиенте туже локаль что и на сервере, тогда Oracle считает что всё у всех хорошо и больше ничего не проверят и ничего не пытается перекодировать.
А вообще по этому поводу стоит ботать Globalization Support Guide.
С локалью есть два подхода:
1. Честно настроить её на сервере, правда я не знаю что при этом станет с существующими данными.
2. Выставлять на клиенте туже локаль что и на сервере, тогда Oracle считает что всё у всех хорошо и больше ничего не проверят и ничего не пытается перекодировать.
А вообще по этому поводу стоит ботать Globalization Support Guide.
Есть параметр NLS_LANG, но в его описании сказано что он уровня сесси.Не только уровня сессии. Можно выставить в окружении клиента, и будет он во всех сессиях (пока не переопределишь через alter session...)
на винде - в реестре: HKLM\SOFTWARE\ORACLE\ALL_HOMES\IDn
на юниксах - установкой переменной окружения
Я извиняюсь, я имел в виду NLS_SORT. Уже исправил.
nls_language string AMERICANПорядок сортировки по умолчанию определяется тем, какой у тебя NLS_LANGUAGE. Он у тебя AMERICAN. Судя по данным, которые ты показывал, они у тебя явно не на английском языке. Ну так выстави на клиенте правильный NLS_LANG, например: NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252 или NLS_LANG=FRENCH_FRANCE.UTF8 (смотря какую кодировку ты используешь). Как следствие получишь правильный NLS_LANGUAGE=FRENCH и правильный порядок сортировки.
Если тебе сортировка в соответствии с алфавитом используемого языка не подходит, а нужна именно BINARY, то выстави переменную NLS_SORT=BINARY на клиенте или каждый раз делай ALTER SESSION SET NLS_SORT=BINARY; ну или повесь триггер на логон, который будет делать alter session, но это уже изврат
P.S. В какой у тебя кодировке база данных, кстати?
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';
Французские строки там - это так, досадное недоразумение.
На самом деле наполнение примерно такое: 60% на английском и 40% на французском.
Причём половина строк имеет перевод на русский язык в виде:
Поскольку отображается всё равно в браузер (кодировка 1251 французские спецсимволы обходятся html-кодами. Пофиг, что они влияют на сортировку.
Где надо - я вставлял "1", "2" в начала строк, которые мне надо, чтобы выдавались в начале списка. А вот тут меня ожидал облом - она цифры в конец засунула. Вот и начал разбираться.
На самом деле наполнение примерно такое: 60% на английском и 40% на французском.
Причём половина строк имеет перевод на русский язык в виде:
Иванов Петр Михайлович / Ivanoff Pyotr Mikhajlovich
Поскольку отображается всё равно в браузер (кодировка 1251 французские спецсимволы обходятся html-кодами. Пофиг, что они влияют на сортировку.
Где надо - я вставлял "1", "2" в начала строк, которые мне надо, чтобы выдавались в начале списка. А вот тут меня ожидал облом - она цифры в конец засунула. Вот и начал разбираться.
NLS_CHARACTERSET CL8MSWIN1251
NLS_LANGUAGE AMERICAN
А какие у тебя параметры клиента? (NLS_)
кстати, а чего говорит
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_SORT';
BINARY?
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER='NLS_SORT';
BINARY?
...
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
...
Вряд ли. А по умолчанию идёт язык вытянутый из NLS_LANG
Я просто залез в документацию и прочитал что show parameters показывает initialization parameters, т.е. параметры базы, а не текущей сессии. Параметры текущей сессии - в представлении NLS_SESSION_PARAMETERS.
Вряд ли. А по умолчанию идёт язык вытянутый из NLS_LANGкстати, для NLS_LANGUAGE=AMERICAN в NLS_SORT будет "BINARY"
Нет такого языка "AMERICAN". И "ENGLISH" тоже нет.
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101...
Наверное, потому что они с BINARY совпадают, на крайняк - с UNICODE_BINARY.
Если я правильно понял таблицу приоритетов, то вытягиваться будет из локального NLS_LANG. Который обычно на Windows-клиентах проставлен честно.
Судя по ответам автора темы, у него на клиенте стоит NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251, откуда получим NLS_SORT=BINARY (можно увидеть в NLS_SESSION_PARAMETERS)
Вряд ли, тогда бы не было фигни с сортировкой. Тем более как ты справедливо заметил это всё параметры базы:
И вообще пока автор темы не приведёт параметры сессии, это всё умозрительные заключения.
SHOW PARAMETER NLS;
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';
И вообще пока автор темы не приведёт параметры сессии, это всё умозрительные заключения.
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter='NLS_CHARACTERSET';Я бы даже сказал, просто SELECT * FROM NLS_SESSION_PARAMETERS;
Попробовал у себя поменять NLS_LANG на клиенте с AMERICAN_AMERICA.UTF8 на FRENCH_FRANCE.UTF8 и обратно. Если ставлю AMERICAN, то получаю параметр сессии NLS_SORT=BINARY, если же FRENCH, то NLS_SORT=FRENCH (проверял переконнекчиваясь к базе и выполняя запрос SELECT * FROM NLS_ SESSION_PARAMETERS WHERE parameter='NLS_SORT'; )
SELECT * FROM NLS_SESSION_PARAMETERS;
NLS_LANGUAGE RUSSIAN
NLS_TERRITORY CIS
NLS_CURRENCY р.
NLS_ISO_CURRENCY CIS
NLS_NUMERIC_CHARACTERS ,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-Mon-RRRR
NLS_DATE_LANGUAGE RUSSIAN
NLS_SORT RUSSIAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY р.
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Кое-что проясняется. В базе вообще были предусмотрены только русские строки. Но вот захотелось иметь ещё и английские с французскими.
Не надо менять кодировку на UTF и проч.
Национальные символы навроде ö нормально заменяются на html-тэги.
Национальные символы навроде ö нормально заменяются на html-тэги.в общем, определяйся, хочешь ты всё-таки иметь французский в базе (и UTF-8 или хочешь html-тэги
а что в Оракл нельзя задавать параметры кодировки и сортировки индивидуально на отдельное текстовое поле?
Насколько я знаю, кодировка у базы одна. А откуда такой вопрос и как он связан со всем предыдущим обсуждением?
Я это говорил с самого начала. Мне НУЖНЫ html-тэги. Мне не нужна UTFx
Как выставить NLS_SORT в BINARY глобально для всех сессий?
Прямой ответ на твой вопрос - вроде только повесить триггер на логон.
А если по уму, то это за система в которой ты не контролируешь окружение клиентов, напрямую подключающихся к базе?
А если по уму, то это за система в которой ты не контролируешь окружение клиентов, напрямую подключающихся к базе?
а что в Оракл нельзя задавать параметры кодировки и сортировки индивидуально на отдельное текстовое поле?а что, это можно делать в какойто другой СУБД?
Много гетерогенных клиентов (штук 7 на вскидку) - на трёх языках программирования написанных (Java, Delphi, C++). Каждый специально локаль не выставляет - используется "настройки по умолчанию". Вот и не хочется в коде каждого клиента копошиться, а сделать всё на сервере.
Это даже не столько клиенты, сколько около-"сервер приложений"
Это даже не столько клиенты, сколько около-"сервер приложений"
ну так если это ограниченное число апп-серверов, выстави просто на каждом в окружении NLS_LANG и ничего не надо править в коде.
"окружение" = переменные среды?
ага, у простых клиентов, сработало.
Однако до jsp-сервлетов почему-то не дошло. перезапуск сервисов не помог. может, машину перегрузить?...
ага, у простых клиентов, сработало.
Однако до jsp-сервлетов почему-то не дошло. перезапуск сервисов не помог. может, машину перегрузить?...
если Windows, то в реестре
upd. ребут не помог.
хм. можно более конкретное место в реестре?
хм. можно более конкретное место в реестре?
HKLM\SOFTWARE\ORACLE\HOME0
так, в какое значение надо поставить NLS_LANG?
сейчас стоит AMERICAN_AMERICA.CL8MSWIN1251
ЗЫ. Добавление параметра NLS_SORT=BINARY (REG_SZ) не помогло
сейчас стоит AMERICAN_AMERICA.CL8MSWIN1251
ЗЫ. Добавление параметра NLS_SORT=BINARY (REG_SZ) не помогло
сейчас стоит AMERICAN_AMERICA.CL8MSWIN1251ты это проверил по NLS_SESSION_PARAMETERS?
нет, посмотрел в реестре
select * from NLS_SESSION_PARAMETERS выдаёт
select * from NLS_SESSION_PARAMETERS выдаёт
NLS_LANGUAGE RUSSIAN
Значит я тебе не то место в реестре указал. Поищи, где там ещё есть NLS_LANG, причём "RUSSIAN_CIS.CL8MSWIN1251"
Дока по теме:
Oracle9i Database Getting Started Release 2 (9.2) for Windows
9 Configuration Parameters and the Registry
Registry Parameters
(ссылка на онлайновую доку)
Дока по теме:
Oracle9i Database Getting Started Release 2 (9.2) for Windows
9 Configuration Parameters and the Registry
Registry Parameters
(ссылка на онлайновую доку)
Так, рассмотрим дополнительно вариант тригера на логон.
Либо я чего-то недопонимаю в тригерах либо его нельзя создать в принципе.
Ну не даёт он мне ALTER SESSION SET NLS_SORT=BINARY в тело тригера ставить
Либо я чего-то недопонимаю в тригерах либо его нельзя создать в принципе.
Ну не даёт он мне ALTER SESSION SET NLS_SORT=BINARY в тело тригера ставить
create or replace trigger after_logon
after logon on database
begin
execute immediate
'alter session set nls_sort = ''BINARY''';
end;
/
after logon on database
begin
execute immediate
'alter session set nls_sort = ''BINARY''';
end;
/
Я тебе советую не использовать тригер, лучше настрой локали.
Дело в том что при несовпадении локалей Oracle производит перекодирование и если какой-то символ вдруг оказывается непечатаемым, то он заменяется на знаки вопроса.
Дело в том что при несовпадении локалей Oracle производит перекодирование и если какой-то символ вдруг оказывается непечатаемым, то он заменяется на знаки вопроса.
Насколько я знаю, кодировка у базы одна. А откуда такой вопрос и как он связан со всем предыдущим обсуждением?Просто интересно — надо было оффтоп поставить. Полезный фокус может получиться: если, например, текстовое поле играет роль идентификатора, и используется только для организации связи, то можно было бы использовать бинарную сортировку, т.к. она быстрее, чем сортировка по словарю. В прочем, не знаю, насколько это интересно на практике, в теории кажется что полезнo.
P. S. В SQL Server управлять параметрами сортировки можно для отдельных текстовых полей.
Полезный фокус может получиться: если, например, текстовое поле играет роль идентификатора, и используется только для организации связи, то можно было бы использовать бинарную сортировку, т.к. она быстрее, чем сортировка по словарю. В прочем, не знаю, насколько это интересно на практике, в теории кажется что полезнo.На практике ни разу не видел текстовых полей, используемых для организации связи.
P. S. В SQL Server управлять параметрами сортировки можно для отдельных текстовых полей.можно для отдельного поля задать порядок сортировки, который будет использоваться по умолчанию?
Поэтому индексы тоже будут правильными. А пример я просто так привел.
Как в Оракл реализовать словарь, когда нужно искать слово по одному языку и по другому языку. По идее нужно два индекса, в каждом сортировка определяется своим языком.
Как в Оракл реализовать словарь, когда нужно искать слово по одному языку и по другому языку. По идее нужно два индекса, в каждом сортировка определяется своим языком.
Как в Оракл реализовать словарь, когда нужно искать слово по одному языку и по другому языку. По идее нужно два индекса, в каждом сортировка определяется своим языком.CREATE INDEX french_index ON employees (NLSSORT(employee_id, 'NLS_SORT=FRENCH';
CREATE INDEX german_index ON employees (NLSSORT(employee_id, 'NLS_SORT=GERMAN';
P.S. ссылка на документацию:
Oracle9i Database Globalization Support Guide Release 2 (9.2)
4 Linguistic Sorting
Using Linguistic Indexes
да, не повезло юзерам и админам
как-то странно такое видеть в объектно-реляционной СУБД
как-то странно такое видеть в объектно-реляционной СУБД
что не нравится?
и как это делается в SQL Server, с которым "повезло" юзерам и админам?
и как это делается в SQL Server, с которым "повезло" юзерам и админам?
по идее указывается правильный collation для столбца при создании таблицы, дальше все делает SQL Server сам
дальше все делает SQL Server самвсё - это что?
как он поймёт, когда ты хочешь сортировку по-французски, а когда по-немецки, если (как ты написал) разработчик засунул и то, и другое в один столбец?
как он поймёт, когда ты хочешь сортировку по-французски, а когда по-немецки, если так сложилось, что разработчик засунул и то, и другое в один столбец?ну, возможно, что это будет не лучшее решение разработчика смешивать два языка в одном столбце
А как ты вообще собирался засовывать в разные языки в одни столбец, если они не в одной кодировке(например utf32)?
а почему не в одной? я имел ввиду два разных языка в одном столбце в одной кодировке (тот же UTF8)
А если в одной тогда нет проблем с сортировкой и поиском, единственный вопрос сначала будут отсортированные английские строки потом отсортированные французские или наоборот.
Оставить комментарий
yolki
т.е. он работает, но результат противоречит здравому смыслу, вот фрагмент:Видно, что порядок НЕ ТОТ. Такое впечатление, что Оракл при сортировке игнорирует точки, не различает регистр и т.п.
Как сделать правильный порядок?
Он должен быть таким (фрагмент)