Saturday, November 16, 2013

DML- DATATYPE

Today We are going to study about oracle datatypes and their type where to use , which to use.
I am going to post today difference between varchar2(1 char) and nvarchar2(1).



Today only one difference, but timely i will update this post, with more diff's of datatypes.
Check the below set of queries
create table t (a varchar2(1));
create table t1 (b varchar2(1 char));

SQL> insert into t values (unistr('\0161'));
insert into t values (unistr('\0161'))
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."A" (actual: 2, maximum: 1)
SQL> insert into t1 values (unistr('\0161'));
1 row created.
SQL> select * from t1;
B
-
Ü

If you look at above queries, here i create two tables having datatypes varchar2(1) and
varchar2(1 char). If i insert data in both tables (data is same and byte len is two), table with datatype varchar2(1) is fail to insert and table with datatype varchar2(1 char) has been not failed the reason being byte length of string.

 On other hand nvarchar2(1) and varchar2(1 char) will not fail in above case.
The NVARCHAR2 datatype was for that want to use Unicode values without changing the character set for database (which is used by VARCHAR2). The NVARCHAR2 is a Unicode-only datatype.
Both columns in your example (Unicode VARCHAR2(1 CHAR) and NVARCHAR2(1)) would be able to store the same data, however the byte storage will be different. Some strings may be stored more efficiently in one or the other.

No comments:

Post a Comment

web stats