This discussion is archived
14 Replies Latest reply: Sep 29, 2013 10:23 AM by xerces8 RSS

Documentation about JDBC locale?

xerces8 Newbie
Currently Being Moderated

In Oracle8i JDBC Developer's Guide and Reference there is a chapter ( link: Advanced Topics ) about JDBC and NLS saying:

 

The Thin driver obtains language and territory settings (NLS_LANGUAGE and NLS_TERRITORY) from the Java locale in the JVM user.language property.

Is there any more recent documentation? For version 11.2?

 

The closest I found is Globalization Support in

Oracle® Database JDBC Developer's Guide
11g Release 2 (11.2)

E16548-03

 

But in there is no mention of this topic.

 

What I'm interested in is the NLS_SORT for linguistic sorting.

 

It appears with 11.2 it is affected by the user.language setting as written above, but I don't want to rely on undocumented features.

 

Many thanks,

David

  • 1. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated

    Is there any more recent documentation? For version 11.2?

     

    The closest I found is Globalization Support in

    Oracle® Database JDBC Developer's Guide
    11g Release 2 (11.2)

    E16548-03

     

    But in there is no mention of this topic.

    What I'm interested in is the NLS_SORT for linguistic sorting.

    Umm - yes there is explicit 'mention of this topic'. You must have missed this note on the very first page of that link:

    Note:
    •   Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

    Did you notice that line 'driver does not check NLS environment'?

    It appears with 11.2 it is affected by the user.language setting as written above, but I don't want to rely on undocumented features.

    Not sure what you even mean by that.

     

    Why don't you tell us WHAT it is you are trying to do or what problem you are having doing it. We can't help you if we don't know what it is you are having a problem with.

  • 2. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    OK, I'll repeat.

     

    What I'm interested in is the NLS_SORT for linguistic sorting.

    As I (and you too, it seems) figured out that the later versions of the driver does not check NLS environment, my question is (I thought so) obvious: How to affect the behavior then?

    How to change the sorting order?

  • 3. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated

    OK, I'll repeat.

    Why don't you tell us WHAT it is you are trying to do or what problem you are having doing it. We can't help you if we don't know what it is you are having a problem with.

    You still haven't posted any info. We can't help you if you don't tell us what you are trying to do and how you are trying to do it.

    How to change the sorting order?

     

    You've posted in the JDBC forum. JDBC doesn't do any sorting. Oracle does sorting. If you want to sort a result set add an ORDER BY clause on the query. If you want to modify the NLS parameters for your session issue ALTER SESSION commands to modify them before you do your query and ORDER BY.

  • 4. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    Lookup "linguistic sorting" and when you understand it, come back. Not sooner. Thank you.

  • 5. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated
    Lookup "linguistic sorting" and when you understand it, come back. Not sooner. Thank you.

    You're several years late on that one. The 'messenger' isn't the problem here. The problem is that, as with many of your other posts, you still don't want to provide the information needed to help you. As I just said in my last reply:

    You still haven't posted any info. We can't help you if you don't tell us what you are trying to do and how you are trying to do it.
  • 6. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    How to affect NLS_SORT on the application jevel?

    (application = Java web application in a J2EE container, using JDBC to access Oracle 11g database)

  • 7. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated
    How to affect NLS_SORT on the application jevel?

    (application = Java web application in a J2EE container, using JDBC to access Oracle 11g database)

    The only answer that I have for you is what I said before:

    JDBC doesn't do any sorting. Oracle does sorting. If you want to sort a result set add an ORDER BY clause on the query. If you want to modify the NLS parameters for your session issue ALTER SESSION commands to modify them before you do your query and ORDER BY.

    Other than the above I don't know what answer you are expecting. If the data is coming from Oracle and you want it sorted based on NLS_SORT then you need to change your session parameters.

     

    See NLS_SORT in the database reference

    http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams145.htm

    NLS_SORT specifies the collating sequence for ORDER BY queries.

    •   If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
    •   If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

    Note:

    Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.

     

    You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.

  • 8. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    > JDBC doesn't do any sorting. Oracle does sorting.

    Of course. SELECT .... ORDER BY does sorting. Everybody knows that. What great knowledge will you give next? After day comes night?

    The order of that sorting can be affected. That is the topic. Not basics covered at every elementary school nowadays.

     

    > If you want to modify the NLS parameters for your session issue ALTER SESSION commands.

    As said, I have a J2EE application and inserting ALTER SESSION commands in places is bad maintainability.

     

    Let me restate from the first post:

    NLS_SORT in  (JDBC) 11.2 it is affected by the user.language setting.

    But that fact is not documented.

     

    So:

    - is it actually documented and I did not find the correct documentation?

    - it is undocumented and can go away with the next patch?

  • 9. Re: Documentation about JDBC locale?
    masijade Explorer
    Currently Being Moderated

    Why worry about it, the NLS_SORT function takes arguments

     

    NLSSORT(name, 'NLS_SORT=german')

     

    Use that in the order by.

     

    such as

     

    SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=german');
  • 10. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated
    Of course. SELECT .... ORDER BY does sorting. Everybody knows that

    We can only go by what you post. And based on your repeated posts you don't act like you know that!

     

    This was in my very first reply:

    •   Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

    And this was your reply:

    my question is (I thought so) obvious: How to affect the behavior then? 

    How to change the sorting order?

     

    That doesn't indicate that you understand that sorting is done on the server!

    So I said this in my next reply after you didn't take the first advice:

    If you want to modify the NLS parameters for your session issue ALTER SESSION commands to modify them before you do your query and ORDER BY.

    And yet you still ask this on your last question:

     

    How to affect NLS_SORT on the application jevel?

    (application = Java web application in a J2EE container, using JDBC to access Oracle 11g database)

    Someone that was told sorting happens on the database and so now 'knows' that sorting happens on the database would NOT be asking how to affect it in the client. Even if they didn't cover that at your elementary school I told you.

    As said, I have a J2EE application and inserting ALTER SESSION commands in places is bad maintainability.

    Not if you use a connection pool like most J2EE applications do. Then you can issue those statements when you create the connections.

     

    Or use an AFTER LOGON trigger and do it in the database.

  • 11. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    rp0428 wrote:

    This was in my very first reply:

    •   Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

    And this was your reply:

    my question is (I thought so) obvious: How to affect the behavior then?

    How to change the sorting order?

     

    That doesn't indicate that you understand that sorting is done on the server!

    So I said this in my next reply after you didn't take the first advice:

    If you want to modify the NLS parameters for your session issue ALTER SESSION commands to modify them before you do your query and ORDER BY.

    So what are you saying? That on 10g sorting was done on the client?

    Because later you claim, that because of "sorting is done on the server" it can not be affected by a change on the client.

     

    Also: Someone that was told sorting happens on the database and so now 'knows' that sorting happens on the database would NOT be asking how to affect it in the client.

     

    Issuing an ALTER SESSION _is_ "in the client". It is a change of the client code or configuration, and zero change on the DB code or configuration. I call that "in the client". You might call it something else, but changes I do in the client I call "change on the client".

     

    Also, you keep missing the point. I am not asking "how to do it". I already know (see the first post, also: read it). The questions is: Is it a documented feature on not?

  • 12. Re: Documentation about JDBC locale?
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > The questions is: Is it a documented feature on not?

     

    Probably doesn't matter if it is or not.  Presumably you are looking for documentation for some assurance that it wont change in radical ways.  But that hasn't been my experience with Oracle.  Some things do not change for years even when it would seem like they should.  Some change radically and quicly with no notice even when there seems to be no reason for the change.

  • 13. Re: Documentation about JDBC locale?
    rp0428 Guru
    Currently Being Moderated

    So what are you saying? That on 10g sorting was done on the client?

    Because later you claim, that because of "sorting is done on the server" it can not be affected by a change on the client.

     

    Also: Someone that was told sorting happens on the database and so now 'knows' that sorting happens on the database would NOT be asking how to affect it in the client.

     

    Issuing an ALTER SESSION _is_ "in the client". It is a change of the client code or configuration, and zero change on the DB code or configuration. I call that "in the client". You might call it something else, but changes I do in the client I call "change on the client".

     

    Also, you keep missing the point. I am not asking "how to do it". I already know (see the first post, also: read it). The questions is: Is it a documented feature on not?

    All I can suggest at this point is that you reread that Chapter on Globalization Support in the Oracle 11gr2 JDBC doc I quoted from earlier.

    http://docs.oracle.com/cd/E11882_01/java.112/e16548/global.htm#CHDGGECB

     

    And you should also read the Oracle 8i doc section on JDBC and NLS that YOU quoted from to begin with.

     

    You seem to be confused about what happens in the server versus what happens in the client. You also seem to be confused about what NLS settings are available in the server, which are available in the client and what impact those settings have.

    What I'm interested in is the NLS_SORT for linguistic sorting.

    Let me restate from the first post:

    NLS_SORT in  (JDBC) 11.2 it is affected by the user.language setting.

    But that fact is not documented.

    NLS_SORT is a SERVER-SIDE setting. It is NOT on the client-side now and never has been. The server setting default is BINARY and is only changed if a user changes it with an ALTER command that executes on the server; that is seldom done since, as the doc link I provided says

    Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer.

    NLS_LANGUAGE and NLS_TERRITORY are SERVER-SIDE settings. NLS_LANG is a CLIENT-SIDE environment variable. Then you have the client-side LOCALE setting.

     

    The Locale setting and the NLS_LANG setting (if set) were used by the JDBC driver (as your initial quote from the 8i doc says) to affect the NLS_LANGUAGE and NLS_TERRITORY settings. The driver did that by actually issuing ALTER commands to change those settings on the server for the session.

     

    That all changed from 10g on which is why I provided that doc quote:

    •   Starting from Oracle Database 10g, the NLS_LANG variable is no longer part of the JDBC globalization mechanism. The JDBC driver does not check NLS environment. So, setting it has no effect.

    Then you have a SERVER-SIDE character set and a CLIENT-SIDE character set. The JDBC driver has to perform conversions between the two when there is communication between the client and server. The client character can be affected by the client-side settings that you use for the Java locale but the server NEVER changes character set once the database has been created.

     

    Sorting is ALWAYS done on the server and ALWAYS uses the NLS_SORT parameter which ONLY exists on the server. That 'sorted' data may then be transmitted to the client where the client 'may' need to perform character set conversion on the data. If the client characterset collating sequence is different than the server characterset collating sequence then the data, when viewed on the client may appear to NOT be in order.

     

    While the client settings can alter the order that the data 'seems to be in' it can NOT alter the actual order of the data.

     

    So to use linguistic sorting you have to issue an ALTER statement to the server to modify the NLS_SORT parameter. That has ALWAYS been the case and still is.

     

    The only change is in how the JDBC driver handles globalization support and that support did not, and does not, affect the NLS_SORT setting on the server.

     

    Here are some links that discuss some JDBC issues related to yours:

    http://amitstechblog.wordpress.com/2011/11/22/oracle-case-insensitivity-and-jdbc-drivers/

     

    Setting NLS_COMP and NLSL_SORT for all Sessions

    https://forums.oracle.com/thread/2197067?start=0&tstart=0

     

    Configuring Oracle Database Globalization Support

    http://docs.oracle.com/cd/E11882_01/install.112/e24186/gblsupp.htm

  • 14. Re: Documentation about JDBC locale?
    xerces8 Newbie
    Currently Being Moderated

    xerces8 wrote:

     

    In Oracle8i JDBC Developer's Guide and Reference there is a chapter ( link: Advanced Topics ) about JDBC and NLS saying:

     

    The Thin driver obtains language and territory settings (NLS_LANGUAGE and NLS_TERRITORY) from the Java locale in the JVM user.language property.

    Is there any more recent documentation? For version 11.2?

     

     

    Yes, there is, in chapter Programming with Unicode in Oracle® Database Globalization Support Guide 11g Release 2 (11.2)) it says:

    At database connection time, JDBC sets the server NLS_LANGUAGE and NLS_TERRITORY parameters to correspond to the locale of the Java VM that runs the JDBC driver.

     

    Thank you, David!

     

    You're welcome, David!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points