| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Determining char/varchar2 column length
>>For 8i, character length = data_length
>>
>>For 9i, character length = char_length (which /may/ equal data_length
>>depending on the value of char_used)
>>
>>
>
>No, the character/data length ratio depends on whether you're using a single
>or multibyte character set, not version.
>
>
That's correct, but version determines how you can calculate it to begin
with.
By "character length" or "column length" I mean the column length as specified in the table declaration, e.g.,
create table foo (
bar varchar2(30);
)
In 8i, data_length would be set to "30" (which is the byte_length), but if you're using a multibyte character set, the actual number of characters that can be stored there will vary. (e.g., 30 US7ASCII characters, but say only 10 multibyte Japanese characters).
In 9i, the nls_length_semantics allows one to specify how Oracle treats the length specified during table definition. Using "byte" semantics, it acts the same as 8i. Using "char" semantics, however, data_length no longer reflects the length specified in the column definition, but rather calculates this length (the actual number of bytes needed) using the database character set. For UTF8, this is typically three-times the actual length specified in the column definition.
So, the idea is we want a query to say "here's the value specified in the table create statement" for the length of that column. Whereas in 8i we could query data_length, in 9i data_length does /not/ always reflect this value. Rather, it's stored in char_length, a field that does not exist in 8i.
Obviously there are a zillion ways to work around this, but I was wondering if there's a cross-platform compatible way to do this calculation, via a single query. For example, does Oracle store the multiplication factor is used to convert characters to byte in char-semantics multibyte database characters sets anywhere?
Thanks,
Adam
>Tanel.
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jul 14 2004 - 15:29:37 CDT
 |
 |