Re: Determining char/varchar2 column length

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Thu, 15 Jul 2004 00:04:19 +0300
Message-ID: <00b501c469e6$25446390$63a423d5@porgand>

> That's correct, but version determines how you can calculate it to begin
> with.

> 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

One minor addition, with *variable width* multibyte charsets, yes this can vary, with fixed width it will be fixed how many bytes a char takes.

> 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.

Btw, this is an important point, that nls_length_semanics affects only table column (and possibly type) definitions, but no PL/SQL datatypes for example. A client of mine had problems after migrating to UTF8 w. character nls length semantics, PL/SQL variables were still defined in byte lengths..

>
> 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.

I'm not sure whether I understand your question, but check whether CHAR_COL_DECL_LENGTH column in DBA_TAB_COLUMNS helps you.

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

Received on Wed Jul 14 2004 - 16:01:03 CDT