SUBTYPE definitions (PL/SQL)

A subtype is a definition of a type based on a built-in type.

Subtypes provide a layer of abstraction between variables and parameters and the data types that they use. This layer allows you to concentrate any changes to the data types in one location. You can add constraints to subtypes so that they cannot be nullable or limited to a specific range of values.

Subtypes can be defined in:
  • CREATE PACKAGE statement (PL/SQL)
  • CREATE PACKAGE BODY statement (PL/SQL)
  • CREATE PROCEDURE (PL/SQL)
  • CREATE FUNCTION (PL/SQL)
  • CREATE TRIGGER (PL/SQL)
  • Anonymous block (PL/SQL)

Syntax

Read syntax diagramSkip visual syntax diagramSUBTYPEtype-nameISbuilt-in-type RANGEstart-value..end-value NOT NULL

Description

SUBTYPE type-name
Specifies an identifier for the subtype. You cannot specify BOOLEAN as the built-in type.
built-in-type
Specifies the built-in data type that the subtype is based on.
RANGE start-value .. end-value
Optionally defines a range of values within the domain of the subtype that is valid for the subtype.
NOT NULL
Optionally defines that the subtype is not nullable.

Example

The following example shows a package that defines a subtype for small integers:
CREATE OR REPLACE PACKAGE math
IS
SUBTYPE tinyint IS INTEGER RANGE -256 .. 255 NOT NULL
END;