Re: Length of CLOB in bytes.

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 30 Aug 2007 11:31:39 GMT
Message-ID: <1188473496.982415@proxy.dienste.wien.at>

Krivenok Dmitry <krivenok.dmitry_at_gmail.com> wrote:

>> | > | Is there a way to get the length of a CLOB in bytes (not in chars)?
>> | >
>> | > dbms_lob.getlength if your database is single byte character set
>> | > 2*dbms_lob.getlength if your database is multi byte character set
>> |
>> When database character set is multi-byte, CLOB is always stored
>> in UCS2 whatever is the database character set.

>
> Ok, suppose I have a UTF8 string which consist of 2 characters.
> Suppose also that 1st character's length = 4 bytes and 2nd character's
> length = 3 bytes.
>
> What about this simple example?
> How many UCS2 characters needed to store this string in CLOB?

If the database is 10g with characters set AL32UTF8, compatibility mode is 10.0 and the table was not migrated from 9.2, CLOBs and NCLOBs will be stored in AL16UTF16 (see Metalink 257772.1).

In AL16UTF16, almost all characters are 2 bytes wide, so twice the number of characters is correct in almost all cases.

In the rare case when you use characters with UNICODE code points beyond 0x10000 (some rare or dead languages, musical symbols, ...), these characters will be encoded as 4 bytes in AL16UTF16.

So, the 100% correct answer is: convert the CLOB to AL16UTF16 and count the bytes...

Yours,
Laurenz Albe Received on Thu Aug 30 2007 - 06:31:39 CDT