We help IT Professionals succeed at work.

Odd Define of Table Column Includes CHAR in Column Length

Julie Kurpa
Julie Kurpa asked
on
80 Views
Last Modified: 2019-03-21
We have an Oracle 11g database.

Recently a vendor altered a column this way:

ALTER TABLE myTable MODIFY myColumn varchar2(12 char);

I don't know what the column looked like before but when you describe the table it looks like this for that column:

Name                 Type
-----------------      -----------------------------
myColumn       VARCHAR2(12 CHAR)


In looking at the dba_tab_columns table, the column shows this way:

table_name    column_name    data_type     data_length
-------------         -----------------        ----------------   ---
myTable          myColumn          VARCHAR2   12


Google searches show others have done this but I haven't found an explanation on WHY?  
Can anyone explain why you would want to specify "CHAR" in the length of a column?

Thank you.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dmitry KurashkinDeveloper
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
The documentation goes into more detail.

Also, the default is not BYTES.  Check the documentation link.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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';

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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?
Julie KurpaSr. Systems Programmer

Author

Commented:
Based on Dmitry Kurashkin's explanation, do you think that because this column is used to hold passwords, the vendor defined it as "12 CHAR" to allow the use of special characters?
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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?
Julie KurpaSr. Systems Programmer

Author

Commented:
Still not completely understanding the "why" but perhaps that's something I need to ask the vendor.   :(

My database shows:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
nls_length_semantics                 string      BYTE

Does anyone know how to find out if there are any other table columns with this CHAR in the length?
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
select owner, table_name, column_name
from all_tab_columns
where data_type = 'CHAR';
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks Awking00.  But that's not giving me what I need.  

I want to find out what tables have columns defined like the one below.  The (12 CHAR) part of the type doesn't show up that way in the user_tab_columns.  

desc MyTable

Name                                      Null?    Type
----------------------------------------- -------- --------------------

MyCode                                  NOT NULL NUMBER(10)
MyName                                  NOT NULL VARCHAR2(75)
MyUserID                                                    VARCHAR2(50)
MyPassWord                                             VARCHAR2(12 CHAR)   <-- this shows in "desc" but not in user_tab_columns.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
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';
Julie KurpaSr. Systems Programmer

Author

Commented:
oh...I missed that.  Sorry.

Using that query I see that no other tables besides the two I'm concerned about have the length set that way.

I guess now I pursue the vendor to find out the "why" they have set it that way.
Julie KurpaSr. Systems Programmer

Author

Commented:
Thanks everyone.   I very much appreciate your assistance with this.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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
Julie KurpaSr. Systems Programmer

Author

Commented:
Thank you so much for that explanation!

In my database, would special characters like @,%, &, # etc use more bytes than a regular letter?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

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

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions