Data Types In Oracle SQL - Part 1 - Character Data Types
In relational databases, the data is stored in the form of tables. A table is set of rows, and a row contains multiple columns. each such a row is called as record, and all the data related to that record, resides in columns.
Each column in sql has a data type. A data type specifies which kind of data a column can accommodate. While creating tables, you must specify, data type for each column.
The built in data types that oracle supports are categorized into :
1.Character Data Types
2.Numeric Data Types
3.Raw, Long Raw and Long Data Types
4.Date and Time Data Types
5.Large Object Data Types
6.RowID Data Types
Let’s see all the categories in detail,
Character Data Types :
The kind of data that you can store in any character data type is alphanumeric data, such as words, text from any language. All the data stored in the form of Strings, with the values corresponding to the character encoding scheme.
Character encoding scheme is a set of characters / symbols and their encodings. Suppose if you have a set of characters i.e character set with 2 alphabets ‘A’, ‘B’, you can give encoding to the both characters as ‘A’ = 1, ‘B’ = 2.
Oracle database supports most national, international, and vendor-specific encoded character set standards, such as 7-Bit ASCII Character Set, UTF-8, UTF-16 etc.
The different data types that come under character data types are
1.CHAR
2.VARCHAR
3.VARCHAR2
4.NCHAR
5.NVARCHAR2
CHAR DATA TYPE :
1.It specifies fixed-length character strings.
2.You must specify the string length in terms of bytes or characters, when you are defining a column in a table.
3.The length of the string should be in between 1 and 2000 bytes.
4.The default length is 1 byte and maximum is 2000 bytes.
5.If the data you are inserting into the column is less than the specified column length, then the blank pads will be inserted in the remaining place.
6.If the value, you are specifying is too large, Oracle returns an error.
7.The size of a character can range from 1 Byte to 4 Bytes depending on the database character set.
VARCHAR2 Data Type:
1.The VARCHAR2 data type stores variable length character strings.
2.The minimum size is 1 byte and maximum size is 4000 bytes.
3.The Oracle database allocates memory to the column based on the size of the value it is going to store. for example , even though, you have defined 2000 bytes as the maximum capacity for a varchar2 column, and if you are storing a value of size 3 bytes, the oracle database reserves only 3 bytes to the column, instead of 2000 bytes, which saves a lot of memory.
4.If the value, you are storing in a varchar2 column, exceeds maximum length, Oracle database returns an error.
VARCHAR Datatype :
The VARCHAR data type is synonymous with the VARCHAR2 datatype. As per Oracle, it is recommended to use the VARCHAR2 datatype to store variable length character strings.
NCHAR Data Type :
1.NCHAR data type stores Unicode character data.
2.The character set of NCHAR can be either AL16UTF16 or UTF8, and is specified at the time of database creation as national character set.
3.Similar to CHAR data type, it stores fixed length character strings that correspond to the national character set.
4.The maximum length allowed is 2000 bytes.
5.If the value is shorter than actual size then the blank pads are applied.
6.When you create a table with an NCHAR column, the maximum size specified is always in character length semantics. for example, if the national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes.
1.NVARCHAR2 data type stores unicode character data.
2.The character set of NVARCHAR2 can be either AL16UTF16 or UTF 8, and is specified at the time of database creation as national character set.
3.Similar to VARCHAR2 , it stores variable length character strings.
4.The minimum size is 1 byte and maximum size is 4000 bytes.
Each column in sql has a data type. A data type specifies which kind of data a column can accommodate. While creating tables, you must specify, data type for each column.
The built in data types that oracle supports are categorized into :
1.Character Data Types
2.Numeric Data Types
3.Raw, Long Raw and Long Data Types
4.Date and Time Data Types
5.Large Object Data Types
6.RowID Data Types
Let’s see all the categories in detail,
Character Data Types :
The kind of data that you can store in any character data type is alphanumeric data, such as words, text from any language. All the data stored in the form of Strings, with the values corresponding to the character encoding scheme.
Character encoding scheme is a set of characters / symbols and their encodings. Suppose if you have a set of characters i.e character set with 2 alphabets ‘A’, ‘B’, you can give encoding to the both characters as ‘A’ = 1, ‘B’ = 2.
Oracle database supports most national, international, and vendor-specific encoded character set standards, such as 7-Bit ASCII Character Set, UTF-8, UTF-16 etc.
The different data types that come under character data types are
1.CHAR
2.VARCHAR
3.VARCHAR2
4.NCHAR
5.NVARCHAR2
CHAR DATA TYPE :
1.It specifies fixed-length character strings.
2.You must specify the string length in terms of bytes or characters, when you are defining a column in a table.
3.The length of the string should be in between 1 and 2000 bytes.
4.The default length is 1 byte and maximum is 2000 bytes.
5.If the data you are inserting into the column is less than the specified column length, then the blank pads will be inserted in the remaining place.
6.If the value, you are specifying is too large, Oracle returns an error.
7.The size of a character can range from 1 Byte to 4 Bytes depending on the database character set.
VARCHAR2 Data Type:
1.The VARCHAR2 data type stores variable length character strings.
2.The minimum size is 1 byte and maximum size is 4000 bytes.
3.The Oracle database allocates memory to the column based on the size of the value it is going to store. for example , even though, you have defined 2000 bytes as the maximum capacity for a varchar2 column, and if you are storing a value of size 3 bytes, the oracle database reserves only 3 bytes to the column, instead of 2000 bytes, which saves a lot of memory.
4.If the value, you are storing in a varchar2 column, exceeds maximum length, Oracle database returns an error.
VARCHAR Datatype :
The VARCHAR data type is synonymous with the VARCHAR2 datatype. As per Oracle, it is recommended to use the VARCHAR2 datatype to store variable length character strings.
NCHAR Data Type :
1.NCHAR data type stores Unicode character data.
2.The character set of NCHAR can be either AL16UTF16 or UTF8, and is specified at the time of database creation as national character set.
3.Similar to CHAR data type, it stores fixed length character strings that correspond to the national character set.
4.The maximum length allowed is 2000 bytes.
5.If the value is shorter than actual size then the blank pads are applied.
6.When you create a table with an NCHAR column, the maximum size specified is always in character length semantics. for example, if the national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes.
CREATE TABLE student(name NCHAR(30));7.The above statement creates a column with maximum character length of 30 and maximum byte length of 90.
The maximum byte length = maximum character length * maximum no of bytes in each character.NVARCHAR2 Data Type :
1.NVARCHAR2 data type stores unicode character data.
2.The character set of NVARCHAR2 can be either AL16UTF16 or UTF 8, and is specified at the time of database creation as national character set.
3.Similar to VARCHAR2 , it stores variable length character strings.
4.The minimum size is 1 byte and maximum size is 4000 bytes.
Comments
Post a Comment