Data Types in Oracle

What are the different types of data types in oracle?

Above, I have classified the oracle data type in two different parts,

1)     Scalar data types and

2)     Composite data types.

 Scalar data type includes number, char, varchar, varchar2, date, pls_integer etc. composite data type includes records and collections. Collection again divided into three parts, index by tables this also called as associative array, varray and nested tables.

What scalar data type is?

Scalar data type can hold single value at a time with no internal component.

Let’s look at the category and description of the various scalar data types:-

Category	Data Description
Numeric	Numeric values, on which you can perform arithmetic operations.
Character	Alphanumeric values that represent single characters or strings of characters, which you can manipulate.
BOOLEAN	Logical values, on which you can perform logical operations.
Datetime	Dates and times, which you can manipulate.
Interval	Time intervals, which you can manipulate.

Let’s look at the data type family and data type name of scalar data type:-

Data type family	Data type name
NUMERIC	NUMBER PLS_INTEGER BINARY_FLOAT BINARY_DOUBLE
CHARACTER	CHAR VARCHAR2 NCHAR NVARCHAR2
BOOLEAN	BOOLEAN
DATETIME	DATE TIMESTAMP
INTERVAL	INTERVAL YEAR TO MONTH INTERVAL DAY TO SECONDS

What is difference between CHAR, VARCHAR2 and VARCHAR2 scalar data types?

Char and varchar are similar but differ in the way that they store and retrieve. The char and varchar type are declared with the length that indicates maximum number of characters you want to store. Like char (10) or varchar2 (10).

CHAR: - CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.

VARCHAR: - VARCHAR behaves exactly the same as VARCHAR2.

VARCHAR2:- VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

What is difference between INTEGER, PLS_INTEGER and BINARY_INTEGER?

-          
      Pls_integer is a subtype with base type binary integer

-          
      Binary integer is a subtype with base type integer with a range of '-2147483647'...2147483647

-          Integer is a subtype with base type number (38, 0)



    Read Also:-  Data types in MySql