Inserting Unicode Data.

From: linevoltage_halogen <tropicalfruitdrops_at_yahoo.com>
Date: 29 Oct 2004 11:13:18 -0700
Message-ID: <bee7ac87.0410291013.4d57d6ee@posting.google.com>

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'?

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'

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'                 

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?

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. Received on Fri Oct 29 2004 - 13:13:18 CDT