Oracle Data types

Last updated on October 8th, 2019 at 05:43 pm

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

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

Oracle Data types

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.

VARCHAR is a variable length Data Type. For example, if you declare a variable/column of VARCHAR (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 VARCHAR (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

-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.

Number datatype

-NUMBER

-The NUMBER datatype stores number with precision and scale.

-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. Oracle rounds the floating-point numbers.

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

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

Related Articles

Oracle Indexes and types of indexes in oracle with example

Top 49 Oracle Interview questions : Basics , Oracle SQL

How to work with date Functions in Oracle sql

Useful information about INSERT statement in Oracle

Update statement in oracle

Leave a Reply