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.
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.
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.
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.
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. |
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
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
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;
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 — IF, ELSIF, CASE
IF / ELSIF / ELSE
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;
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
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 — 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
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
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
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
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 — 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.
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.
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
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;
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 — 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.
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
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
-- 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;
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 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
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
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 — 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
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
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-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 — 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
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
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
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. |
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 — 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
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
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
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;
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 — 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
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
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
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
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;
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.
Common 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
NULLswallows the error silently. The calling code has no idea something failed. Always log at minimumSQLCODEandSQLERRM, 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_CURSORSlimit, 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.