PL/ SQL Data Types

PL/SQL (Procedural Language/Structured Query Language) is a procedural extension language for SQL used specifically for the Oracle database to ease the management of data and the flow of operations. A core feature of PL/SQL is its diverse set of data types, designed to handle everything from simple numbers and strings to large, unstructured data such as images or text files.

In this article, We will learn about subtypes of data types of PL/SQL, namely scalar types, numeric types, character types, Boolean types, date and time types, large objects, and a type by user-defined subtypes.

Introduction to PL/SQL Data Types

PL/SQL uses many data types corresponding to different types of data including scalar and composite data types and reference data types along with large objects (LOBs).

These data types are used to declare the sharable variables and constants in the packages, parameters for subroutines, and return values of function and procedure subroutines in PL/SQL programs. PL/SQL data types are very much similar to SQL data types and have extra characteristics attached to PL/SQL.

Scalar Data Types in PL/SQL

Scalar data types are basic types that store only one value at a time.like numbers, characters, or even logical values each representing an individual value. The scalar data types are categorized into:

• Numeric Types: It Stores any integer value along with a fractional entity whatever large it is or as per the requirement of the program.
• Character Types: Nodes represent strings of text and These act as structures of text whereby an individual string is represented by a node.
• Boolean Types: It Contains ‘true’ or ‘false’ values.
• Datetime Types: It is Used to represent date and time values which are of typical usage in computer systems.

Subtypes are defined based on the base scalar types and are formed by placing additional constraints upon values that can be assigned.

Numeric Data Types and Subtypes in PL/SQL

Numeric data types store numbers, both integers and real numbers, and allow developers to perform arithmetic operations. The main numeric types include:

• NUMBER: A highly flexible type that can store fixed-point or floating-point numbers. It has precision and scale parameters. For example, NUMBER(5, 2) can store up to 5 digits, with 2 of them after the decimal point.
• BINARY_INTEGER/PLS_INTEGER: These types are used for signed integers and are often faster than the generic NUMBER type because they use machine-dependent formats.
• FLOAT: It is a subtype of NUMBER designed for storing floating-point numbers. You can specify an optional precision such as FLOAT(10) which allows for storing a number with up to 10 digits of precision

Subtypes of Numeric Data Types

• NATURAL: A subtype of BINARY_INTEGER representing non-negative integers (0 or greater).
• NATURALN: It is Similar to NATURAL but cannot be null.
• POSITIVE: A subtype of BINARY_INTEGER representing positive integers (greater than 0).
• POSITIVEN: It is Similar to POSITIVE but cannot be null.

Character Data Types and Subtypes in PL/SQL

Character data types are designed to store any text, numbers or symbols in the form of alphanumeric. They are used specifically for string manipulation and are a vital part of PL/SQL.

• CHAR: It Handles variable-length binary data and fixed-length character strings. If the string is less than the defined length, then the rest is filled up with spaces. For instance, CHAR(10) would store string as CHAR data type that has a length of 10 characters regardless of the actual string length.
• VARCHAR2: To store character strings of varying lengths. VARCHAR2 is slightly different because it only allocates the required amount of space required to store the string. For instance, VARCHAR2(10) data type can accommodate a string with as many as 10 characters.
• LONG: It Can store variable-length character strings of up to 2 gigabytes. However, it is deprecated and it should be replaced with CLOB to 2 GB. However, it is deprecated and should be avoided in favor of CLOB.

Subtypes of Character Data Types

• STRING: A subtype of VARCHAR2, used to represent variable-length strings.
• LONG VARCHAR: A deprecated subtype of VARCHAR2, previously used to store large strings.

PL/SQL Boolean Data Types

The Boolean data type is unique to PL/SQL, allowing you to store logical values and use them in conditional expressions.

BOOLEAN: This type can have three possible values: TRUE, FALSE, or NULL. It is used in conditional statements and logical comparisons. Notably, the BOOLEAN data type is unique to PL/SQL and cannot be used in SQL statements directly.

PL/SQL Datetime and Interval Types

Datetime data types contain date and time and interval types contain the difference between two datetime values. PL/SQL provides the following datetime and interval types:

• DATE: It stores date and time values. These are the year, month, day, hour, minute, and second as a part of the Date type.
• TIMESTAMP: It is an extension of the DATE data type with the added feature of fractional seconds.
• TIMESTAMP WITH TIME ZONE: Saves a TIMESTAMP, but with information about the time zone in which it has been set.
• TIMESTAMP WITH LOCAL TIME ZONE: Standalone function that converts the TIMESTAMP to the time zone of the current database session.
• INTERVAL YEAR TO MONTH: Saves the amount of time measured in years and months.
• INTERVAL DAY TO SECOND: Saves as the time duration in terms of days, hours, minutes, and seconds.

PL/SQL Large Object (LOB) Data Types

LOB data types store large amounts of unstructured data, such as text, images, videos, and audio. PL/SQL provides several LOB types:

• BLOB(Binary Large Object): Stores binary large objects, such as images or multimedia files.
• CLOB(Character Large Object): Stores large character data.
• NCLOB(National Character Large Object): Stores large character data using the national character set.
• BFILE(Binary File): Stores a reference to a binary file stored outside of the database.

LOB types can store up to 4 gigabytes of data, making them ideal for handling large and complex datasets.

Important Points for Using Data Types in PL/SQL

• Choosing the correct data type (e.g., CHAR for fixed text, VARCHAR2 for variable text) optimizes storage and improves readability.
• By using subtypes like NATURAL or POSITIVE, you can enforce constraints (such as no negative values) at the data type level, improving code reliability.
• Use LOB types (like CLOB and BLOB) for large datasets to efficiently handle multimedia, documents, or other large data objects without performance degradation.
• When working with timestamps, consider TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE to avoid time-related issues, especially if your application spans multiple time zones.