Odd Define of Table Column Includes CHAR in Column Length

The documentation goes into more detail.

Also, the default is not BYTES.  Check the documentation link.

For your DBA_TAB_COLUMNS query, you can see the difference with the CHAR_USED column:

create table tab1(col1 varchar2(1 char), col2 varchar2(1 byte));

select column_name,data_type,char_used from dba_tab_columns where table_name='TAB1';

Select all Open in new window

Looks like at least in 12c and above, the system default is byte.

It is controlled by nls_length_semantics:
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/NLS_LENGTH_SEMANTICS.html

Sorry, I was trying to point out, that it is a changeable default value.  You can modify the system parameter and change the "default".

It is not a hard default that you can always count on, that is a default to me.  You can say that the default for NLS_DATE_FORMAT is DD-MON-RR, but would you depend on that always being the case?

I would say that slightwv also explained it.  But, this only makes a difference in systems that allow and support multi-byte characters, like those used in some Asian languages (Chinese, Japanese, Korean, etc.).  In systems that support only European languages (with single-byte characters) this column definition makes no difference.

Does your database support a multi-byte character set?

select owner, table_name, column_name
from all_tab_columns
where data_type = 'CHAR';

Pretty sure that slightwv already gave it, but here it is again:

select table_name, column_name from user_tab_columns where char_used = 'C';

>>I guess now I pursue the vendor to find out the "why" they have set it that way.

No need.  They explicitly used CHAR to ensure 12 characters can be stored in that field no matter what the database character set is.

Pick almost any unicode character and it is a multi-byte character.  If your database is set up for multi-byte characters and your database has nls_length_semantics set to BYTE you need to use CHAR to ensure a specific number of characters.

For example, take the smiley face emoji:
https://www.fileformat.info/info/unicode/char/263a/index.htm

It is actually 3 bytes in length.  If your database's default character set is AL32UTF8 and nls_length_semantics is BYTE.

You create a table like:
create table tab1(col1 varchar2(10), col2 varchar2(10 CHAR));

You can store 3 smiley face emojis in col1 and 10 in col2.

Why?  col1 is 10 bytes in length and the emoji is 3 bytes.  col2 was declared as 10 characters no matter how many bytes each character contains.

To see your database setup for NLS:
select * from nls_database_parameters;

The important one in the list is:  NLS_CHARACTERSET

>>In my database, would special characters like @,%, &, # etc use more bytes than a regular letter?

ASCII characters are single-byte:
http://www.asciitable.com/