Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: character set doesn't work

    hi all

    I've got a strange situation to report and ask for some help.
    I have a small 10g database at home, where I do some tests, with PL/SQL, database configuration, etc.. The computer with that database as been off for about 3-4 weeks. The last time I've worked with it, it all worked fine regarding do character set, language and territory (WE8ISO8859P15, Portuguese, Portugal).
    Yesterday when I connected the computer, it made some of OS updates (it's a CentOS 4), don't know if this is the cause, but now all the special characters in VARCHAR fields (, etc.) were replaced, some with the character without the spcial connotation (caeo, etc.) and others with a '?'.
    I've checked the database tried to change character set, but it doesn't allows that kind of characters....

    Any ideas???
    Many thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    Check the NLS_LANG environment variable on the client (OS) side.
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Posts
    95
    if I make this:

    select value from v$parameter where name = 'nls_language';

    the result is Portuguese.

    Also, I've just noticed that the return messages are in fact in Portuguese, but without those kind of characters.

    What can I do now?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Not in the database!

    I did not say to check the NLS parameters in the database, but rather the CLIENT side (OS) environment variables.

    On WinDoze check (or set) the registry entry for NLS_LANG
    On *nix, check (or set) NLS_LANG environment variable
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by LKBrwn_DBA
    On WinDoze check (or set) the registry entry for NLS_LANG.
    On Windows you can use an environment variable as well. I think on Windows the environment variable NLS_LANG will overwrite the registry setting (but it could also be the other way round - I'm not sure about the precedences here)

    For details with Linux, check the installation manual:
    http://download.oracle.com/docs/cd/B...pp_gblsupp.htm

  6. #6
    Join Date
    May 2004
    Posts
    95
    sorry for the confusion between parameters and environment variables.
    as soon after I replied here, I've been back to my db documentation and realize wich one you ment.

    if I type in OS this:

    %> locale

    at first it returns:

    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=en_Us.UTF-8

    an now I've changed, so that it returns, in all parameters: Portuguese_Portugal.WE8ISO8859P15

    but the problem continues....

    Thanks.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect the problem is strictly a presentation issue.
    You can use the UNISTR() function to verify the actual contents of the database.
    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.

  8. #8
    Join Date
    May 2004
    Posts
    95
    nop... it doesn't give me anything with the UNISTR() function

    take the example of '' character, ASCII code 191

    if I run this:
    select chr(ascii('')) from dual
    or
    select UNISTR(ASCIISTR(chr(191))) from dual

    the return is the same: '?'

    any other ideas??? thanks.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT ASCIISTR(<field_containg_nonascii>) from <table_containing_nonascii_field> WHERE KEY_ID = ?????

    What does such a similar query return?
    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 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by xixo
    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=en_Us.UTF-8

    an now I've changed, so that it returns, in all parameters: Portuguese_Portugal.WE8ISO8859P15

    but the problem continues....
    The variable for Oracle is called NLS_LANG as has been mentioned several times now.
    I don't see NLS_LANG in the above list.

    I'm not experienced with Linux, but the documentation (see the link I posted) clearly states that only the NLS_LANG variable is checked in Linux, nothing else.

  11. #11
    Join Date
    May 2004
    Posts
    95
    anacedent:

    If i do this:
    SELECT ASCIISTR(name) from film_type WHERE film_type_id = 45
    it returns:
    'Hist\00F3rico' (the right way is 'Histrico')

    PS-I've changed the field and table name to english, for your better understand.

    shammat:
    I've looked up how to set up the NLS_LANG variable, wich doesn't say in your link, but I found it here: http://www.oracle.com/technology/tec...#_Toc110410551

    the variable is allways refered as LANG. And I've done what was required to set it up.

    thanks.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by xixo
    anacedent:
    If i do this:
    SELECT ASCIISTR(name) from film_type WHERE film_type_id = 45
    it returns:
    'Hist\00F3rico' (the right way is 'Hist&#243;rico')
    No, ASCIISTR provided correct & desired results!
    ASCIISTR display unicode characters in pure ASCII (less than CHR(128)) characters.
    The output show a hexadecimal value of "F3"; which equals(16*15)+3=243 [the "F" the hexadecimal equivalent to decimal 15].
    This exercise proves the data is correctly stored within the database as Unicode characters.

    You do not see them as expected because you have a problem presenting them correctly by the client s/w!
    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.

  13. #13
    Join Date
    May 2004
    Posts
    95
    I know that the information is in the correct form inside the database, because this database was working fine for about 6 months, and now it doesn't. The problem is not only on the client side. If I run SQL Plus on the server it returns the same thing. In fact all my selects and returns are done on the server side.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If I run SQL Plus on the server it returns the same thing. In fact all my selects and returns are done on the server side

    You don't KNOW, what you don't know.

    Oracle RDBMS is server software.
    Any & all utilities which interacts with the database SERVER is client software.
    SQL*Plus is client software; even when invoked from the DB server system!

    If you really, really knew what is/was actually happening, you would not be here hoping that somebody will provide you at least a clue if not a complete solution.
    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.

  15. #15
    Join Date
    May 2004
    Posts
    95
    okay...

    i've just created the NLS_LANG variable on my windows based client (never needed so far) and now my sw is working fine (SQL Navigator).

    doing the same on the Unix server (putting the NLS_LANG variable), the results still don't return the special characters

Posting Permissions

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