PL/SQL — Avoiding TOO_MANY_ROWS Errors in Batch Jobs

SQL is declarative — you describe what data you want and the database figures out how to get it. But real-world database logic often requires conditional branching, loops, error handling, and reusable procedures. That's what PL/SQL adds.

PL/SQL (Procedural Language extension to SQL) is Oracle's procedural extension to SQL. It runs inside the Oracle database engine itself, which means it avoids the round-trip overhead of sending individual SQL statements from an application. A loop that issues 1000 SQL statements from Java sends 1000 network requests. The same loop in PL/SQL sends one.

By the end of this article you'll understand the PL/SQL block structure, how to use variables, write conditionals and loops, and handle exceptions — the four pillars of every PL/SQL program.

What PL/SQL's TOO_MANY_ROWS Error Actually Means

PL/SQL is Oracle's procedural extension to SQL, embedding imperative logic directly in the database. The TOO_MANY_ROWS exception fires when a SELECT INTO statement returns more than one row — a hard runtime error, not a warning. This is a fundamental contract: SELECT INTO expects exactly one row; anything else is an exception.

In batch jobs, this bites teams because a single unexpected duplicate or data drift can kill an entire multi-hour run. The error surfaces at the exact row where the violation occurs, leaving no partial results and no easy rollback. Unlike a bulk COLLECT which gracefully handles zero or many rows, SELECT INTO is brittle by design — it enforces a one-row guarantee at the cost of zero tolerance for variance.

Use SELECT INTO only when the query's cardinality is guaranteed by a unique constraint or primary key. For batch processing where data quality may degrade over time, prefer explicit cursor loops with a counter check or BULK COLLECT with LIMIT. The real cost isn't the error itself — it's the lost batch window and the manual recovery effort.

The PL/SQL Block Structure

Every PL/SQL program is a block. Blocks have four sections: DECLARE (optional, for variables), BEGIN (required, the logic), EXCEPTION (optional, error handling), and END. Blocks can be anonymous (run once, not stored) or named (procedures and functions stored in the database). Understanding this structure is the foundation of everything else in PL/SQL.

You'll see the slash (/) at the end — that's what tells SQL*Plus, SQL Developer, and most tools to actually execute the block. Without it, nothing happens.

-- Anonymous PL/SQL block — runs immediately, not stored
DECLARE
    -- Variable declarations
    v_employee_name  VARCHAR2(50);   -- String up to 50 chars
    v_salary         NUMBER(10, 2);  -- Number with 2 decimal places
    v_department_id  NUMBER := 10;   -- Initialised to 10

BEGIN
    -- Query into variables using SELECT INTO
    SELECT first_name || ' ' || last_name, salary
    INTO   v_employee_name, v_salary
    FROM   employees
    WHERE  department_id = v_department_id
    AND    ROWNUM = 1;

    -- Output using DBMS_OUTPUT (enable with SET SERVEROUTPUT ON)
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || TO_CHAR(v_salary, '999,999.99'));

EXCEPTION
    -- Handle the case where no rows are found
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found in department ' || v_department_id);
END;
/  -- The slash executes the block in SQL*Plus and SQLDeveloper

Variables, Conditions, and Loops

PL/SQL variables are strongly typed. The %TYPE attribute lets you declare a variable that automatically matches a table column's data type — if the column type changes, your variable adapts automatically. %ROWTYPE does the same for an entire row structure. Conditionals use IF/ELSIF/ELSE. Loops come in three flavours: basic LOOP, WHILE, and the most common FOR loop. The FOR loop is preferred for simple iterations because it manages the loop counter implicitly and you don't need a separate variable declaration.

DECLARE
    -- %TYPE anchors variable type to the actual column definition
    v_salary    employees.salary%TYPE;
    v_grade     VARCHAR2(10);
    v_counter   NUMBER;

BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE employee_id = 100;

    -- IF / ELSIF / ELSE
    IF v_salary > 20000 THEN
        v_grade := 'Executive';
    ELSIF v_salary > 10000 THEN
        v_grade := 'Senior';
    ELSIF v_salary > 5000 THEN
        v_grade := 'Mid-level';
    ELSE
        v_grade := 'Junior';
    END IF;

    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);

    -- FOR loop (most common) — implicit counter, no DECLARE needed
    FOR v_counter IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_counter);
    END LOOP;

END;
/

Exception Handling in PL/SQL

PL/SQL provides an EXCEPTION block where you can catch and handle errors gracefully. Without it, any runtime error terminates the block and rolls back uncommitted changes. Common built-in exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, and ZERO_DIVIDE. You can also define your own exceptions using RAISE_APPLICATION_ERROR.

The WHEN OTHERS clause catches every unhandled exception. In production code, every block should include at least WHEN OTHERS to log the error and decide whether to re-raise or continue.

DECLARE
    v_employee_id employees.employee_id%TYPE := 9999;
    v_name        employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_name
    FROM employees WHERE employee_id = v_employee_id;

    DBMS_OUTPUT.PUT_LINE('Found: ' || v_name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || v_employee_id || ' not found.');
        -- Optionally log or re-raise
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees with same ID — data error.');
        -- Log for investigation
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;  -- Re-raise after logging; don't hide it
END;
/

Working with Cursors

When you need to process multiple rows from a query, use a cursor. An explicit cursor is declared, opened, fetched, and closed manually. PL/SQL also offers implicit cursors via the FOR loop — the simplest and most common approach for single-table queries. For large row sets, BULK COLLECT speeds processing by fetching many rows at once into a collection, drastically reducing context switches between SQL and PL/SQL.

DECLARE
    -- Explicit cursor declaration
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_emp_rec emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_rec;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_rec.employee_id || ': ' || v_emp_rec.first_name);
    END LOOP;
    CLOSE emp_cursor;
END;
/

-- Simpler: Implicit cursor FOR loop (recommended)
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name FROM employees WHERE department_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || ': ' || emp_rec.first_name);
    END LOOP;
END;
/

Stored Procedures and Functions

While anonymous blocks run once, stored procedures and functions live in the database and can be called repeatedly — by other PL/SQL, from application code, or even directly from SQL. Procedures perform actions (INSERT, UPDATE, etc.) and can have OUT parameters to return values. Functions return a single value and can be used in SQL statements if they are free of side effects (i.e., they don't modify database state). Packages group related procedures, functions, types, and variables together, providing encapsulation and namespace management.

-- Stored procedure: updates salary and returns the new value via OUT parameter
CREATE OR REPLACE PROCEDURE raise_salary(
    p_employee_id IN employees.employee_id%TYPE,
    p_percent     IN NUMBER,
    p_new_salary  OUT employees.salary%TYPE
) IS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE employee_id = p_employee_id
    RETURNING salary INTO p_new_salary;

    COMMIT;
END raise_salary;
/

-- Stored function: returns a single value, callable from SQL
CREATE OR REPLACE FUNCTION get_annual_salary(
    p_employee_id employees.employee_id%TYPE
) RETURN NUMBER
DETERMINISTIC   -- Indicates same input always returns same output
IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary
    FROM employees WHERE employee_id = p_employee_id;

    RETURN v_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END get_annual_salary;
/

-- Calling the function from SQL
SELECT employee_id, get_annual_salary(employee_id) AS annual_salary
FROM employees
WHERE department_id = 10;

Why PL/SQL Exists (And Why You Should Care)

SQL is declarative. You tell the database what you want, not how to get it. That's fine until you need to validate data across three tables, roll back a transaction when a constraint fails, or loop through result sets and fire off audit records. That's where SQL breaks.

PL/SQL is Oracle's procedural extension. It lets you wrap SQL in logic: if-then-else, loops, exception handling, and stateful variables. Same database, same data, but now you control the sequence of operations. It's not a separate language — it's SQL with a brain.

Every time you write a stored procedure, trigger, or anonymous block, you're telling the database engine: "Execute these steps in order, and if something breaks, do this instead." That's the entire point. No ORM, no middleware, no network round-trips for simple business rules. The database becomes the application server.

// io.thecodeforge — database tutorial

-- Without PL/SQL: three separate round-trips
SELECT status FROM orders WHERE order_id = 1001;
-- returns 'PENDING'
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
INSERT INTO audit_log (order_id, action) VALUES (1001, 'SHIPPED');

-- With PL/SQL: one block, all or nothing
DECLARE
   v_status orders.status%TYPE;
BEGIN
   SELECT status INTO v_status FROM orders WHERE order_id = 1001;
   IF v_status = 'PENDING' THEN
      UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
      INSERT INTO audit_log (order_id, action) VALUES (1001, 'SHIPPED');
      UPDATE orders SET status = 'SHIPPED' WHERE order_id = 1001;
      COMMIT;
   ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Order not in PENDING status');
   END IF;
END;
/

What PL/SQL Won't Tell You (The Hidden Features)

Every tutorial lists the same features: tight SQL integration, error checking, loops, conditionals. Fine. Here's what they bury: PL/SQL gives you bulk operations that make row-by-row processing look like a relic. The FORALL statement and BULK COLLECT are not optional — they're the difference between a query that finishes in three seconds and one that still runs during lunch.

Another hidden gem: associative arrays (index-by tables). They work like hash maps in the database. Need to cache a lookup table for a single session? That's a PL/SQL associative array, not a temp table. No I/O, no parsing, instant access.

And the autoreconnect trick: PL/SQL won't save you from a network blip. But if your transaction is wrapped in a loop with SAVEPOINT and EXCEPTION handling, you can retry the failed step without blowing away the entire transaction. That's not in the brochure.

Finally: autonomous transactions. PRAGMA AUTONOMOUS_TRANSACTION lets you commit logging or auditing independently of your main transaction. Audit trail survives even if the main rollback happens. Use it sparingly — it breaks atomicity — but when you need it, nothing else works.

// io.thecodeforge — database tutorial

-- Slow: row-by-row (avoid this in production)
DECLARE
   CURSOR c_orders IS SELECT order_id FROM orders WHERE status = 'PENDING';
BEGIN
   FOR r IN c_orders LOOP
      UPDATE orders SET processed_date = SYSDATE WHERE order_id = r.order_id;
   END LOOP;
END;
/

-- Fast: bulk collect + forall
DECLARE
   TYPE order_id_t IS TABLE OF orders.order_id%TYPE;
   v_ids order_id_t;
   CURSOR c_orders IS SELECT order_id FROM orders WHERE status = 'PENDING';
BEGIN
   OPEN c_orders;
   FETCH c_orders BULK COLLECT INTO v_ids;
   CLOSE c_orders;

   FORALL i IN 1..v_ids.COUNT
      UPDATE orders SET processed_date = SYSDATE WHERE order_id = v_ids(i);
   
   COMMIT;
END;
/

Who Actually Needs PL/SQL in 2024?

If you're building a CRUD app with Rails, Django, or Node and your database is just a dumb store, you probably don't need PL/SQL. But the moment you need data integrity that survives application bugs, or you're processing millions of rows nightly, or your compliance team demands auditable transaction logs, you do.

PL/SQL is for: backend engineers who maintain legacy Oracle systems (they're still everywhere in finance, healthcare, and logistics). Data engineers running ETL pipelines that transform data inside the database. DevOps folks who need to write database migrations that actually roll back cleanly. And architects who understand that pushing business logic into the database isn't 'old school' — it's the difference between eventual consistency and actual consistency.

The job market backs this up. Oracle PL/SQL developers consistently rank in the top 10 highest-paid database roles. Why? Because companies running Oracle at scale can't find people who understand both SQL and procedural logic. They're desperate for engineers who can open a procedure and debug it instead of rewriting everything in Java.

If you're a junior who thinks PL/SQL is dead, you're wrong. It's just not trendy. But trendiness doesn't keep bank transactions atomic or hospital records accurate. PL/SQL does.

// io.thecodeforge — database tutorial

-- Real scenario: nightly batch processing for a payroll system
DECLARE
   v_total_salary NUMBER := 0;
   CURSOR c_active IS 
      SELECT employee_id, salary FROM employees WHERE status = 'ACTIVE';
BEGIN
   FOR rec IN c_active LOOP
      v_total_salary := v_total_salary + rec.salary;
      
      -- Insert payroll record with autonomous audit
      INSERT INTO payroll_ledger (employee_id, amount, processed_date)
      VALUES (rec.employee_id, rec.salary, SYSDATE);
   END LOOP;
   
   DBMS_OUTPUT.PUT_LINE('Total payroll: ' || v_total_salary);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      -- Log failure to separate table (survives rollback)
      INSERT INTO payroll_errors (error_msg, error_date)
      VALUES (SQLERRM, SYSDATE);
      COMMIT;
      RAISE;
END;
/