Scalar Data Types in PL/SQL – Complete Guide for Beginners

In Oracle PL/SQL, scalar data types are the most basic types of variables that store a single value at a time. Unlike composite types (like records or collections), scalar types don’t contain internal components — they’re simple, fast, and widely used.

What Are Scalar Data Types?

A scalar data type stores a single unit of data, such as a number, a string, a date, or a boolean value. These are foundational in PL/SQL because they’re used in variable declarations, conditions, loops, expressions, and more.

Categories of Scalar Data Types in PL/SQL

Scalar types in PL/SQL can be grouped into four main families:

Family	Description
Character / String	Stores text data like names, addresses, or messages
Number	Stores numeric values (integer, float, etc.)
Boolean	Stores logical values (`TRUE`, `FALSE`, `NULL`)
Date / Time	Stores date and time values (e.g., timestamps)

Character and String Scalar Data Types in PL/SQL

These are used to store text or alphanumeric characters.

Examples:

CHAR(size) – Fixed-length string
VARCHAR2(size) – Variable-length string
LONG – Large text blocks (deprecated for most uses)

DECLARE
  v_name VARCHAR2(50) := 'AmantPoint';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_name);
END;

Number Data Types

Used for numeric operations like arithmetic, finance, and calculations.

Examples:

NUMBER(p, s) – Precision and scale
INTEGER, FLOAT, BINARY_FLOAT, BINARY_DOUBLE

DECLARE
  v_salary NUMBER := 45000.50;
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_salary);
END;

Boolean Data Type

Stores true/false logic. Booleans are unique to PL/SQL and are not valid as table column types.

Values:

TRUE
FALSE
NULL (unknown)

DECLARE
  is_active BOOLEAN := TRUE;
BEGIN
  IF is_active THEN
    DBMS_OUTPUT.PUT_LINE('Account is active');
  END IF;
END;

Date/Time Data Types

Used to store date and time values.

Examples:

DATE – Includes date and time
TIMESTAMP – More precise than DATE
INTERVAL – Represents time differences

DECLARE
  v_now DATE := SYSDATE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Current date is: ' || v_now);
END;

Summary

Scalar Type	Example Values	Used For
`VARCHAR2(30)`	`'John'`, `'Oracle'`	Names, descriptions
`NUMBER(8,2)`	`99.99`, `12000`	Salaries, amounts
`BOOLEAN`	`TRUE`, `FALSE`	Flags, conditions
`DATE`	`'25-JUN-25'`, `SYSDATE`	Timestamps, schedules

More PL/SQL Type Families

Aside from scalar types, PL/SQL supports:

Composite Types (records, collections)
Reference Types (pointers, REF CURSORs)
LOBs (CLOB, BLOB, BFILE)

➡️ Learn more about PL/SQL Data Types in Oracle Docs

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.