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?
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 Kurpa
ASKER
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 Geerlings
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?
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?
awking00
select owner, table_name, column_name
from all_tab_columns
where data_type = 'CHAR';
Julie Kurpa
ASKER
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 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.
Also, the default is not BYTES. Check the documentation link.