Oracle NLS sorting
slafs
It's my first message here so forgive any mistakes. I don't know even
if I should write about this here.
Anyway...
I was having a problem with proper (polish) ordering of my queries
within a queryset from an Oracle backend.
(check http://groups.google.com/group/django-users/browse_thread/thread/bf140e82bb175f42#
for details)
After a discussion on Django IRC channel thanks to 'rozwell' and
'KyleMac' I found this:
http://code.djangoproject.com/browser/django/trunk/django/db/backends/oracle/base.py#L312
and managed to add one line with NLS_SORT that made Django sorting
'correctly' my queries.
So now the code looks like this:
312 cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT =
'YYYY-MM-DD HH24:MI:SS' "
313 "NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD
HH24:MI:SS.FF' "
314 "NLS_TERRITORY = 'POLAND' "
315 "NLS_SORT = 'POLISH'")
another thing is that I don't understand why NLS_TERRITORY has to be
set to 'AMERICA'.
I know it's not quite elegant to fix a source code of Django (but I
don't how can I subclass a db backend and then use it in my Django
project - maybe someone can guide me to some docs?).
So my question(?) would be:
Shouldn't this be fixed?
Regards
Antoni Aloy
>
> Hi there!
>
> It's my first message here so forgive any mistakes. I don't know even
> if I should write about this here.
> Anyway...
> I was having a problem with proper (polish) ordering of my queries
> within a queryset from an Oracle backend.
>
It needs an evironment variable set propertly in order to work as
expected. One could think that a PL or the encoding should just depend
on the server, but it really depends on the client configuration.
I think in your case is quite the same, take a look at
http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm
and the set the enviornment variable NLS_LANG on your application. In
the setting perhaps.
Hope it helps!
--
Antoni Aloy López
Blog: http://trespams.com
Site: http://apsl.net
Ian Kelly
> another thing is that I don't understand why NLS_TERRITORY has to be
> set to 'AMERICA'.
Because the region determines how the days of the week are numbered.
I have no idea what mapping POLAND uses, but if it's different from
AMERICA, then expect to get incorrect results from the 'weekday'
lookup.
> I know it's not quite elegant to fix a source code of Django (but I
> don't how can I subclass a db backend and then use it in my Django
> project - maybe someone can guide me to some docs?).
> So my question(?) would be:
> Shouldn't this be fixed?
We could add keys to the DATABASE_OPTIONS setting that would cause
this to be set. I would be concerned about allowing NLS_TERRITORY to
be changed in this manner unless we also implement a different
solution for the weekday inconsistency.
Regards,
Ian
Ian Kelly
> I think in your case is quite the same, take a look at
> http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm
> and the set the enviornment variable NLS_LANG on your application. In
> the setting perhaps.
This won't work, because the Oracle backend will override the NLS_LANG
setting with ".UTF8" when it loads. There may be a use case for
changing it to only override the character set part of the variable.
Ian
slafs
On 5 Cze, 22:39, Ian Kelly <ian.g.ke...@gmail.com> wrote:
> We could add keys to the DATABASE_OPTIONS setting that would cause
> this to be set. I would be concerned about allowing NLS_TERRITORY to
> be changed in this manner unless we also implement a different
> solution for the weekday inconsistency.
'AMERICA' in 'Polish environment' like mine but don't You agree that
adding a NLS_SORT variable support in Django Oracle backend would be
helpfull ?
Regards
slafs
Just to clarify things up. Setting an environment variable NLS_SORT="POLISH" ensures the correct sorting in Oracle. This is in case anyone hit this topic.
Regards
Sławek