msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

SQL Developer and UTF8

In this global economy the need for software globalization is increasing. As a result of this globalization, localization is becoming an important part of software development. And as a result of this localization, software developers like myself will have to work with multiple languages. So, it is becoming more crucial to have development tools that support multi-byte in addition to single-byte characters, like Chinese versus English for example.

As an Oracle database developer I have been using Quest Software Inc.’s SQL Navigator for years. We are currently involved in the implementation of Oracle E-Business Suite in our new plant in China. So, now we have the Chinese characters to worry about. It turns out that SQL Navigator does not support multi-byte characters and UTF8 encoding (which is needed to display Chinese characters), however, Oracle’s SQL developer does :)

Here is how SQL Navigator displays a query result that returns Chinese characters:

sql_navigator_utf8.gif

And here is the same query in SQL developer:

sql_developer_utf8.gif

SQL Navigator developers are going to include UTF8 support in the next release (5.5) which should be out by the end of summer.

Does TOAD support UTF8? Just curious.

Update:

Thanks to Braj, I was able to make SQL Navigator display Chinese characters. Here’s what you have to do (on Win XP):

  1. Go to your computer’s “Control Panel”
  2. Open the “Regional and Language Options”
  3. In the “Regional Options”, change the “Standards and formats” to “Chinese (PRC)” and Change the Location to “China”
  4. In the “Advanced” tab, change the “Language for non-Unicode programs” to “Chinese (PRC)”
  5. Restart your computer.

Now, you need to set the NLS_LANG environment variable. Here is how you do it:

  1. Go to your computer’s “Control Panel”
  2. Open “System”
  3. Select the “Advanced” tab
  4. Click on the “Environment Variables” button
  5. Click on the “New” button under “System variables”
  6. Enter NLS_LANG in the “Variable name”
  7. Enter AMERICAN_AMERICA.ZHS16GBK in the “Variable value”
  8. Click “OK” three times to exit the “System” properties window
  9. Start SQL Navigator
  10. Run a query that returns Chinese characters

You should see “real” Chinese characters in the query result. Of course, you will have to know Chinese to read the query result :)

To revert back to English (or whatever language you had before), just undo what you did in the steps above.


Filed in Oracle, Tips on 06 Jul 06 | Tags: , , , ,


Reader's Comments

  1. |

    Hi!

    TOAD and SQL Navigator supports UTF8 at native level as they are written in Delphi. You have to set your PC regional Setting to the region of lang ( like Chineese) and then you oracle client NLS_LANG to point to ZHS setting then it will work for Chineese character. I like SQLD for this out of box support for UTF8.

    I think TOAD Release 10 is going to be totally rewritten in C++ to support UTF8 out of box.

  2. |

    braj, I tried what you suggested, but SQL Navigator still did not display Chinese characters correctly. Thanks for the info though.

  3. |

    Hi! Eddie,

    I tried with SQL Navigator 5.0 and it worked.

    first you have to set regional settings as per given instruction in General Tab first selec t Chinese (PRC) and in location select China

    In the advance tab of Regional Setting select Chinese (PRC)

    After that reboot your m/c and then set NLS_LANG of your oracle home which you are going to use for SQL Nav NLS_LANG= AMERICAN_AMERICA.ZHS16GBK

    After that start SQL Navigator or TOAD and then run you chinese related query.

    – braj

  4. |

    Hi! I have prepared small pdf to guide the setup required to see chinese character in toad and SQL Navigator. First try above mentioned steps and still you are not able to run chinese related query, i will send you the pdf.

  5. |

    Thanks braj. I will retry and post the result in the comment section here.

  6. |

    Hi Braj, thanks for taking the efforts to post the steps but unfortunately, I’m having problems with the above steps. Would it be possible to have a look at your pdf ?

    Do you know of any other SQL Navigator troubleshooting related sites ? One that I came across recently was inside.quest.com/sqlnavigator (and yours of course). Having a play with Raptor too…not bad.

  7. |

    Hi!

    I do not have any blog or site to post the pdf. Please send me your email id. I will mail you the pdf documents with screenshots.

    my mail id is brajm@asw.com.hk

    braj

  8. |

    Braj, it worked. I have updated the post accordingly.

    Owen, thanks for the link. I was not aware of the existence of such a useful SQL Navigator site.

  9. |

    Hi Braj, you can send the pdf to ogamer@mindless.com. Thanks again.

    Owen

  10. |

    Hi!

    I have emailed the doc to both of you. Anyway ,Eddie has already updated the post with new findings. One thing to add whatever language setting you do , you will always able to query english data. After langauge setting you can also see the correct chinese data in sqlplus and putty session also.

    – braj

  11. |

    Excellent, thanks Braj ! :)

  12. |

    Great guide, Braj. Did you have much difficulties in entering Chinese characters as data via SQL Navigator ? The table data in your screenshot in the PDF…were they entered via SQL Navigator or by some other means ?

  13. |

    Hi!

    Data is seeded by EBIS for fnd_languages_tl. If you have chinese input method (key board layout software) , you can also type chinese in sql navigator or toad on even on the sqlplus or unix session. you can also upload data using insert scripts.

    – braj

  14. |

    If the server use US7ASCII or WE8ISO8859P1, I have to way to display Chinese in SQL Developer. How to resolve it?

  15. |

    If the server has a database character set of US7ASCII or WE8ISO8859P1, VARCHAR2 and CHAR columns cannot properly encode Chinese characters. Those character sets only support English and (for ISO 8859-1) Western European characters.

    There are some unsupported methods for getting Chinese data in and out of databases where the character set does not support Chinese, but you’re going to be out of luck trying to read this data using just about any third party application.

  16. |

    Hi Broj, I am not able to view chinese characters in TOAD. Can you please send the PDF document you prepared to the following id rbalaji_it@yahoo.co.in

  17. |

    hi …thanks….i’m able to see the chinese characters in toad now. but cold u plz tell me how can i retrieve the chinese data using java? i’d be a big help!!!

  18. |

    Can anyone send me that pdf to qwe88 @ hotmail.com please? I want to read/load Chinese data via toad or sqlplus. I am in English Windows XP. What steps I need to do in order to see the Chinese characters instead of the ? mark? I change the regional setting to China – Chinese. What do I need to change in my oracle home (any details step?). Thanks a lot in advance!

  19. |

    Can some one send me the pdf to srikant.br@gmail.com

    Thanks in advance

  20. |

    Hi all. Some of you recieved pdf with instructions. Can someone send it to me (tomaz.bracic@gmail.com). I am having some trouble with russian characters in TOAD 9.

    Thanks in advance.

    Tomaz

  21. |

    For all of you who want a copy of the PDF, I have uploaded it to scribd. You can access it here.

  22. |

    Hi Braj, I’ve a problem here with chinese word… first, i need to upload the chinese word from text file by using sqlloader to Oracle 8i then i need to display the chinese word in porlet…but it keep displaying ‘????’ or encrypted word, you have any idea?? can you list down the steps to upload the chinese character?? and also the displaying steps, in case I missed out any of it. TQ.

  23. |

    Hi, I am facing issue with viewing chinese characters. The data is in a flat file and is being read by external table. The query select * from table shows box. The column is of type varchar2. Can you please help me with the chinese display