What does all_tab_columns.data_length actually store?

[Mark Leonard]

Can somebody please shed some light on the following?

I have created two Oracle (9.2) users and some tables by running a
.sql script (using sqlplus) twice in the same UNIX environment.

Therefore I expect the tables to have the same properties, just
different owners. However, this does not appear to be the case. Here
are some sample outputs. The table name has been changed...

User 1:

SQL> desc my_table_name;
Name Null? Type
------------------------------------ --------
--------------------------------
DATESTAMP NOT NULL DATE
COMPONENTID NOT NULL VARCHAR2(12 CHAR)

select column_name, data_type, data_length, data_precision, data_scale
from all_tab_columns where table_name = 'MY_TABLE_NAME'

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION
DATA_SCALE
--------------------- -------------------- ----------- --------------
----------
DATESTAMP DATE 7
COMPONENTID VARCHAR2 48

(Log out and log in as) User 2:

SQL> desc my_table_name;
Name Null? Type
-------------------------------------- --------
--------------------------------
DATESTAMP NOT NULL DATE
COMPONENTID NOT NULL VARCHAR2(12)

select column_name, data_type, data_length, data_precision, data_scale
from all_tab_columns where table_name = 'MY_TABLE_NAME'

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION
DATA_SCALE
--------------------- -------------------- ----------- --------------
----------
DATESTAMP DATE 7
COMPONENTID VARCHAR2 12

--

My questions are
1. Why does describe for user 1 show VARCHAR2(12 CHAR) ?
2. Why is the data length 4 * 12 rather than just 12 ?
All of the VARCHAR2 columns have this property (4 * number of
chars).

[Jusung Yang]

DATA_LENGTH is measured in byte.
Are these two users in the same database?
What do you see with these?

show parameter semantics
select DATA_LENGTH,CHAR_LENGTH,CHARACTER_SET_NAME from user_tab_columns ....
select * from v$nls_parameters where parameter like '%CHARACTERSET';

- Jusung Yang

mark.l...@tertio.com (Mark Leonard) wrote in message news:<467336d2.03061...@posting.google.com>...

[Mark Leonard]

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.

[Jusung Yang]

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.l...@tertio.com (Mark Leonard) wrote in message news:<467336d2.03061...@posting.google.com>...