Thanks for the question, Sambhav.

Asked: November 15, 2016 - 3:47 am UTC

Last updated: November 30, 2016 - 10:43 am UTC

Version: Oacle 11G

Viewed 50K+ times! This question is

You Asked

Hi team,

Could you please explain the difference between the below two data types :

difference between varchar2(10) and varchar2(10 char) in oracle asktom

I know varcha2(10 char) , we can use in multibyte characters. So could you pleas eexplain which one is good to use in our code and why.

many thanks in advance.

and Chris said...

Varchar2(10) uses the current value of NLS_LENGTH_SEMANTICS to determine the limit for the string.

If this is byte, then it's 10 bytes.

If it's char, then it's 10 characters.

In multibyte character sets these can be different! So if NLS_LENGTH_SEMANTICS = byte, you may only be able to store 5 characters in your varchar2.

So varchar2(10 char) is explicit. This can store up to 10 characters. Varchar2(10) is implicit. It may store 10 bytes or 10 characters, depending on the DB configuration.

It's better to be explicit (10 char). Although all your prod/test/dev databases should have the same setting for NLS_LENGTH_SEMANTICS, doesn't mean they do...

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DATA_LENGTH = CHAR_LENGTH and CHAR_USED = 'C'

Duke Ganote, November 15, 2016 - 3:47 pm UTC

So I was following-up on this by querying:

  select decode(data_length,char_length,'=','!')
       , char_used, count(*), max(data_length), max(char_length)
       , count(unique owner) cnt
    from all_tab_columns
   where owner not like 'SYS%'
   group by decode(data_length,char_length,'=','!'), char_used
   order by 1,2;

D C     COUNT(*) MAX(DATA_LENGTH) MAX(CHAR_LENGTH)          CNT
- - ------------ ---------------- ---------------- ------------
! C       30,105             4000             2000           40
!         38,190            32767                0           65
= B       31,048             4000             4000           52
= C          384             4000             4000            9
=             35                0                0            9

Note the next-to-last result record.

I looked at a few examples. They're user-defined tables/columns. VARCHAR2(4000 CHAR) where the data_length = 4000 ?

Not many, but a few. Odd.

November 15, 2016 - 6:34 pm UTC

What's odd about that?

Not so constant multiple

Duke Ganote, November 15, 2016 - 7:45 pm UTC

I guess I'd expected a constant multiple between the data_length and char_length for a CHAR-based string. Generally true, but not always, it seems:

   select data_length/char_length, count(*)
     from all_tab_columns
    where owner not like 'SYS%'
      and char_used = 'C'
      and char_length > 0
    group by data_length/char_length order by 1;

DATA_LENGTH/CHAR_LENGTH     COUNT(*)
----------------------- ------------
                      1          383
                      2          248
              2.0010005           42
                3.90625            8
             3.92156863            3
                      4       29,804

6 rows selected.

November 16, 2016 - 4:05 am UTC

Our good friend Unicode :-) can be the cause of that

SQL> select data_type
  2     from all_tab_columns
  3    where owner not like 'SYS%'
  4      and char_used = 'C'
  5      and char_length > 0
  6  and data_length/char_length > 1
  7  /

DATA_TYPE
---------------------------------------
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2
NVARCHAR2

Bet me... :)

Duke Ganote, November 16, 2016 - 3:44 pm UTC

Oracle is just "wagering" that very-long variable-length strings are rarely filled. I can create longer and longer varchar columns -- but the same data_length results:

SQL> create table bvar ( b100 varchar2(100 char), b1000 varchar2(1000 char), b2000 varchar2(2000 char), b4000 varchar2(4000 char));

Table created.

Elapsed: 00:00:00.11
SQL> select column_name, data_length, char_length, data_length/char_length from user_tab_columns where table_name = 'BVAR' order by column_id;

COLUMN_NAME                         DATA_LENGTH CHAR_LENGTH DATA_LENGTH/CHAR_LENGTH
----------------------------------- ----------- ----------- -----------------------
B100                                        400         100                       4
B1000                                      4000        1000                       4
B2000                                      4000        2000                       2
B4000                                      4000        4000                       1

However, when Oracle knows the string will be filled (CHAR data type) it refused to go over 2000 characters:

SQL> create table bfix ( b100 char(100 char), b1000 char(1000 char), b2000 char(2000 char));

Table created.

SQL> select column_name, data_length, char_length, data_length/char_length from user_tab_columns where table_name = 'BFIX' order by column_id;

COLUMN_NAME                         DATA_LENGTH CHAR_LENGTH DATA_LENGTH/CHAR_LENGTH
----------------------------------- ----------- ----------- -----------------------
B100                                        400         100                       4
B1000                                      2000        1000                       2
B2000                                      2000        2000                       1

SQL> create table b2001 ( b2001 char(2001 char));
create table b2001 ( b2001 char(2001 char))
                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

The 12c documentation says:

"Independently of the maximum length in characters, the length of VARCHAR2 data cannot exceed:
• 32767 bytes if MAX_STRING_SIZE = EXTENDED
• 4000 bytes if MAX_STRING_SIZE = STANDARD"

https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021

November 17, 2016 - 2:49 am UTC

True, but this is not the same as saying "I can set varchar2 to any length I want and everything stays the same".

A client program requesting a varchar2(1000) column (or array of them) will allocate more memory than one requesting a varchar2(10) column.

Capacity vs Length Constraint

Sergiusz, November 30, 2016 - 7:15 am UTC

It is important to realize that VARCHAR2(x CHAR) is a length constraint, not a guaranteed capacity. VARCHAR2(x CHAR) happens to be the column/variable capacity as well as long as (x <= data type capacity / max char width in the database character set).
Oracle data type capacity is defined in bytes and depends on the data type, SQL vs PL/SQL, and max_string_size (in 12c).

Note, if you want a column size expressed in bytes, it is actually better to write VARCHAR2(x BYTE), not VARCHAR2(x), to make sure that the corresponding DDL is independent of the session NLS_LENGTH_SEMANTICS values.

November 30, 2016 - 10:43 am UTC

Yep, good advice. Thanks Sergiusz!

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer