PL/SQL Data Types Explained: Scalar, Composite, Reference, and LOB with Examples
Introduction to PL/SQL Data Types
In Oracle PL/SQL, data types define the kind of data a variable or constant can store. While many PL/SQL types mirror database column types, PL/SQL has its own classification tailored for procedural operations. Choosing the right data type is critical for performance, data integrity, and functionality.
PL/SQL data types are not exactly the same as Oracle database column data types. While there’s overlap, PL/SQL also includes additional features specific to its block-based programming structure.
Categories of PL/SQL Data Types
PL/SQL data types fall into four major categories:
| Category | Description |
|---|---|
| Scalar | Hold single values like numbers, characters, or Booleans |
| Composite | Group multiple values into records or collections |
| Reference | Store references (pointers) to other program items |
| LOB | Used for large objects like text, images, and videos |
1. Scalar Data Types
Scalar types store a single value. They are simple, atomic data types with no internal components. Scalar types are further broken into four subcategories:
| Subcategory | Examples |
|---|---|
| Numeric | NUMBER, BINARY_INTEGER, PLS_INTEGER |
| Character | CHAR, VARCHAR2, NCHAR, NVARCHAR2 |
| Boolean | BOOLEAN |
| Date/Time | DATE, TIMESTAMP, INTERVAL |
Example: Declaring Scalar Variables
DECLARE
v_id NUMBER(5);
v_name VARCHAR2(50);
v_is_active BOOLEAN := TRUE;
v_hire_date DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Scalar variables declared.');
END;
2. Composite Data Types
Composite types allow grouping multiple values into a single unit. There are two primary types in PL/SQL:
| Composite Type | Description | Example |
|---|---|---|
| Records | A group of related fields | Employee record with ID, name, salary |
| Collections | Lists or sets of elements | Arrays or tables |
Record Example:
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
emp_name VARCHAR2(100),
salary NUMBER
);
v_employee emp_rec;
BEGIN
v_employee.emp_id := 101;
v_employee.emp_name := 'Shubham';
v_employee.salary := 60000;
END;
Collection Example (Nested Table):
DECLARE
TYPE number_list IS TABLE OF NUMBER;
v_numbers number_list := number_list(10, 20, 30);
BEGIN
FOR i IN 1..v_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || v_numbers(i));
END LOOP;
END;
3. Reference Data Types
Reference data types store memory addresses or references to other items.
| Type | Description |
|---|---|
REF CURSOR | Points to a query result set |
%TYPE | Takes data type of a column or variable |
%ROWTYPE | Takes row structure of a table or cursor |
Example: %TYPE and %ROWTYPE
DECLARE
v_emp_name employees.last_name%TYPE;
v_emp_row employees%ROWTYPE;
BEGIN
SELECT last_name INTO v_emp_name FROM employees WHERE employee_id = 100;
SELECT * INTO v_emp_row FROM employees WHERE employee_id = 100;
END;
4. LOB (Large Object) Data Types
LOBs are used to store large data objects like files, long text, or binary content.
| Type | Description |
|---|---|
BLOB | Binary large object |
CLOB | Character large object |
NCLOB | National character LOB |
BFILE | Pointer to external file stored outside the DB |
Example: Declaring LOB Variables
DECLARE
v_text CLOB;
v_image BLOB;
BEGIN
DBMS_OUTPUT.PUT_LINE('LOB variables declared.');
END;
Why Use Proper PL/SQL Data Types?
| Benefit | Impact |
|---|---|
| Performance | Reduces memory and CPU usage |
| Data Integrity | Prevents wrong data assignments |
| Code Readability | Improves maintainability |
| Schema Consistency | %TYPE and %ROWTYPE keep code aligned with schema |
AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.
