Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Determining char/varchar2 column length

Re: Determining char/varchar2 column length

From: Adam Donahue <adonahue_at_opsware.com>
Date: Wed, 14 Jul 2004 14:18:55 -0700
Message-ID: <40F5A33F.4070902@opsware.com>

Apparently char_col_decl_length does not work as advertised:

My test:

SQL> create table foo( a varchar2(50 byte), b varchar2(50 char) );

Table created.

SQL> select column_name, data_length, char_length, char_col_decl_length from all_tab_columns where table_name = 'FOO';

COLUMN_NAME                    DATA_LENGTH CHAR_LENGTH CHAR_COL_DECL_LENGTH
------------------------------ ----------- ----------- --------------------
A                                       50          50                   50
B                                      150          50                  150

I would have expected char_col_decl_length to be the same for both columns.

Also tried without an explicit char specification in the datatype (depending on the system setting of nls_length_semantics instead), but got the same results.

Adam

Tanel Põder wrote:

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



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:16:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US