PL/SQL variables, constants, and parameters must have a valid data type, specifying a valid range of storage formats, constraints, and values.
category |
Description |
Scalar |
Single value does not have an internal component, such as number, DATE, or BOOLEAN |
Large Object (LOB) |
Store large objects that point to other data items such as text, graphics images, video clips, and sounds |
Composite |
A data item with an internal component that can be accessed separately. For example, collections and records |
Reference |
Point to other data items |
First, scalar
1.pl/sql scalar data types and subtypes
Data Type |
Description |
Digital |
The numeric value of the arithmetic operation performed on it |
Character |
An alphanumeric string value that represents a single character or character |
Boolean |
Logical value of the logical operation on which to execute |
Date Time |
Date and time |
2.pl/sql numeric data types and subtypes
Data Type |
Description |
Pls_integer |
Signed integers in the range 2,147,483,647 to 2147483648, denoted by 32 bits |
Binary_integer |
A signed integer in the range of 2,147,483,647 to 2147483648, represented by 32 bits |
Binary_float |
Floating-point number in single-precision IEEE 754 format |
Binary_double |
Double-precision floating-point number in IEEE 754 format |
Number (PREC, scale) |
Fixed-point or floating-point number in the range 1E-130 to (but not including) the absolute value 1.0E126. The number variable can also represent 0 |
DEC (PREC, scale) |
ANSI specific fixed-point type uses 38-bit decimal maximum precision |
DECIMAL (PREC, scale) |
IBM-specific fixed point type with 38 decimal places maximum precision |
NUMERIC (pre, Secale) |
Floating-point types have a maximum precision of 38 decimal places. |
DOUBLE PRECISION |
With 126 binary digits maximum precision ANSI-specific float (approx. 38 decimal digits) |
FLOAT |
With 126 binary digits (approximately 38 decimal digits) Maximum precision ANSI and IBM-specific floating-point type |
Int |
With 38 decimal places maximum precision ANSI specific integer type |
INTEGER |
38-bit decimal maximum precision specific integer type for ANSI and IBM |
SMALLINT |
38-bit decimal maximum precision specific integer type for ANSI and IBM |
REAL |
Maximum precision floating point with 63-bit binary digits (approximately 18 decimal places) |
3.pl/sql character data types and subtypes
data type |
description |
CHAR |
a fixed-length string with a maximum size of 32,767 bytes |
VARCHAR2 |
has a maximum size of 32,767 bytes variable length string |
RAW |
The maximum size of 32,767 bytes of a variable-length binary or byte string, rather than PL/SQL interpretation |
NCHAR |
A fixed-length country string with a maximum size of 32,767 bytes |
NVARCHAR2 |
has a maximum size of 32,767 bytes variable length country string |
Long |
has a 32,760-byte maximum size variable length string |
LONG RAW |
The maximum size of a variable-length binary or byte string of 32,760 bytes, rather than PL/SQL interpretation |
rowid |
physical row identifier, one row in the address of a normal table |
urowid |
physical Universal Row Identifier (physical, logical, or foreign row identifier) |
4.pl/sql Boolean data type
A logical value stored using a Boolean data type in a logical operation. The logical values are Boolean values that are true and false, and NULL values.
However, SQL does not have a data type equal to Boolean. Therefore, the Boolean value cannot be used for:
. SQL statements
. Built-in SQL functions (such as TO_CHAR)
. Calling the PL/SQL function from an SQL statement
5.pl/sql date Time and interval type
Field name |
valid datetime value |
valid interval value |
Year |
-4712 to 9999 (excluding year 0) |
Any non-0 integer |
MONTH |
12 |
0 to 11 |
Day |
31 (subject to the values of the year and month, as set by the locale of the calendar) |
Any non-0 integer |
HOUR |
XX to 23 |
0 to 23 |
MINUTE |
XX to 59 |
0 to 59 |
SECOND |
XX to 59.9 (n), where 9 (n) is the time fractional seconds precision |
0 to 59.9 (N), where 9 (n) is the interval fractional seconds precision |
Timezone_hour |
-12 to 14 (range accommodates daylight saving time changes) |
Not applicable |
Timezone_minute |
XX to 59 |
Not applicable |
Timezone_region |
Find V$timezone_names in Dynamic performance view |
Not applicable |
Timezone_abbr |
Find V$timezone_names in Dynamic performance view |
Not applicable |
Ii. large Object (LOB)
Large object (LOB) data types are large to data items such as text, graphic images, video clips, and sound waveforms. LOB data types allow for efficient, random, segmented access to these data. The following are the predefined PL/SQL LOB data types:
Data Type |
Description |
size |
BFILE |
System files for storing large binary objects outside the operational database |
dependent on system, cannot exceed 4,000 gigabytes (GB) |
Blob |
Used to store large binary objects in the database |
8 to 128 terabytes (TB) of |
Clob |
Used to store characters in large chunks of data in a database |
8-128 TB |
NCLOB |
Used to store large chunks of nchar data in a database |
8-128 TB |
(c) PL/SQL data type