Complete Guide · PL/SQL Mastery Series

PL/SQL Complete Guide: Oracle Procedural Programming

SQL answers what data you want. PL/SQL answers how to process it. This complete guide takes you from your first PL/SQL block to production-grade packages, triggers, bulk processing, and dynamic SQL — with Oracle Retail context throughout.

⏱38 min read📅December 1, 2025✍️Priyanshu Pandey📚PL/SQL Mastery Series

What is PL/SQL & How It Differs from SQL

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension of SQL. While SQL is a declarative language (you describe what you want), PL/SQL is procedural (you describe how to process data step by step).

PL/SQL runs directly inside the Oracle database engine, giving it three major advantages over application-side processing:

Security — business logic lives in the database, not in application code that can be bypassed or reverse-engineered.

Performance — SQL statements inside a PL/SQL block are executed in the same engine, eliminating the network round-trip overhead of sending each SQL statement from an application server.

Context — PL/SQL code has direct access to every Oracle feature: transactions, sequences, LOBs, UTL packages, Oracle Advanced Queuing, and more.

Column	Type	Description
Declarative	Style	You describe the result. The engine decides how to get it.
Single statement	Unit	One complete query at a time.
No variables	State	No way to store intermediate values.
No conditional logic	Flow	Can't branch based on data values within the statement.
No error handling	Errors	If a statement fails, it fails.

PL/SQL adds all of this: variables, conditional logic (IF/CASE), loops, exception handling, cursors, and the ability to group reusable logic into stored procedures, functions, packages, and triggers.

💡

SQL inside PL/SQL still uses the SQL engine

When you write a SELECT or INSERT inside a PL/SQL block, it is still executed by Oracle's SQL engine. PL/SQL orchestrates the flow; the SQL engine does the data work. This means SQL performance tuning (indexes, execution plans, bind variables) still applies inside PL/SQL.

Block Structure

PL/SQL Block Structure

Every PL/SQL program — from a two-line anonymous block to a 1000-line package body — follows the same four-section structure.

PL/SQL

DECLARE
   -- DECLARE section (optional)
   -- Define variables, constants, cursors, exceptions, types
   v_employee_name   VARCHAR2(100);
   v_salary          NUMBER(10,2)  := 0;
   c_tax_rate        CONSTANT NUMBER := 0.18;

BEGIN
   -- BEGIN section (mandatory)
   -- Contains executable SQL and PL/SQL statements
   SELECT first_name || ' ' || last_name, salary
   INTO   v_employee_name, v_salary
   FROM   employees
   WHERE  emp_id = 101;

   DBMS_OUTPUT.PUT_LINE(
       'Employee: ' || v_employee_name ||
       ' | Net Salary: ' || ROUND(v_salary * (1 - c_tax_rate), 2)
   );

EXCEPTION
   -- EXCEPTION section (optional)
   -- Catches and handles runtime errors
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee 101 not found.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);

END;
/  -- Terminates anonymous block in SQL*Plus / SQLcl

Column	Type	Description
DECLARE	Optional	Define all variables, cursors, constants, types, and exceptions used in the block.
BEGIN	Mandatory	The executable section. Contains SQL statements and PL/SQL logic.
EXCEPTION	Optional	Error handlers. Control returns here when a runtime error occurs.
END;	Mandatory	Terminates the block. The trailing / executes the block in interactive tools.

💡

Anonymous blocks vs Named blocks

An anonymous block (no name, shown above) runs once and is not stored in the database. A named block — a procedure, function, package, or trigger — is compiled and stored in the data dictionary. Use anonymous blocks for one-off scripts; use named blocks for reusable logic.

Variables & Data Types

Variables store data temporarily during a PL/SQL block's execution. Every variable must have a data type.

Declaring Variables

PL/SQL

DECLARE
   -- Basic scalar variables
   v_emp_id      NUMBER(10);
   v_name        VARCHAR2(100);
   v_hire_date   DATE;
   v_is_active   BOOLEAN;       -- PL/SQL-only; no equivalent Oracle column type

   -- Variable with a default value
   v_status      VARCHAR2(1) := 'A';

   -- Constant (value cannot change after declaration)
   c_tax_rate    CONSTANT NUMBER(5,4) := 0.18;
   c_max_retries CONSTANT INTEGER     := 3;

   -- %TYPE — adopts the data type of a column or another variable
   -- Best practice: guarantees your variable always matches the column type
   v_salary      employees.salary%TYPE;
   v_dept_id     employees.department_id%TYPE;

   -- %ROWTYPE — adopts the entire row structure of a table or cursor
   v_emp_row     employees%ROWTYPE;
BEGIN
   -- Access columns using dot notation on a %ROWTYPE variable
   v_emp_row.emp_id    := 101;
   v_emp_row.salary    := 75000;
   DBMS_OUTPUT.PUT_LINE(v_emp_row.salary);
END;

Collections — PL/SQL-Specific Types

PL/SQL

DECLARE
   -- Associative Array (INDEX BY) — like a hash map, no sequential constraint
   TYPE salary_map_t IS TABLE OF NUMBER INDEX BY VARCHAR2(50);
   v_salaries salary_map_t;

   -- Nested Table — ordered collection, can be stored in database columns
   TYPE name_list_t IS TABLE OF VARCHAR2(100);
   v_names name_list_t := name_list_t();

   -- VARRAY — fixed-size array
   TYPE dept_array_t IS VARRAY(10) OF NUMBER;
   v_depts dept_array_t := dept_array_t(10, 20, 30);
BEGIN
   v_salaries('Engineering') := 80000;
   v_salaries('Finance')     := 75000;
   DBMS_OUTPUT.PUT_LINE(v_salaries('Engineering'));

   v_names.EXTEND;
   v_names(1) := 'Priya Sharma';
END;

✨

Always use %TYPE and %ROWTYPE in production code

Hardcoding VARCHAR2(50) for a variable that mirrors a column creates a maintenance trap — when the column is resized, your code silently breaks with data-truncation errors. v_name employees.first_name%TYPE adapts automatically and communicates intent clearly.

Conditional Statements

Conditional Statements — IF, ELSIF, CASE

IF / ELSIF / ELSE

PL/SQL

DECLARE
   v_salary     NUMBER := 85000;
   v_grade      VARCHAR2(20);
BEGIN
   -- Simple IF
   IF v_salary > 100000 THEN
      v_grade := 'Executive';
   ELSIF v_salary BETWEEN 70000 AND 100000 THEN
      v_grade := 'Senior';
   ELSIF v_salary BETWEEN 40000 AND 69999 THEN
      v_grade := 'Mid Level';
   ELSE
      v_grade := 'Entry Level';
   END IF;

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

⚠️

Use ELSIF — not ELSEIF

Oracle PL/SQL uses ELSIF (no second E). Writing ELSEIF causes a compilation error. This trips up developers coming from MySQL (which uses ELSEIF) and most other languages (which use else if or elif).

CASE Statement in PL/SQL

PL/SQL

DECLARE
   v_status   VARCHAR2(1) := 'A';
   v_label    VARCHAR2(20);
BEGIN
   -- Simple CASE
   CASE v_status
      WHEN 'A' THEN v_label := 'Active';
      WHEN 'I' THEN v_label := 'Inactive';
      WHEN 'T' THEN v_label := 'Terminated';
      ELSE           v_label := 'Unknown';
   END CASE;
   DBMS_OUTPUT.PUT_LINE(v_label);

   -- Searched CASE (more flexible — evaluates boolean conditions)
   DECLARE
      v_commission NUMBER;
      v_dept_id    NUMBER := 10;
   BEGIN
      CASE
         WHEN v_dept_id IN (10, 20) THEN v_commission := 500;
         WHEN v_dept_id = 30        THEN v_commission := 750;
         ELSE                            v_commission := 250;
      END CASE;
      DBMS_OUTPUT.PUT_LINE('Commission: ' || v_commission);
   END;
END;

Loops

Loops — LOOP, WHILE LOOP, FOR LOOP

PL/SQL provides three loop constructs. Choose based on whether you know the count upfront.

Basic LOOP — Run Until EXIT WHEN

PL/SQL

DECLARE
   v_count NUMBER := 1;
BEGIN
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_count);
      v_count := v_count + 1;
      EXIT WHEN v_count > 5;  -- termination condition
   END LOOP;
END;

WHILE LOOP — Condition-First

PL/SQL

DECLARE
   v_total  NUMBER := 0;
   v_i      NUMBER := 1;
BEGIN
   WHILE 10 >= v_i LOOP
      v_total := v_total + v_i;
      v_i     := v_i + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Sum 1 to 10: ' || v_total);
END;

FOR LOOP — Known Range

PL/SQL

BEGIN
   -- Numeric FOR loop — loop variable is implicit (no DECLARE needed)
   FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Row: ' || i);
   END LOOP;

   -- Reverse iteration
   FOR i IN REVERSE 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
   END LOOP;
END;

Cursor FOR LOOP — Most Common in Production

PL/SQL

BEGIN
   -- Cursor FOR LOOP: Oracle automatically opens, fetches, and closes the cursor
   FOR rec IN (SELECT emp_id, first_name, salary FROM employees WHERE status = 'A') LOOP
      DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
   END LOOP;
END;
-- This pattern is concise and Oracle optimizes it well. Use it for most row-by-row processing.

Cursors

Cursors — Implicit & Explicit

A cursor is a pointer to the memory area (cursor cache) where Oracle holds the result set of a SQL statement.

Implicit Cursors

Oracle automatically creates an implicit cursor for every DML statement (INSERT, UPDATE, DELETE) and SELECT INTO. You access its state via cursor attributes.

PL/SQL

BEGIN
   UPDATE employees
   SET    salary = salary * 1.10
   WHERE  department_id = 10
     AND  status = 'A';

   -- Implicit cursor attributes
   IF SQL%FOUND THEN
      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' employees received a raise.');
   ELSE
      DBMS_OUTPUT.PUT_LINE('No matching employees found.');
   END IF;

   COMMIT;
END;

Column	Type	Description
SQL%FOUND	BOOLEAN	TRUE if the last DML/SELECT INTO affected/returned at least one row.
SQL%NOTFOUND	BOOLEAN	TRUE if the last DML/SELECT INTO found no rows.
SQL%ROWCOUNT	NUMBER	Number of rows affected by the last DML statement.
SQL%ISOPEN	BOOLEAN	Always FALSE for implicit cursors (Oracle manages open/close automatically).

Explicit Cursors

Defined by the developer when a SELECT can return multiple rows and you need row-by-row control.

PL/SQL

DECLARE
   -- 1. DECLARE the cursor
   CURSOR c_active_emp IS
      SELECT emp_id, first_name, salary
      FROM   employees
      WHERE  status = 'A'
      ORDER  BY salary DESC;

   -- Declare a %ROWTYPE variable to hold each fetched row
   v_emp c_active_emp%ROWTYPE;
BEGIN
   -- 2. OPEN the cursor (executes the query, positions pointer before first row)
   OPEN c_active_emp;

   LOOP
      -- 3. FETCH the next row
      FETCH c_active_emp INTO v_emp;

      -- 4. EXIT when no more rows
      EXIT WHEN c_active_emp%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ' earns ' || v_emp.salary);
   END LOOP;

   -- 5. CLOSE the cursor (releases resources)
   CLOSE c_active_emp;
END;

Parameterized Cursor

PL/SQL

DECLARE
   -- Cursor with a parameter — reusable for different departments
   CURSOR c_dept_emp (p_dept_id NUMBER) IS
      SELECT emp_id, first_name, salary
      FROM   employees
      WHERE  department_id = p_dept_id
        AND  status = 'A';
BEGIN
   -- Open the cursor for department 10
   FOR rec IN c_dept_emp(10) LOOP
      DBMS_OUTPUT.PUT_LINE(rec.first_name);
   END LOOP;

   -- Open again for department 20
   FOR rec IN c_dept_emp(20) LOOP
      DBMS_OUTPUT.PUT_LINE(rec.first_name);
   END LOOP;
END;

💡

Use Cursor FOR LOOP when you can

The Cursor FOR LOOP (FOR rec IN cursor_or_query LOOP) automatically handles OPEN, FETCH, %NOTFOUND check, and CLOSE. It is more concise and Oracle's optimizer can apply additional optimizations. Use explicit cursor control (OPEN/FETCH/CLOSE) only when you need to test %FOUND before the first fetch or share a cursor across multiple loops.

Exception Handling

Exception Handling — Predefined & User-Defined

When a runtime error occurs in PL/SQL, execution stops and control passes to the EXCEPTION block. If no handler matches, the exception propagates to the calling block.

Predefined Exceptions

Oracle automatically raises these when specific error conditions occur.

PL/SQL

DECLARE
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary
   FROM   employees
   WHERE  emp_id = 9999;  -- no such employee

   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No employee found with ID 9999.');

   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('Error: SELECT INTO returned more than one row.');

   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero.');

   WHEN OTHERS THEN
      -- Catch-all: always log SQLCODE and SQLERRM
      DBMS_OUTPUT.PUT_LINE('Unexpected error [' || SQLCODE || ']: ' || SQLERRM);
END;

Column	Type	Description
NO_DATA_FOUND	ORA-01403	SELECT INTO returned zero rows.
TOO_MANY_ROWS	ORA-01422	SELECT INTO returned more than one row.
ZERO_DIVIDE	ORA-01476	Division by zero.
DUP_VAL_ON_INDEX	ORA-00001	INSERT/UPDATE violated a unique constraint.
VALUE_ERROR	ORA-06502	Type conversion error or string too long for variable.
INVALID_CURSOR	ORA-01001	Cursor operation on a cursor that is not open.
OTHERS	Catch-all	Catches any exception not explicitly named above it.

User-Defined Exceptions

PL/SQL

DECLARE
   -- Declare a custom exception
   e_invalid_salary  EXCEPTION;
   v_new_salary      NUMBER := -5000;
BEGIN
   -- Validate business rule
   IF v_new_salary < 0 THEN
      RAISE e_invalid_salary;  -- manually raise the exception
   END IF;

   UPDATE employees SET salary = v_new_salary WHERE emp_id = 101;
   COMMIT;

EXCEPTION
   WHEN e_invalid_salary THEN
      DBMS_OUTPUT.PUT_LINE('Business rule violation: salary cannot be negative.');
      ROLLBACK;

   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
      ROLLBACK;
END;

RAISE_APPLICATION_ERROR — Returning Custom ORA Errors

Use RAISE_APPLICATION_ERROR to return a proper ORA- error to calling code

PL/SQL

-- Error numbers must be in range -20000 to -20999
PROCEDURE validate_employee (p_emp_id IN NUMBER) IS
   v_count NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_count FROM employees WHERE emp_id = p_emp_id;

   IF v_count = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Employee ID ' || p_emp_id || ' does not exist.');
   END IF;
END;

⚠️

WHEN OTHERS should always re-raise or log — never swallow silently

A bare WHEN OTHERS THEN NULL; swallows errors silently. This is one of the most dangerous patterns in Oracle development. Always at minimum log SQLCODE and SQLERRM. If you cannot handle the error, re-raise it: WHEN OTHERS THEN ROLLBACK; RAISE;

Procedures & Functions

Procedures vs Functions

Procedures and functions are named PL/SQL blocks stored in the database. The key difference: a function must return a value; a procedure may or may not (via OUT parameters).

Stored Procedure

Create a procedure to apply a salary raise

PL/SQL

CREATE OR REPLACE PROCEDURE apply_raise (
   p_emp_id   IN  NUMBER,        -- IN: input only, cannot be modified
   p_pct      IN  NUMBER,        -- percentage raise
   p_new_sal  OUT NUMBER         -- OUT: returns a value to the caller
)
IS
   v_current_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_current_salary
   FROM   employees
   WHERE  emp_id = p_emp_id;

   p_new_sal := ROUND(v_current_salary * (1 + p_pct / 100), 2);

   UPDATE employees SET salary = p_new_sal WHERE emp_id = p_emp_id;

   COMMIT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_emp_id);
END apply_raise;
/

-- Calling the procedure
DECLARE
   v_new_salary NUMBER;
BEGIN
   apply_raise(101, 10, v_new_salary);
   DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_salary);
END;

Stored Function

Create a function that returns a calculated value

PL/SQL

CREATE OR REPLACE FUNCTION get_annual_salary (
   p_emp_id IN NUMBER
) RETURN NUMBER
IS
   v_salary employees.salary%TYPE;
BEGIN
   SELECT salary INTO v_salary
   FROM   employees
   WHERE  emp_id = p_emp_id;

   RETURN v_salary * 12;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END get_annual_salary;
/

-- Functions can be called in SQL directly
SELECT emp_id, first_name, get_annual_salary(emp_id) AS annual_salary
FROM   employees
WHERE  status = 'A';

Column	Type	Description
Returns a value	Comparison	Optional (via OUT parameters). Mandatory via RETURN clause.
Callable in SQL	Comparison	No (only from PL/SQL). Yes — can appear in SELECT, WHERE, ORDER BY.
DML inside	Comparison	Yes. Only if function is not used in SQL DML context.
Use case	Comparison	Actions, batch processing, complex logic. Calculations, transformations, lookups.

Packages

Packages — Specification & Body

A package groups related procedures, functions, variables, and cursors into a single named unit. It is the primary way to organize PL/SQL code in enterprise Oracle systems.

Packages have two parts:

Specification — the public interface (what callers can see and use)
Body — the implementation (the actual code, invisible to callers)

Package Specification

Package specification = public interface

PL/SQL

CREATE OR REPLACE PACKAGE emp_pkg IS
   -- Public constant
   c_default_raise_pct  CONSTANT NUMBER := 5;

   -- Public procedure declarations
   PROCEDURE apply_raise    (p_emp_id IN NUMBER, p_pct IN NUMBER DEFAULT c_default_raise_pct);
   PROCEDURE transfer_dept  (p_emp_id IN NUMBER, p_new_dept IN NUMBER);

   -- Public function declarations
   FUNCTION get_annual_salary (p_emp_id IN NUMBER) RETURN NUMBER;
   FUNCTION is_eligible_for_raise (p_emp_id IN NUMBER) RETURN BOOLEAN;

END emp_pkg;
/

Package Body

Package body = implementation

PL/SQL

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

   -- Private variable (only accessible within this package body)
   g_last_updated DATE;

   -- Private helper function (not declared in spec = not accessible to callers)
   FUNCTION calculate_tax (p_salary NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN ROUND(p_salary * 0.18, 2);
   END calculate_tax;

   -- Public procedure implementation
   PROCEDURE apply_raise (p_emp_id IN NUMBER, p_pct IN NUMBER DEFAULT c_default_raise_pct) IS
   BEGIN
      UPDATE employees
      SET    salary = ROUND(salary * (1 + p_pct / 100), 2)
      WHERE  emp_id = p_emp_id;

      g_last_updated := SYSDATE;
      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_emp_id);
   END apply_raise;

   PROCEDURE transfer_dept (p_emp_id IN NUMBER, p_new_dept IN NUMBER) IS
   BEGIN
      UPDATE employees SET department_id = p_new_dept WHERE emp_id = p_emp_id;
      g_last_updated := SYSDATE;
      COMMIT;
   END transfer_dept;

   FUNCTION get_annual_salary (p_emp_id IN NUMBER) RETURN NUMBER IS
      v_salary employees.salary%TYPE;
   BEGIN
      SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;
      RETURN v_salary * 12;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
   END get_annual_salary;

   FUNCTION is_eligible_for_raise (p_emp_id IN NUMBER) RETURN BOOLEAN IS
      v_months NUMBER;
   BEGIN
      SELECT MONTHS_BETWEEN(SYSDATE, last_raise_date)
      INTO   v_months
      FROM   employees
      WHERE  emp_id = p_emp_id;
      RETURN v_months >= 12;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN FALSE;
   END is_eligible_for_raise;

END emp_pkg;
/

-- Calling package routines from PL/SQL
BEGIN
   emp_pkg.apply_raise(101, 8);
   DBMS_OUTPUT.PUT_LINE(emp_pkg.get_annual_salary(101));
END;

-- Calling package function from SQL
SELECT emp_id, emp_pkg.get_annual_salary(emp_id) AS annual_sal FROM employees;

✨

Package state is session-scoped

Package-level variables (like g_last_updated above) retain their values for the entire session. This is useful for caching, but can cause subtle bugs in connection-pooled environments where sessions are reused. Always initialize package state explicitly if relying on it.

Triggers

Triggers — BEFORE, AFTER, INSTEAD OF

A trigger is a named PL/SQL block that fires automatically in response to a DML event (INSERT, UPDATE, DELETE) on a table or view, or a DDL/database event.

BEFORE Trigger — Validate or Modify Before the Change

Automatically set audit fields on every INSERT

PL/SQL

CREATE OR REPLACE TRIGGER trg_employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   :NEW.created_date  := SYSDATE;
   :NEW.created_by    := SYS_CONTEXT('USERENV', 'SESSION_USER');
   :NEW.status        := NVL(:NEW.status, 'A');

   -- Business rule validation
   IF :NEW.salary < 0 THEN
      RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be negative.');
   END IF;
END;
/

AFTER Trigger — Audit Log After the Change

Write to audit log after every salary update

PL/SQL

CREATE OR REPLACE TRIGGER trg_employees_after_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary != NEW.salary)  -- only fire when salary actually changed
BEGIN
   INSERT INTO employee_audit_log (
       emp_id, change_type, old_value, new_value, changed_by, change_date
   ) VALUES (
       :OLD.emp_id, 'SALARY_CHANGE',
       :OLD.salary, :NEW.salary,
       SYS_CONTEXT('USERENV', 'SESSION_USER'),
       SYSDATE
   );
END;
/

INSTEAD OF Trigger — DML on Views

Allow INSERT into a view that joins two tables

PL/SQL

CREATE OR REPLACE TRIGGER trg_vw_emp_dept_instead_of_insert
INSTEAD OF INSERT ON vw_active_employees
FOR EACH ROW
BEGIN
   -- Route the INSERT to the correct underlying table
   INSERT INTO employees (emp_id, first_name, last_name, status)
   VALUES (:NEW.emp_id, :NEW.first_name, :NEW.last_name, 'A');
END;
/

Column	Type	Description
BEFORE	DML trigger	Before the DML executes. Both :OLD and :NEW available.
AFTER	DML trigger	After the DML executes. Both :OLD and :NEW available.
INSTEAD OF	View trigger	Replaces the DML entirely. Both :OLD and :NEW available.

⚠️

Avoid DML inside row-level triggers on the same table

A row-level trigger that does a SELECT or DML against its own triggering table will cause a mutating table error (ORA-04091). Use statement-level triggers, package-level collections, or compound triggers (Oracle 11g+) to work around this constraint.

BULK COLLECT & FORALL

BULK COLLECT & FORALL — Performance Processing

Every time PL/SQL communicates with the SQL engine it performs a context switch. When processing thousands of rows one at a time in a loop, thousands of context switches degrade performance dramatically. BULK COLLECT and FORALL solve this by processing data in sets.

BULK COLLECT — Fetch All Rows at Once

PL/SQL

DECLARE
   -- Declare a collection type to hold the bulk-fetched data
   TYPE emp_id_list_t  IS TABLE OF employees.emp_id%TYPE;
   TYPE salary_list_t  IS TABLE OF employees.salary%TYPE;

   v_emp_ids   emp_id_list_t;
   v_salaries  salary_list_t;
BEGIN
   -- Fetch ALL active employees in ONE context switch (vs N switches in a cursor loop)
   SELECT emp_id, salary
   BULK COLLECT INTO v_emp_ids, v_salaries
   FROM   employees
   WHERE  status = 'A';

   DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_emp_ids.COUNT || ' employees');

   -- Process in PL/SQL (no further SQL context switches)
   FOR i IN 1 .. v_emp_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('EMP: ' || v_emp_ids(i) || ' SAL: ' || v_salaries(i));
   END LOOP;
END;

BULK COLLECT with LIMIT — Batch Processing for Large Tables

PL/SQL

DECLARE
   CURSOR c_emp IS SELECT emp_id, salary FROM employees WHERE status = 'A';

   TYPE emp_rec_t IS TABLE OF c_emp%ROWTYPE;
   v_batch  emp_rec_t;
   v_limit  CONSTANT NUMBER := 1000;  -- process 1000 rows at a time
BEGIN
   OPEN c_emp;
   LOOP
      -- Fetch up to 1000 rows per iteration
      FETCH c_emp BULK COLLECT INTO v_batch LIMIT v_limit;

      EXIT WHEN v_batch.COUNT = 0;

      -- Process this batch
      FOR i IN 1 .. v_batch.COUNT LOOP
         -- ... business logic
         NULL;
      END LOOP;
   END LOOP;
   CLOSE c_emp;
END;

FORALL — Bulk DML

PL/SQL

DECLARE
   TYPE emp_id_list_t IS TABLE OF employees.emp_id%TYPE;
   v_emp_ids  emp_id_list_t;
BEGIN
   -- First: collect the emp_ids to update (BULK COLLECT)
   SELECT emp_id
   BULK COLLECT INTO v_emp_ids
   FROM   employees
   WHERE  department_id = 10
     AND  status = 'A';

   -- Then: apply DML to ALL of them in ONE SQL call (FORALL)
   FORALL i IN 1 .. v_emp_ids.COUNT
      UPDATE employees
      SET    salary = ROUND(salary * 1.10, 2)
      WHERE  emp_id = v_emp_ids(i);

   DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
   COMMIT;
END;

✨

BULK COLLECT + FORALL is the standard pattern for high-volume processing

In Oracle Retail batch programs (like STKLEDGR, SAEXPAND, POSUPC), the BULK COLLECT + FORALL pattern is used throughout for processing millions of stock ledger rows, order lines, and price changes. A loop that processed 500,000 rows in 10 minutes with row-by-row cursors may run in under 30 seconds with bulk processing.

Dynamic SQL

Dynamic SQL — EXECUTE IMMEDIATE

Dynamic SQL allows you to build and execute SQL or PL/SQL statements at runtime — when the full text of the statement is not known at compile time.

When you need Dynamic SQL:

The table name changes at runtime (multi-tenant architectures)
The WHERE clause structure varies based on user input
DDL statements (CREATE TABLE, DROP INDEX) inside PL/SQL
Running SQL across a list of table names from a metadata query

EXECUTE IMMEDIATE — DDL

PL/SQL

BEGIN
   -- DDL cannot run as static SQL inside PL/SQL — use EXECUTE IMMEDIATE
   EXECUTE IMMEDIATE 'CREATE TABLE temp_results (id NUMBER, result_val VARCHAR2(200))';

   EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_results';

   EXECUTE IMMEDIATE 'DROP TABLE temp_results PURGE';
END;

EXECUTE IMMEDIATE — DML with Bind Variables

PL/SQL

DECLARE
   v_dept_id  NUMBER := 10;
   v_pct      NUMBER := 8;
BEGIN
   -- Always use bind variables (:1, :v_dept) — prevents SQL injection
   -- and allows Oracle to reuse the cursor cache
   EXECUTE IMMEDIATE
      'UPDATE employees SET salary = ROUND(salary * (1 + :1/100), 2)
       WHERE department_id = :2 AND status = ''A'''
   USING v_pct, v_dept_id;

   DBMS_OUTPUT.PUT_LINE('Updated: ' || SQL%ROWCOUNT || ' rows');
   COMMIT;
END;

EXECUTE IMMEDIATE — Dynamic SELECT INTO

PL/SQL

DECLARE
   v_table_name  VARCHAR2(30) := 'EMPLOYEES';
   v_count       NUMBER;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT COUNT(*) FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(v_table_name)
   INTO v_count;

   DBMS_OUTPUT.PUT_LINE('Row count: ' || v_count);
END;

Building Dynamic WHERE Clauses

PL/SQL

CREATE OR REPLACE PROCEDURE search_employees (
   p_dept_id   IN NUMBER  DEFAULT NULL,
   p_status    IN VARCHAR2 DEFAULT NULL,
   p_min_sal   IN NUMBER  DEFAULT NULL
) IS
   v_sql   VARCHAR2(4000);
   TYPE emp_cur_t IS REF CURSOR;
   v_cur   emp_cur_t;
   v_name  employees.first_name%TYPE;
BEGIN
   v_sql := 'SELECT first_name FROM employees WHERE 1=1';

   IF p_dept_id IS NOT NULL THEN
      v_sql := v_sql || ' AND department_id = :dept_id';
   END IF;
   IF p_status IS NOT NULL THEN
      v_sql := v_sql || ' AND status = :status';
   END IF;

   -- Use a REF CURSOR to execute a dynamic SELECT returning multiple rows
   OPEN v_cur FOR v_sql USING p_dept_id, p_status;
   LOOP
      FETCH v_cur INTO v_name;
      EXIT WHEN v_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_name);
   END LOOP;
   CLOSE v_cur;
END;

⚠️

Always use bind variables — never concatenate user input into dynamic SQL

Concatenating user-provided values directly into SQL strings creates SQL injection vulnerabilities: 'SELECT ... WHERE name = ''' || p_user_input || ''''. Always use bind variables (USING p_value). For table and column names that must be dynamic, validate with DBMS_ASSERT.SQL_OBJECT_NAME() before concatenation.

Gotchas

Common Gotchas

Important Gotchas

!
SELECT INTO with no rows raises NO_DATA_FOUND, not a warning. Unlike SQL where a no-result query returns an empty result set, SELECT INTO in PL/SQL raises a hard exception when no row is found. Always handle NO_DATA_FOUND explicitly or use a cursor FOR loop for zero-or-many row scenarios.
!
SELECT INTO with multiple rows raises TOO_MANY_ROWS. SELECT ... INTO v_var FROM table WHERE ... must return exactly one row. If your WHERE clause can match more than one row, use an explicit cursor or aggregate the result first.
!
WHEN OTHERS masking real errors. A WHEN OTHERS handler that only does NULL swallows the error silently. The calling code has no idea something failed. Always log at minimum SQLCODE and SQLERRM, and usually ROLLBACK and re-raise.
!
Forgetting to CLOSE cursors. Unclosed cursors are not released until the session ends. In long-running batch jobs or connection-pooled environments, cursor leaks exhaust Oracle's OPEN_CURSORS limit, causing ORA-01000 errors. Cursor FOR loops close automatically — explicit OPEN/FETCH/CLOSE loops must call CLOSE.
!
Using ELSIF not ELSEIF. Oracle PL/SQL uses ELSIF (one word, no second E). Writing ELSEIF causes a compilation error. This catches developers coming from MySQL, Python, or most other languages.
!
Dynamic SQL without bind variables. Concatenating values into SQL strings in EXECUTE IMMEDIATE creates SQL injection risk and pollutes the shared SQL area with non-reusable cursors (hard parses). Always use USING clause bind variables for values; validate object names with DBMS_ASSERT.
!
Row-by-row cursor loops on large datasets. Fetching and processing 100,000 rows one at a time with 100,000 context switches is orders of magnitude slower than BULK COLLECT (one context switch) + FORALL. For any loop processing more than a few hundred rows, use bulk processing.

Key Takeaways

Every PL/SQL program uses the four-section block structure: DECLARE (optional) → BEGIN (mandatory) → EXCEPTION (optional) → END. Anonymous blocks run once; named blocks (procedures, functions, packages, triggers) are stored in the database.
Use %TYPE and %ROWTYPE to anchor variable declarations to column and table definitions. This prevents type mismatch bugs and communicates intent clearly.
SELECT INTO must return exactly one row. Handle NO_DATA_FOUND and TOO_MANY_ROWS explicitly. For zero-or-many rows, use explicit cursors or Cursor FOR loops.
Always CLOSE explicit cursors. Cursor FOR loops close automatically; OPEN/FETCH/CLOSE patterns require an explicit CLOSE call. Unclosed cursors cause ORA-01000 in production.
Exception handling must not swallow errors silently. Log SQLCODE and SQLERRM in WHEN OTHERS. Use RAISE_APPLICATION_ERROR(-20000 to -20999) for business rule violations.
Packages are the preferred way to organize production PL/SQL. The spec defines the public interface; the body contains the implementation. Package state is session-scoped.
BULK COLLECT fetches multiple rows in one context switch. FORALL executes DML against a collection in one context switch. Together they reduce processing time for large datasets by orders of magnitude.
EXECUTE IMMEDIATE allows runtime construction of SQL and DDL. Always use bind variables (USING clause) for values to prevent SQL injection and improve cursor reuse.

← Previous

SQL Advanced — Window Functions, CTEs & Analytics

Next in series →

The Organizational Hierarchy — Stores, Warehouses & Locations

PL/SQL Complete Guide: Oracle Procedural Programming

What is PL/SQL & How It Differs from SQL#

SQL inside PL/SQL still uses the SQL engine

PL/SQL Block Structure#

Anonymous blocks vs Named blocks

Variables & Data Types#

Declaring Variables

Collections — PL/SQL-Specific Types

Always use %TYPE and %ROWTYPE in production code

Conditional Statements — IF, ELSIF, CASE#

IF / ELSIF / ELSE

Use ELSIF — not ELSEIF

CASE Statement in PL/SQL

Loops — LOOP, WHILE LOOP, FOR LOOP#

Basic LOOP — Run Until EXIT WHEN

WHILE LOOP — Condition-First

FOR LOOP — Known Range

Cursor FOR LOOP — Most Common in Production

Cursors — Implicit & Explicit#

Implicit Cursors

Explicit Cursors

Parameterized Cursor

Use Cursor FOR LOOP when you can

Exception Handling — Predefined & User-Defined#

Predefined Exceptions

User-Defined Exceptions

RAISE_APPLICATION_ERROR — Returning Custom ORA Errors

WHEN OTHERS should always re-raise or log — never swallow silently

Procedures vs Functions#

Stored Procedure

Stored Function

Packages — Specification & Body#

Package Specification

Package Body

Package state is session-scoped

Triggers — BEFORE, AFTER, INSTEAD OF#

BEFORE Trigger — Validate or Modify Before the Change

AFTER Trigger — Audit Log After the Change

INSTEAD OF Trigger — DML on Views

Avoid DML inside row-level triggers on the same table

BULK COLLECT & FORALL — Performance Processing#

BULK COLLECT — Fetch All Rows at Once

BULK COLLECT with LIMIT — Batch Processing for Large Tables

FORALL — Bulk DML

BULK COLLECT + FORALL is the standard pattern for high-volume processing

Dynamic SQL — EXECUTE IMMEDIATE#

EXECUTE IMMEDIATE — DDL

EXECUTE IMMEDIATE — DML with Bind Variables

EXECUTE IMMEDIATE — Dynamic SELECT INTO

Building Dynamic WHERE Clauses

Always use bind variables — never concatenate user input into dynamic SQL

Common Gotchas#

Important Gotchas

Key Takeaways#

Key Takeaways

Related Articles

PL/SQL Masterclass: Collections & Bulk Processing

PL/SQL Dynamic SQL Mastery: EXECUTE IMMEDIATE vs DBMS_SQL

PL/SQL Performance Tuning: Beyond the Basics

RC:OMS

RC:Storefront

RC:Pulse

What is PL/SQL & How It Differs from SQL

PL/SQL Block Structure

Variables & Data Types

Conditional Statements — IF, ELSIF, CASE

Loops — LOOP, WHILE LOOP, FOR LOOP

Cursors — Implicit & Explicit

Exception Handling — Predefined & User-Defined

Procedures vs Functions

Packages — Specification & Body

Triggers — BEFORE, AFTER, INSTEAD OF

BULK COLLECT & FORALL — Performance Processing

Dynamic SQL — EXECUTE IMMEDIATE

Common Gotchas

Key Takeaways