PL/SQL Interview Questions: Answers by Level and Topic

If you write Oracle PL/SQL for a living, the interview rarely tests whether you can recite syntax. It tests whether you understand what the engine does — when a cursor closes, why an exception propagates the way it does, what a package spec buys you over a bare procedure. This guide gives you the questions that come up most, grouped by difficulty and by topic, with short answers you can actually say out loud. Where a code snippet clears things up faster than a paragraph, you get the snippet.

Every answer here lines up with Oracle's own PL/SQL Language Reference, so you are not memorizing folklore. Use these to check your own understanding, not to parrot back word for word — interviewers can tell the difference.

Beginner: blocks, variables, and control flow

These come early and set the tone. Get them crisp.

What is PL/SQL, and how does it differ from SQL?

PL/SQL is Oracle's procedural extension to SQL. SQL is declarative — you say what you want, the engine decides how. PL/SQL adds variables, loops, conditionals, and error handling so you can wrap procedural logic around SQL statements and run it inside the database. As Wikipedia's PL/SQL entry puts it, it is "Oracle Corporation's procedural extension for SQL."

Describe the structure of a PL/SQL block.

Every block has up to three parts: a declarative section (DECLARE, optional), an executable section (BEGIN ... END, required), and an exception-handling section (EXCEPTION, optional).

DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees;
  DBMS_OUTPUT.PUT_LINE('Rows: ' || v_count);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No rows.');
END;

What is an anonymous block?

A block with no name that is not stored in the database. It compiles and runs once, then it is gone. Named units — procedures, functions, packages, triggers — are stored schema objects you can call again.

Difference between `%TYPE` and `%ROWTYPE`?

%TYPE declares a variable that inherits the datatype of a single column or another variable. %ROWTYPE declares a record whose fields match every column in a row or cursor. Both keep your code in sync when the table definition changes.

v_name   employees.last_name%TYPE;   -- one column's type
v_emp    employees%ROWTYPE;          -- a full row

Intermediate: cursors, exceptions, and subprograms

This is where most interviews spend their time. It is also where sloppy answers show up.

What is a cursor, and what are the two kinds?

A cursor is a pointer to the private SQL area that holds a statement and its result. Per Oracle's cursor documentation, there are two: implicit cursors, which PL/SQL creates and manages automatically for every SQL statement, and explicit cursors, which you declare and control yourself with OPEN, FETCH, and CLOSE. Use an explicit cursor when you need to process multiple rows one at a time.

Name the cursor attributes.

%ISOPEN, %FOUND, %NOTFOUND, and %ROWCOUNT. For an implicit cursor you reference them as SQL%...; note that SQL%ISOPEN is always FALSE, because an implicit cursor closes the moment its statement finishes.

When should you prefer a cursor FOR loop?

When you are simply iterating over every row. The FOR loop opens, fetches, and closes the cursor for you, so you cannot 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;

Predefined versus user-defined exceptions?

Oracle's error handling reference splits exceptions into two families. Predefined exceptions (such as NO_DATA_FOUND and ZERO_DIVIDE) are named for common runtime errors and need no declaration. User-defined exceptions are ones you declare for business rules and raise yourself with RAISE.

DECLARE
  e_negative EXCEPTION;
  v_balance  NUMBER := -5;
BEGIN
  IF v_balance < 0 THEN
    RAISE e_negative;
  END IF;
EXCEPTION
  WHEN e_negative THEN
    DBMS_OUTPUT.PUT_LINE('Balance cannot be negative.');
END;

What do `SQLCODE` and `SQLERRM` return?

SQLCODE returns the numeric error code of the current exception; SQLERRM returns the matching message text. You use them in a WHEN OTHERS handler to log what actually went wrong instead of swallowing it silently.

How does a procedure differ from a function?

A function must return a value with a RETURN clause and can be used inside a SQL expression; a procedure performs an action and returns nothing (though it can pass data back through OUT parameters). Oracle's subprograms reference covers both, along with parameter modes IN, OUT, and IN OUT.

Interviewers often chain these into a broader loop about design and testing — the same terrain covered in our technical interview questions guide.

Topic cheat sheet: what each question is really testing

Interviewers pick questions to probe a specific skill. Knowing the intent behind each one helps you answer the right thing.

Advanced: triggers, packages, and performance

Senior rounds go here. Depth matters more than coverage.

What is a trigger, and what types exist?

A trigger is a named PL/SQL unit that fires automatically when a defined event occurs. Oracle's trigger documentation describes DML triggers (on INSERT, UPDATE, DELETE) and system triggers (on schema or database events). DML triggers can be statement-level (fire once per statement) or row-level (fire once per affected row, with access to :NEW and :OLD values).

Why use a package instead of standalone subprograms?

A package groups related procedures, functions, cursors, types, and variables under one namespace. The spec declares the public interface; the body holds the implementation. That gives you encapsulation, easier versioning, and better performance — changing the body does not invalidate code that depends only on the spec. Package state also persists for the length of a session.

Explain `BULK COLLECT` and `FORALL`.

Every switch between the PL/SQL engine and the SQL engine costs a context switch. A row-by-row loop pays that cost on every iteration. BULK COLLECT fetches many rows into a collection in one round trip, and FORALL sends many DML statements to the SQL engine at once. On large data sets the difference is dramatic.

DECLARE
  TYPE id_tab IS TABLE OF employees.employee_id%TYPE;
  v_ids id_tab;
BEGIN
  SELECT employee_id BULK COLLECT INTO v_ids FROM employees;
  FORALL i IN 1 .. v_ids.COUNT
    UPDATE employees SET salary = salary * 1.05
    WHERE employee_id = v_ids(i);
END;

What is a mutating table error?

It happens when a row-level trigger tries to query or modify the same table that fired it, while that table is mid-change. The classic fixes are to move the logic to a statement-level trigger, use a compound trigger, or defer the work — this is a favorite senior-level trap, so name the cause and at least one fix.

How do you find where PL/SQL time is going?

Use DBMS_PROFILER or the hierarchical DBMS_HPROF profiler to see which lines and subprograms dominate. For SQL inside your code, check the execution plan and look for full scans, missing bind variables, or excessive context switching. Optimization questions like this overlap with broader system design interview questions, so be ready to reason about the whole data path, not just one query.

Get in front of the person who decides

Here is the part candidates forget. Nailing every question above gets you through the technical rounds — but only after you land the interview. And the fastest route to landing one is not the application portal. It is reaching the hiring manager or a database engineer on the team directly.

That is what Articuler is built for. Semantic search across 980M+ profiles finds the specific person hiring for the role. A Playbook helps you prep for that individual interviewer, and AI-assisted outreach lands replies at 40-60% versus the 5-8% you get from cold applications. If you want to prep for a named interviewer, the AI meeting prep tooling turns a profile into a briefing before you walk in. Study the technical answers, then use the find the right people search to make sure a human actually reads your name.

If your target is a broader data role, pair this with our data engineer interview questions guide, which covers pipelines and modeling alongside SQL depth.

FAQ

How many PL/SQL interview questions should I prepare for?

Cover the core topics — blocks, cursors, exceptions, subprograms, triggers, packages, and performance — with two or three solid answers each. Depth on those beats a shallow list of fifty. Interviewers dig into whatever you claim to know, so prepare to defend your answers rather than just deliver them.

Do I need to memorize exact syntax?

No. You need the concepts right and the ability to write clean, working code at a whiteboard or terminal. Knowing why BULK COLLECT is faster matters more than remembering every clause. Small syntax slips are usually forgiven; a wrong mental model is not.

What is the single most common PL/SQL interview mistake?

Wrapping everything in a bare WHEN OTHERS that swallows errors without logging SQLCODE and SQLERRM. It hides bugs in production, and experienced interviewers treat it as a red flag. Always log or re-raise.

How is a PL/SQL interview different from a general SQL interview?

A SQL interview focuses on querying and set logic. A PL/SQL interview adds procedural concerns: cursor management, exception propagation, trigger design, and package structure. Expect questions about performance and the SQL-to-PL/SQL context switch, which pure SQL roles rarely raise.