test

1.    It is used to store fixed length values.

2.    By default, the size is 1 character, and max size is 2000 chars or bytes.

3.     If the data is less than the original specified size, blank pads are applied.

4.     If you try to insert a value that is too long for the column, then Oracle returns an error.

5.     A 1-byte string can be inserted into a CHAR (10) column, but the string is blank-padded to 10 bytes before it is stored.

6.     If you don’t explicitly specify BYTE or CHAR followed the length, Oracle uses the BYTE by default

7.     The Size of a Character Can Range From 1 Byte to 4 Bytes Depending on The Database Character Set.

           CHAR(length CHAR)

CHAR(length BYTE)

Example Blank Space usage:

 

create table test_Spaces(     name char(20),     ename varchar2(20) );   insert into test values('welcome', 'welcome1');          select * from test;

Output:

                  

NAME	ENAME
Welcome	Welcome1

 

CREATE TABLE test_dump (     col1 NUMBER5, 2),     col2 FLOAT(5) ); INSERT INTO test VALUES (1.23, 1.23); INSERT INTO test VALUES (7.89, 7.89); INSERT INTO test VALUES (12.79, 12.79); INSERT INTO test VALUES (123.45, 123.45);

select name,dump(name),ename,dump(ename) from test;

Output:

 

NAME	DUMP(NAME)
welcome	Typ=96 Len=20: 119,101,108,99,111,109,101,32,32,32,32,32,32,32,32,32,32,32,32,32

 

ENAME	DUMP(ENAME)
welcome1	Typ=1 Len=8: 119,101,108,99,111,109,101,49

 

Dump function to return the detailed information of columns:

The string Oracle takes 7 bytes but padded 13 more spaces on the right of the string to make its length 20 bytes for the name column. It is not the case for the ename column because the data type of ename column is a variable-length character string (VARCHAR2).

select name,length(name),ename,length(ename)from test;

Output:

 

NAME	LENGTH(NAME)	ENAME	LENGTH(ENAME)
welcome	20	welcome1	8

NVARCHAR2 Data Type1

 1.    To store variable length UNICODE character values or national character se.

2.    Max size is 4000 bytes or chars.

3.    You specify the national character set as either AL16UTF16 or UTF8 when you create your database. 

 

AL16UTF16 and UTF8

 1.    AL16UTF16 and UTF8 are two encoding forms of the Unicode character set (UTF-16 and CESU-8, correspondingly) and hence NVARCHAR2 is a Unicode-only data type.

2.    When you create a table with an NVARCHAR2 column, you must specify the column length as size characters, or more precisely, code points in the national character set.

3.    One code point has always 2 bytes in AL16UTF16 and from 1 to 3 bytes in UTF8, depending on the particular character encoded by the code point.

4.    Oracle stores a character value in an NVARCHAR2 column exactly as you specify it, without any blank-padding, provided the value does not exceed the length of the column.

5.    If you try to insert a value that exceeds the specified length, then Oracle returns an error.

          The minimum value of size is 1. The maximum value is:

Ø  16383 if MAX_STRING_SIZE = EXTENDED and the national character set is AL16UTF16

Ø  32767 if MAX_STRING_SIZE = EXTENDED and the national character set is UTF8

Ø  2000 if MAX_STRING_SIZE = STANDARD and the national character set is AL16UTF16

Ø  4000 if MAX_STRING_SIZE = STANDARD and the national character set is UTF8

6.    Independently of the maximum column length in characters, the absolute maximum length of any value that can be stored into an NVARCHAR2 column is 32767 or 4000 bytes, depending on MAX_STRING_SIZE.

7.    For example, even if you define the column length to be 16383 characters, Oracle returns an error if you try to insert a 16383-character value but the national character set is UTF8 and all code points are 3 bytes wide.

8.    The value of size is a length constraint, not guaranteed capacity. 

9. If you want an NVARCHAR2 column to be always able to store size characters in both national character sets, use a value of size that is less than or equal to 10922, if MAX_STRING_SIZE = EXTENDED, or 1333, if MAX_STRING_SIZE = STANDARD