Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20

    Question Unanswered: Alter NLS settings based on ISO locale?

    Hi.

    Is there an easy way to change session NLS parameters mid-session, based on a Unix locale code?

    I understand that I can set the parameters with ALTER SESSION, but if I do that, I'll have to write the code to associate the ISO code with Oracle's NLS settings. For example, if I want to change my locale to th_TH (Thai in Thailand), I would have to have code (or data) that knew how to translate th_TH into the language 'THAI' and the territory 'THAILAND'.

    Oracle seems to be able to handle this automatically if I my locale is th_TH when I connect. Isn't there a way to do that mid-session, without me having to code the Unix to Oracle mappings?

    Thanks,
    Philip
    Last edited by philgarr; 11-23-05 at 14:40.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why would your locale change mid-session? Is this something which would be better handled without changing the locale.

    Alan

  3. #3
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    Quote Originally Posted by AlanP
    Why would your locale change mid-session? Is this something which would be better handled without changing the locale.

    Alan
    This is for a web application. The database connection persists across requests, and must be able to handle the correct locale for the current request.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >must be able to handle the correct locale for the current request.
    User<==>Browser<==>Http_server<==>APP_server<==>DB _Server
    How in the world is the DB to "know" what/where this local/locale current user is at?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    Quote Originally Posted by anacedent
    >must be able to handle the correct locale for the current request.
    User<==>Browser<==>Http_server<==>APP_server<==>DB _Server
    How in the world is the DB to "know" what/where this local/locale current user is at?
    It'll know because I'll tell it. PL/SQL, ALTER SESSION, whatever. That's what I'm looking for.

    I already said that I know how to change the settings manually, but to my knowledge that would require me to translate "en_US" to "american.america" in MY code. I would prefer to use an Oracle built-in.

    My confusion lies in the fact that SQL*Plus seems to know how to do it. It takes my Unix LC_ALL environment variable and sets everything just right. Surely that means there is a mapping between Unix-ish locales and Oracle-ish locales somewhere within Oracle? If somebody knows of a built-in Oracle table or view that maps the two together, that would really help...

    I'm using Oracle 9iR2, btw.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by philgarr
    My confusion lies in the fact that SQL*Plus seems to know how to do it. It takes my Unix LC_ALL environment variable and sets everything just right.
    Are you absolutely sure about this ? AFAIK sql*plus, as any Oracle client, uses the NLS_LANG environment variable (eg AMERICAN_AMERICA.UTF8, in a more general way :l "language_territory.charset"), I've never heard of Oracle client using LC_ALL.

    Now concerning your problem, the "Globalization support guide" should be of help to you. I'm not sure Unix territory/language codes are the same as Oracle ones, and I wouldn't count on it. What you could do is assigning a "locale profile" to the user in a table (maybe you already have a table with user details) in which you would put the Oracle language/territory codes concerning this user, and only send the user id on each user's query, so that inside your stored procedure you would get its locale profile from the database thanks to its user id, alter session accordingly and send back results. Instead of altering the session you can try to code the locale inside the SQL functions called in the procedure, that should work too (see the link I gave you for more details).

    HTH & Regards,

    RBARAER
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    Are you absolutely sure about this ? AFAIK sql*plus, as any Oracle client, uses the NLS_LANG environment variable (eg AMERICAN_AMERICA.UTF8, in a more general way :l "language_territory.charset"), I've never heard of Oracle client using LC_ALL.
    Now that I test it to prove it to you, I find that it's not working.

    I'm not sure what I was doing to make it work before, but that apparently wasn't it. Sorry for wasting your time.

    Now concerning your problem, the "Globalization support guide" should be of help to you.
    I've seen it, but thanks. I had hoped that there was some Oracle trick to do this, but it looks like i'll be rolling my own.

    Thanks.

  8. #8
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20

    Found solution

    Quote Originally Posted by philgarr
    I had hoped that there was some Oracle trick to do this, but it looks like i'll be rolling my own.
    Eureka! The UTL_I18N package does exactly what I need (map_language_from_iso). Too bad it's only available 10g.

    It looks like I'll still be making my own, but I thought someone else might find it useful.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Eureka! The UTL_I18N package does exactly what I need (map_language_from_iso). Too bad it's only available 10g.
    Thanks for reporting this back to the list.
    About a decade ago my production DB was V7.3.4.5 & then Oracle released V8.1.6 which had UTL_SMTP & I always wanted to send email from PL/SQL.
    Upgrading the production DB was not an option at the time, so I just created my own V8.1.6 DB so I could make calls to it from V7.3.4.5 & get email sent!
    What's stopping you from making use of the 10G package?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20
    Quote Originally Posted by anacedent
    What's stopping you from making use of the 10G package?
    It's just too much trouble for such a small thing as this, unless there's a way to install the 10g package in a 9i db -- and I'm guessing there isn't.

    I'm not a DBA, though, so there's little chance of it happening anyway. I have to leave the platform decisions up to the folks who are willing to get up at 3am to restore a server ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •