Re: varchar2 vs. nvarchar2 and confusing unicode
From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Tue, 19 Jan 2010 20:27:25 +0100
Message-ID: <2f1ba$4b56079e$524ba3af$15583_at_cache5.tilbu1.nb.home.nl>
Christian Kütbach wrote:
> Hello NG,
>
> I've got an question: What is the best practice to create a unicode
> supporting column?
>
> I have to set up a Table with unicode support.
>
> Sometime my Table will be installed into an existing Oracle Instance
> (witch may ot be with the startet with the correct NLS settings).
>
> The Documentation is a litlee bit confusing:
>
> If a create a table with following DDL:
>
> create table MyUtf8Test ( vrchr2000Char varchar2(2000 CHAR) );
>
> and insert a (Java String) with 2000 ¤-Signs, i get following error:
> Error: java.sql.SQLException: ORA-01704: Zeichenfolge zu lang, SQL
> State: 42000, Error Code: 1704
>
> Did I cacluate right, that because of the 4000bytes limit, Strings
> longer than 1333 charackters may produce this error?
>
> What is the best practice to create a unicode supporting column?
>
> I have to store 2000 unicode-signs.
> I have an own schema.
>
> I've looked at the oracle website, but it didn't helped.
>
> And where is the difference between varchar2(2000 char) an
> nvarchar2(2000) ?
>
Date: Tue, 19 Jan 2010 20:27:25 +0100
Message-ID: <2f1ba$4b56079e$524ba3af$15583_at_cache5.tilbu1.nb.home.nl>
Christian Kütbach wrote:
> Hello NG,
>
> I've got an question: What is the best practice to create a unicode
> supporting column?
>
> I have to set up a Table with unicode support.
>
> Sometime my Table will be installed into an existing Oracle Instance
> (witch may ot be with the startet with the correct NLS settings).
>
> The Documentation is a litlee bit confusing:
>
> If a create a table with following DDL:
>
> create table MyUtf8Test ( vrchr2000Char varchar2(2000 CHAR) );
>
> and insert a (Java String) with 2000 ¤-Signs, i get following error:
> Error: java.sql.SQLException: ORA-01704: Zeichenfolge zu lang, SQL
> State: 42000, Error Code: 1704
>
> Did I cacluate right, that because of the 4000bytes limit, Strings
> longer than 1333 charackters may produce this error?
>
> What is the best practice to create a unicode supporting column?
>
> I have to store 2000 unicode-signs.
> I have an own schema.
>
> I've looked at the oracle website, but it didn't helped.
>
> And where is the difference between varchar2(2000 char) an
> nvarchar2(2000) ?
>
To store a Euro sign, you do not need Unicode. WE8ISO8859 (in fact, any Oracle RDBMS) will do that. The P15 variant has a glyph defined for the Euro - the P1 does not (but it will happily store 0x80, or the MS Windows Euro glyph).
If you did things correctly, you are trying to store 6000 byte, as the Unicode for the euro is 3 byte: 226,130,172 (Decimal) Obviously, you cannot store 6000 byte in a 4000 byte column.
Yup - a varchar2 cannot be larger than 4000 byte (in a table). Bytes, not characters.
-- Regards, Frank van Bortel Topposting in Usenet groups I regard as offensive - I will not replyReceived on Tue Jan 19 2010 - 13:27:25 CST