Re: Inserting Unicode Data.

linevoltage_halogen wrote:

> Greetings All, I was hoping that someone might be able to clear up a
> bit of confusion for me. I have read the Globalization Support Guide
> and I still have some confusion regarding string literals, unicode,
> how they are passed and how they are used. Here are the two
> situations I am studying, 9iR2, 10G:
>
> 1.) Unicode Data Types:
> Database Character Set: WE8MSWIN1252
> National Character Set: AL16UTF16
>
> 2.) Unicode Database:
> Database Character Set: AL32UTF8
>
> In scenario 1.) I would employ the N* data types: nchar(N),
> nvarchar2(N), and nclob. My question in this case is do I need to use
> the N prefix if my application is passing in Unicode data?
> e.g.)
> insert ... values (N'Unicode String')?
> or can I do ...
> insert ... values ('Unicode String')?
> What if my application does not pass in Unicode data,
> do I need to preface the insert or update string literal
> with an = 'N'?
>

No.
BTW/OT: why still use char?!? (or nchar)

> Now, what about predicates and the N* data types. Am
> I required to use the N prefix, e.g.)
> select ... where MyUnicodeColumn = N'Unicode String literal'

No, you don't use varchar2 in selects, do you? Just use the column name.
>
> I tested this situation and no implicit conversion
> took place when I did not preface the predicate with and N, i.e.)
> create table nls_test (unicode1 nvarchar2(20));
> create index nls_test_idx on nls_test(unicode1);
> insert 40,000 rows of data
> select * from nls_test where unicode1 = 'string literal'
>
> the execution plan for the above was identical to
>
> select * from nls_test where unicode1 = N'string literal'

I would suspect, Oracle simply ignores the "N"...

>
>
> In scneario 2.) I would simply dump any and all data that is passed to
> my application into the char(N), varchar2(N), and clob fields
> without worrying about whether or not it is Unicode or
> would I still need to preface the insert/update with the N
> prefix?
>

Implied by answer(s) above: no, just dump. Make sure the environment is Unicode aware, i.e. NLS_ variables are unicode, not
Japanese, or West European.
>
> The underlying idea beneath this question has to do with making ready
> a packaged application for globalization. Currently this app only
> supports 8bit western encoding, we would like to retrofit the database
> to support any number of languages including Japanese, and most
> European languages and Unicode will allow this.
>
> Currently this is still in the "let's think about this" phase so I
> want to fully understand as much of this as possible.
>
> Thank You. TFD.

Iirc, somewhere in these manuals, there is a remark, that a scenario as above ('any language'), is best served with AL16UTF16 (not(!) the default AL32UTF8!!!), especially when the clients run Windows. There's even a rationale, which I cannot remember right now.

-- 

Regards,
Frank van Bortel