This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Aug 5, 2012 6:39 AM by Sergiusz Wolicki (Oracle) RSS

Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?

883679 Newbie
Currently Being Moderated
Hi,

Now I have a new problem.

I have a Oracle9i instance, with this configurations.

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.1.0

I have a xHarbour DOS Client program that reads, shows in the screen, and write "perfectly fine" a text with Brazilian accentuation stored in a CLOB field.

Following you can see the sample of the text with Brazilian accentuation in my DOS application http://www.screencast.com/t/U5PXwCEo8

In order to my xHarbour DOS Client program works fine with this Brazilian accented characters, I must to set:

SET NLS_LANG=PORTUGUESE_BRAZIL.WE8MSWIN1252

And all is OK from my xHarbour DOS Client program.

...........................................

My problem is because when I query this data manually using a SELECT from any client DOS/WINDOWS program like SQLPLUS, SQLDEVELOPER, TOAD, etc, I get bad characters instead the correct Brazilian accented characters.

Following you can see the same text into the Toad http://www.screencast.com/t/A1tal2Rtg

(you will see bad characters instead the correct Brazilian accented characters).

Following you can see the result of querying this field using a SELECT from SQLPLUS.

Certifico que por decis„o proferida no processo n§ @@@@@@@@@@@@@@, 
foi reconhecida a n„o incidˆncia do ITBI na transa‡„o do(s) im¢vel(is) 
abaixo caracterizado(s), com base no art.156, @ 2§, I, da Constitui‡„o 
Federal de 1988 e no art.6§, II, da Lei Municipal n§ 1.364 de 19/12/1988. 

(you will see bad characters instead the correct Brazilian accented characters).

How to make "SQLPLUS", "TOAD" (and others Windows or DOS Clients programs) show me the Brazilian accented characters correctly?

Thanks in advance,

Luigggye
  • 1. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    First, pl confirm that you can see all of the characters "correctly" when querying data thru SQL Developer. If you can, that will confirm that the data is stored correctly in the database - if so, then it is a matter of the tool (TOAD, SQL*plus etc) showing the data incorrectly that should be fixed relatively easily. Is this the same database as in your other thread ?

    HTH
    Srini
  • 2. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    With the configuration you described, you have, most probably, WE8PC850 data in your database, not WE8MSWIN1252. You need to fix the database character set before you can see the data correctly outside of the Command Prompt window.

    To confirm my diagnosis, please, run:
    C:\> chcp
    before you start your xHarbour DOS Client and tell me what the command returns.

    Also, if you can, locate one of the Portuguese values in your database (one containing non-ASCII characters) and query it using:
    SELECT column_name, DUMP(column_name,1016) FROM table_name WHERE ... 
    specifying some condition to select the row with this particular value. Paste the result here.


    -- Sergiusz
  • 3. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    Hi, Thanks for your response.

    I can confirm you, that querying the database with SqlDeveloper, SqlPlus, Toad, etc., I receive "bad characters" instead Brazilian accentuated characters.

    I can confirm you, that my xHarbour DOS application connects to the same Oracle Database and show me the Brazilian accentuated characters correctly (read, write and display in the screen).

    Following you can see the sample of the text with Brazilian accentuation into my xHarbour DOS application http://www.screencast.com/t/U5PXwCEo8

    In order to my xHarbour DOS Client program works fine with this Brazilian accented characters, I must to set: (in DOS)

    SET NLS_LANG=PORTUGUESE_BRAZIL.WE8MSWIN1252

    And all is OK from my xHarbour DOS Client program.

    But see the result of the query built from SqlDeveloper, as you can see there are bad characters as result of select:

    SELECT DESCDEF FROM CERDE.BCER010 WHERE LEI = '005'

    Certifico que por decis„o proferida no processo n§ @@@@@@@@@@@@@@, 
    foi reconhecida a n„o incidˆncia do ITBI na transa‡„o do(s) im¢vel(is) 
    abaixo caracterizado(s), com base no art.156, @ 2§, I, da Constitui‡„o 
    Federal de 1988 e no art.6§, II, da Lei Municipal n§ 1.364 de 19/12/1988. 

    Following you can see the same text into the Toad http://www.screencast.com/t/A1tal2Rtg

    The database is the same as in my other thread. Oracle9i Release 9.2.0.1.0, but it problem happen with any database I try to work. (included 11g).

    In this link you can see the current configuration of the database http://www.screencast.com/t/EbBkz0sTtn

    Thanks in advance,

    PS:

    I have this Oracle Database in a Windows Server available in the internet in order make all this tests, so if anybody wants to try to fix the problem, I have not any problem to send the access to this server (to a private e-mail address of course) I am happy to pay for this assistance if required.

    Luigggye
  • 4. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    C:\WINDOWS\system32>chcp
    Página de códigos ativa: 850


    SELECT DESCDEF, DUMP(DESCDEF,1016) FROM BCER010 WHERE LEI = '005'

    ORA-00932: inconsistent datatypes: expected - got CLOB




    Starting from the beginning.....

    Is my Oracle Database correctly configured to works with Brazilian accentuated characters ? ( wherever the version I will use 9i or 11g r2 )

    In this link you can see the current configuration of the database http://www.screencast.com/t/EbBkz0sTtn

    I have no problem to recreate the database, reconfigure it, load it again, etc.

    I just want to load all my DBF files to Oracle (with Brazilian accentuated characters), them run a xHarbour DOS program, and share this data with other windows applications with any problem with this Brazilian accentuated characters.

    I will appreciate any help to start again, step by step. (from the creation of database if necessary).

    Thanks in advance,

    Edited by: 880676 on Jul 27, 2012 1:55 PM
  • 5. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Try this:

    SELECT DESCDEF, DUMP(DBMS_LOB.SUBSTR(DESCDEF,4000,1),1016) FROM BCER010 WHERE LEI = '005'
    -- Sergiusz
  • 6. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    SELECT DESCDEF, DUMP(DBMS_LOB.SUBSTR(DESCDEF,4000,1),1016) FROM CERDE.BCER010 WHERE LEI = '005'

    Certifico que por decis„o proferida no processo n§ @@@@@@@@@@@@@@, 
    foi reconhecida a n„o incidˆncia do ITBI na transa‡„o do(s) im¢vel(is) 
    abaixo caracterizado(s), com base no art.156, @ 2§, I, da Constitui‡„o 
    Federal de 1988 e no art.6§, II, da Lei Municipal n§ 1.364 de 19/12/1988. 


    Typ=1 Len=295 CharacterSet=WE8MSWIN1252: 20,20,20,20,43,65,72,74,69,66,69,63,6f,20,71,75,65,20,70,6f,72,20,64,65,63,69,73,84,6f,20,70,72,6f,66,65,72,69,64,61,20,6e,6f,20,70,72,6f,63,65,73,73,6f,20,6e,a7,20,40,40,40,40,40,40,40,40,40,40,40,40,40,40,2c,20,8d,a,66,6f,69,20,72,65,63,6f,6e,68,65,63,69,64,61,20,61,20,6e,84,6f,20,69,6e,63,69,64,88,6e,63,69,61,20,64,6f,20,49,54,42,49,20,6e,61,20,74,72,61,6e,73,61,87,84,6f,20,64,6f,28,73,29,20,69,6d,a2,76,65,6c,28,69,73,29,20,8d,a,61,62,61,69,78,6f,20,63,61,72,61,63,74,65,72,69,7a,61,64,6f,28,73,29,2c,20,63,6f,6d,20,62,61,73,65,20,6e,6f,20,61,72,74,2e,31,35,36,2c,20,40,20,32,a7,2c,20,49,2c,20,64,61,20,43,6f,6e,73,74,69,74,75,69,87,84,6f,20,8d,a,46,65,64,65,72,61,6c,20,64,65,20,31,39,38,38,20,65,20,6e,6f,20,61,72,74,2e,36,a7,2c,20,49,49,2c,20,64,61,20,4c,65,69,20,4d,75,6e,69,63,69,70,61,6c,20,6e,a7,20,31,2e,33,36,34,20,64,65,20,31,39,2f,31,32,2f,31,39,38,38,2e,20,8d,a


    http://www.screencast.com/t/tVr0EEWQ
  • 7. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    OK, if you look at the word "decisäo" in your DOS program (shouldn't it be "decisão", by the way?), it is encoded by the following bytes: 64,65,63,69,73,84,6f.
    d = 0x64
    e = 0x65
    c = 0x63
    i = 0x69
    s = 0x73
    ä = 0x84
    o = 0x6f
    So, the code for "ä" in your database is 0x84. If you look at the definition of the WE8MSWIN1252 character set, for example here: http://msdn.microsoft.com/en-us/goglobal/cc305145 , you will see that the expected code is 0xe4. On the other hand, if you look at the definition of WE8PC850 here: http://msdn.microsoft.com/en-us/goglobal/cc305160 , you will see that the code for "ä" is 0x84 -- the one you have in your database.

    This proves my initial diagnosis that you have incorrectly labeled data in your database. By setting the NLS_LANG to the same character set as the database, you created a so-called pass-through configuration. This configuration does not require any character set conversion, hence what you put into the database is what you can retrieve unchanged. As CHCP shows, your DOS program (the correct name is actually "console mode program") works in the Microsoft OEM Code Page 850, which corresponds to the Oracle character set WE8PC850. So this is the character set that your application sends to Oracle and this is what gets stored in the database. If you set the NLS_LANG to .WE8PC850 before inserting data, the database would know that the received data is in WE8PC850 and would convert it properly to the database character set WE8MSWIN1252. This data could then be properly converted as required for other clients such as Toad or SQL Developer. As the NLS_LANG was WE8MSWIN1252, Oracle wrongly assumed that the application data is already in WE8MSWIN1252 and did not do any conversion. You ended up with mislabeled data.

    To fix the issue, you should repair the database character set declaration to correspond to the actual database content, i.e. WE8PC850. You should then change the NLS_LANG for the DOS program (and only for the DOS program, not for Toad or other Windows GUI apps) to WE8PC850 as well. I am assuming here, and this is important!, that all non-ASCII (e.g. Portuguese) characters in your database come from this one DOS program.

    To repair the database character set declaration:

    1) Upgrade to 10g or 11g
    2) Run the csscan utility with parameters FROMCHAR=WE8PC850 TOCHAR=WE8PC850. The scan report from the utility (scan.txt) should report only changeless data.
    3) After running the scan, execute the ?/rdbms/admin/csalter.plb script in SQL*Plus.


    -- Sergiusz
  • 8. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    Ok, thanks so much for your response. You are very clear.

    Some considerations:

    We must to work with Oracle9i for the next 12 months, we will upgrade to 11 gR2 in 2013.

    I can re-create the Oracle 9i database quickly, with the settings suggested by you, and loads all the DBF files (with Brazilian accentuation characters) to Oracle again, starting all the process from the beginning... do you agree ? is very easy and quickly to do that for me.

    After that, I can query the database manually using SQLPLUS, SQLDEVELOPER, TOAD, etc, and see what happen.

    If I could see the Brazilian accentuation characters fine quering the database from this windows utilities………..

    I would run my EXE-DOS program to access the data stored in this “new” Oracle Database and see what happen.

    Do you agree with this steps?

    If your response if “yes”, could you tell me please the configurations I must to use to create the new Oracle 9i database right know?

    And, what configuration you recommend to set to NLS_LANG in DOS (if I must to set any) before to load all the DBF files to Oracle?

    I have no words to say thank you again for your assistance.

    Luigggye
  • 9. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Yes, it sounds reasonable. To recap the requirements/assumptions:

    1) Your DBF files contain MS OEM Code Page 850 characters. This is very probable seeing that dBase was a DOS tool.
    2) You load the DBF files without conversion.
    3) You create the new database with the WE8PC850 character set.
    4) You set the NLS_LANG variable to .WE8PC850 using the SET command in the same Command Prompt (DOS) window in which you start your application.
    5) You set the NLS_LANG variable to .WE8MSWIN1252 in the Registry for consumption of Windows GUI programs, such as Toad. (SQL Developer does not need any configuration).

    This should work.

    One question: how do you load the DBF files into your database?

    -- Sergiusz
  • 10. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    My answers.....

    1) Your DBF files contain MS OEM Code Page 850 characters. This is very probable seeing that dBase was a DOS tool.
    Yes


    2) You load the DBF files without conversion.
    Not, my utility program converts to CP850 before to save in Oracle, but I will disable this conversion as suggested by you.


    3) You create the new database with the WE8PC850 character set.
    Ok, I will. Please confirm to me if this settings are correct to create the database as suggested by you http://www.screencast.com/t/HdZkM6S8vv7p


    4) You set the NLS_LANG variable to .WE8PC850 using the SET command in the same Command Prompt (DOS) window in which you start your application.
    Ok, I will.


    5) You set the NLS_LANG variable to .WE8MSWIN1252 in the Registry for consumption of Windows GUI programs, such as Toad. (SQL Developer does not need any configuration).
    Ok, I will.
    One question.... SqlPlus needs any configuration or not?

    We use o PL/SQL Developer from allroundautomation

    http://www.allroundautomations.com/plsqldev.html?gclid=CPPJ9PDrurECFQtU7AodL2IA_A

    It needs any configuration or not?



    This should work.

    I hope, thanks.


    One question: how do you load the DBF files into your database?

    I use a xHarbour program built by myself that connect to oracle using OCI, is a very simple program (I will disable the conversion routine).


    Could I procced ?

    Thanks in advance,

    Luigggye

    Edited by: 880676 on Jul 27, 2012 6:51 PM

    Edited by: 880676 on Jul 27, 2012 6:53 PM
  • 11. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    I see a contradiction here. In 1), you say the files are in code page 850. In 2), you say you convert to code page 850. Why?

    In bullet point 3), the settings are OK.

    SQL*Plus is a console mode application so it requires the same setup as your console mode application.
    PL/SQL Developer is an OCI-based tool, as far as I know, so it will use the Registry setting (.WE8MSWIN1252).

    Note that if you do conversion in your tool, you could also create a WE8MSWIN1252 database and convert DBF from 850 to 1252 in the loading tool. The NLS_LANG settings should remain as advised.


    -- Sergiusz
  • 12. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    Hi.

    Regarding: “I see a contradiction here. In 1), you say the files are in code page 850. In 2), you say you convert to code page 850. Why?”

    In fact, if a character in DBF already is CP 850, the DBF_TO_ORACLE utility do not converts nothing.


    Starting the process from the beginning:

    I have successfully created the database with the setting suggested by you, see the current settings http://www.screencast.com/t/z5cFwveDFYkN is it ok?

    Before to proceed to load the data from DBF to Oracle.

    You said: “4) You set the NLS_LANG variable to .WE8PC850 using the SET command in the same Command Prompt (DOS) window in which you start your application.”

    This is exactly the set you suggest ?

    SET NLS_LANG=PORTUGUESE_BRAZIL.WE8PC850

    And…… must I set it “before” to load the data from DBF to Oracle using my tool (without any conversion inside ? )

    Thanks in advance,

    Luigggye
  • 13. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    883679 Newbie
    Currently Being Moderated
    Hi

    I have set (before to load the DBF to Oracle)

    NLS_LANG=BRAZILIAN PORTUGUESE_BRAZIL.WE8PC850

    In DOS http://www.screencast.com/t/CtFRSSs9XuLv

    In Windows Registry http://www.screencast.com/t/xpcsmNvIkjPS

    I loaded the DBF to Oracle:

    Now this is the new data in the new environment:

    See the data in SQLPLUS in dos http://www.screencast.com/t/q9TCzzxG4bcK

    See the data in SQLPLUS in Windows http://www.screencast.com/t/jtEMEv6XPDpp

    See the data in TOAD http://www.screencast.com/t/Codekje7vp3V

    See the data in PL SQL Developer http://www.screencast.com/t/XvjRbXP2Ibm

    Now see the data in my DOS application http://www.screencast.com/t/UxI6PdWbQubF


    As you can see we are very near of 100 % of success !!!

    My DOS application shows the data 100% in the screen, great.

    The SQLPLUS (WINDOWS / DOS) TOAD and PL SQL Developer, shows the data almost fine, but there are an invalid character " ì "

    Any idea about how to fix it ?

    Thanks in advance,

    Luigggye
  • 14. Re: How to change the NLS_NCHAR_CHARACTERSET from WE8ISO8859P1 to AL16UTF16 ?
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    This extra character must come from your loading program or the DBF files. It comes from the sequence 0x8D 0x0A, where 0x8D is "ì" and 0x0A is LF (line feed). 0x8D looks like 0x0D = CR (carriage return) plus the highest bit set. It looks like a special way for your application to mark end-of-lines in the text.


    -- Sergiusz
1 2 Previous Next