Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2006
    Posts
    56

    Unanswered: Problems with NLS_LANG

    Hi all!!

    This problem has been driving me nuts these past weeks and I just can't get it solved. I'm not very much familiar with Linux/Unix OS and that might be getting in the way of sorting this thing out.

    Here's what's happening. I run applications with languages on ISO-8859-1 (latin). It's an old 9i DB that's been up for at least 8 years. I think it started as v8 and then was upgraded to 9i, but I'm not the DBA, so I'm not sure. What I'm sure is that this DB was created using latin language patterns, and that several applications are currently running fine based on that DB.

    So I developed my own PHP web site and hosted it in a shared CentOS4 server which is in en_US. That's when my problems started.

    Now every query run through PHP pages returns ? replacing latin characters. That doesn't happen on my testing environment, which is Windows in Brazilian Portuguese (latin language), nor on TOAD. I've run several tests to make sure it's not an Apache or PHP issue, or even HTML headers. Those are all fine. It's a sure thing that the queries are being returned with replaced characters.

    Now, here's some info:
    OS: CentOS4
    Server: Apache 1 stable
    Parser: PHP 4 stable
    DB: Oracle 9i
    Client: 10g v_10.1.3 (not instant client)
    Client Install Folder: /u01/app/oracle

    HTML Headers:
    HTML Code:
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    File httpd.conf settings (Apache Config):
    AddLanguage pt .pt
    AddLanguage pt-BR .pt-br
    LanguagePriority pt-BR en ca cs da de el eo es et fr he hr it ja ko ltz nl nn no pl pt ru sv zh-CN zh-TW
    ForceLanguagePriority Prefer Fallback
    AddDefaultCharset ISO-8859-1
    AddCharset ISO-8859-1 .iso8859-1 .latin1
    AddCharset ISO-8859-2 .iso8859-2 .latin2 .cen

    On CentOS, if I run the command locale, that's the output (US English):
    LANG=en_US.UTF-8
    LC_CTYPE="en_US.UTF-8"
    LC_NUMERIC="en_US.UTF-8"
    LC_TIME="en_US.UTF-8"
    LC_COLLATE="en_US.UTF-8"
    LC_MONETARY="en_US.UTF-8"
    LC_MESSAGES="en_US.UTF-8"
    LC_PAPER="en_US.UTF-8"
    LC_NAME="en_US.UTF-8"
    LC_ADDRESS="en_US.UTF-8"
    LC_TELEPHONE="en_US.UTF-8"
    LC_MEASUREMENT="en_US.UTF-8"
    LC_IDENTIFICATION="en_US.UTF-8"
    LC_ALL=

    The output of the command locale -a shows a whole lot of languages, inclusive (these are Brazilian Portuguese and Portugal Portuguese):
    pt_BR
    pt_BR.iso88591
    pt_BR.utf8
    pt_PT
    pt_PT@euro
    pt_PT.iso88591
    pt_PT.iso885915@euro
    pt_PT.utf8


    I've read elsewhere that the Oracle Client will prioritize the OS language settings when returning queries. I don't know if there's any truth in that, but if so, is it possible to override that?

    Also some people have told me I might have to edit the NLS_LANG settings in the Oracle Client profile to
    NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1
    but nobody has been able to tell me where that profile is located, so I don't even know what's it's set to, if anything.


    That's all the info I've got.

    Any help will be extremely appreciated.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It may be just me, but I am unclear on how many different systems & different databases are involved in your problem description.

    Adding to the mix "Oracle client" has me even more confused.

    AFAIK, Oracle does NOT care about any LC* environmental variables, but can behave differently to some/many/most NLS* variables.

    It is unclear to this reader, whether you have a data storage issue or a data display issue.

    If the 'foreign' characters are not being stored properly in the database, there is little you can do to remedy the situation; other than starting over with a DB configured to hold the incoming characters.

    If it is a display issue, then you should be able to tweak 1 or more things to correct this problem.

    HTH & YMMV!
    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.

  3. #3
    Join Date
    Jul 2006
    Posts
    56
    Hi! Thank you very much for your reply.

    I think I understood most of what you wrote, but I'm not familiar with the terms "AFAIK", "HTH" & "YMMV"! =( Sorry, bad English ...

    It is unclear to this reader, whether you have a data storage issue or a data display issue.
    I haven't tried to insert any data throu this system because it was not online, it was just in my computer (the DB was online, but the PHP pages were localhost). All the inserts were made throu my testing environment, and all the data WAS inserted properly.

    If the 'foreign' characters are not being stored properly in the database, there is little you can do to remedy the situation; other than starting over with a DB configured to hold the incoming characters.
    The DB is configured to hold incoming characters. I know that for a fact because I've testing it for months. When the inserts were made throu the PHP pages that run in my localhost, ok. The data is also displayed properly in my localhost environment, and it's also displayed properly in TOAD.

    The problem is only taking place in this CentOS4 remote server in which the PHP pages will in fact be hosted. There I have not tried to insert any data to the DB, but I think the data would not be inserted properly if I tried. Displaying data returns replaced characters, not ok. There's certainly a display issue. I just don't know if there's an insert issue, but I'll perform some tests and post back.

    If it is a display issue, then you should be able to tweak 1 or more things to correct this problem.
    Meanwhile, could you help me with the displaying issue? =)

    Again, thanks for helping.
    Last edited by igordonin; 04-04-08 at 19:34.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but I'm not familiar with the terms "AFAIK", "HTH" & "YMMV"! =
    As Far As I Know (AFAIK)
    Hope This Helps (HTH)
    You're Mileage May Vary(YMMV) -[your results may be different from mine]

    You have three separate components between the DB & the end user;
    PHP, Apache & Browser that can result in garbled characters.
    Since you are convinced that data is correctly stored in the DB,
    I suggest you post this problem in PHP and/or Apache forums,
    because I am clueless when it come to globalization (working with non-ASCII characters).
    I suspect the fix is nothing more than setting 1 or more Unix environmental
    variables for either the PHP process or the Apache process.
    I can not begin to guess as to which variables or what values need to be changed.
    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
    Jul 2006
    Posts
    56
    I agree, but not entirely. Don't take me for an expert, but this is what I think happens:

    The end user requests a page, and now I'm ignoring all the DNS requests and going straight from the end user browser to the Internet Server.
    1) The Internet server (in my case an Apache Server running on an old CentOS4) receives the request and "calls" the page;
    2) The PHP reads the document and uses the supplied DB conn data to connect to a DB throu
    3) a DB client (in my case, Oracle 10g Client 10.1.3). The DB client parses the queries to the DB and receives a result, which it then reports to the Apache/PHP server;
    4) which, lastly, puts up an HTML page that will be seen by the end user.

    See, I don't think that Apache nor PHP can interpret results from DBs without the required clients. Maybe some softwares from the same organization can do that, like Oracle IAS (Internet Application Service) and EM (Enterprise Manager).

    That been said, I know I've got the Apache/PHP settings covered. What I may be missing are some configuration files from the UNIX, like you said, or maybe the Oracle client.

    Now, just for an example, there is a whole folder dedicated to Apache inside the Oracle 10.1.3 client folder (/u01/app/oracle/product/10.1.3/Apache). As a matter of fact, there are folders to all major ISs. Digging deeper inside that folder I found a file called apachectl (/u01/app/oracle/product/10.1.3/Apache/bin) which had these entries:
    ORACLE_HOME=/u01/app/oracle/product/10.1.3; export ORACLE_HOME
    #NLS_LANG=${NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P 1"}; export NLS_LANG
    NLS_LANG=${NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"}; export NLS_LANG
    I commented out the AMERICAN_AMERICA stuff and placed the BRAZILIAN PORTUGUESE instead. Still no success.

    Now I'm really confused, and I'm wondering: does anyone know for a fact that the client cannot be set for some charset, or maybe that the client doesn't prioritize OS language settings?

    I know this is not a CentOS forum, and I don't expect it to be. I'm just wondering if anyone had to work around language settings defined by the client or the OS.

    Thanks all.
    Last edited by igordonin; 04-05-08 at 02:17.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I commented out the AMERICAN_AMERICA stuff and placed the BRAZILIAN PORTUGUESE instead.
    Did you restart Apache are making the configuration change?
    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.

  7. #7
    Join Date
    Jul 2006
    Posts
    56
    Yes, I did restart the apache service. I didn't boot the computer though. Sadly, that'll be harder to do ...

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I came across the following URL
    http://www.adviesenzo.nl/examples/ph...l_charset_fix/
    Yes, I realize it deals with MYSQL & not Oracle,
    but I suspect similar changes might be necessary for Oracle.
    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.

  9. #9
    Join Date
    Jul 2006
    Posts
    56
    Hi! Thank you very much for replying, anacedent.

    I got it fixed!!

    You know what, you were right all along! I've changed so many config settings I can't tell for a fact that, and I quote you here:,
    the fix is nothing more than setting 1 or more Unix environmental variables for either the PHP process or the Apache process.
    But true enough I had something missing! The NLS_LANG environmental var was not being set upon Apache init! I don't know how I missed that!

    Anyways, after doing all that's already reported here, I got it fixed by doing this:

    1. I created a "on database logon" trigger and forced Brazilian Portuguese NLS settings to my web user (the one with no privileges that I use to connect PHP to the DB).

    2. I added the NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1" to the "httpd" file in "/etc/init.d". Someone told me that file might be named "apache" instead. Also, init.d was in fact a shortcut to "/etc/rc.d/init.d".

    3. Finally, but I don't know if this played any part at all in all this, I changed my UNIX locale setting in that same "httpd" file to "LANG=pt_BR.iso88591".

    That's it. Hope this helps someone.

    Once again, many thanks to anacedent who so kindly helped me out.

    Cheers!
    Last edited by igordonin; 04-15-08 at 13:04.

Posting Permissions

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