Home › Blog › PL/SQL Interview Questions (2026): 32 Questions With Answers and Code

PL/SQL Interview Questions (2026): 32 Questions With Answers and Code

PL/SQL interviews test Oracle's procedural language: block structure, cursors, exceptions, procedures, functions and packages, triggers, collections, bulk processing, dynamic SQL and performance. As of November 17, 2026, Oracle's documentation index lists Oracle AI Database 26ai as the current release alongside 21c and 19c, and the 26ai SQL reference includes a native BOOLEAN data type.

PL/SQL interviews test Oracle's procedural language: block structure, cursors, exceptions, procedures, functions and packages, triggers, collections, bulk processing, dynamic SQL and performance. As of November 17, 2026, Oracle's documentation index lists Oracle AI Database 26ai as the current release alongside 21c and 19c, and the 26ai SQL reference includes a native BOOLEAN data type. Below are 32 questions by level with short code, five error scenarios and how interviews test PL/SQL.

PL/SQL collection types

TypeIndexed byBounded?Stored in a table column?
Associative array (INDEX BY)PLS_INTEGER or VARCHAR2NoNo; PL/SQL only
Nested tableInteger, starting at 1NoYes
VARRAYInteger, starting at 1Yes, fixed maximumYes

Fundamentals

  1. What is the structure of a PL/SQL block? An optional DECLARE section, a required BEGIN section, an optional EXCEPTION section and END. Anonymous blocks run once; named blocks such as procedures are stored in the database: BEGIN DBMS_OUTPUT.PUT_LINE('hi'); END;
  2. What do %TYPE and %ROWTYPE do? They declare variables from the database: v_salary employees.salary%TYPE; matches a column, and r_emp employees%ROWTYPE; matches a whole row, so code survives column changes.
  3. Procedure or function? A function returns a value and can be called from SQL if it has no disallowed side effects; a procedure performs an action and returns values only through OUT parameters.
  4. Implicit or explicit cursor? Oracle opens an implicit cursor for every single SQL statement; you declare an explicit cursor to fetch multi-row results yourself. Attributes such as %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN report their state.
  5. What is a cursor FOR loop? A loop that opens, fetches and closes the cursor for you: FOR r IN (SELECT id, name FROM customers) LOOP ... END LOOP;
  6. What is a REF CURSOR? A pointer to a result set that can be opened for different queries and passed to clients; SYS_REFCURSOR is the predefined weak type.
  7. Which predefined exceptions come up most? NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX, VALUE_ERROR and INVALID_NUMBER. Handle the ones you expect, and if you use WHEN OTHERS, log and re-raise.
  8. What is RAISE_APPLICATION_ERROR? A procedure that raises a custom error with a number from -20000 to -20999 and a message: RAISE_APPLICATION_ERROR(-20001, 'Credit limit exceeded');
  9. What does PRAGMA EXCEPTION_INIT do? It binds a named exception to an Oracle error number so you can handle it by name: PRAGMA EXCEPTION_INIT(e_child_exists, -2292);
  10. How do you capture error details? SQLCODE and SQLERRM give the error number and message, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE gives the line where it was raised.
  11. Why use packages? A specification exposes public items and a body hides implementation, which allows overloading, package-level state per session and fewer invalidations when only the body changes.
  12. Which kinds of triggers exist? Row-level and statement-level, BEFORE and AFTER, INSTEAD OF triggers on views, compound triggers, and system or DDL event triggers. Row triggers can read :OLD and :NEW.

Intermediate

  1. What is the mutating table error? ORA-04091: a row-level trigger queries or changes the table that fired it. Fix it with a compound trigger that collects rows and acts in the AFTER STATEMENT section, or move the logic out of the trigger.
  2. How does BULK COLLECT work? It fetches many rows into a collection in one call: FETCH c BULK COLLECT INTO l_rows LIMIT 1000; inside a loop. LIMIT keeps memory bounded on large result sets.
  3. How does FORALL work, and what does SAVE EXCEPTIONS add? FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS UPDATE orders SET status = 'X' WHERE id = l_ids(i); sends one bulk DML call. With SAVE EXCEPTIONS, failures raise ORA-24381 at the end and details are in SQL%BULK_EXCEPTIONS.
  4. What is context switching? Each switch between the PL/SQL and SQL engines costs time. Row-by-row loops switch for every row; set-based SQL or bulk binds switch once per batch.
  5. How do you write safe dynamic SQL? With EXECUTE IMMEDIATE and bind variables: EXECUTE IMMEDIATE 'DELETE FROM t WHERE id = :1' USING l_id; Never concatenate user input; validate object names with DBMS_ASSERT. Use DBMS_SQL when the column list is unknown until runtime.
  6. What is an autonomous transaction? A procedure marked PRAGMA AUTONOMOUS_TRANSACTION commits or rolls back independently of the caller, which suits error logging that must survive a rollback. It must end its own transaction before returning.
  7. How do you manage transactions in PL/SQL? COMMIT, ROLLBACK and SAVEPOINT. Let the caller decide when to commit, avoid commits inside loops, and roll back to a savepoint for partial undo.
  8. What are parameter modes, and what does NOCOPY do? IN, OUT and IN OUT. NOCOPY asks Oracle to pass large OUT or IN OUT parameters by reference instead of copying them; it is a hint, and the compiler may ignore it.
  9. Definer's rights or invoker's rights? By default (AUTHID DEFINER) code runs with the owner's privileges; AUTHID CURRENT_USER runs with the caller's, which suits shared utility code.
  10. What is a pipelined table function? A function that returns rows as it produces them with PIPE ROW, so SQL can query it like a table: SELECT * FROM TABLE(get_orders(:id));
  11. How do BOOLEAN values work with SQL? Oracle AI Database 26ai's SQL has a native BOOLEAN type. On older releases such as 19c, PL/SQL BOOLEAN values can't be used in SQL, so functions called from SQL return 'Y' and 'N' or 1 and 0.

Experienced and senior

  1. How do you tune slow PL/SQL? Measure first with the hierarchical profiler (DBMS_HPROF) and execution plans, replace row-by-row logic with set-based SQL or bulk binds, then consider the function result cache and native compilation.
  2. What is the function result cache? Declaring a function RESULT_CACHE keeps results in shared memory keyed by inputs, so repeated calls with the same arguments skip execution. It suits read-mostly lookups.
  3. How do you design error logging? An autonomous logging procedure that records SQLCODE, SQLERRM, the backtrace and context, called from exception handlers that then re-raise, so failures are never silently swallowed.
  4. How do you load or migrate millions of rows? Direct-path inserts with the APPEND hint, parallel DML where licensed and appropriate, or DBMS_PARALLEL_EXECUTE to process chunks in parallel, committing per chunk.
  5. How do you test PL/SQL? With a unit testing framework such as utPLSQL, run in CI against a disposable schema, with tests for each package procedure, including error paths.
  6. How do you deploy changes without downtime? Keep package specifications stable so dependants don't invalidate, and use edition-based redefinition for online upgrades of code and views.
  7. What does ORA-04068 mean after a deployment? "Existing state of packages has been discarded": a package with session state was recompiled while sessions held that state. Minimize package-level state or deploy in a window.
  8. How do you secure PL/SQL code? Bind variables against SQL injection, least-privilege schemas, invoker's rights where appropriate, and code-based access control that grants roles to program units rather than users.
  9. Tell me about a procedure you made faster. Structure the story: the symptom, how you measured it, the root cause (often row-by-row processing or a bad plan), the fix, and the before-and-after numbers.

Five error scenarios interviewers give

How interviews test PL/SQL

Employers don't publish question banks, but Oracle developer candidates commonly report writing a procedure or function live, cursor and exception questions, a performance scenario and a strong SQL round; practice with our SQL interview questions. Roles close to operations add database administrator interview questions, and theory rounds draw on DBMS interview questions.

Where CoPilot Interview fits

CoPilot Interview is an AI interview assistant whose free AI mock interview builds PL/SQL and Oracle development questions from your résumé and the job description, reads them aloud and scores each spoken answer out of 10. The browser demo at /try has 145 practice questions in 10 tracks, scored out of 10, with no signup. The free plan gives 15 minutes a day of live help in sessions of up to 15 minutes, with AI mock interviews included; Standard costs $24.99 a month and Pro $39.99. Live coding tasks, take-homes and online assessments usually forbid outside help, so prepare for those; in any live interview, follow your employer's rules.

Disclosure: CoPilot Interview is our product, so weigh what we say about it accordingly.

Frequently asked questions

What is the difference between SQL and PL/SQL?

SQL is a declarative language for querying and changing data; PL/SQL is Oracle's procedural extension with variables, loops, conditions and exception handling that wraps SQL statements.

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

A function must return a value and can be used in SQL expressions; a procedure performs actions and returns results through OUT parameters.

What causes a mutating table error?

A row-level trigger reading or modifying the table it is defined on. Compound triggers or moving the logic out of the trigger fix it.

Is PL/SQL still in demand?

Oracle databases still run many banking, telecom, government and ERP systems, so PL/SQL skills remain in job posts for maintenance and migration work, often alongside SQL and cloud database skills.

How do I prepare for a PL/SQL interview?

Write a package with a procedure using BULK COLLECT and FORALL, proper exception handling and logging, then explain the performance difference against a row-by-row version.

Practice this interview before the real one

The AI mock interview builds questions from your resume and the job description, reads them aloud and scores each spoken answer out of 10. Included on the free plan.

Start a mock interview