PL SQL Real Time Interview Questions and Answers 2026

Share
PL SQL Real Time Interview Questions and Answers 2026

Most PL/SQL interview failures are not caused by unknown syntax. They come from half-answers on classic topics: the implicit commit in TRUNCATE, the hidden cost of row-by-row processing, and the mutating table error. That gap is older than most candidates in the room. Oracle's database concepts documentation states that the first version of PL/SQL was introduced with Oracle Database Version 6, and an Oracle technical overview places PL/SQL 1.0 in 1991 with the Oracle 6.0 data server, when IF THEN logic arrived because SQL did not provide it. Oracle7, released in 1992, added stored procedures and triggers so business rules could live next to the data.

Interviewers still use that split. SQL describes the set operation. PL/SQL adds variables, branching, loops, subprograms, and structured exception handling around those statements. A junior answer stops at "PL/SQL is procedural." A senior answer says why the logic belongs in the database at all.

The eight topics below come up again and again for PL/SQL real time interview questions and answers. Each one has a direct answer, a production-shaped example, and the follow-up that separates memorized syntax from judgment. Treat them as connected themes. Transaction boundaries, row processing, and error handling leak into every later question.

1. DELETE vs TRUNCATE vs DROP

The trap is treating these three as three ways to "remove data." They are not the same class of statement, and the follow-up is almost always "what happens to the transaction?"

DELETE is DML. It can take a WHERE clause, fires row-level triggers, and can be rolled back until you commit. The table, indexes, and grants stay. TRUNCATE is DDL. It removes every row, cannot filter with WHERE, resets the high-water mark, and performs an implicit commit. DROP is DDL that removes the table definition, its data, its indexes, and dependent privileges.

Say a nightly job must purge expired session rows but keep anything still active.

DELETE FROM app_sessions
 WHERE expires_at < SYSDATE;
, still inside your transaction until COMMIT

A staging table that is rebuilt before each ETL load is a different problem. TRUNCATE is the fast empty, and you cannot undo it with ROLLBACK because the implicit commit already happened.

A comparison chart explaining the differences between DELETE, TRUNCATE, and DROP commands in PL/SQL database management.

Wrong answer I hear constantly: "TRUNCATE is just a faster DELETE." Faster is true. Recoverable is not. If any of those rows might need to come back, DELETE with a WHERE clause is the safe statement. If the interviewer asks about triggers, DELETE can fire them and TRUNCATE does not behave like row-by-row DML.

Practical rule: Name the implicit commit out loud before you praise TRUNCATE for speed.

If you want to hear how that answer lands under a follow-up, rehearse it once in a PL/SQL mock interview rather than only reading it.

2. Explicit vs Implicit Cursors

Oracle opens an implicit cursor for single-row DML and for SELECT INTO. You read it through SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT. You do not OPEN or CLOSE it yourself. An explicit cursor is one you declare, open, fetch, and close, and you need it when the query can return more than one row.

A focused man wearing glasses works on a laptop in a bright home office environment.

The follow-up trap is "so explicit cursors are always better." They are not. A cursor FOR loop opens, fetches, and closes for you, which removes a whole class of leaked-cursor bugs. I reach for a manual loop only when I must stop early, skip fetches, or pass parameters and inspect attributes between rows.

A bonus applied to one department is a FOR loop job:

FOR emp IN (
  SELECT employee_id, salary
    FROM employees
   WHERE department_id = p_dept
) LOOP
  UPDATE employees
     SET salary = emp.salary * 1.05
   WHERE employee_id = emp.employee_id;
END LOOP;

Open invoices for one customer fit a parameterized explicit cursor: OPEN c_inv(p_customer_id), then FETCH until you are done.

In a hand-written loop, check %NOTFOUND after every FETCH. Skip that check and you process one extra empty row, which is how duplicate audit lines and null-key updates show up in production. The senior sentence is this: prefer the FOR loop unless you need control of each fetch, and never leave an explicit cursor open on an exception path.

3. Exception Handling in PL/SQL

Handlers live in the EXCEPTION section, not in comments around the SQL. Predefined names you should say without looking them up include NO_DATA_FOUND (ORA-01403), TOO_MANY_ROWS (ORA-01422), ZERO_DIVIDE, and DUP_VAL_ON_INDEX. User-defined exceptions are declared, then fired with RAISE. RAISE_APPLICATION_ERROR returns a custom code from -20000 to -20999 plus a message the caller can catch.

A focused developer working on code in a clean home office setting while preparing for interviews.

Two answers that sound the same and are not

A lookup that may miss is a default, not a crash:

BEGIN
  SELECT credit_limit INTO v_limit
    FROM customers WHERE customer_id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_limit := 0;
END;

A negative order total is a business rule, so raise your own error instead of returning a quiet zero. The interviewer then asks about WHEN OTHERS. Log SQLCODE, SQLERRM, and the error backtrace, then re-raise with a bare RAISE so the original error number survives. Swallowing the exception makes the block "succeed" while the data is wrong.

The depth question is why a cursor FOR loop that returns zero rows does not raise NO_DATA_FOUND. SELECT INTO expects one row. A loop expects a set, and an empty set is a valid set. If you treat those the same, your "not found" handling fires in the wrong place.

4. Stored Procedures vs Functions

A procedure performs an action. It is called as a statement and may pass values back through OUT or IN OUT parameters. A function must RETURN one value and can sit inside a SQL expression, including a SELECT list or a WHERE clause. Callers expect that function to be free of side effects.

A funds transfer is a procedure. It debits one account, credits another, and writes an audit row. Those are actions, and OUT parameters can return the new balances. A full-name formatter is a function, because a report query needs a value, not a transaction.

The follow-up that catches juniors is "can I call it from SQL?" A function invoked from a query should not perform DML. Do that and you are in ORA-14552 territory. Procedures are the right home for changes. Functions are the right home for calculations the optimizer may call more than once.

  • Side effects: A function that inserts a log row will surprise you the moment someone puts it in a WHERE clause.
  • DETERMINISTIC: Add that keyword only when the result depends only on the inputs. A function that reads SYSDATE or a table is not deterministic, and lying to the compiler is worse than omitting the keyword.
  • Return shape: One value means a function. Several values, or no value, means a procedure with parameters.

Wrong answer: "Functions can't have exception handlers." They can. What they should not have, when SQL calls them, is DML.

5. PL/SQL Packages

A package has a specification and an optional body. The specification lists the public surface: procedures, functions, types, constants, and cursors. The body holds the implementation plus anything private. Callers compile against the specification, which is why you can fix a bug in the body and leave every dependent untouched.

A software developer working at a desk, viewing code structure for reusable logic on a computer monitor.

What the interviewer is really testing

An order package exposes place_order and cancel_order. Validation, tax lookup, and sequence handling stay private in the body. A date utility exposes two format_date functions with different parameter lists. That overloading only works cleanly inside a package.

Package state is the trap. A package variable lives for the session and survives from one call to the next. A local variable in a procedure dies when the call ends. Juniors describe packages as "folders for procedures." Seniors say the specification is the contract, the body is the implementation, and a package variable is session memory, not a safer global you can ignore.

Keep the specification stable. Change the body when you fix bugs. If you alter a public signature, you recompile callers, and that is how a "small" change becomes an outage window. Also say why packages help performance: Oracle can load the package into memory once for the session instead of resolving a pile of standalone units on every call.

6. BULK COLLECT and FORALL for Performance

Row-by-row PL/SQL is where senior interviews get honest. Each SQL statement inside a loop is another context switch between the PL/SQL engine and the SQL engine. Oracle's bulk SQL documentation describes FORALL as sending DML from PL/SQL to SQL in batches, and BULK COLLECT as retrieving query results in batches. The same guidance tells you to benchmark before you optimize, and it points FORALL at INSERT, UPDATE, and DELETE while pointing BULK COLLECT at multi-row retrieval.

FORALL does not loop in PL/SQL and then call SQL. It hands the batch to SQL in one go. BULK COLLECT without a bound is how PGA gets eaten. In production I always write a limit, in the spirit of FETCH ... BULK COLLECT INTO ... LIMIT 500, or batches of 1000 when the rows are narrow. Load a staging table in those chunks. Update thousands of balances with one FORALL, not thousands of single-row UPDATEs.

Oracle's FORALL reference is the follow-up. Omit SAVE EXCEPTIONS and an unhandled error in one DML operation stops the statement and rolls back changes made by earlier DML in that FORALL. With SAVE EXCEPTIONS, execution continues, failures land in SQL%BULK_EXCEPTIONS, and Oracle raises one aggregate exception, ORA-24381, after the statement finishes. The LIMIT clause also restricts how many rows and how much collection you process.

Use one set-based SQL statement first. Use FORALL only when procedural logic is unavoidable. Then compare elapsed time, rows, and memory on representative data. A "faster" batch that changes commit behavior is not a free win.

Watch a short walkthrough of the batch pattern before you try to recite it from memory.

7. Triggers and the Mutating Table Error

Oracle's subprogram guidance draws the line interviewers want: the database invokes a trigger when its event occurs, while a procedure must be called. You can disable a trigger, and a disabled trigger does not fire. That sounds basic until the follow-up lands: who controls execution, the caller or the event?

Row-level triggers use FOR EACH ROW and see :NEW and :OLD. Statement-level triggers run once per statement. An AFTER ROW trigger writing each salary change to an audit table is a fair use. A BEFORE STATEMENT trigger rejecting DML outside business hours is another. Keep both short. Complex rules belong in a procedure the trigger calls, because hidden logic in triggers is where side effects and debugging pain multiply.

ORA-04091, the mutating table error, is the question behind the question. It appears when a row-level trigger queries or changes the same table that fired it. Describe the failure before offering a fix. The weak answer is to reach for pragma autonomous_transaction as a magic escape. The stronger answer is a compound trigger: collect keys in the row section, then query or apply the rule in a later section, when the table is no longer in the middle of the row operation.

Oracle's autonomous transaction notes give the boundary. An autonomous transaction commits or rolls back independently of the caller. If it is still open when the routine exits, Oracle raises an exception, and an unhandled exception rolls that autonomous work back. A trigger must be autonomous to run transaction control or DDL, and DDL from it must use native dynamic SQL. Use that for a log that must survive the caller's rollback, never as a shortcut around ORA-04091.

The junior answer names the error. The senior answer says when each section runs, why autonomous transactions are the wrong default, and closes cleanly: triggers observe events, procedures own business logic.

8. Collections and Record Types in PL/SQL

PL/SQL keeps lists in memory as three collection types, and interviewers want the trade-off, not the glossary.

  • Associative arrays: INDEX BY tables. They can be sparse and indexed by integers or strings. They are the right cache for a batch job, such as country codes looked up once and reused. They are not stored as a column.
  • Nested tables: Must be initialized with a constructor. They can be dense enough for SQL operations and can be stored in database columns.
  • VARRAYs: A fixed maximum size. Use them when the business rule really is "no more than N elements," not because the name sounds like an array.

Records group related fields. Anchored declarations are the part juniors skip. %TYPE ties a variable to a column. %ROWTYPE ties a record to a whole row, so a fetch of employees into an emp_rec keeps compiling when someone widens a VARCHAR2.

BULK COLLECT and FORALL work with collections, not with a pile of scalars. If you cannot say which collection you would bulk-fetch into, the performance answer from earlier collapses. Associative arrays and nested tables are the usual targets in application code. A VARRAY's fixed ceiling is a feature only when the ceiling is real.

Wrong answer: "collections and tables are the same." A collection is session memory with a type and an index. A table is durable storage with a high-water mark, locks, and a transaction. The one sentence that sounds senior is that %TYPE and %ROWTYPE make the code follow the schema, while the collection type decides whether the list can be sparse, stored, or capped.

8 PL/SQL Interview Topics at a Glance

Topic Core Skill Tested Common Follow-Up Trap Senior-Level Add-On
DELETE vs TRUNCATE vs DROP Distinguishing row deletion, table clearing, and object removal Forgetting that TRUNCATE implicitly commits and cannot be rolled back Compare transaction behavior, trigger effects, and storage impact
Explicit vs Implicit Cursors Knowing when Oracle creates a cursor versus managing one explicitly Missing the %NOTFOUND check after a manual fetch Use cursor FOR loops or parameterized cursors; prefer set-based SQL when possible
Exception Handling in PL/SQL Handling predefined, user-defined, and Oracle errors Swallowing errors in WHEN OTHERS without re-raising Log SQLERRM and the error backtrace, then re-raise; distinguish zero-row cursor loops from NO_DATA_FOUND
Stored Procedures vs Functions Choosing between action-oriented procedures and value-returning functions Overlooking SQL restrictions on functions that perform DML Consider context-switch costs and use DETERMINISTIC only when output depends solely on inputs
PL/SQL Packages Separating a package's public specification from its implementation Changing the specification unnecessarily and invalidating dependent objects Account for session-persistent package state, especially with connection pooling
BULK COLLECT and FORALL for Performance Reducing context switches with bulk fetching and DML Using unbounded BULK COLLECT and exhausting PGA memory Use LIMIT and handle per-row failures with SAVE EXCEPTIONS and SQL%BULK_EXCEPTIONS
Triggers and the Mutating Table Error Understanding DML trigger timing, scope, and row values Querying or modifying the triggering table inside a row-level trigger Use compound triggers or move complex logic into procedures; keep trigger behavior traceable
Collections and Record Types in PL/SQL Choosing collection types and anchoring variables to schema definitions Choosing a collection type that complicates SQL integration Match collection types to BULK COLLECT and FORALL; use %TYPE and %ROWTYPE for schema resilience

From Knowing the Answers to Delivering Them Under Pressure

Knowing these eight topics cold is necessary and still not sufficient. Interviews reward people who answer crisply, in order, and then survive the follow-up. The implicit commit, the empty cursor loop, WHEN OTHERS that hides the error, a function that does DML, a package variable that leaks across calls, an unbounded BULK COLLECT, a row trigger that queries its own table, and a collection picked because the name was familiar. Those are one conversation, not eight flashcards.

I would rehearse out loud, not by rereading notes. ParakeetAI's Mock Interview lets you practice against an AI interviewer that talks back, and every account gets one free mock. Its Question Bank shows questions real candidates were asked, company by company, ranked by how often they come up. Pick the weakest item above and rebuild the answer around the trap in that item: what rolls back, what does not fire, and what changes memory or locks. That depth is what separates candidates when the syntax is a tie.

Say the decision before the syntax. DELETE when rows must be recoverable, TRUNCATE when the table is scratch and you accept the commit, DROP when the object itself should disappear. A cursor FOR loop unless you need the fetch. Log and re-raise. Procedures for change, functions for values. Packages for a stable contract. Set-based SQL first, then bounded bulk DML. Short triggers. Anchored types. If you can defend those choices with one example each, you are ready for the round that calls itself PL/SQL real time interview questions and answers.


ParakeetAI is an interview assistant for Oracle and other technical rounds. It listens to the question and shows suggested answers from your resume and notes, and it can help on coding questions with code suggestions, without joining the call. Practice the eight topics above, then open ParakeetAI when you want that support on the next PL/SQL screen.

Read more