30 PL/SQL Interview Questions and Answers to Know

30 PL/SQL Interview Questions and Answers to Know

You are staring at an Oracle database developer role with an interview lined up in a day or two, and you don't want to be caught off guard by a syntax question you saw once in a training video years ago. Whether you're the one asking the questions or the one answering them, having a solid set of pl sql interview questions ready makes the whole process faster and less stressful. Recruiters looking to hire PL/SQL developers in India face the same problem from the other side: sorting candidates who genuinely know cursors, triggers, and exception handling from those who just memorized buzzwords.

This list answers that need directly. You'll get 30 pl sql interview questions and answers, covering core PL/SQL blocks, exception handling, cursors, packages, and the SQL fundamentals interviewers pair with them, like joins and indexing. Each answer is written in plain language, not textbook definitions, so you can actually use it in conversation.

We built this guide the way we build every hiring resource at Olibr: with the recruiter's screening process in mind. If you're prepping candidates or building your own technical interview checklist, this list gives you a ready reference you can lean on today, alongside our wider library of interview question banks for software engineers.

1. Beginner PL/SQL interview questions

These are the questions that show up first in almost every screening call, whether it's a phone screen or an in-person round. Recruiters ask them to confirm a candidate actually understands Oracle's procedural extension to SQL before moving into harder territory. If you're prepping a list of interview questions in PL SQL for a junior or mid-level developer, start here.

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

PL/SQL stands for Procedural Language extensions to SQL. It's Oracle's proprietary language that wraps SQL statements inside procedural constructs like loops, conditionals, and variables. Plain SQL is declarative: you tell the database what data you want, and it figures out how to get it, which is the first thing any beginner-friendly guide to SQL drills into you. PL/SQL adds the "how," letting you write blocks of logic that execute step by step, handle errors, and reuse code through procedures and functions. A good answer here also mentions that PL/SQL runs inside the Oracle engine itself, so it can process multiple SQL statements in a single call to the database instead of sending each one separately, which cuts down on network round trips.

What is the basic structure of a PL/SQL block?

Every PL/SQL block follows the same four-part shape, and interviewers expect you to know it cold. The DECLARE section is optional and holds variable and cursor declarations. The BEGIN section is mandatory and contains the executable logic. The EXCEPTION section is optional and catches runtime errors. END closes the block.

DECLARE
 v_salary NUMBER;
BEGIN
 SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
 DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('No employee found.');
END;

Memorize this skeleton, because almost every scenario question later in the interview builds on it.

What are the different PL/SQL data types?

PL/SQL supports scalar types (NUMBER, VARCHAR2, DATE, BOOLEAN), composite types (records, tables, varrays), reference types (REF CURSOR), and LOB types (CLOB, BLOB) for large objects. Candidates often forget BOOLEAN isn't a valid column type in a database table, it only exists inside PL/SQL code, which is a small detail that trips up people who've only worked in SQL. Knowing which types map to real table columns versus which ones are PL/SQL-only shows an interviewer you've actually written production code, not just tutorials.

What is a cursor in PL/SQL?

A cursor is a pointer to the private memory area Oracle uses to execute a SQL statement and hold its result set. Think of it as a way to walk through query results one row at a time when a single SELECT INTO won't cut it because more than one row comes back. Every SQL statement you run technically uses a cursor behind the scenes, but developers usually only talk about "cursors" when they need to loop through multiple rows explicitly.

A cursor is just a controlled way to fetch multiple rows one at a time instead of all at once.

What is the difference between implicit and explicit cursors?

Oracle creates an implicit cursor automatically for any SQL statement that doesn't have a cursor declared by name, like a standalone SELECT INTO, INSERT, UPDATE, or DELETE. An explicit cursor is one you declare and name yourself when you need to process a multi-row result set with full control over opening, fetching, and closing it.

Candidates who can name the cursor attributes, not just describe the difference, usually stand out here. It's a small thing, but it separates people who've read documentation from people who've debugged a batch job at 2 a.m.

What are PL/SQL triggers and when do they fire?

Triggers are stored PL/SQL blocks that execute automatically in response to a specific database event, like an INSERT, UPDATE, or DELETE on a table. They fire at defined timing points relative to the event:

• BEFORE triggers run before the triggering statement executes, often used for validation or defaulting values.
• AFTER triggers run after the statement completes, commonly used for auditing or cascading updates.
• INSTEAD OF triggers replace the triggering action entirely, mainly used on views that aren't directly updatable.
• Row-level triggers fire once for every affected row.
• Statement-level triggers fire once for the entire statement, regardless of how many rows it touches.

Getting this distinction right matters because it's the foundation for later questions about mutating tables and compound triggers, both of which show up constantly in sql and pl sql interview questions aimed at more senior candidates.

What is the use of %TYPE and %ROWTYPE?

%TYPE and %ROWTYPE are anchoring attributes that let you declare variables based on the structure of an existing column or table, instead of hardcoding a data type. %TYPE ties a single variable to the data type of a specific column, so if the column definition changes later, your code doesn't break. %ROWTYPE does the same thing for an entire row, creating a record variable that mirrors every column in a table or cursor.

DECLARE
 v_emp_name employees.first_name%TYPE;
 v_emp_row employees%ROWTYPE;
BEGIN
 SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 100;
 SELECT * INTO v_emp_row FROM employees WHERE employee_id = 100;
END;

Experienced developers reach for these two attributes by default rather than typing out VARCHAR2(50) everywhere, because it keeps code resilient when a DBA quietly widens a column. If a candidate can explain why that matters for maintenance, not just what the syntax does, that's a strong signal for a recruiter evaluating real-world experience rather than memorized definitions.

2. Intermediate PL/SQL interview questions

Once a candidate clears the basics, interviewers move into territory that separates people who've read the Oracle documentation from people who've shipped code that survived a production load. This tier of pl sql interview questions and answers covers packages, exception handling, and the transaction control commands that show up in almost every code review. Recruiters use these to gauge whether a developer can be trusted with logic that touches real data, not just a training sandbox, which is the same bar applied to every profile when you hire a vetted SQL developer.

What is the difference between a procedure and a function?

Both are named PL/SQL blocks you can call repeatedly, but they serve different purposes. A function must return exactly one value and is typically used inside a SQL expression, like a SELECT statement. A procedure doesn't have to return anything and is built to perform an action, such as updating a table or sending output through OUT parameters.

Candidates who mix these up usually haven't written enough packages to feel the difference in practice.

What are PL/SQL packages and why use them?

Packages bundle related procedures, functions, variables, and cursors into a single schema object with two parts: a specification that defines what's public, and a body that holds the implementation. Developers reach for packages because they group logic that belongs together, hide implementation details from callers, and let Oracle cache the whole package in memory after the first call, which speeds up repeated execution. They also support overloading, so you can have multiple procedures with the same name but different parameter lists.

What are the types of exceptions in PL/SQL?

Exception handling questions come up constantly because production code fails in ways tutorials never show. PL/SQL recognizes three categories:

• Predefined exceptions, like NO_DATA_FOUND or TOO_MANY_ROWS, which Oracle raises automatically for common errors.
• Non-predefined exceptions, internal Oracle errors that don't have a name until you assign one with PRAGMA EXCEPTION_INIT.
• User-defined exceptions, which you declare and raise explicitly for business rule violations that Oracle itself wouldn't catch.

A candidate who can name all three, with an example of each, is showing you they've actually debugged something rather than skimmed a cheat sheet.

How do you create a user-defined exception with PRAGMA EXCEPTION_INIT?

PRAGMA EXCEPTION_INIT lets you associate a named exception with a specific Oracle error number, so you can catch it by name instead of checking SQLCODE manually.

DECLARE
 e_fk_violation EXCEPTION;
 PRAGMA EXCEPTION_INIT(e_fk_violation, -2292);
BEGIN
 DELETE FROM departments WHERE department_id = 10;
EXCEPTION
 WHEN e_fk_violation THEN
 DBMS_OUTPUT.PUT_LINE('Cannot delete, related records exist.');
END;

This pattern is common when a candidate wants readable error handling for well-known Oracle error codes, like foreign key violations, without hardcoding numbers throughout the codebase.

What is the purpose of COMMIT, ROLLBACK, and SAVEPOINT?

Transaction control keeps data consistent when multiple statements need to succeed or fail together. COMMIT makes all changes since the last commit permanent. ROLLBACK undoes them entirely. SAVEPOINT marks a point inside a transaction you can roll back to without discarding everything that happened before it, which is useful in long batch processes where you don't want one bad record to wipe out an hour of valid work.

What causes a mutating table error and how do you avoid it?

Mutating table errors happen when a row-level trigger tries to query or modify the same table that's currently being changed by the triggering statement, and Oracle can't guarantee a consistent view of that data mid-operation. It typically shows up in triggers that run SELECT or DML against their own table.

A mutating table error means your trigger is trying to read a table Oracle hasn't finished writing to.

The common fix is to move the logic into a compound trigger or use a package-level collection to defer the check until after the statement finishes.

What are IN, OUT, and IN OUT parameters?

Parameters control how data moves between a calling program and a procedure or function. IN parameters pass a value into the block and can't be changed inside it. OUT parameters pass a value back out, ignoring whatever was passed in. IN OUT parameters do both, accepting an initial value and allowing the block to modify and return it.

How do you check whether an UPDATE statement affected any rows?

The SQL%ROWCOUNT attribute tells you exactly how many rows the last DML statement touched, right after it runs.

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 90;
IF SQL%ROWCOUNT = 0 THEN
 DBMS_OUTPUT.PUT_LINE('No rows updated.');
END IF;

This is a favorite scenario question because it tests whether a candidate validates their own work instead of assuming a statement did what it was supposed to.

3. SQL vs PL/SQL interview questions

Recruiters often blend SQL and PL/SQL interview questions together in the same round, because a developer who can't articulate the boundary between the two usually can't optimize either one. This section covers the comparison questions that trip up candidates who know syntax but haven't thought about why Oracle split the two languages apart in the first place, and it pairs well with our set of SQL questions for data analysts. Expect these right after the basics, testing whether someone understands the engine underneath the code they write every day.

What are the key differences between SQL and PL/SQL?

SQL is a declarative, set-based language: you describe the result you want, and the optimizer decides how to fetch it. PL/SQL is procedural, letting you write loops, conditionals, and error handling around that same SQL. The two work together constantly, but they solve different problems, and a candidate who can only describe one in terms of the other hasn't really internalized the distinction.

SQL tells the database what you want; PL/SQL tells it how to get there step by step.

What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?

A plain ROLLBACK undoes the entire current transaction back to its start, discarding every change since the last COMMIT. ROLLBACK TO SAVEPOINT only undoes changes made after a named savepoint, leaving earlier work in the transaction intact. Developers reach for the savepoint version inside batch jobs where one failed record shouldn't force a restart of the whole run, while a full rollback is the right call when the entire transaction is compromised and nothing in it should survive.

What is the difference between a mutating table and a constraining table?

Mutating and constraining table errors both come from Oracle protecting data consistency, but they trigger under different conditions. A mutating table is one currently being modified by the statement that fired the trigger, and a row-level trigger can't query or change it mid-operation. A constraining table is a table the trigger doesn't directly modify but is linked to through a foreign key or other constraint, and it might be locked or restricted depending on how that relationship is defined. Interviewers ask this pairing to see if a candidate actually understands why the error appears, not just the workaround for it.

What is the difference between DECODE and CASE?

Both DECODE and CASE compare a value against multiple conditions and return a result, but CASE is the newer, more flexible option. DECODE only handles simple equality checks and can't evaluate range conditions or complex logic. CASE supports full boolean expressions, including greater-than, less-than, and multi-condition logic, and it reads closer to standard procedural code.

SELECT employee_id,
 CASE
 WHEN salary > 10000 THEN 'High'
 WHEN salary > 5000 THEN 'Medium'
 ELSE 'Low'
 END AS salary_band
FROM employees;

Most Oracle shops have shifted to CASE for anything beyond a single equality check, so candidates who still default to DECODE for range logic are usually working from older habits picked up early in their career.

What is the difference between a unique index and a unique constraint?

Both prevent duplicate values in a column, but they exist for different reasons. A unique constraint is a logical rule enforced at the schema level to guarantee data integrity, and Oracle automatically creates a unique index behind it to enforce that rule efficiently. A unique index, created directly, exists purely for performance and duplicate prevention without necessarily being tied to a named constraint that shows up in data dictionary views the same way. Practically speaking, most developers create the constraint and let Oracle manage the index, only building a standalone unique index when they need finer control over storage or indexing options, which is exactly the kind of nuance that separates strong answers in oracle sql and plsql interview questions and answers from generic ones.

4. Advanced PL/SQL interview questions

Advanced rounds stop testing syntax and start testing judgment. By this point, interviewers assume you know what a cursor or a trigger is, and they want to see whether you can choose the right tool when performance, scale, or maintainability is on the line. This tier of pl/sql interview questions and answers is where senior Oracle developers and architects tend to separate themselves from mid-level candidates, because these questions usually come with a follow-up: "why not just do it the simple way?"

What is BULK COLLECT and how does it improve performance?

BULK COLLECT pulls an entire result set into a PL/SQL collection in one round trip instead of fetching row by row inside a loop. Each fetch inside a normal cursor loop means a separate context switch between the SQL engine and the PL/SQL engine, and those switches add up fast once you're processing tens of thousands of rows. BULK COLLECT eliminates most of that overhead by batching the fetch, which is why it's the default answer whenever someone asks how to speed up a slow row-by-row loop.

DECLARE
 TYPE t_names IS TABLE OF employees.first_name%TYPE;
 v_names t_names;
BEGIN
 SELECT first_name BULK COLLECT INTO v_names FROM employees WHERE department_id = 50;
END;

What is the difference between BULK COLLECT and FORALL?

Candidates often lump these together, but they solve opposite halves of the same problem. BULK COLLECT brings data into PL/SQL in bulk for a SELECT. FORALL sends data out to the database in bulk for INSERT, UPDATE, or DELETE statements, replacing a loop that would otherwise fire one DML statement per row.

Combining both in the same batch job is a common pattern: BULK COLLECT reads a large set, some procedural logic transforms it, and FORALL writes it back out, all with a fraction of the context switches a naive loop would need.

What is dynamic SQL and how do you use EXECUTE IMMEDIATE?

Dynamic SQL lets you build and run a SQL statement as a string at runtime, which matters when the table name, column list, or WHERE clause isn't known until the code executes. EXECUTE IMMEDIATE is the most common way to run it in modern PL/SQL.

DECLARE
 v_table VARCHAR2(30) := 'employees';
 v_count NUMBER;
BEGIN
 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table INTO v_count;
 DBMS_OUTPUT.PUT_LINE(v_count);
END;

Good candidates flag the risk immediately: concatenating raw strings opens the door to SQL injection, so bind variables should replace string concatenation wherever user input touches the statement.

What is a REF CURSOR and when should you use one?

A REF CURSOR is a pointer to a result set that isn't tied to a fixed query at compile time, which makes it the standard way to return a result set from a stored procedure back to a calling application like Java or .NET. Unlike a regular cursor, a REF CURSOR can be passed as a parameter, letting one procedure open it and another consume it. It's especially useful when the same procedure needs to return different shapes of data depending on runtime conditions, something a fixed cursor definition can't do.

What is a compound trigger and how does it prevent mutating table errors?

A compound trigger combines multiple timing sections, BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT, into a single trigger body that shares state across all of them. Instead of querying the table mid-row (which triggers the mutating table error covered earlier), you collect the row data you need during the row-level sections and run your validation or aggregation logic once, safely, in the AFTER STATEMENT section after Oracle has finished writing.

A compound trigger works because it waits to touch the table until the statement is done changing it.

This pattern shows up constantly in oracle pl sql interview questions and answers because it's the cleanest fix available in versions after Oracle 11g, replacing older workarounds involving temporary tables or package-level collections.

What are PRAGMA directives like EXCEPTION_INIT and RESULT_CACHE?

PRAGMA is a compiler directive, an instruction to the PL/SQL engine rather than executable logic itself. You've already seen PRAGMA EXCEPTION_INIT, which maps a named exception to an Oracle error number. PRAGMA RESULT_CACHE tells Oracle to cache the return value of a function based on its input parameters, so repeated calls with the same inputs skip re-execution entirely and pull from memory instead. It's a strong fit for lookup functions that get called thousands of times with a small set of recurring inputs, like currency conversion or status-code translation, where recalculating the same answer over and over wastes cycles for no benefit.

5. Scenario-based PL/SQL interview questions

Scenario questions show up in senior and architect-level interviews because they test judgment under conditions a textbook never covers: production load, live traffic, and code that has to keep running while data keeps changing. Recruiters use these pl sql interview questions and answers to see whether a candidate can reason through tradeoffs out loud, not just recite a definition. If you're building a technical interview checklist for a lead developer role, this is the section that actually predicts on-the-job performance, and these pre-screening questions with sample answers cover the round that comes before it.

How would you design a trigger for a high-concurrency system?

Under heavy concurrent load, a trigger's biggest risk is contention, not logic errors. A row-level trigger that runs a query against a busy table on every single insert can quickly become the bottleneck for the whole system, especially if it locks rows other sessions need. The strongest answer keeps trigger logic minimal, defers anything expensive like aggregation or cross-table validation to a compound trigger's AFTER STATEMENT section, and avoids raising exceptions mid-row when a batched check at the end would do the same job with far less locking.

The fewer locks your trigger holds and the shorter it holds them, the less it costs everyone else running against that table.

Interviewers listen for whether a candidate mentions autonomous transactions carefully, since they can hide contention rather than fix it, and for whether they'd reach for a queue-based design, such as Advanced Queuing or a simple staging table processed asynchronously, instead of doing everything inline inside the trigger.

How do you process large transactions in manageable batches?

Processing millions of rows in a single transaction risks long lock durations, a bloated undo segment, and a rollback that takes as long as the original job if anything fails partway through. The standard fix is batching: commit every few thousand rows instead of committing once at the end, using a counter or ROWNUM-based loop combined with BULK COLLECT and LIMIT to control batch size.

DECLARE
 CURSOR c_emp IS SELECT employee_id FROM employees;
 TYPE t_ids IS TABLE OF employees.employee_id%TYPE;
 v_ids t_ids;
BEGIN
 OPEN c_emp;
 LOOP
 FETCH c_emp BULK COLLECT INTO v_ids LIMIT 5000;
 FORALL i IN 1..v_ids.COUNT
 UPDATE employees SET last_reviewed = SYSDATE WHERE employee_id = v_ids(i);
 COMMIT;
 EXIT WHEN c_emp%NOTFOUND;
 END LOOP;
 CLOSE c_emp;
END;

Batching this way also gives you a natural restart point if the job fails halfway through, which matters far more in practice than shaving a few seconds off total runtime.

How do you prevent and handle deadlocks in PL/SQL?

Deadlocks happen when two sessions each hold a lock the other one needs, and Oracle resolves them by killing one session and raising ORA-00060 in whichever transaction it picks. Preventing them matters more than handling them, and the most reliable prevention is consistent lock ordering, meaning every process that updates multiple tables or rows does so in the same sequence every time, so two sessions never approach the same resources from opposite directions.

When a deadlock does happen, the losing session should catch the specific error and retry the transaction after a short pause rather than failing silently.

BEGIN
 UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
 UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 IF SQLCODE = -60 THEN
 ROLLBACK;
 DBMS_OUTPUT.PUT_LINE('Deadlock detected, retry needed.');
 END IF;
END;

Candidates who mention keeping transactions short and avoiding unnecessary locks inside loops are showing real production experience, not just theory pulled from a manual.

How do you organize complex business logic into reusable packages?

Sprawling logic scattered across dozens of standalone procedures becomes unmaintainable fast, which is why most serious Oracle shops organize code by domain instead of by task. A payroll package might hold every procedure related to salary calculation, while a separate package handles leave accrual, each with its own private helper functions hidden from the specification.

Group related logic using a pattern like this:

• One package per business domain (payroll, orders, inventory)
• Public specification exposing only what callers need
• Private helper procedures in the body, invisible outside the package
• Shared package-level variables for session-level state, used sparingly
• Consistent naming so related packages are easy to find

This structure keeps oracle pl sql interview questions and answers about maintainability grounded in something concrete: a candidate who can describe how they'd split a messy 2,000-line procedure into a clean package is showing the kind of design sense recruiters can't easily test with a syntax quiz.

Next steps for your PL/SQL interview prep

Thirty questions won't cover every curveball an interviewer throws, but they cover the ones that show up again and again: cursors, exceptions, packages, bulk operations, and the judgment calls that separate a mid-level developer from a senior one. Practice explaining these out loud, not just reading them silently, because interviews reward clarity under pressure more than memorized definitions.

If you're on the hiring side, screening for this depth manually across dozens of resumes eats a full week you don't have. Recruiters who need to move faster typically want a pre-vetted shortlist instead of another stack of unverified resumes to sort through. Olibr's shared database already holds verified Oracle and SQL developer profiles with real skill data attached, so you can skip the guessing game entirely. Shortlist Oracle developers from 180,000+ verified profiles who've already proven they know this material, not just claimed it.

For engineers

Find work worth your time.

Live engineering roles across India and the US, matched to your stack. Build a profile recruiters actually discover.

Browse jobsHow it works