When you create an Oracle table, you have to specify a datatype for each column, which is associated with a specific storage format, constraints, and a valid range of values. The knowledge of data types is necessary before creating the table. Here in this article, we will see important oracle data types which you will be using in PLSQL, SQL quite often while working with Oracle database
Oracle provides the following categories of built-in datatypes:
Character Datatypes
Numeric Datatypes
DATE Datatype
LOB Datatypes
RAW and LONG RAW Datatypes
ROWID and UROWID Datatypes
Character datatype
-CHAR, NCHAR, VARCHAR2 & NVARCHAR2.
The CHAR datatype is a fixed-length alphanumeric string which has a maximum length in bytes.
-When creating a CHAR datatype, the database will preserve space for the incoming data and if the data is shorter than maximum size, it will be space-padded to the right
For example, if you declare a variable/column of CHAR (5) data type, then it will always take 5 bytes irrespective of whether you are storing 1 character or 5 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(5), so we can store max 5 characters in this column.
-The VARCHAR2 datatype is a variable-length alphanumeric string, which has a maximum length in bytes. It can store up to 4000 bytes.
VARCHAR2 is a variable length Data Type. For example, if you declare a variable/column of VARCHAR 2(10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR2 (10), so we can store max 10 characters in this column. The space utilized would be depending of the values in the column and there will be not wastage of space.
Also I would stress here one thing that when we give VARCHAR2(10) here is 10 is the number of the bytes not the character. Number of character and bytes are similar when ASCII character are involved,but the equation get changed we start using using character other than ASCII
The length semantics of character datatypes can be measured in bytes or characters. Byte semantics treat strings as a sequence of bytes. This is the default for character datatypes. Character semantics treat strings as a sequence of characters. A character is technically a codepoint of the database character set. So VARCHAR (10 Byte) has length measured in byte. VARCHAR (10 CHAR) has length measured in character. The default for VARCHAR is based on NLS_LENGTH_SEMANTICS parameter set in the database. By default this is set to Byte,so when we use VARCHAR(10) , it uses length measured in byte
-The VARCHAR datatype is synonymous with the VARCHAR2 datatype. To avoid possible changes in behavior, always use the VARCHAR2 datatype to store variable-length character strings.
-NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. The character set of NCHAR and NVARCHAR2 datatypes can only be either AL16UTF16 or UTF8 and is specified at database creation time as the national character set. AL16UTF16 and UTF8 are both Unicode encoding.
Example create table test ( First_name char(6), Last_name varchar2(10));
Number datatype
-NUMBER or NUMBER(p,s)
-The NUMBER(p,s) datatype stores number with precision and scale.
– NUMBER datatype stores up-to 38 digits of precision
-Numeric datatypes store negative and positive integers fixed-point numbers and floating-point numbers
-When a column is defined as NUMBER (6, 2),the range of values can be stored from –9999.99 to 9999.99. So we have total digit as 6 and 4 digit before decimal and 2 digit after decimal Oracle rounds the floating-point numbers.
Example create table test ( First_name char(6), Last_name varchar2(10) age number; Salary number(10,2) );
Date and Time
–DATE, TIMESTAMP (with time zone or local time zone),
-The DATE data type is used to store date and time information.
-This data type has a number of specific functions for manipulating, formatting and viewing its data.
-The DATE data type holds storage of seven bytes and has information about century, year, month, day, hours, minutes & seconds.-The NLS_DATE_FORMAT parameter can be changed to control the viewing of the data. The SYSDATE function returns the current date
–Oracle database also provide timezone aware DATE datatype
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
Example create table test ( First_name char(6), Last_name varchar2(10) age number, Salary number(10,2), start_date date );
Large Objects
-BLOB(binary large object), CLOB(character large object), NCLOB & BFILE
-Columns of these datatypes can store unstructured data including text, image, video, and spatial data. -The CLOB datatype can store up to eight terabytes of character data using the CHAR database character set.
-The BLOB datatype is used to store unstructured binary large objects such as those associated with image and video data where the data is simply a stream of “bit” values.
-The BFILE data type value works as a file locator or pointer to file on the server’s file system. The maximum file size supported is 8 TB to 128 TB.
Long
Variable length character data up to 2 G
rowid
A 64 base number system representing the unique address of the row in the table
Hope you like this content on Oracle Data types
Related Articles
Oracle Indexes
Oracle Interview questions
Date Functions in Oracle
INSERT statement in Oracle
Update statement in oracle
how to check all constraints on a table in oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832
