Variables, Constants & Types

PL/SQL is a strongly typed language. Every variable has a data type known at compile time, which Oracle enforces to prevent runtime type surprises.

Declaring Variables

Variables are declared in the DECLARE section, one per line:

DECLARE
    v_employee_id   NUMBER(6);            -- no initial value (NULL)
    v_first_name    VARCHAR2(20);
    v_hire_date     DATE;
    v_salary        NUMBER(8,2) := 0;     -- initialized to 0
    v_is_active     BOOLEAN    := TRUE;
    v_department    VARCHAR2(30) DEFAULT 'Unknown';
BEGIN
    NULL;
END;
/

Naming Conventions

Oracle professionals prefix variable names to avoid confusion with column names:

Prefix	Meaning
`v_`	Local variable
`p_`	Parameter
`c_`	Constant
`g_`	Package-level global
`l_`	Loop/local

Scalar Data Types

Type	Description	Example
`NUMBER(p,s)`	Numeric, precision p, scale s	`NUMBER(8,2)` for salary
`PLS_INTEGER`	32-bit signed integer, fastest integer type	Loop counters
`BINARY_INTEGER`	Alias for PLS_INTEGER	Legacy code
`VARCHAR2(n)`	Variable-length string, max n bytes	`VARCHAR2(100)`
`CHAR(n)`	Fixed-length string, padded with spaces	`CHAR(1)` for flag
`DATE`	Date + time (to the second)	`SYSDATE`
`TIMESTAMP`	Date + time with fractional seconds	High-precision logging
`BOOLEAN`	`TRUE`, `FALSE`, or `NULL` — PL/SQL only, not a SQL type	Flags
`CLOB`	Character Large Object, up to 128 TB	Long text
`BLOB`	Binary Large Object	Files, images

Assignment Operator

PL/SQL uses := for assignment, not = (which is used only for comparison):

DECLARE
    v_count   PLS_INTEGER := 0;
    v_name    VARCHAR2(50);
    v_today   DATE;
BEGIN
    v_count := v_count + 1;
    v_name  := 'Steven King';
    v_today := SYSDATE;

    -- String concatenation uses ||
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Count: ' || v_count);
END;
/

%TYPE — Anchored Declarations

%TYPE anchors a variable's type to a table column or another variable. If the column type changes, the variable automatically adapts — no code change needed:

DECLARE
    -- Anchored to the column definition in the data dictionary
    v_emp_id      employees.employee_id%TYPE;
    v_salary      employees.salary%TYPE;
    v_first_name  employees.first_name%TYPE;
    v_last_name   employees.last_name%TYPE;
BEGIN
    SELECT employee_id, salary, first_name, last_name
    INTO   v_emp_id, v_salary, v_first_name, v_last_name
    FROM   employees
    WHERE  employee_id = 100;

    DBMS_OUTPUT.PUT_LINE(
        v_first_name || ' ' || v_last_name ||
        ' earns $' || TO_CHAR(v_salary, 'FM999,999.00')
    );
END;
/

Output:

Steven King earns $24,000.00

✓

Always use %TYPE for variables that hold column values. It prevents silent truncation bugs when a VARCHAR2(20) column is later changed to VARCHAR2(50).

%ROWTYPE — Row Anchoring

%ROWTYPE declares a record that matches an entire table or view row. Covered in depth in the Records topic, but worth introducing here:

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

    DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' — dept ' || v_emp.department_id);
END;
/

Constants

Constants are declared with the CONSTANT keyword and must be initialized. They cannot be modified after declaration:

DECLARE
    c_max_salary    CONSTANT NUMBER       := 250000;
    c_company_name  CONSTANT VARCHAR2(50) := 'Acme Corp';
    c_pi            CONSTANT NUMBER       := 3.14159265358979;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Max allowed: $' || c_max_salary);
    -- c_max_salary := 300000;  -- PLS-00363: cannot assign to a constant
END;
/

NOT NULL Constraint

Variables can be declared NOT NULL, forcing an initial value:

DECLARE
    v_status  VARCHAR2(10) NOT NULL := 'ACTIVE';
    -- v_code  NUMBER       NOT NULL;  -- PLS-00218: compile error, no default
BEGIN
    -- v_status := NULL;  -- PLS-00382: expression is of wrong type
    DBMS_OUTPUT.PUT_LINE('Status: ' || v_status);
END;
/

Subtypes — User-Defined Type Aliases

SUBTYPE creates an alias for an existing type, optionally with constraints:

DECLARE
    SUBTYPE t_name     IS VARCHAR2(100);
    SUBTYPE t_salary   IS NUMBER(10,2) NOT NULL;
    SUBTYPE t_percent  IS NUMBER(5,2) RANGE 0 .. 100;

    v_emp_name  t_name   := 'Steven King';
    v_pay       t_salary := 24000;
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_emp_name || ': $' || v_pay);
END;
/

Default Values

Both := and DEFAULT are valid for initial values:

DECLARE
    v_region   VARCHAR2(20) := 'EMEA';          -- := syntax
    v_limit    NUMBER        DEFAULT 100;        -- DEFAULT keyword
    v_active   BOOLEAN       DEFAULT TRUE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Region: ' || v_region || ', Limit: ' || v_limit);
END;
/

Multiple Assignments in One Statement

Oracle does not support a := b := 0; chaining. Assign variables individually:

DECLARE
    v_a NUMBER;
    v_b NUMBER;
    v_c NUMBER;
BEGIN
    v_a := 10;
    v_b := 20;
    v_c := v_a + v_b;
    DBMS_OUTPUT.PUT_LINE('Sum: ' || v_c);
END;
/

⚠

An uninitialized variable in PL/SQL is NULL, not zero or empty string. Any arithmetic with NULL propagates NULL: 5 + NULL = NULL. Always initialize numeric counters to 0.

Summary

Declare variables in the DECLARE section; use := or DEFAULT for initial values.
Use %TYPE to anchor a variable to a column — adapts automatically to schema changes.
Use %ROWTYPE to hold an entire table row.
CONSTANT variables cannot be reassigned after declaration.
NOT NULL forces a variable to always have a value.
BOOLEAN (TRUE/FALSE/NULL) exists in PL/SQL but not in Oracle SQL columns (pre-Oracle 23ai).
PLS_INTEGER is the fastest integer type — prefer it for loop counters and arithmetic.