Data type varchar2 can be stored as BYTE or CHAR. If in DDL it's not defined as which, it create automatically as BYTE. This can cause problems since some characters are bigger than one byte. So basically 1 byte is not always equal 1 char. I could not find where to change the default setting for varchar2 data type to CHAR instead of BYTE.
So basically i want define DDL as
CREATE TABLE TBL1 (COLUMN1 VARCHAR2(100))
and get TBL1 with column1 as varchar2(100 CHAR) and not varchar2(100 BYTE)
You can change the default using:
alter system set NLS_LENGTH_SEMANTICS=CHAR
If you want to change it at session level you can use:
alter session set NLS_LENGTH_SEMANTICS=CHAR
from oracle-base.
It's probably safest to set the parameter for the session instead of the instance. According to the Database Reference entry for NLS_LENGTH_SEMANTICS:
Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.