DB2 10.5 for Linux, UNIX, and Windows

Data types (PL/SQL)

A wide range of data types are supported and can be used to declare variables in a PL/SQL block.

Table 1. Supported scalar data types that are available in PL/SQL
PL/SQL data type	DB2® SQL data type	Description
BINARY_INTEGER	INTEGER	Integer numeric data
BLOB	BLOB (4096)	Binary data
BLOB (`n`)	BLOB (`n`) `n` = 1 to 2 147 483 647	Binary large object data
BOOLEAN	BOOLEAN	Logical Boolean (true or false)
CHAR	CHAR (`n`) `n` = 63 if the string units of the environment is set to CODEUNITS32 `n` = 254 otherwise	Fixed-length character string data of length `n`
CHAR (`n`)	CHAR (`n`) `n` = 1 to 254	Fixed-length character string data of length `n`
CHAR (`n` CHAR)	CHAR (`n` CODEUNITS32 ) `n` = 1 to 63	Fixed-length character string data of length `n` UTF-32 code units¹
CHAR VARYING (`n`)	VARCHAR (`n`)	Variable-length character string data of maximum length `n`
CHAR VARYING (`n` CHAR)	VARCHAR (`n` CODEUNITS32 ) `n` = 1 to 8 168	Variable-length character string data of maximum length n UTF-32 code units¹
CHARACTER	CHARACTER (`n`) `n` = 63 if the string units of the environment is set to CODEUNITS32 `n` = 254 otherwise	Fixed-length character string data of length `n`
CHARACTER (`n`)	CHARACTER (`n`) `n` = 1 to 254	Fixed-length character string data of length `n`
CHARACTER (`n` CHAR)	CHARACTER (`n` CODEUNITS32 ) `n` = 1 to 63	Fixed-length character string data of length `n` UTF-32 code units¹
CHARACTER VARYING (`n`)	VARCHAR (`n`) `n` = 1 to 32 672	Variable-length character string data of maximum length `n`
CHARACTER VARYING (`n` CHAR)	VARCHAR (`n` CODEUNITS32 ) `n` = 1 to 8 168	Variable-length character string data of maximum length `n` UTF-32 code units¹
CLOB	CLOB (1M)	Character large object data
CLOB (`n`)	CLOB (`n`) `n` = 1 to 2 147 483 647	Character large object data of length `n`
CLOB (`n` CHAR)	CLOB (`n` CODEUNITS32 ) `n` = 1 to 536 870 911	Character large object string data of length `n` UTF-32 code units¹
DATE	DATE ²	Date and time data (expressed to the second)
DEC	DEC (9, 2)	Decimal numeric data
DEC (`p`)	DEC (`p`) `p` = 1 to 31	Decimal numeric data of precision `p`
DEC (`p`, `s`)	DEC (`p`, `s`) `p` = 1 to 31; `s` = 1 to 31	Decimal numeric data of precision `p` and scale `s`
DECIMAL	DECIMAL (9, 2)	Decimal numeric data
DECIMAL (`p`)	DECIMAL (`p`) `p` = 1 to 31	Decimal numeric data of precision `p`
DECIMAL (`p`, `s`)	DECIMAL (`p`, `s`) `p` = 1 to 31; `s` = 1 to 31	Decimal numeric data of precision `p` and scale `s`
DOUBLE	DOUBLE	Double precision floating-point number
DOUBLE PRECISION	DOUBLE PRECISION	Double precision floating-point number
FLOAT	FLOAT	Float numeric data
FLOAT (`n`) `n` = 1 to 24	REAL	Real numeric data
FLOAT (`n`) `n` = 25 to 53	DOUBLE	Double numeric data
INT	INT	Signed four-byte integer numeric data
INTEGER	INTEGER	Signed four-byte integer numeric data
LONG	CLOB (32760)	Character large object data
LONG RAW	BLOB (32760)	Binary large object data
LONG VARCHAR	CLOB (32760)	Character large object data
NATURAL	INTEGER	Signed four-byte integer numeric data
NCHAR	NCHAR (`n`) ³ `n` = 63 if the NCHAR_MAPPING configuration parameter is set to GRAPHIC_CU32 or CHAR_CU32 `n` = 127 otherwise	Fixed-length national character string data of length `n`
NCHAR (`n`) `n` = 1 to 2000	NCHAR (`n`) ³	Fixed-length national character string data of length `n`
NCLOB ⁴	NCLOB(1M) ³	National character large object data
NCLOB (`n`)	NCLOB (`n`) ³	National character large object data of maximum length `n`
NVARCHAR2	NVARCHAR ³	Variable-length national character string data
NVARCHAR2 (`n`)	NVARCHAR (`n`) ³	Variable-length national character string data of maximum length `n`
NUMBER	NUMBER ⁵	Exact numeric data
NUMBER (`p`)	NUMBER (`p`) ⁵	Exact numeric data of maximum precision `p`
NUMBER (`p`, `s`)	NUMBER (`p`, `s`) ⁵ `p` = 1 to 31; `s` = 1 to 31	Exact numeric data of maximum precision `p` and scale `s`
NUMERIC	NUMERIC (9.2)	Exact numeric data
NUMERIC (`p`)	NUMERIC (`p`) `p` = 1 to 31	Exact numeric data of maximum precision `p`
NUMERIC (`p`, s)	NUMERIC (`p`, `s`) `p` = 1 to 31; `s` = 0 to 31	Exact numeric data of maximum precision `p` and scale `s`
PLS_INTEGER	INTEGER	Integer numeric data
RAW	BLOB (32767)	Binary large object data
RAW (`n`)	BLOB (`n`) `n` = 1 to 32 767	Binary large object data
SMALLINT	SMALLINT	Signed two-byte integer data
TIMESTAMP (0)	TIMESTAMP (0)	Date data with timestamp information
TIMESTAMP (`p`)	TIMESTAMP (`p`)	Date and time data with optional fractional seconds and precision `p`
VARCHAR	VARCHAR (4096)	Variable-length character string data with a maximum length of 4096
VARCHAR (`n`)	VARCHAR (`n`)	Variable-length character string data with a maximum length of `n`
VARCHAR (`n` CHAR)	VARCHAR (`n` CODEUNITS32 ) `n` = 1 to 8 168	Variable-length character string data of maximum length `n` UTF-32 code units¹
VARCHAR2 (`n`)	VARCHAR2 (`n`) ⁶	Variable-length character string data with a maximum length of `n`
VARCHAR2 (`n` CHAR)	VARCHAR2 (`n` CODEUNITS32 ) `n` = 1 to 8 168⁶	Variable-length character string data of maximum length `n` UTF-32 code units¹
If the string units of the environment is set to CODEUNITS32, the CHAR attribute of the length is implicit. This behavior is similar to NLS_LENGTH_SEMANTICS=CHAR in an Oracle database. When the DB2_COMPATIBILITY_VECTOR registry variable is set for the DATE data type, DATE is equivalent to TIMESTAMP (0). National character strings are synonyms for character strings or graphic strings with the mapping of data types determined by the NCHAR_MAPPING configuration parameter. See “National character strings” for details. For restrictions on the NCLOB data type in certain database environments, see "Restrictions on PL/SQL support". This data type is supported when the number_compat database configuration parameter set to ON. This data type is supported when the varchar2_compat database configuration parameter set to ON.

In addition to the scalar data types described in Table 1, the DB2 data server also supports collection types, record types, and REF CURSOR types.