Autocoder

PL/SQL Interview Questions and Answers

Last updated 2026-11-19

PL/SQL is Oracle's procedural extension to SQL, and it is asked in interviews for Oracle developer, database support and many enterprise service-company roles. The questions cover the block structure, cursors, exception handling, subprograms and packages, triggers, and the performance idioms BULK COLLECT and FORALL.

Code on this page is Oracle PL/SQL, and answers refer to Oracle 12c and later. It uses the familiar employees and departments style of table, with columns like emp_id, salary and dept_id. Output from DBMS_OUTPUT appears only when SERVEROUTPUT is on. For the SQL foundation see SQL interview questions, and for the PostgreSQL equivalent of procedural code see PostgreSQL interview questions.

DatabaseOracle 12c and later
LanguagePL/SQL
LevelFreshers to experienced

Anatomy of a PL/SQL block

A block has an optional DECLARE section for variables, cursors and types, a mandatory BEGIN ... END executable section, and an optional EXCEPTION section for handlers. Blocks can be anonymous or named (procedures, functions, packages, triggers) and can nest.

sql
SET SERVEROUTPUT ON
DECLARE
  v_name  employees.name%TYPE;
  v_count NUMBER := 0;
BEGIN
  SELECT name INTO v_name FROM employees WHERE emp_id = 1;
  DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such employee');
END;
/

Topics interviewers return to

PL/SQL Interview Questions and Answers: questions with worked answers

  1. 1

    What is PL/SQL and how is it different from SQL?

    easy

    SQL is a declarative language for querying and changing data, one statement at a time. PL/SQL adds variables, conditions, loops, exception handling and modular code (procedures, functions, packages, triggers) around SQL, so business logic can run inside the database. A PL/SQL block is sent to the server as one unit, which reduces network round trips compared with sending many separate statements. The PL/SQL engine executes the procedural parts and hands SQL statements to the SQL engine.

  2. 2

    What are %TYPE and %ROWTYPE?

    easy

    %TYPE declares a variable with the same data type as a column or another variable, so the code adapts if the column definition changes. %ROWTYPE declares a record whose fields mirror all columns of a table or cursor row. Both avoid hardcoding types and reduce maintenance errors.

    sql
    DECLARE
      v_sal  employees.salary%TYPE;
      r_emp  employees%ROWTYPE;
    BEGIN
      SELECT * INTO r_emp FROM employees WHERE emp_id = 2;
      v_sal := r_emp.salary * 1.1;
    END;
  3. 3

    What is the difference between implicit and explicit cursors?

    medium

    An implicit cursor is created automatically by Oracle for every DML statement and for SELECT INTO; its attributes are read through SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT and SQL%ISOPEN. An explicit cursor is declared by the programmer for a multi-row query, then opened, fetched in a loop and closed. The cursor FOR loop opens, fetches and closes automatically and is the simplest form. SELECT INTO raises NO_DATA_FOUND for zero rows and TOO_MANY_ROWS for more than one, so use a cursor for multi-row results.

    sql
    DECLARE
      CURSOR c_emp IS SELECT name, salary FROM employees WHERE dept_id = 20;
    BEGIN
      FOR r IN c_emp LOOP
        DBMS_OUTPUT.PUT_LINE(r.name || ' ' || r.salary);
      END LOOP;
    END;
  4. 4

    List the cursor attributes and what they return.

    medium

    %FOUND is true if the last fetch returned a row. %NOTFOUND is the opposite and is the usual loop exit test (EXIT WHEN c%NOTFOUND). %ISOPEN reports whether the cursor is open (always false for an implicit cursor after the statement completes). %ROWCOUNT returns the number of rows fetched so far, or rows affected for an implicit cursor. Using %FOUND on a cursor that has not yet been opened raises INVALID_CURSOR.

  5. 5

    How does exception handling work? Name predefined exceptions and show a user-defined one.

    medium

    When an error occurs, control jumps to the EXCEPTION section of the current block, which handles it by name or with WHEN OTHERS. If there is no matching handler the exception propagates to the enclosing block and finally to the caller. Predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, VALUE_ERROR and INVALID_NUMBER. A user-defined exception is declared in DECLARE and raised with RAISE; RAISE_APPLICATION_ERROR returns an error number between -20000 and -20999 to the caller. SQLCODE and SQLERRM give the error number and message inside a handler.

    sql
    DECLARE
      e_low_balance EXCEPTION;
      v_bal NUMBER := 50;
    BEGIN
      IF v_bal < 100 THEN RAISE e_low_balance; END IF;
    EXCEPTION
      WHEN e_low_balance THEN
        RAISE_APPLICATION_ERROR(-20001, 'Balance below minimum');
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ': ' || SQLERRM);
    END;
  6. 6

    What is the difference between a procedure and a function?

    medium

    A function must return a value through RETURN and can be called from SQL statements (SELECT, WHERE) if it does not perform DML or violate purity rules, and in expressions. A procedure does not return a value directly, is called as a statement, and can return data through OUT or IN OUT parameters. Both can have IN, OUT and IN OUT parameters and both can be stored in the database. A function that modifies tables cannot be used in a SELECT.

    sql
    CREATE OR REPLACE FUNCTION annual_pay(p_emp NUMBER) RETURN NUMBER IS
      v_sal employees.salary%TYPE;
    BEGIN
      SELECT salary INTO v_sal FROM employees WHERE emp_id = p_emp;
      RETURN v_sal * 12;
    END;
    /
    CREATE OR REPLACE PROCEDURE raise_salary(p_dept NUMBER, p_pct NUMBER) IS
    BEGIN
      UPDATE employees SET salary = salary * (1 + p_pct / 100) WHERE dept_id = p_dept;
      COMMIT;
    END;
    /
  7. 7

    What is a package and why use one?

    medium

    A package groups related procedures, functions, types and variables into one unit with a specification (the public interface) and a body (the implementation). Benefits: modularity, information hiding (private items live only in the body), better performance because the whole package is loaded into memory on first call, overloading of subprograms, and package-level variables that persist for the session. Changing only the body does not invalidate dependent objects, while changing the specification does.

    sql
    CREATE OR REPLACE PACKAGE emp_pkg AS
      PROCEDURE hire(p_name VARCHAR2, p_dept NUMBER);
      FUNCTION headcount(p_dept NUMBER) RETURN NUMBER;
    END emp_pkg;
    /
  8. 8

    What are triggers, and what are :NEW and :OLD?

    medium

    A trigger is a stored block that fires automatically on a DML event (INSERT, UPDATE, DELETE), a DDL event or a database event. Timing is BEFORE or AFTER, and a row-level trigger (FOR EACH ROW) fires once per affected row, while a statement-level trigger fires once. In a row-level trigger, :OLD holds the row values before the change and :NEW the values after; :NEW is assignable in BEFORE triggers, which is how you set defaults or audit columns. INSERT has no :OLD values and DELETE has no :NEW values.

    sql
    CREATE OR REPLACE TRIGGER trg_emp_audit
    BEFORE UPDATE OF salary ON employees
    FOR EACH ROW
    BEGIN
      INSERT INTO salary_audit (emp_id, old_sal, new_sal, changed_on)
      VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
    END;
    /
  9. 9

    What is the mutating table error (ORA-04091) and how do you avoid it?

    hard

    It occurs when a row-level trigger queries or modifies the same table that is being changed by the statement that fired it, because the table is in an inconsistent mid-change state. Fixes: restructure so the trigger does not read its own table, use a compound trigger that collects row ids in the row-level section and processes them in the AFTER STATEMENT section, or move the logic into a procedure called after the statement. Using an autonomous transaction to bypass the error reads stale data and is not recommended.

  10. 10

    Explain BULK COLLECT and FORALL. Why are they faster?

    hard

    Every SQL statement executed from inside a PL/SQL loop causes a context switch between the PL/SQL and SQL engines. Row-by-row processing pays that cost per row. BULK COLLECT fetches many rows into a collection in one switch, and FORALL sends a whole collection of DML operations to the SQL engine in a single switch. Use the LIMIT clause with BULK COLLECT on big tables to cap memory, and SAVE EXCEPTIONS with FORALL to continue after failed rows.

    sql
    DECLARE
      TYPE t_ids IS TABLE OF employees.emp_id%TYPE;
      v_ids t_ids;
    BEGIN
      SELECT emp_id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = 20;
      FORALL i IN 1 .. v_ids.COUNT
        UPDATE employees SET salary = salary * 1.05 WHERE emp_id = v_ids(i);
      COMMIT;
    END;
  11. 11

    What is a ref cursor (SYS_REFCURSOR)?

    medium

    A cursor variable that points to a query result and can be passed between subprograms and returned to client applications such as Java or .NET. Unlike a static explicit cursor, its query can be decided at run time with OPEN ... FOR. A strongly typed ref cursor declares a return type, and SYS_REFCURSOR is the built-in weakly typed one used for most return-a-result-set procedures.

    sql
    CREATE OR REPLACE PROCEDURE get_emps(p_dept NUMBER, p_rc OUT SYS_REFCURSOR) IS
    BEGIN
      OPEN p_rc FOR SELECT emp_id, name, salary FROM employees WHERE dept_id = p_dept;
    END;
    /
  12. 12

    What is dynamic SQL and what is the risk?

    medium

    Dynamic SQL builds and runs a statement at run time with EXECUTE IMMEDIATE (or DBMS_SQL for complex cases), needed for DDL inside PL/SQL or when table names vary. The risk is SQL injection when user input is concatenated into the text, and poor performance from repeated hard parses. Pass values with bind variables (USING) and validate identifiers with DBMS_ASSERT.

    sql
    EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary + :1 WHERE dept_id = :2' USING 500, 20;
  13. 13

    What is an autonomous transaction?

    medium

    A subprogram declared with PRAGMA AUTONOMOUS_TRANSACTION runs in its own transaction, independent of the caller: it must commit or roll back itself, and its commit does not commit the caller's work. The typical use is writing to an error log or audit table so the log row survives a rollback of the main transaction. Misuse leads to inconsistent data and deadlocks, because the autonomous transaction cannot see the caller's uncommitted changes.

  14. 14

    What is the difference between a stored procedure and an anonymous block?

    easy

    An anonymous block has no name, is compiled each time it is sent and is not stored in the database. A stored procedure is compiled once, stored in the data dictionary with dependency tracking, can be called by name, granted to users with EXECUTE privilege and reused. Anonymous blocks suit one-off scripts and testing, while stored subprograms are for reusable logic.

  15. 15

    How do collections differ: associative arrays, nested tables and VARRAYs?

    medium

    An associative array (index-by table) is sparse, keyed by integer or string, exists only in PL/SQL and needs no initialisation. A nested table is an unbounded collection that can be stored in a database column and becomes sparse after deletes. A VARRAY has a fixed maximum size, is dense and keeps order, and can also be stored in a column. Associative arrays are the usual choice for in-memory lookups, nested tables for set operations and BULK COLLECT.

Frequently asked questions

Is PL/SQL asked for freshers?

Yes, in Oracle and enterprise service-company drives, mostly blocks, cursors, exceptions and the procedure versus function difference.

What is the difference between PL/SQL and T-SQL?

PL/SQL is Oracle's procedural language, T-SQL is Microsoft SQL Server's. Both extend SQL with variables, flow control and error handling, with different syntax. Packages exist only in PL/SQL.

Can I use COMMIT inside a trigger?

Not in an ordinary trigger, because the trigger runs inside the statement's transaction. An autonomous transaction trigger can commit its own work.

What should I revise alongside PL/SQL?

Standard SQL, joins and transactions: see SQL interview questions and SQL joins.

Related guides

More in Programming Language Interview Questions by Language

See all in this section