An Introduction to Commonly Used PL/SQL Data Types

PL/SQL has two primary categories of data types: Scalar data types and Composite data types.

• Scalar data types store single values without internal components. They represent a single value like a number, string, or date.
• Composite data types store multiple values, such as records and collections.

Today, I will only discuss Scalar data types.

PL/SQL is an extension of SQL, and many SQL features, including data types, can be used in PL/SQL. However, there are PL/SQL-specific data types as well. It is important to be cautious when using these types, especially when working with database tables, as certain data types in PL/SQL execution blocks may not be directly interpretable in the context of a table (or may need to be handled differently).

Since there are so many data types in both SQL and PL/SQL, including subtypes of scalar data types, deprecated types, and those only maintained for backward compatibility, I will focus on the most commonly used data types as a starting point for beginners like myself.

Scalar data types can be divided into four families, which store numbers, characters, boolean values, and date/time values.

1. Number Types
   NUMBER
PLS_INTEGER

2. Character Types
   CHAR
VARCHAR2

3. Boolean Type
   BOOLEAN

4. Datetime Types
   DATE

Number Types
Number types allow us to store numeric data.

NUMBER
The NUMBER data type stores fixed-point or floating-point numbers. It has a range from 1E-130 to 10E125. You can specify two attributes for the NUMBER type:

• Precision: The total number of digits (both to the left and right of the decimal point).
• Scale: The number of digits to the right of the decimal point.

Syntax:

NUMBER[(precision, scale)]. *[ ] means optional

NUMBER Subtypes
The following are subtypes of NUMBER that can be used in PL/SQL:
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INTEGER
INT
NUMERIC
REAL
SMALLINT

These subtypes are useful because they provide names that are compatible with other programming languages. For example, you can use INT in your PL/SQL code, which is also recognized in languages like Java.

PLS_INTEGER
PLS_INTEGER is a PL/SQL-specific data type used to store signed integers. It has a range from -2^31 to 2^31 - 1, or -2,147,483,648 to 2,147,483,647. Since PLS_INTEGER uses machine arithmetic, it requires less storage and is faster than NUMBER for integer operations.

Character Types
Character types allow us to store alphanumeric data.

CHAR
The CHAR data type stores a fixed-length character string, with the maximum size being 32,767 bytes. If the stored string is shorter than the defined length, it will be padded with spaces to the right.

Syntax

CHAR[(maximum_size [CHAR | BYTE])]

If you don’t specify the maximum size, it defaults to 1.

VARCHAR2
The VARCHAR2 data type stores a varying-length character string, with the maximum size also being 32,767 bytes. Unlike CHAR, VARCHAR2 only uses the necessary amount of storage for the string and does not pad with spaces.

Syntax

VARCHAR2(maximum_size [CHAR | BYTE])

Difference Between CHAR and VARCHAR2
The key difference between CHAR and VARCHAR2 is:

• CHAR stores a fixed-length string and pads the string with spaces on the right if it is shorter than the defined size.
• VARCHAR2 stores a variable-length string and does not pad the string with spaces.

Here’s an example:

str1 CHAR(2) := ‘a’; — This will store ‘a ‘ (with a space)
str2 VARCHAR2(2) := ‘a’; — This will store ‘a’ (no space)

Boolean Type
The BOOLEAN data types store logical values: TRUE, FALSE, and NULL. PL/SQL supports the BOOLEAN type, but SQL does not. This means that BOOLEAN values cannot be used in SQL statements, built-in SQL functions, or PL/SQL functions invoked from SQL.

Datetime Types
DATE
The DATE data type stores date and time values. It includes both the date portion (year, month, day) and the time portion (hours, minutes, seconds).

• The time portion is measured in seconds since midnight.
• If only the date is specified, the time portion defaults to midnight.
• The date portion defaults to the current date when no date is provided.
• The SYSDATE function returns the current date and time based on the server’s system clock.

The default date format in Oracle is 'DD-MON-YY' (e.g., ‘01-OCT-25’).

Cutoff Rule
The ‘01-OCT-25’ example represents October 1, 2025. But how does Oracle decide whether it’s 2025 or 1925? Oracle uses a default windowing rule to determine the century. The default cutoff year is 50:

• If the year is between 00 and 49, Oracle interprets it as 2000 to 2049.
• If the year is between 50 and 99, Oracle interprets it as 1950 to 1999.

To avoid this ambiguity, it’s recommended to specify the year using four digits (e.g., ‘01-OCT-2025’) instead of two digits.

These are the commonly used data types in PL/SQL. As I mentioned earlier, there are other types, including subtypes. Initially, I felt overwhelmed by the vast variety of available types, but now it’s becoming clearer to focus on the fundamental ones to start with. Since PL/SQL is all about handling data, it’s essential to understand the specific data types involved. Expanding our knowledge of each data type will definitely help improve our coding skills.