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