PL/SQL Interview Questions: Procedures, Cursors, Triggers & Packages

pl/sql interview questions focus on four imperative-programming primitives layered on top of Oracle SQL: stored procedures and functions that encapsulate business logic in the database, cursors (explicit and implicit) that walk through result sets row by row, triggers (BEFORE, AFTER, INSTEAD OF) that fire on DML events, and packages that bundle related procedures and functions into a single namespace with a public spec and a private body. Whether you're prepping for pl sql interview questions at an Oracle-heavy enterprise or plsql interview questions for experienced at a financial-services data team, the same four themes — plus exception handling, BULK COLLECT / FORALL, and the differences between pl sql vs sql — show up in every loop.

This guide walks through every theme in the pl sql interview questions and answers ecosystem that reviewers love to test in data engineering interview questions: the PL/SQL block structure (DECLARE / BEGIN / EXCEPTION / END), procedure vs function pl sql, explicit cursors (OPEN / FETCH / CLOSE), implicit cursors (FOR rec IN (SELECT …) loops), the triggers in pl sql lifecycle, packages and package bodies with public/private separation, BULK COLLECT and FORALL for high-throughput data movement, and the EXCEPTION WHEN OTHERS THEN discipline. Every section ends as sql interview questions with answers: a runnable PL/SQL block, a traced execution, an output table, and a concept-by-concept why this works breakdown — the exact shape sql for data engineers rounds reward when PL/SQL comes up.

When you want hands-on reps immediately after reading, browse SQL practice library →, drill the database problems →, sharpen data-manipulation drills →, rehearse ETL SQL patterns →, or widen coverage on the full SQL practice library →.

---

On this page

• Why PL/SQL interview questions test the four imperative primitives
• PL/SQL block structure — DECLARE / BEGIN / EXCEPTION / END
• Procedures and functions — procedure vs function pl sql
• Cursors — explicit, implicit, and FOR cursor loops
• Triggers — BEFORE, AFTER, INSTEAD OF, and the trigger lifecycle
• Packages — spec, body, and the public / private split
• Exception handling, BULK COLLECT, FORALL, and PL/SQL gotchas
• Choosing the right PL/SQL primitive (cheat sheet)
• Frequently asked questions
• Practice on PipeCode

---

1. Why PL/SQL interview questions test the four imperative primitives

PL/SQL = SQL + control flow — and reviewers test all five layers

The one-sentence invariant: PL/SQL adds imperative control flow (IF, LOOP, exception handlers) to declarative SQL so business logic can live in the database; pl/sql interview questions probe the five primitives in turn — blocks, procedures/functions, cursors, triggers, packages — plus the safety patterns around them. Once you know all five, every prompt in the pl sql interview questions roster becomes "which primitive does this answer?"

The five PL/SQL primitives at a glance.

• Block — DECLARE / BEGIN / EXCEPTION / END; — the atomic unit of execution; everything else is built on top.
• Procedure / function — named blocks stored in the database; procedures don't return values, functions do.
• Cursor — a handle to a multi-row result set; explicit (OPEN / FETCH / CLOSE) or implicit (FOR rec IN (SELECT …)).
• Trigger — an anonymous block that fires automatically on DML events (BEFORE / AFTER / INSTEAD OF).
• Package — a namespace bundling procedures and functions; spec (public signature) and body (private impl).

Why interviewers love PL/SQL.

• It tests imperative thinking on top of relational — loops, conditionals, variables — primitives that pure SQL avoids.
• It exposes data-quality discipline — EXCEPTION WHEN OTHERS plus bulk error logging is a senior-signal pattern.
• It surfaces performance instincts — BULK COLLECT and FORALL vs naïve row-by-row processing (the "context switch" cost).
• It hits operational realism — packages organise code, triggers enforce invariants, audit columns get set in BEFORE UPDATE triggers.

pl/sql vs sql — the comparison every interview demands.

• SQL — declarative, set-based; you describe the result, the planner figures out the steps.
• PL/SQL — imperative, procedural; you describe the steps; the engine executes them sequentially.
• When to reach for PL/SQL — multi-statement transactions with conditional logic, error recovery, calling external APIs, encapsulating business rules.
• When to stay in pure SQL — set operations, aggregations, joins; PL/SQL row-by-row loops over SQL set ops is the #1 anti-pattern reviewers hunt for.

The pl sql interview questions for experienced checklist.

• Bulk operations — BULK COLLECT to fetch many rows at once; FORALL to bind many rows into one DML.
• Autonomous transactions — PRAGMA AUTONOMOUS_TRANSACTION for separate commit scope (audit logs).
• %ROWTYPE and %TYPE — anchored types that follow the underlying schema.
• Composite types — RECORD, TABLE (associative arrays), VARRAY, nested tables.
• Dynamic SQL — EXECUTE IMMEDIATE and DBMS_SQL.

Worked example — a PL/SQL block that uses all five primitives

Detailed explanation. Real PL/SQL routines bundle multiple primitives. The block below shows the canonical shape: declare variables and a cursor, loop through with FOR rec IN cursor, conditionally call a procedure, handle exceptions, all wrapped in a BEGIN / EXCEPTION / END envelope.

Question. Write an anonymous PL/SQL block that iterates through orders from the last day, logs each 'paid' order to an audit_log table via a procedure, and catches any exception by logging it without aborting the rest of the loop.

Code (PL/SQL).

DECLARE
    v_count NUMBER := 0;

    CURSOR cur_recent_orders IS
        SELECT order_id, customer_id, amount, status
        FROM   orders
        WHERE  order_date >= TRUNC(SYSDATE) - INTERVAL '1' DAY;

    PROCEDURE log_order (
        p_order_id IN orders.order_id%TYPE,
        p_amount   IN orders.amount%TYPE
    ) IS
    BEGIN
        INSERT INTO audit_log (order_id, amount, logged_at)
        VALUES (p_order_id, p_amount, SYSTIMESTAMP);
    END log_order;

BEGIN
    FOR rec IN cur_recent_orders LOOP
        BEGIN
            IF rec.status = 'paid' THEN
                log_order(rec.order_id, rec.amount);
                v_count := v_count + 1;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                INSERT INTO error_log (order_id, err_msg, logged_at)
                VALUES (rec.order_id, SQLERRM, SYSTIMESTAMP);
        END;
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Audited ' || v_count || ' paid orders.');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Step-by-step explanation.

1. DECLARE opens the variable / cursor / nested-procedure section.
2. CURSOR cur_recent_orders is an explicit cursor over the last day's orders.
3. The nested PROCEDURE log_order is private to this block — it could also be in a package.
4. FOR rec IN cur_recent_orders LOOP is the implicit-cursor shape — automatically opens, fetches, and closes the cursor.
5. The inner BEGIN / EXCEPTION / END block scopes exception handling per iteration — one bad row doesn't abort the loop.
6. SQLERRM returns the error message string for logging.
7. COMMIT makes the audit log durable; outer EXCEPTION rolls back if anything escapes the inner handlers.

Output. The audit_log table fills with one row per processed 'paid' order; the error_log captures any failures.

Rule of thumb: PL/SQL blocks combine all five primitives — variables, cursors, procedures, conditional logic, and exception handling — into one transactional unit; this layered shape is exactly what reviewers expect to see.

SQL
Topic — sql
PL/SQL practice library

Practice →

SQL
Topic — database
Database SQL problems

Practice →

---

2. PL/SQL block structure — DECLARE / BEGIN / EXCEPTION / END

pl sql block — every routine has the same four-section shape

Every PL/SQL routine — anonymous block, procedure body, function body, trigger body, package body — has the same four-section structure: DECLARE for variables and types, BEGIN for the executable code, EXCEPTION for error handling, END to close. Knowing this shape cold is the foundation of every pl sql interview questions answer.

The four sections.

• DECLARE — variables, constants, cursors, types, nested procedures / functions. Optional but common.
• BEGIN — executable code; SQL statements, control flow, calls to procedures, cursor operations.
• EXCEPTION — handlers for runtime errors; WHEN <name> THEN clauses; WHEN OTHERS THEN catches anything else.
• END; — closes the block; the trailing semicolon is required.

Anonymous block (no name, runs once).

DECLARE
    v_total NUMBER := 0;
BEGIN
    SELECT SUM(amount) INTO v_total FROM orders;
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No orders.');
END;
/

• No name — runs immediately; not stored in the database.
• / terminator — submits the block to the engine in SQL*Plus / SQLcl.
• Use case — ad-hoc scripts, one-off data fixes, test harnesses.

Variable declarations and assignment.

• Simple types — v_count NUMBER := 0, v_name VARCHAR2(100), v_today DATE := SYSDATE.
• Anchored types %TYPE — v_amount orders.amount%TYPE — copies the column's type; bulletproof against schema changes.
• Record types %ROWTYPE — v_order orders%ROWTYPE — a record matching the row shape.
• Constants — c_max_retries CONSTANT NUMBER := 3 — value cannot be reassigned.

Control flow.

• IF / ELSIF / ELSE / END IF — branching conditionals.
• CASE WHEN … THEN … ELSE … END — expression-form switching.
• LOOP / EXIT WHEN / END LOOP — generic loop with conditional exit.
• WHILE <cond> LOOP / END LOOP — pre-test loop.
• FOR i IN 1..10 LOOP / END LOOP — counted loop.
• FOR rec IN cursor LOOP / END LOOP — cursor FOR loop (see §4).

pl sql data types — the cross-cut.

• Scalar — NUMBER, VARCHAR2(n), CHAR(n), DATE, TIMESTAMP, BOOLEAN (PL/SQL-only; not a SQL type).
• Composite — RECORD, TABLE OF <type> (associative arrays), VARRAY(n) OF <type>, nested tables.
• Anchored — %TYPE, %ROWTYPE.
• PLS_INTEGER / BINARY_INTEGER — faster than NUMBER for tight loops.

Nested blocks.

• A BEGIN / EXCEPTION / END block can be nested inside another block's BEGIN section.
• Inner exception handlers scope to the inner block; uncaught exceptions propagate to the outer handler.
• Use case — per-iteration error handling inside a loop, where one bad row shouldn't kill the batch.

SQL
Topic — sql
PL/SQL block drills

Practice →

SQL
Topic — database
PL/SQL exception patterns

Practice →

---

3. Procedures and functions — procedure vs function pl sql

procedure vs function pl sql — one returns, the other doesn't

procedure vs function pl sql is one of the most-asked pl/sql interview questions of all. The distinction in one sentence: procedures encapsulate logic and side effects (DML, calls, prints) without returning a value; functions compute and return a single value (or table-type, with pipelined functions) usable inside SELECT expressions.

CREATE OR REPLACE PROCEDURE — the imperative routine.

CREATE OR REPLACE PROCEDURE update_order_status (
    p_order_id IN  orders.order_id%TYPE,
    p_status   IN  VARCHAR2,
    p_rows_updated OUT NUMBER
) IS
BEGIN
    UPDATE orders
    SET    status = p_status,
           updated_at = SYSTIMESTAMP
    WHERE  order_id = p_order_id;

    p_rows_updated := SQL%ROWCOUNT;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END update_order_status;
/

• IN parameter — input only (the default); cannot be reassigned inside the body.
• OUT parameter — output only; returned to the caller (note: not a return value — procedures don't have those).
• IN OUT — both directions; rare but useful for accumulator patterns.
• SQL%ROWCOUNT — implicit cursor attribute giving the number of rows affected by the most recent SQL statement.

CREATE OR REPLACE FUNCTION — returns a value.

CREATE OR REPLACE FUNCTION get_customer_lifetime_value (
    p_customer_id IN customers.customer_id%TYPE
) RETURN NUMBER IS
    v_total NUMBER := 0;
BEGIN
    SELECT NVL(SUM(amount), 0)
    INTO   v_total
    FROM   orders
    WHERE  customer_id = p_customer_id
      AND  status = 'paid';

    RETURN v_total;
EXCEPTION
    WHEN OTHERS THEN
        RETURN 0;
END get_customer_lifetime_value;
/

• RETURN <type> in the signature names the return type.
• RETURN <value>; statement inside the body actually returns.
• Usable inside SELECT — SELECT customer_id, get_customer_lifetime_value(customer_id) AS clv FROM customers.
• DETERMINISTIC keyword — FUNCTION … RETURN … DETERMINISTIC IS — tells the optimizer that same input → same output (enables result caching).

procedure vs function pl sql — the decision table.

Aspect	Procedure	Function
Returns a value?	No (uses OUT parameters)	Yes (RETURN <expr>;)
Usable in SELECT?	No	Yes
Side effects?	Common (DML, prints)	Discouraged (especially for functions called in SELECT)
Call syntax	EXEC proc(args) or inside PL/SQL	Inside SQL or PL/SQL expressions
Multiple results	OUT parameters	Single scalar (or pipelined table)

Parameter modes — IN / OUT / IN OUT.

• IN — default; pass by value (logically); read-only inside the body.
• OUT — write-only; the body must assign a value before returning; passed back to caller.
• IN OUT — passed in, can be modified, passed back; useful for accumulators or modifying composite types.

Default parameter values.

• p_status VARCHAR2 := 'pending' — default if the caller omits the argument.
• Named-parameter call — update_order_status(p_order_id => 42, p_status => 'paid') lets the caller pass arguments out of order or use defaults selectively.

Stored vs anonymous — when to promote a block.

• Anonymous block for one-off scripts.
• Procedure when the logic is reused or called from multiple places.
• Function when the logic computes a single value that's usable inline.
• Package member when related procedures / functions should share a namespace (§6).

SQL
Topic — sql
Procedure / function drills

Practice →

SQL
Topic — data-manipulation
Stored-routine patterns

Practice →

---

4. Cursors — explicit, implicit, and FOR cursor loops

cursors in pl sql — three shapes that cover every row-by-row pattern

cursors in pl sql are how you walk through a multi-row result set in imperative code. Three shapes cover every case: explicit cursors (manual OPEN / FETCH / CLOSE), implicit cursors (SELECT INTO for single-row, plus the SQL%ROWCOUNT family of attributes), and the cursor FOR loop (the most-used shape — automatic open / fetch / close).

Explicit cursor — full manual control.

DECLARE
    CURSOR cur_orders IS
        SELECT order_id, customer_id, amount
        FROM   orders
        WHERE  status = 'pending';

    v_order_id   orders.order_id%TYPE;
    v_customer   orders.customer_id%TYPE;
    v_amount     orders.amount%TYPE;
BEGIN
    OPEN cur_orders;
    LOOP
        FETCH cur_orders INTO v_order_id, v_customer, v_amount;
        EXIT WHEN cur_orders%NOTFOUND;
        -- process each row
        DBMS_OUTPUT.PUT_LINE(v_order_id || ' / ' || v_amount);
    END LOOP;
    CLOSE cur_orders;
END;
/

• OPEN opens the cursor — executes the query, positions before the first row.
• FETCH … INTO advances and copies the current row into variables.
• %NOTFOUND attribute — TRUE when the most recent FETCH returned no row.
• CLOSE releases the cursor — always do this; don't leak.

Implicit cursor — SELECT INTO for single-row queries.

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM orders WHERE status = 'paid';
    DBMS_OUTPUT.PUT_LINE('Paid orders: ' || v_count);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No paid orders.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('More than one row returned.');
END;
/

• SELECT INTO — for queries returning exactly one row; raises NO_DATA_FOUND or TOO_MANY_ROWS otherwise.
• SQL%FOUND / SQL%NOTFOUND / SQL%ROWCOUNT — implicit cursor attributes for the most recent DML statement.
• Use case — single-row lookups, COUNT / SUM aggregates, existence checks.

Cursor FOR loop — the idiomatic shape.

BEGIN
    FOR rec IN (
        SELECT order_id, customer_id, amount
        FROM   orders
        WHERE  status = 'pending'
    ) LOOP
        UPDATE orders SET status = 'review'
        WHERE order_id = rec.order_id;
    END LOOP;
    COMMIT;
END;
/

• FOR rec IN (<query>) LOOP — automatic open, fetch, close; no manual lifecycle management.
• rec is a record with one field per SELECT column.
• Cleaner than the explicit form by 5-10 lines; the right default for row-by-row iteration.
• cursor for loop in pl sql is the most-searched form of this idiom.

Cursor attributes — %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.

• Explicit cursor — cur_name%FOUND etc.
• Implicit cursor (last DML) — SQL%FOUND etc.
• %ROWCOUNT — number of rows fetched so far (explicit) or rows affected (implicit).
• %ISOPEN — TRUE if the cursor is currently open.

Parameterized cursors.

DECLARE
    CURSOR cur_orders_by_region (p_region VARCHAR2) IS
        SELECT order_id, amount
        FROM   orders
        WHERE  region = p_region
          AND  status = 'paid';
BEGIN
    FOR rec IN cur_orders_by_region('US') LOOP
        -- process US orders
        NULL;
    END LOOP;
END;
/

• Cursor with parameters — the cursor's query references parameters at open-time.
• Open with arguments — OPEN cur_name(arg1, arg2) or pass arguments to the FOR loop.

REF CURSOR — dynamic cursors and cursor variables.

• Strong REF CURSOR — TYPE t_cur IS REF CURSOR RETURN <row_type> — type-anchored.
• Weak REF CURSOR — TYPE t_cur IS REF CURSOR — any SELECT query.
• Use case — returning a result set from a procedure; opening different queries based on input.

The row-by-row anti-pattern — and BULK COLLECT as the fix.

• The anti-pattern — looping with a cursor and issuing one DML per row; each iteration is a SQL→PL/SQL context switch.
• The fix — BULK COLLECT INTO <collection> to fetch many rows at once; FORALL to bind many DML statements as one.
• See §7 for the full BULK COLLECT / FORALL pattern.

SQL
Topic — sql
PL/SQL cursor drills

Practice →

SQL
Topic — data-manipulation
Cursor + ETL patterns

Practice →

---

5. Triggers — BEFORE, AFTER, INSTEAD OF, and the trigger lifecycle

triggers in pl sql — fire automatically on DML events

triggers in pl sql are anonymous PL/SQL blocks attached to a table or view, executed automatically by the engine when a DML event occurs. The three timing keywords (BEFORE, AFTER, INSTEAD OF) plus the granularity choice (FOR EACH ROW vs statement-level) define the trigger's behaviour.

The trigger types.

• BEFORE INSERT / UPDATE / DELETE — fires before the row change; can modify :NEW values.
• AFTER INSERT / UPDATE / DELETE — fires after the row change; cannot modify the row.
• INSTEAD OF INSERT / UPDATE / DELETE — only on views; replaces the DML entirely.

Row-level vs statement-level.

• FOR EACH ROW — fires once per affected row; :NEW and :OLD pseudorecords accessible.
• Statement-level (default) — fires once per DML statement, regardless of affected-row count.

BEFORE INSERT trigger — the most common pattern.

CREATE OR REPLACE TRIGGER trg_orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- normalize and set audit columns
    :NEW.region     := UPPER(:NEW.region);
    :NEW.created_at := NVL(:NEW.created_at, SYSTIMESTAMP);
    :NEW.created_by := NVL(:NEW.created_by, USER);
END trg_orders_before_insert;
/

• :NEW — the row being inserted; modifiable in BEFORE triggers.
• :OLD — the row before the change (only meaningful for UPDATE / DELETE).
• Use case — auto-fill audit columns, normalise data, validate before write.

AFTER INSERT trigger — audit-log shape.

CREATE OR REPLACE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, row_id, action, action_ts, action_by)
    VALUES ('orders', :NEW.order_id, 'INSERT', SYSTIMESTAMP, USER);
END trg_orders_after_insert;
/

• AFTER triggers can't modify :NEW — the row already exists.
• Use case — audit logs, downstream-table maintenance, cache invalidation.

INSTEAD OF trigger — on views.

CREATE OR REPLACE TRIGGER trg_orders_view_iou
INSTEAD OF INSERT ON v_active_orders
FOR EACH ROW
BEGIN
    INSERT INTO orders (order_id, customer_id, amount, status)
    VALUES (:NEW.order_id, :NEW.customer_id, :NEW.amount, 'active');
END;
/

• Only on views — most join-views aren't directly DML-writable; INSTEAD OF translates the view DML to base-table DML.
• Use case — making a join-view appear writable to an application that doesn't know about the base tables.

Trigger ordering.

• FOLLOWS clause (Oracle 11g+) — CREATE TRIGGER … FOLLOWS another_trigger to control fire order.
• By default — Oracle does not guarantee a specific order between triggers of the same type.

Conditional triggers — WHEN clause.

CREATE OR REPLACE TRIGGER trg_orders_paid_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (NEW.status = 'paid' AND OLD.status <> 'paid')
BEGIN
    INSERT INTO paid_audit (order_id, paid_ts)
    VALUES (:NEW.order_id, SYSTIMESTAMP);
END;
/

• WHEN (cond) — fires only when the predicate is true.
• Note: inside WHEN, omit the colons — NEW.status not :NEW.status.
• Use case — fire on a specific state transition.

Trigger pitfalls.

• Mutating table error — querying the trigger's own table from inside a FOR EACH ROW trigger fails (ORA-04091); workaround with statement-level triggers or compound triggers.
• Cascading triggers — one trigger fires another; can produce surprising chains.
• Performance — every DML pays the trigger cost; avoid heavy logic in FOR EACH ROW triggers.
• Hidden business logic — triggers can hide important rules from application engineers; document them.

SQL
Topic — sql
Trigger SQL drills

Practice →

SQL
Topic — database
Trigger + audit patterns

Practice →

---

6. Packages — spec, body, and the public / private split

packages in pl sql — bundle related routines under one namespace

packages in pl sql are the senior-level organising primitive: bundle related procedures, functions, types, constants, and cursors under one name. A package has two halves — the spec (what callers see) and the body (the implementation). The spec/body split provides encapsulation, performance benefits, and a clean API surface.

The package spec — public signature.

CREATE OR REPLACE PACKAGE order_pkg AS
    -- public constants
    g_paid_status CONSTANT VARCHAR2(20) := 'paid';

    -- public types
    TYPE t_order_ids IS TABLE OF orders.order_id%TYPE;

    -- public procedure signatures
    PROCEDURE mark_paid (p_order_id IN orders.order_id%TYPE);

    -- public function signatures
    FUNCTION get_total_revenue (p_region IN VARCHAR2) RETURN NUMBER;

    -- public cursor (rare)
    CURSOR cur_pending_orders IS
        SELECT order_id FROM orders WHERE status = 'pending';

END order_pkg;
/

The package body — private implementation.

CREATE OR REPLACE PACKAGE BODY order_pkg AS
    -- private variable (not in spec)
    g_call_count NUMBER := 0;

    -- private helper procedure (not in spec)
    PROCEDURE log_call (p_proc_name VARCHAR2) IS
    BEGIN
        g_call_count := g_call_count + 1;
        INSERT INTO call_log (proc_name, call_ts)
        VALUES (p_proc_name, SYSTIMESTAMP);
    END log_call;

    -- public procedure implementation
    PROCEDURE mark_paid (p_order_id IN orders.order_id%TYPE) IS
    BEGIN
        log_call('mark_paid');
        UPDATE orders
        SET    status = g_paid_status,
               updated_at = SYSTIMESTAMP
        WHERE  order_id = p_order_id;
        COMMIT;
    END mark_paid;

    -- public function implementation
    FUNCTION get_total_revenue (p_region IN VARCHAR2) RETURN NUMBER IS
        v_total NUMBER;
    BEGIN
        SELECT NVL(SUM(amount), 0) INTO v_total
        FROM   orders
        WHERE  region = p_region AND status = g_paid_status;
        RETURN v_total;
    END get_total_revenue;
END order_pkg;
/

Why packages matter — five benefits.

• Namespace — order_pkg.mark_paid(42) is clearer than a global mark_paid; avoids name clashes.
• Encapsulation — private routines and variables are inaccessible outside the body.
• Performance — the engine loads the whole package once; subsequent calls hit a cache.
• Session state — package variables persist across calls within a session (state survives between procedure invocations).
• Modular dependency — changes to the package body don't invalidate dependents; only changes to the spec do.

Public vs private split.

• Public (in the spec) — accessible from outside the package; the API surface.
• Private (only in the body) — accessible only inside the package body; helper routines, internal state.
• Best practice — keep the spec small; put implementation details in the body.

pragma directives in packages.

• PRAGMA SERIALLY_REUSABLE — release session state at the end of each call; useful for memory-tight environments.
• PRAGMA AUTONOMOUS_TRANSACTION (in a body procedure) — that procedure runs in its own transaction; independent commit / rollback.
• PRAGMA RESTRICT_REFERENCES — legacy; declares purity (WNDS / RNDS / WNPS / RNPS).

Calling package members.

• From SQL — SELECT order_pkg.get_total_revenue('US') FROM dual.
• From PL/SQL — order_pkg.mark_paid(42); inside a procedure body.
• Schema-qualified — app_owner.order_pkg.get_total_revenue('US') when called across schemas.

SQL
Topic — sql
PL/SQL package drills

Practice →

SQL
Topic — database
Stored-routine architecture

Practice →

---

7. Exception handling, BULK COLLECT, FORALL, and PL/SQL gotchas

exception handling in pl sql and bulk processing — the senior signal

The senior-level pl sql interview questions and answers roster always probes two patterns: robust exception handling (EXCEPTION WHEN OTHERS THEN discipline) and high-throughput bulk processing (BULK COLLECT + FORALL). Together they separate the engineer who writes correct PL/SQL from the one who writes correct, fast PL/SQL.

exception handling in pl sql — the named-exception toolbox.

• NO_DATA_FOUND — SELECT INTO returned no rows.
• TOO_MANY_ROWS — SELECT INTO returned more than one row.
• DUP_VAL_ON_INDEX — insert violated a UNIQUE constraint.
• VALUE_ERROR — type conversion or numeric overflow.
• ZERO_DIVIDE — divide by zero.
• OTHERS — catch-all; pair with SQLCODE / SQLERRM to identify what was caught.

The canonical exception block.

BEGIN
    -- ...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- specific handler
        NULL;
    WHEN DUP_VAL_ON_INDEX THEN
        -- another specific handler
        NULL;
    WHEN OTHERS THEN
        INSERT INTO error_log (err_code, err_msg, raised_at)
        VALUES (SQLCODE, SQLERRM, SYSTIMESTAMP);
        RAISE;   -- re-raise after logging
END;

• SQLCODE — the numeric error code (e.g. -1 for DUP_VAL_ON_INDEX).
• SQLERRM — the error message string.
• RAISE; with no name — re-raises the current exception to the outer block.
• RAISE_APPLICATION_ERROR(-20001, 'msg') — raise a user-defined error with a custom number (in the -20000..-20999 range) and message.

User-defined exceptions.

DECLARE
    e_invalid_status EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_invalid_status, -20100);
BEGIN
    IF :NEW.status NOT IN ('pending', 'paid', 'refund', 'cancel') THEN
        RAISE e_invalid_status;
    END IF;
EXCEPTION
    WHEN e_invalid_status THEN
        RAISE_APPLICATION_ERROR(-20100, 'Invalid order status: ' || :NEW.status);
END;

• Declare with e_name EXCEPTION;.
• Bind a number with PRAGMA EXCEPTION_INIT(e_name, -20100);.
• Raise with RAISE e_name;.
• Handle with WHEN e_name THEN ….

BULK COLLECT — fetch many rows in one call.

DECLARE
    TYPE t_order_ids IS TABLE OF orders.order_id%TYPE;
    v_ids t_order_ids;
BEGIN
    SELECT order_id BULK COLLECT INTO v_ids
    FROM   orders
    WHERE  status = 'pending'
    LIMIT  1000;

    -- v_ids is now a collection of up to 1000 IDs
    FOR i IN 1..v_ids.COUNT LOOP
        -- process each
        NULL;
    END LOOP;
END;
/

• BULK COLLECT INTO <collection> — populates a collection in one round trip.
• LIMIT <n> clause caps the fetch size — important for very large result sets to avoid OOM.
• Pattern — loop: FETCH cursor BULK COLLECT INTO v_collection LIMIT 1000; EXIT WHEN v_collection.COUNT = 0;.

FORALL — bind many DML statements in one call.

FORALL i IN 1..v_ids.COUNT
    UPDATE orders
    SET    status = 'review'
    WHERE  order_id = v_ids(i);

• FORALL — sends the entire bind array to the SQL engine in one call (one context switch instead of N).
• Massive speedup — typical gains of 10-50× over row-by-row UPDATE.
• SAVE EXCEPTIONS clause — FORALL i IN … SAVE EXCEPTIONS UPDATE …; … EXCEPTION WHEN OTHERS THEN FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP … — continues past failed rows, captures errors.

The seven PL/SQL gotchas interviewers test most often.

• Mutating-table error — querying / mutating the trigger's own table inside a FOR EACH ROW trigger.
• NO_DATA_FOUND on SELECT INTO — single-row selects that return no row raise this; guard with EXCEPTION WHEN NO_DATA_FOUND.
• WHEN OTHERS THEN NULL — silent error-swallowing; the worst PL/SQL anti-pattern; always log or re-raise.
• Row-by-row processing instead of BULK COLLECT + FORALL — 10-50× slowdown.
• Uncommitted transactions in autonomous triggers — autonomous transactions must COMMIT or the change is lost.
• COMMIT inside a trigger — illegal in row-level triggers (ORA-04092); use autonomous transactions if commit is required.
• NULL in PL/SQL boolean expressions — IF v_bool where v_bool IS NULL neither runs the THEN nor the ELSE (UNKNOWN); use IF NVL(v_bool, FALSE) for safety.

SQL
Topic — sql
BULK COLLECT / FORALL drills

Practice →

SQL
Topic — etl
ETL + bulk processing

Practice →

---

Choosing the right PL/SQL primitive (cheat sheet)

A one-screen cheat sheet for the most-asked pl/sql interview questions patterns.

You want to …	PL/SQL primitive	Notes
Run an ad-hoc script	Anonymous block (DECLARE / BEGIN / END;)	One-off; not stored
Encapsulate logic for reuse	CREATE OR REPLACE PROCEDURE	No return value
Compute a value usable in SELECT	CREATE OR REPLACE FUNCTION	RETURN <type>
Walk a multi-row result set	FOR rec IN (SELECT …) LOOP	Cursor FOR loop — the default
Fetch a single row	SELECT … INTO v_var FROM …	Guard with WHEN NO_DATA_FOUND
Audit every INSERT	AFTER INSERT ON t FOR EACH ROW trigger	Insert into audit_log
Auto-fill columns on INSERT	BEFORE INSERT … FOR EACH ROW trigger	Modify :NEW.col
Make a view DML-writable	INSTEAD OF INSERT trigger	On the view, not the base table
Bundle related routines	CREATE PACKAGE / PACKAGE BODY	Public spec, private body
Catch a specific error	EXCEPTION WHEN NO_DATA_FOUND THEN …	Named exception
Catch any error	EXCEPTION WHEN OTHERS THEN … RAISE;	Always log; always re-raise
Process 100k rows fast	BULK COLLECT INTO + FORALL	10-50× faster than row-by-row
Independent commit inside a trigger	PRAGMA AUTONOMOUS_TRANSACTION	In a separate procedure
Anchor types to a column	v_col table.col%TYPE	Survives schema changes
Anchor a row variable	v_row table%ROWTYPE	Whole-row record

---

Frequently asked questions

What's the difference between a procedure and a function in PL/SQL?

A procedure encapsulates logic and side effects (DML, DBMS_OUTPUT prints, calls to other procedures) and does not return a value — output is communicated via OUT parameters. A function computes and returns a single value via the RETURN <expr>; statement; the return type is named in the function signature (RETURN NUMBER, RETURN VARCHAR2(100)). Functions are usable inside SELECT expressions — SELECT customer_id, get_lifetime_value(customer_id) FROM customers — while procedures must be invoked via EXEC proc(args) from SQL*Plus or called from within another PL/SQL block. The interview rule of thumb: reach for a function when the routine computes a value usable inline; reach for a procedure when the routine performs DML or other side effects.

What are the three types of triggers in PL/SQL?

The three trigger timing keywords are BEFORE (fires before the row change; can modify :NEW values via :NEW.col := …), AFTER (fires after the row change; cannot modify the row but commonly inserts into an audit log), and INSTEAD OF (fires only on views; replaces the DML entirely — used to make join-views appear writable). Triggers can be row-level (FOR EACH ROW, fires once per affected row, :NEW and :OLD records available) or statement-level (default, fires once per DML statement regardless of row count). The senior-level interview gotcha is the mutating-table error — ORA-04091 — raised when a row-level trigger queries or modifies the same table it's attached to; work around with statement-level triggers or compound triggers.

What's the difference between an explicit cursor and an implicit cursor in PL/SQL?

An explicit cursor is declared with CURSOR cur_name IS SELECT … in the DECLARE section, then manually opened (OPEN cur_name), fetched (FETCH cur_name INTO v1, v2), and closed (CLOSE cur_name). It gives full control over the cursor lifecycle and supports attributes like cur_name%FOUND, cur_name%NOTFOUND, cur_name%ROWCOUNT. An implicit cursor is created automatically by the engine for every SELECT INTO, INSERT, UPDATE, DELETE statement — accessed via the SQL% prefix (SQL%FOUND, SQL%ROWCOUNT). The idiomatic third shape is the cursor FOR loop — FOR rec IN (SELECT …) LOOP … END LOOP — which combines the explicit cursor's structure with automatic open / fetch / close, and is the right default for row-by-row processing in most cases. For high-throughput pipelines, replace cursor loops entirely with BULK COLLECT + FORALL.

What is a package in PL/SQL and why use one?

A package is a database object that bundles related procedures, functions, types, constants, and cursors under a single namespace. It has two parts: the spec (CREATE PACKAGE … AS … END;) declares the public API — procedure signatures, function signatures, public types, public constants — and the body (CREATE PACKAGE BODY … AS … END;) contains the implementations plus any private helper routines and variables. The five benefits: namespace (avoid global name clashes), encapsulation (private routines aren't accessible outside the body), performance (the engine loads the package once and caches it), session state (package variables persist across calls within a session), and modularity (changes to the body don't invalidate dependents — only changes to the spec do). The interview-canonical pattern: every set of related routines (order_pkg.mark_paid, order_pkg.get_total_revenue, etc.) belongs in one package; loose-leaf standalone procedures are a code smell at scale.

What are BULK COLLECT and FORALL in PL/SQL?

BULK COLLECT and FORALL are the high-throughput pair that replace naïve row-by-row processing. BULK COLLECT INTO <collection> fetches many rows in one round trip — SELECT col BULK COLLECT INTO v_collection FROM t LIMIT 1000 populates a collection with up to 1000 entries in one SQL→PL/SQL context switch. FORALL i IN 1..v_collection.COUNT UPDATE … WHERE id = v_collection(i) sends the entire bind array to the SQL engine in a single call, executing the DML once per array element with one bulk bind. Together they replace the row-by-row anti-pattern (cursor loop with per-row DML, N context switches) with a two-call pattern (one fetch, one DML), typically yielding 10-50× speedups. Combine with SAVE EXCEPTIONS on FORALL to capture per-row failures into SQL%BULK_EXCEPTIONS and continue past errors instead of aborting the whole batch — the textbook pl sql interview questions and answers for experienced pattern.

---

Practice on PipeCode

PipeCode ships 450+ data-engineering interview problems — including SQL practice keyed to the same core concepts that underpin PL/SQL (stored procedures, cursors, triggers, packages), plus the SQL set-operations / aggregation / window-function fundamentals that PL/SQL routines wrap. Whether you're drilling pl sql interview questions for freshers or grinding pl/sql interview questions and answers for experienced, the practice library gives you the SQL reps to back up every PL/SQL pattern this guide teaches.

Kick off via Explore practice →; drill the SQL practice lane →; fan out into the database lane →; rehearse data-manipulation patterns →; reinforce ETL SQL drills →; widen coverage on the full SQL practice library →.