In this tutorial, we will learn about different data types. Here we will learn data types available in ORACLE SQL.
What is a Data Type?
The data type define domain of values that that each column can store. Each value in SQL in manipulated by a Data Type.
The value of one data type is different from another data type.
Built-in Data Types in Oracle
- CHARACTER Data Types
- NUMBER Data Types
- LONG and RAW Data Types
- DATETIME Data Types
- LARGE OBJECT Data Types
- ROWID Data Types
CHARACTER Data Types
It is used to store Alphanumeric Data and information is stored in string with Byte collection as values.It can belong to either 7 – Bit ASCII character set or EBCDIC (extended binary coded decimal interchange code).
The different character Data Types are:
- CHAR : Fixed size and should be specified, if data is less than specified size Blank Pads are applied. Range – 1 bytes to 4 bytes and Default length is 1 Byte and the maximum is 2000 Bytes.
- NCHAR : It contains Unicode data and length is determined by National Character Set Definition. Size is fixed and Padded if data is shorter than specified. Maximum size is 2000 Bytes.
- VARCHAR2 : Size is variable and occupies only supplied space. Size varies from 1 Byte to 4000 Bytes.
- NVARCHAR2 : Contains only Unicode data and minimum size is 1 byte and maximum size is 4000 Bytes.
NUMERIC Data Types
- Number Data Types : It stores zero, positive, negative fixed and floating point numbers.
- Float Data Type : It facilitates to have a decimal point from the first to last digit.
- Long Data Type : This data type stores very lengthy Text Strings.
DATE & TIME Data Types
- DATE Data Types: It stores Date and Time information. The information reveled by date is Century, Year, Month, Date, Hour, Minute, Second.
- TIMESTAMP Data Types : It is an extension of date data type, it stores the data in the form of Century, Year, Month, Date, Hour, Minute, Second.
RAW & LONG RAW Data Types
- RAW & LONG RAW Data types are used to store Binary data or Byte string.
- These are variable length data types.
- Mostly used to store graphics, sound, scanned documents etc.
- Each Hexadecimal Character represents four bits of RAW data.
LARGE OBJECT (LOB) Data Types
- The built-in LOB types are BLOB, CLOB and NCLOB and they store data internally.
- The Bfile is a LOB which stores data externally.
- Maximum size of these data types are up to 4 GB.
- BLOB : Stores unstructured Binary Large Objects.
- CLOB : Stores single byte or Multi bytes character data.
- NCLOB : It stores Unicode data using national character set.
- BFILE : It enable access to Binary File LOB’s which are stored in the File System outside ORACLE.
ROWID Data Types
- Each row in the database has an address and these row address can be queried using the PSEUDO Column ROWID.
- ROWID’s efficiently support Partitioned table and Indexes.
Below is a comparison of different data types supported by different DATABASE Vendors.
|FLOAT||NUMBER (SINGLE)||FLOAT, REAL||NUMBER||FLOAT||NUMERIC|
|BINARY OBJECT||OLE Object Memo||BINARY, VARBINARY, IMAGE||LOB’s
|BLOB, TEXT||BINARY, VARBINARY|