Oracle PL/SQL Interview Questions

SQL is a declarative query language — each statement is a single instruction the database executes. PL/SQL is Oracle's procedural extension: it wraps SQL in a real programming language with variables, loops, conditions, and exception handling, so you can build multi-step programs (procedures, functions, packages, triggers) that run inside the database engine.

A block has three parts: an optional DECLARE section (variables, cursors, types), a mandatory BEGIN ... END executable section, and an optional EXCEPTION section to handle runtime errors. An anonymous block has no name; a named block is a procedure, function, or trigger.

DECLARE
  v_total NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_total FROM employees;
  DBMS_OUTPUT.PUT_LINE(v_total);
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
END;

%TYPE declares a variable of the same type as a single column or another variable (v_sal employees.salary%TYPE). %ROWTYPE declares a record matching an entire table row or cursor (rec employees%ROWTYPE). Both keep your code in sync automatically if the underlying column types change.

An anonymous block is compiled and run each time it's submitted and is not stored in the database. A stored subprogram (procedure, function, package) is compiled once, stored in the data dictionary, can be called by name, and can be granted to other users — the basis for reusable, secured server-side logic.

Oracle opens an implicit cursor automatically for every SQL statement — you access its outcome through attributes like SQL%ROWCOUNT and SQL%FOUND. An explicit cursor is one you declare, OPEN, FETCH from in a loop, and CLOSE, giving row-by-row control over a multi-row query.

%FOUND (last fetch returned a row), %NOTFOUND (it didn't — the usual loop-exit test), %ROWCOUNT (rows fetched/affected so far), and %ISOPEN (is the cursor open). They apply to both explicit cursors and the implicit SQL cursor.

A cursor FOR loop implicitly opens the cursor, fetches each row into a loop record, and closes it automatically when done or on exception. It's preferred over manual OPEN/FETCH/CLOSE because it's less code and can't leak an open cursor.

FOR rec IN (SELECT employee_id, salary FROM employees) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.employee_id || ': ' || rec.salary);
END LOOP;

A REF CURSOR is a cursor variable — a pointer to a result set that can be opened for different queries at runtime and passed between subprograms or returned to a client. A weak ref cursor (SYS_REFCURSOR) has no fixed return type; a strong one is bound to a specific record type.

Runtime errors raise exceptions, which transfer control to the block's EXCEPTION section. You handle specific ones with WHEN exception_name THEN and everything else with WHEN OTHERS THEN. Exceptions can be predefined (e.g. NO_DATA_FOUND, TOO_MANY_ROWS), user-defined, or associated to an Oracle error number.

Inside an exception handler, SQLCODE returns the numeric Oracle error code (0 for success, a negative number for most errors) and SQLERRM returns the matching error message text. They're used to log or branch on the specific error, especially inside WHEN OTHERS.

It binds a user-named exception to a specific Oracle error number so you can handle it by name instead of checking SQLCODE. For example, associating a name with ORA-00001 lets you write WHEN dup_value THEN for a unique-constraint violation.

DECLARE
  deadlock_detected EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
  NULL;
EXCEPTION
  WHEN deadlock_detected THEN NULL;
END;

A procedure performs an action and returns values (if any) through OUT parameters; it's called as a statement. A function must return exactly one value via RETURN and can be used inside a SQL or PL/SQL expression. As a rule, use a function when you want a value, a procedure when you want an effect.

IN (the default) passes a read-only value into the subprogram. OUT returns a value to the caller and starts as NULL inside. IN OUT passes a value in and returns a (possibly modified) value out.

A package groups related procedures, functions, types, and variables into a specification (the public interface) and a body (the implementation). Benefits: encapsulation and information hiding, better performance (the whole package loads into memory on first call), session-persistent package state, and the ability to overload subprograms.

The specification declares what is public — subprogram signatures, types, and constants callers can see — and can exist without a body. The body contains the actual implementation plus any private (body-only) subprograms and variables. Changing only the body doesn't invalidate code that depends on the spec.

By timing: BEFORE and AFTER. By level: statement-level (fires once per statement) and row-level (FOR EACH ROW, fires once per affected row, exposing :OLD and :NEW). Plus INSTEAD OF triggers on views, compound triggers, and DDL/database-event triggers.

It's the ORA-04091 error raised when a row-level trigger tries to read or modify the same table that fired it, because the table is mid-change and its state is inconsistent. Classic fixes are a compound trigger, or capturing the needed keys in a row-level trigger and doing the work in an after-statement trigger.

They cut down on context switches between the PL/SQL and SQL engines. BULK COLLECT fetches many rows into a collection in one round trip instead of row by row; FORALL sends an entire collection of DML in a single call. On large data sets they can be an order of magnitude faster than a row-by-row loop.

SELECT * BULK COLLECT INTO emp_tab FROM employees;
FORALL i IN 1 .. emp_tab.COUNT
  UPDATE employees SET salary = salary * 1.05
  WHERE employee_id = emp_tab(i).employee_id;

Marked with PRAGMA AUTONOMOUS_TRANSACTION, it runs as an independent transaction that can COMMIT or ROLLBACK without affecting the calling (main) transaction. The classic use is logging or auditing that must persist even if the parent transaction rolls back.

Dynamic SQL is a statement built and executed at runtime with EXECUTE IMMEDIATE or DBMS_SQL — needed when the table name, columns, or predicates aren't known until execution. Always pass user values as bind variables (USING) rather than concatenating them into the string, to prevent SQL injection and allow cursor reuse.