How to Define Subtypes in PL/SQL

Declaring every airport code as CHAR(3) and every percentage as a number between 0 and 100 repeats the same details throughout the code, and nothing stops a percentage of 120. A subtype gives a type a name, with optional constraints, so variables say what they hold and PL/SQL enforces the limits.

Code for This Guide

The main examples are in the examples/plsql-basics folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

subtype name is base_type [(size)] [range low .. high] [not null];

A subtype can add a size, a NOT NULL constraint, or, for PLS_INTEGER and related types, a RANGE. It can also be based on a column with %TYPE.

A Code and a Percentage

t_airport is a three-character code, and t_percent a PLS_INTEGER limited to 0 to 100.

Example:

declare
  subtype t_airport is char(3);
  subtype t_percent is pls_integer range 0 .. 100;     -- RANGE: for PLS_INTEGER subtypes
  v_origin  t_airport := 'DXB';
  v_load    t_percent;
begin
  v_load := 87;
  dbms_output.put_line(v_origin || ' load factor ' || v_load || '%');
  v_load := 120;
end;
/

Output:

DXB load factor 87%

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error
ORA-06512: at line 9

87 is accepted; 120 raises ORA-06502, value or conversion error, because it is outside the range.

Predefined Subtypes and %TYPE

PL/SQL has predefined subtypes such as NATURAL (0 or more), POSITIVE (1 or more), POSITIVEN (positive and not null), and SIGNTYPE (-1, 0, or 1). A subtype can also follow a column's type with %TYPE.

Example:

-- predefined subtypes: NATURAL is 0 or more, POSITIVEN 1 or more and NOT NULL;
-- a subtype can also be based on a column's type with %TYPE
declare
  subtype t_fare is tickets.fare%type;
  v_fare  t_fare := 513.01;
  v_count natural := 0;
begin
  dbms_output.put_line('fare ' || v_fare || ', count ' || v_count);
  v_count := -1;
end;
/

Output:

fare 513.01, count 0

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: value or conversion error
ORA-06512: at line 7

The fare variable takes the type of TICKETS.FARE, so it follows any change to the column. Assigning -1 to a NATURAL fails with ORA-06502.

Things to Know

• Declare subtypes in a package specification to share them across programs.
• A NOT NULL subtype needs an initial value in every declaration.
• RANGE constraints are checked on every assignment, which costs a little time in tight loops.

Related Guides

• How to Create Domains in Oracle, the SQL counterpart for columns

Conclusion

Subtypes name a type with optional size, range, and NOT NULL constraints, so PL/SQL variables document what they hold and reject values that do not fit. Use predefined subtypes such as NATURAL and POSITIVE, base subtypes on columns with %TYPE, and share them in package specifications.