PL SQL Tutorial

• Pl Sql Introduction
• Pl Sql Blocks
• Pl Sql Operators
• Pl Sql Variables
• Pl Sql Conditional Statements
• Pl Sql Iterative Statements
• Pl Sql Implicit Cursors
• Pl Sql Explicit Cursors
• Pl Sql Data Types

Pl Sql Data Types

Understanding PL/SQL Data Types: A Practical Guide

PL/SQL is Oracle’s procedural extension of SQL, designed for building scalable, efficient database applications. One of the first and most important steps in writing PL/SQL code is choosing the right data types. Just like in any programming language, data types define the kind of data you can store and manipulate in your variables. Choose the wrong one, and you risk performance issues, data loss, or hard-to-debug errors.

Let’s break down the main categories of PL/SQL data types and how to use them effectively.

Scalar Data Types

Scalar types hold a single value—no collections, no composite structures. These are the most commonly used data types in everyday PL/SQL development.

Numeric Types

NUMBER: The workhorse of numeric types. You can define precision and scale (e.g., NUMBER(5,2) means five digits total, two after the decimal).

BINARY_INTEGER / PLS_INTEGER: For whole numbers. PLS_INTEGER is faster and uses machine arithmetic, making it the preferred choice in most cases.

Use PLS_INTEGER for counters, loop indexes, and other whole-number operations where performance matters.

Character Types

CHAR(n): Fixed-length string. Always uses up n bytes, even if the string is shorter.

VARCHAR2(n): Variable-length string up to n bytes. This is the most common choice for strings in PL/SQL.

Tip: Use VARCHAR2 unless you have a specific reason to need a fixed-length string.

Date and Time Types

DATE: Stores date and time down to the second.

TIMESTAMP: More precise than DATE, supports fractional seconds.

INTERVAL: For storing time spans (e.g., number of days, hours, minutes).

Stick to DATE for most cases unless you need sub-second precision or want to calculate time intervals precisely.

Boolean

BOOLEAN: Stores TRUE, FALSE, or NULL.
Only usable in PL/SQL, not in SQL queries directly. Handy for control flow in stored procedures and functions.

Composite Data Types

These hold multiple values, similar to structs or objects in other programming languages.

Records

Records group related data. You define them using the %ROWTYPE attribute (based on a table row) or custom TYPE.

DECLARE
emp_record employees%ROWTYPE;
BEGIN
SELECT * INTO emp_record FROM employees WHERE employee_id = 101;
END;

This is great for fetching an entire row with one query, keeping your code clean and in sync with the database structure.

Collections

PL/SQL supports three types of collections:

Associative Arrays (Index-by tables): Key-value pairs, indexed by numbers or strings.
Nested Tables: Can be stored in the database as a column.
VARRAYs: Bounded arrays with a fixed maximum size.

Use associative arrays for lookups and temporary storage. Use nested tables and VARRAYs when working with collections at the SQL level.

LOB Data Types

LOBs (Large Objects) are for storing large chunks of unstructured data.
CLOB: Character large object (for large text).
BLOB: Binary large object (for images, videos, etc.).
NCLOB: National character large object.
BFILE: Points to a binary file stored outside the database.

Use LOBs when your data can’t fit in regular VARCHAR2 or RAW types (which have size limits). You’ll need to use special functions to read and write them.

User-Defined Types

You can define your own data types using the CREATE TYPE statement.
This lets you build object-oriented structures and work with them in SQL and PL/SQL alike.

CREATE TYPE address_type AS OBJECT (
street VARCHAR2(50),
city VARCHAR2(30),
zip VARCHAR2(10)
);

These are useful in complex applications, especially when dealing with collections of structured data or using Oracle features like object-relational tables.

%TYPE and %ROWTYPE Attributes

To make your code easier to maintain and more robust, PL/SQL lets you inherit data types directly from the database.
%TYPE: Declares a variable with the same type as a column.
%ROWTYPE: Declares a record with the same structure as a table row.

DECLARE
v_salary employees.salary%TYPE;

This ensures your variable matches the column type exactly—even if the schema changes later.

Choosing the Right Data Type

Use PLS_INTEGER for counters or calculations where performance is critical.
Use VARCHAR2 for strings unless you have a fixed-width reason to use CHAR.
Use %TYPE and %ROWTYPE whenever possible to avoid hardcoding.
Avoid over-allocating sizes like VARCHAR2(4000) unless you really need it—it wastes memory.
Use LOBs sparingly and only when dealing with large content.

Final Thoughts

PL/SQL data types may seem straightforward, but choosing the right one makes your code faster, safer, and easier to maintain. Think about size, performance, and how the data will be used—especially when dealing with database-to-application interactions. By leveraging PL/SQL’s strong typing features, you can write code that’s both efficient and future-proof.