PL/SQL Variables — Hard-Coded VARCHAR2 Overflows Batch

PL/SQL Variables, Constants and Data Types is a fundamental concept in Database development. In PL/SQL, every piece of information must be held in a named storage location with a specific format. By mastering these building blocks, you can create programs that are not only functional but also highly maintainable and less prone to data-related errors.

In this guide we'll break down exactly what PL/SQL Variables, Constants and Data Types is, why it was designed this way to ensure strict data integrity within the Oracle engine, and how to use it correctly in real projects.

By the end you'll have both the conceptual understanding and practical code examples to use PL/SQL Variables, Constants and Data Types with confidence.

What Is PL/SQL Variables, Constants and Data Types and Why Does It Exist?

PL/SQL Variables, Constants and Data Types is a core feature of PL/SQL. It was designed to solve the problem of temporary data storage during procedural execution. Unlike standard SQL, which processes sets of data, PL/SQL needs a way to hold individual values, calculation results, and database records in memory. Variables allow for dynamic changes, while Constants protect values that must remain fixed (like a tax rate or API version). Data types (Scalar, Large Object, Composite, and Reference) exist to tell the compiler how much memory to allocate and what operations are valid on that data.

This may seem obvious, but the real power lies in how PL/SQL enforces data integrity. A variable's type determines what you can do with it – you cannot accidentally store a date where a number is expected. The compiler catches these mismatches at compile time, not at 3 AM in production.

-- io.thecodeforge: Standard Variable and Constant Declaration
DECLARE
    -- Scalar Variable
    v_employee_name VARCHAR2(100) := 'TheCodeForge Developer';
    
    -- Constant: Must be initialized and cannot be changed
    gc_tax_rate CONSTANT NUMBER(3,2) := 0.05;
    
    -- Anchored Type: Dynamically matches the column type in the DB
    v_project_id forge_projects.project_id%TYPE;
    
    -- Rowtype: Holds an entire row from a table
    v_project_rec forge_projects%ROWTYPE;
BEGIN
    v_project_id := 101;
    
    -- Selective Assignment
    SELECT * INTO v_project_rec 
    FROM forge_projects 
    WHERE project_id = v_project_id;

    DBMS_OUTPUT.PUT_LINE('Processing: ' || v_project_rec.project_name);
END;

Common Mistakes and How to Avoid Them

When learning PL/SQL Variables, Constants and Data Types, most developers hit the same set of gotchas. A frequent mistake is hard-coding variable lengths (e.g., VARCHAR2(20)) instead of using %TYPE. If the database column later expands to 50 characters, your code will throw a 'Value Error'. Another common error is failing to initialize variables, leading to unexpected NULL logic in calculations.

Knowing these in advance saves hours of debugging production 'Buffer Overflow' and 'Constraint Violation' errors.

But there's a deeper one: using package-level global variables when local variables would suffice. These globals persist across calls within the same session, and if your code relies on their previous values, you're in for a debugging nightmare. A change in one procedure can affect another unintentionally.

-- io.thecodeforge: Avoiding hard-coded data types
-- WRONG: Risk of ORA-06502 if the DB column expands
-- v_name VARCHAR2(10);

-- CORRECT: Using %TYPE to stay synchronized
DECLARE
    v_forge_user_name forge_users.username%TYPE;
    v_current_total   NUMBER(10,2) := 0.00; -- Initialize to avoid NULL arithmetic
BEGIN
    SELECT username INTO v_forge_user_name 
    FROM forge_users 
    WHERE user_id = 1;
    
    v_current_total := v_current_total + 150.50;
END;

Variables vs Constants – When to Use Each

Choosing between a variable and a constant is straightforward: use a constant when the value must never change. That sounds obvious, but developers often use variables for values that are logically fixed – like a maximum retry count, a cutoff date, or a conversion factor.

Constants improve readability and prevent accidental modification. They also help the compiler optimise memory usage. In PL/SQL, a constant must be declared with the CONSTANT keyword and initialized immediately. After that, any attempt to assign a new value raises a compilation error. That's a good thing – it's a contract your code makes with itself.

But there's a nuance: a constant can hold a value fetched from the database at declaration time, but that value becomes fixed for the duration of the block. If the underlying data changes, the constant still holds the old value. Use constants for truly static values, not for session-level configuration you might want to refresh.

-- io.thecodeforge: Proper use of constants vs variables
DECLARE
    -- Constant: tax rate never changes mid-calculation
    gc_tax_rate CONSTANT NUMBER(3,2) := 0.07;
    
    -- Variable: running total changes
    v_gross_amount NUMBER(10,2) := 0.00;
    v_net_amount   NUMBER(10,2) := 0.00;
BEGIN
    v_gross_amount := 1150.00;
    v_net_amount   := v_gross_amount * (1 - gc_tax_rate);
    
    DBMS_OUTPUT.PUT_LINE('Net: ' || v_net_amount);
    
    -- This would fail to compile: gc_tax_rate := 0.06;
END;

Anchored Types: %TYPE and %ROWTYPE for Schema Resilience

Anchored types are PL/SQL's solution to the schema coupling problem. When you declare a variable with %TYPE, you tell the compiler: 'Find the data type of this column and use that.' If the column later changes, your code automatically adapts – no manual edits needed.

%ROWTYPE goes further: it declares a variable that holds an entire row. You can SELECT INTO a %ROWTYPE variable and access each column by name. This eliminates the need to list all columns individually. When you add a column to the table, your %ROWTYPE variables include it automatically (though you may need to adjust SELECT * to include the new column).

The performance cost is negligible – the compiler resolves the type at compile time, not runtime. The real cost saved is maintenance hours. A 200-line procedure that uses %ROWTYPE instead of 20 individual variable declarations is vastly easier to maintain and less error-prone.

But there's a catch: %ROWTYPE cannot be used for columns from joins – it only maps to a single table or cursor. For joins, you need to define a custom record type or use individual variables.

-- io.thecodeforge: Using %TYPE and %ROWTYPE for resilience
DECLARE
    -- %TYPE: single column
    v_order_total forge_orders.total_amount%TYPE;
    
    -- %ROWTYPE: entire row
    v_customer_rec forge_customers%ROWTYPE;
BEGIN
    SELECT total_amount INTO v_order_total 
    FROM forge_orders 
    WHERE order_id = 1001;
    
    SELECT * INTO v_customer_rec
    FROM forge_customers
    WHERE customer_id = 202;
    
    DBMS_OUTPUT.PUT_LINE('Customer: ' || v_customer_rec.name || ' - Order Total: ' || v_order_total);
END;

Data Type Categories and Their Memory Implications

PL/SQL data types fall into four categories: Scalar, Composite, Reference, and LOB (Large Object). Each has distinct memory and performance characteristics.

Scalar types (NUMBER, VARCHAR2, DATE, BOOLEAN) are the simplest – they hold a single value. A VARCHAR2 variable can hold up to 32,767 bytes in PL/SQL, but only 4,000 bytes in SQL. That's a common tripwire: you can build a large string in PL/SQL, but passing it to a SQL context truncates it silently. Use CLOB for larger text.

Composite types (records, collections) consume memory proportional to their elements. A collection of 100,000 NUMBER items can use hundreds of kilobytes. If you declare such a collection at the package level, it persists for the session and can lead to PGA memory exhaustion under high concurrency.

LOB types (CLOB, BLOB) are stored outside the PGA, with a locator in the variable. They're efficient for large data but have slower access than scalars. Reference types (REF CURSOR, REF) hold pointers to data – useful for passing result sets between programs.

Knowing these memory implications helps you avoid performance surprises. A naive variable choice can turn a fast procedure into a memory hog.

-- io.thecodeforge: Understanding data type memory
DECLARE
    -- Scalar: small, fast
    v_small VARCHAR2(100) := 'Hi';
    
    -- Composite: collection can be large
    TYPE num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_big num_table;
    
    -- LOB: large data, uses locator
    v_clob CLOB := 'This is large text...';
    
    -- Reference: pointer to cursor
    v_cursor SYS_REFCURSOR;
BEGIN
    -- Fill collection with 10000 numbers
    FOR i IN 1..10000 LOOP
        v_big(i) := i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_big.COUNT);
    
    -- Open ref cursor
    OPEN v_cursor FOR 'SELECT * FROM dual';
END;

Scope, Lifetime, and Variable Visibility

A variable's scope determines where it can be accessed; its lifetime determines how long it exists. In PL/SQL, these depend on where you declare the variable.

Local variables inside a BEGIN-END block exist only during that block's execution. They are created when the block starts and destroyed when it ends. This is the safest scope – no unintended side effects.

Package-level variables declared in a package specification are visible to any caller. They live for the duration of the user session. If you modify a package variable in one procedure, any subsequent call to another procedure in the same session sees the modified value. This is both a feature (for session context like user ID) and a danger (for accidental state corruption).

Package body variables (declared in the body but not in the spec) are private – only procedures within the package can see them. That's a good middle ground: session-persistent but encapsulated.

The golden rule: declare variables at the smallest possible scope. Start with local. If you need to share state, use package body variables. Use package spec variables only when external callers need to read or set them.

-- io.thecodeforge: Scope and lifetime examples
CREATE OR REPLACE PACKAGE forge_session_ctx IS
    -- Package spec variable: visible to all callers, session lifetime
    g_user_id NUMBER;
    g_user_role VARCHAR2(30);
END forge_session_ctx;
/

CREATE OR REPLACE PACKAGE BODY forge_session_ctx IS
    -- Private package body variable: only visible inside this package
    g_last_access TIMESTAMP;
    
    PROCEDURE set_context(p_user_id NUMBER, p_role VARCHAR2) IS
        -- Local variable: only visible within this procedure
        v_local_counter NUMBER := 0;
    BEGIN
        g_user_id := p_user_id;
        g_user_role := p_role;
        g_last_access := SYSTIMESTAMP;
        v_local_counter := v_local_counter + 1;
    END;
END forge_session_ctx;

Variable Declaration: Memory Allocation You Control

Declaring a variable isn't just syntax—it's a contract with the database engine. Every variable you declare in the declaration section carves out memory for its lifetime. The format is simple: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]. By default, every uninitialized variable is NULL. That seems harmless until you try to concatenate NULLs and get unexpected truncation. Use NOT NULL to enforce a value at compile time—catching logic errors before they hit production. Constrained declarations (like NUMBER(10,2)) use less memory than unconstrained ones (NUMBER). That matters when you declare a hundred variables in a bulk processing block. A real-world trap: mixing VARCHAR2 without a size limit. Oracle defaults to 4000 bytes, but upgrade to 12c+ and it can be 32767. Your code works in dev, but blows in production under heavy string loads. Always specify size.

// io.thecodeforge
DECLARE
   -- constrained declaration, fixed memory
   v_order_total    NUMBER(10,2);
   -- NOT NULL forces initialization
   v_order_status   VARCHAR2(20) NOT NULL := 'PENDING';
   -- unconstrained—watch out
   v_raw_text       VARCHAR2;
BEGIN
   -- v_raw_text defaults to NULL, safe but ambiguous
   v_order_total := 1520.75;
   DBMS_OUTPUT.PUT_LINE('Total: ' || v_order_total);
END;
/

Assigning SQL Query Results to PL/SQL Variables: The Silent Performance Killer

You can pump SQL query results straight into PL/SQL variables using SELECT INTO. It's elegant—until it's not. The SELECT INTO syntax SELECT column INTO variable FROM table WHERE ... works only when the query returns exactly one row. Zero rows throws NO_DATA_FOUND. More than one row throws TOO_MANY_ROWS. Both are unhandled exceptions that abort your block. That is why junior devs' batch jobs crater at midnight. Always wrap SELECT INTO in a BEGIN-EXCEPTION block. Better yet, use BULK COLLECT for multiple rows—it reduces context switches between SQL and PL/SQL engines from N to 1. A common incident: a WHERE clause on an unindexed column returns two rows instead of one in production, but passed all unit tests on a static test set. Your code must handle that. The pattern: log the exception, set a default, or raise a business exception. Never let it hit the user as ORA-01422.

// io.thecodeforge
DECLARE
   v_customer_name   VARCHAR2(100);
   v_customer_id     NUMBER := 42;
BEGIN
   BEGIN
      SELECT customer_name
        INTO v_customer_name
        FROM customers
       WHERE customer_id = v_customer_id;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         v_customer_name := 'UNKNOWN';
         DBMS_OUTPUT.PUT_LINE('Customer ' || v_customer_id || ' not found');
      WHEN TOO_MANY_ROWS THEN
         -- Log the error, don't silently fail
         DBMS_OUTPUT.PUT_LINE('Duplicate customer IDs: ' || v_customer_id);
         RAISE_APPLICATION_ERROR(-20001, 'Data integrity failure');
   END;
   DBMS_OUTPUT.PUT_LINE('Name: ' || v_customer_name);
END;
/