Re: What does all_tab_columns.data_length actually store?

Yes, AL32UTF8 can use up to 4 bytes to store one character. That would explain the 4* lengths. Still I am not quite sure why 2 users would see 2 different things in the all_tab_columns. It takes some playing around to find out, I guess.
Internationalization is a much bigger topic than most people realize. So far you are dealing with the database only. The OS and the application frontend also need to be taken into account.

              loading tool PL/SQL code Client code Presentation
<data source> -----------> <database> ----------> <UI>

A typical data flow like this, one needs to make sure all of these components work together and the data survice the flow without corruption.

If your current concern is only the database side of things, a lot of good materials can be found at Metalink. Pay special attention to the posts by
Sergiusz Wolicki. He is very familiar with all the characterset related issues.

• Jusung Yang

mark.leonard_at_tertio.com (Mark Leonard) wrote in message news:<467336d2.0306120253.5530f29f_at_posting.google.com>...
> Yes. Both users are on the same database.
> The value of nls_length_semantics is CHAR.
> I ran the query you gave and the values of CHAR_LENGTH and
> CHARACTER_SET_NAME are the same for each user's table.
>
> I think 4 bytes of data per char is probably correct,
> as the database is set to use UTF8 encoding.
>
> I now suspect that somebody else in my company has changed the
> initialisation parameters without informing me. The user with BYTE
> semantics was created a day earlier than the user that has CHAR
> semantics.
>
> We have modified our table creation scripts to contain the following
> command:
> alter session set nls_length_semantics=byte;
> which should help on the consistency front.
>
> SQL> select * from v$nls_parameters where parameter like
> '%CHARACTERSET';
>
> PARAMETER
> ----------------------------------------------------------------
> VALUE
> ----------------------------------------------------------------
> NLS_CHARACTERSET
> AL32UTF8
>
> NLS_NCHAR_CHARACTERSET
> AL16UTF16
>
> Thanks for your reply...I'd welcome any advice regarding
> internationalisation.
Received on Thu Jun 12 2003 - 18:36:48 CEST