Re: Determining char/varchar2 column length
- From: Adam Donahue <adonahue@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 14 Jul 2004 13:32:34 -0700
>>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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
