UTF-8 database executing an SQL file saved in ANSI

Toad for Oracle
1

UTF-8 database executing an SQL file saved in ANSI

2

I have more info on this. I missed a critical part.

The critical part is the NLS_LANG of the work station.

So it does not matter how you enter the Accent. It is what happens when you run it. If you save the query and then run it via sql. Here is a table

Work_station Query encoding database result
NLS_LANG encoding
============= ============== =========== ========
Single Byte ansi utf8 works - Oracle handles conversion
Single Byte utf8 utf8 croaks - Oracle tries to convert
Utf8 ansi utf8 croaks - oracle tries to convert
Utf8 utf8 (bom) utf8 SQLPlus croaks on BOM
UTF8 UTF8 (no BOM) utf8 works

The BOM is not an issue if the first line in SQL script is a blank line. You just get a “Invalid Command” SP2 message. But if your first line starts with a “/*” comment then that line gets a “Invalid Command” message and of course is thrown away. So now you have no opening comment. The rest of the line in the supposedly commented area are attempted o be executed. If you commented out a bunch of executable code you are in trouble. If it is just words you just get a bunch more SP2 “Invalid Command” messages.

3

Erwin,

are you by any chance using a 9i client? I'm pretty sure that there was
a lot of trouble when 9i OCI was upgraded to allow UTF8 and a lot of
bugs were introduced. Have you tried your tests on a 10g client (and 10g
Sqlplus?)

Just a thought.

Cheers,
Norm. [TeamT]

4

Unfortunately SQLPlus as well as the other Oracle utilities (exp, imp, ldr) still requires NLS_LANG to precisely match the client character set. Since version 10 Toad itself is entirely Unicode, so every string in Toad is represented automatically as UTF8, and it uses the Unicode Mode of the OCI. The problem customers are seeing now is that, especially in Asia, they used US7ASCII (as both the NLS_LANG and the db char set) to store Chinese character data, which ‘appeared’ to work. Because the client & server matched, no conversion occurred (when it needed to). After upgrading to Toad 10 they are seeing their data for what it really is - stored completely wrong. So many of them have a serious problem on their hands.