Sunday, 21 September 2014

NCHAR and NVARCHAR2 Data types in Oracle

NCHAR and NVARCHAR2 Datatypes in Oracle / Overview of NCHAR Datatype / Overview of NVARCHAR2 Datatype / How to use NCHAR type in Oracle to declare and use Attributes? / Oracle NCHAR datatype How to? /Oracle NVARCHAR2 datatype How to?


NCHAR and NVARCHAR2 Datatypes

NCHAR Datatype
NCHAR datatype is used to store fixed-length Unicode-only character data strings as specified in the national character set. The character set used by NCHAR is either AL16UTF16 or UTF8 which are defined in national character set.
Syntax
NCHAR (size)
where NCHAR is the datatype,
          size is the maximum length of the string to be stored in number of characters
Example
CREATE TABLE Emp (EName NCHAR(30));
Let us assume that the character set used for the creating the table Emp is AL32UTF8. Then, this declaration allocates 60 bytes of memory to store each EName value. That is, every character occupies 2 bytes. In other words, minimum bytes required to store a character is 2, if the character set used is AL32UTF8.
INSERT INTO Emp VALUES( N’Kumar’);
In INSERT INTO statement, the character N (N function) is used to convert the value into Unicode data.
What is Unicode?
It is a Universal Encoded Character set that enables any language to be represented using a single character set. It provides a unique code value for every character, irrespective of the platform, program, or language used.

Overview
Datatype
NCHAR
Default Size
1 Character
Minimum Size
1 – 4 Bytes
Maximum Size
2000 bytes
Default Length Semantics
Character semantics
If the value to be stored is shorter than the declared size?
The memory required for the size characters will be used
If the value to be stored is longer than the declared size?
ERROR
Memory required to store 1 Character
Depends on the character set used. It may vary from 1 byte to 4 bytes. For example, if the character to be stored is English character then 1 byte, if it is Chinese character then 3 bytes
Permitted Values
Alphanumeric characters with various native character data set support


The values that are to be stored and the actual storage along with the total amount of memory consumed are given in the table below for reference;

Declaration
Actual Data
Stored Data
Value to be stored
Bytes needed
(for UTF8 character set)
Data Stored
Bytes used
Bytes saved
NCHAR(5)
‘R101’
4 X 2
‘R101‘
10
0
NCHAR(5)
‘R1001’
5 X 2
‘R1001’
10
0
NCHAR(5)
‘R1’
2 X 2
‘R1‘
10
0
NCHAR(10)
‘ID1456’
6 X 2
‘ID1456‘
10
0
NCHAR(10)
‘ID’
2 X 2
‘ID‘
10
0

Note: Preceding blank spaces and intermediate blank spaces in an input will be considered as data. Only trailing blank spaces gets removed (eliminated) automatically.



NVARCHAR2 Datatype

NVARCHAR2 datatype is used to store variable-length Unicode-only character data strings as specified in the national character set. The character set used by NVARCHAR2 is like NCHAR either AL16UTF16 or UTF8 which are defined in national character set.
Syntax
NVARCHAR2 (size)
where NVARCHAR2 is the datatype,
          size is the maximum length of the string to be stored in number of characters
Example
CREATE TABLE Emp (EName NVARCHAR2(30));
Let us assume that the character set used for the creating the table Emp is AL32UTF8. Then, this declaration needs 60 bytes of memory to store full 30 characters for each EName value. That is, every character occupies 2 bytes. In other words, minimum bytes required to store a character is 2, if the character set used is AL32UTF8.
INSERT INTO Emp VALUES( N’Kumar’);
In INSERT INTO statement, the character N (N function) is used to convert the value into Unicode data.
Overview
Datatype
NVARCHAR2
Default Size
You must specify size
Minimum Size
1 – 4 Bytes
Maximum Size
4000 bytes
Default Length Semantics
Character semantics
If the value to be stored is shorter than the declared size?
Only the memory needed to store the given value will be used.
If the value to be stored is longer than the declared size?
ERROR
Memory required to store 1 Character
Depends on the character set used. It may vary from 1 byte to 4 bytes. For example, if the character to be stored is English character then 1 byte, if it is Chinese character then 3 bytes
Permitted Values
Alphanumeric characters with various native character data set support


The values that are to be stored and the actual storage along with the total amount of memory consumed are given in the table below for reference;

Declaration
Actual Data
Stored Data
Value to be stored
Bytes needed
(for UTF8 character set)
Data Stored
Bytes used
Bytes saved
NCHAR(5)
‘R101’
4 X 2
‘R101‘
8
2
NCHAR(5)
‘R1001’
5 X 2
‘R1001’
10
0
NCHAR(5)
‘R1’
2 X 2
‘R1‘
4
6
NCHAR(10)
‘ID1456’
6 X 2
‘ID1456‘
12
8
NCHAR(10)
‘ID’
2 X 2
‘ID‘
4
16

Note: Preceding blank spaces and intermediate blank spaces in an input will be considered as data. Only trailing blank spaces gets removed (eliminated) automatically.
 



No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...