Re: [cx-oracle-users] maintaining multiple connections with different encodings

[Doug H. <djh...@te...>]

I use a mixed bag of tools, including SQL*Plus, SQL Developer, and
python scripts using cx_Oracle. I need to be able to correctly store and
retrieve a wide range of international characters with all of these
tools. It was very frustrating to have different results depending on
the tools and the environmental configuration. BTW, cx_Oracle always
seemed to work correctly.

I ran some experiments with SQL*Plus from the 11.1 instant client on a
Win7 box with a LAN connected linux box running a 10g server.

SQL*Plus does not recognize the BOM in an  8-bit file script file. It
gets treated as an invalid character.
As far as I could tell, it does not recognize a 16-bit Unicode file at all.

It does read 8-bit source files, and the characters are interpreted
according the setting of the NLS_LANG environment variable.

I use NLS_LANG=ENGLISH_CANADA.UTF8 in my windows batch files to get
utf-8 encoded characters properly loaded into oracle tables.

The NCHR function takes a 16-bit binary unicode code point and creates
the correct character in the database, similar to CHR which takes a
8-bit code to create an ASCII character. This is helpful for generating
the correct character when you cannot rely on the NLS_LANG setting.

One needs to be careful in interpreting the terminal output from
SQL*Plus, as it is sent to the command window, where it may be
incorrectly displayed. For this reason, I use the sql DUMP function to
display the binary content of the database column, instead of relying on
the displayed characters in the command window.

On 2011-05-10 11:47, Anthony Tuininga wrote:
> FYI, I just found out that SQL*Plus on Windows does NOT support
> Unicode at all. If you need to insert Unicode data use SQL Developer
> or i*SQLPlus -- or cx_Oracle, of course! :-)
>
> So there is a very good possibility that SQL*Plus screwed up your data for you.
>
> Glad to hear you got the problem resolved, though.
>
> Anthony
>
> <snip>

-- 
Doug Henderson, Calgary, Alberta, Canada