Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.
Odd Define of Table Column Includes CHAR in Column Length
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.
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialCan anyone explain why you would want to specify "CHAR" in the length of a column?One character can have length more than one byte. If you use only ASCII characters, there no difference how to write: VARCHAR2(12 chars) or VARCHAR2(12 bytes) (the latter is the default). But if you will ever use Unicode symbols, you need more than 1 byte per character (up to 4 bytes), so, if you declared the column as 12 bytes long, you will see, that sometimes it is possible to save only 3 characters. This notation guarantees, that you can always store 12 symbols in the column.
The documentation goes into more detail.
Also, the default is not BYTES. Check the documentation link.
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';
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
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?
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?
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?
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?
Does your database support a multi-byte character set?
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?
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?
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.
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.
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';
select table_name, column_name from user_tab_columns where char_used = 'C';
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.
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.
>>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
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
Thank you so much for that explanation!
In my database, would special characters like @,%, &, # etc use more bytes than a regular letter?
In my database, would special characters like @,%, &, # etc use more bytes than a regular letter?
>>In my database, would special characters like @,%, &, # etc use more bytes than a regular letter?
ASCII characters are single-byte:
http://www.asciitable.com/
ASCII characters are single-byte:
http://www.asciitable.com/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database
From novice to tech pro — start learning today.
-
Windows Server 2016Certification: MCSE Microsoft Certified Systems Engineer - Installatio… 312 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2007 Adva… 188 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Outlook 2016 Par… 211 lessons
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Excel 2016 Part … 129 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
It has to do with multi-byte character sets. Using CHAR allows for 12 characters. The alternative is myColumn varchar2(12 BYTES) which allows 12 bytes. In a single byte character set, they are the same.