PL/SQL - Data Types
In this chapter, we will discuss the Data Types in PL/SQL. The PL/SQL variables,
constants and parameters must have a valid data type, which specifies a storage
format, constraints, and a valid range of values. We will focus on the SCALAR and
the LOB data types in this chapter. The other two data types will be covered in
other chapters.
S.No Category & Description
1 Scalar
Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN.
Large Object (LOB)
2
Pointers to large objects that are stored separately from other data items, such as text,
graphic images, video clips, and sound waveforms.
Composite
3
Data items that have internal components that can be accessed individually. For
example, collections and records.
4 Reference
Pointers to other data items.
PL/SQL Scalar Data Types and Subtypes
PL/SQL Scalar Data Types and Subtypes come under the following categories −
S.No Date Type & Description
1 Numeric
Numeric values on which arithmetic operations are performed.
2 Character
Alphanumeric values that represent single characters or strings of characters.
3
Boolean
, Logical values on which logical operations are performed.
4 Datetime
Dates and times.
PL/SQL provides subtypes of data types. For example, the data type NUMBER has
a subtype called INTEGER. You can use the subtypes in your PL/SQL program to
make the data types compatible with data types in other programs while embedding
the PL/SQL code in another program, such as a Java program.
PL/SQL Numeric Data Types and Subtypes
Following table lists out the PL/SQL pre-defined numeric data types and their sub-
types −
S.No Data Type & Description
1 PLS_INTEGER
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
2 BINARY_INTEGER
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
3 BINARY_FLOAT
Single-precision IEEE 754-format floating-point number
4 BINARY_DOUBLE
Double-precision IEEE 754-format floating-point number
NUMBER(prec, scale)
5
Fixed-point or floating-point number with absolute value in range 1E-130 to (but not
including) 1.0E126. A NUMBER variable can also represent 0
6 DEC(prec, scale)
ANSI specific fixed-point type with maximum precision of 38 decimal digits
7
DECIMAL(prec, scale)
In this chapter, we will discuss the Data Types in PL/SQL. The PL/SQL variables,
constants and parameters must have a valid data type, which specifies a storage
format, constraints, and a valid range of values. We will focus on the SCALAR and
the LOB data types in this chapter. The other two data types will be covered in
other chapters.
S.No Category & Description
1 Scalar
Single values with no internal components, such as a NUMBER, DATE, or BOOLEAN.
Large Object (LOB)
2
Pointers to large objects that are stored separately from other data items, such as text,
graphic images, video clips, and sound waveforms.
Composite
3
Data items that have internal components that can be accessed individually. For
example, collections and records.
4 Reference
Pointers to other data items.
PL/SQL Scalar Data Types and Subtypes
PL/SQL Scalar Data Types and Subtypes come under the following categories −
S.No Date Type & Description
1 Numeric
Numeric values on which arithmetic operations are performed.
2 Character
Alphanumeric values that represent single characters or strings of characters.
3
Boolean
, Logical values on which logical operations are performed.
4 Datetime
Dates and times.
PL/SQL provides subtypes of data types. For example, the data type NUMBER has
a subtype called INTEGER. You can use the subtypes in your PL/SQL program to
make the data types compatible with data types in other programs while embedding
the PL/SQL code in another program, such as a Java program.
PL/SQL Numeric Data Types and Subtypes
Following table lists out the PL/SQL pre-defined numeric data types and their sub-
types −
S.No Data Type & Description
1 PLS_INTEGER
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
2 BINARY_INTEGER
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
3 BINARY_FLOAT
Single-precision IEEE 754-format floating-point number
4 BINARY_DOUBLE
Double-precision IEEE 754-format floating-point number
NUMBER(prec, scale)
5
Fixed-point or floating-point number with absolute value in range 1E-130 to (but not
including) 1.0E126. A NUMBER variable can also represent 0
6 DEC(prec, scale)
ANSI specific fixed-point type with maximum precision of 38 decimal digits
7
DECIMAL(prec, scale)
