Preparing for a PL/SQL Interview Requires Knowing More Than Just Syntax
Most candidates walk into PL/SQL interviews armed with basic CRUD operations and a vague understanding of cursors. That is not enough. I have sat on both sides of the table, asking questions and watching people struggle with concepts they should already understand. The difference between a surface-level answer and one that actually demonstrates competence usually comes down to knowing how the database engine behaves under real conditions. Let us start with something that seems simple but trips up a surprising number of people. Can you explain the difference between %TYPE and %ROWTYPE in PL/SQL? %TYPE declares a variable with the same data type as a specified table column. %ROWTYPE declares a record that represents a row from a table or view. Here is the practical distinction most guides miss. %TYPE binds at compile time to a single column definition. If that column changes later, your code updates automatically. %ROWTYPE gives you a whole row structure but pulls in every column, which can be a performance concern when you only need two fields from a fifty-column table.
I remember a production incident where someone used %ROWTYPE in a high-volume loop processing millions of rows. They were pulling back columns they never referenced. The context switching between the PGA and UGA was adding measurable latency. Switching to explicit %TYPE declarations for only the needed columns cut the runtime by roughly forty percent. It is a small change that most interviewers do not expect you to volunteer unless you have actually debugged something like this. Another question that comes up constantly involves exceptions. What is the difference between user-defined exceptions and predefined exceptions, and how do you handle them? Predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX are built into Oracle. You can reference them by name without declaring them. User-defined exceptions require a DECLARE section and a RAISE statement. The practical thing nobody emphasizes is exception propagation. When you declare a custom exception inside a sub-block, it does not automatically propagate to the outer block unless you handle it or let it bubble up through the exception handler. I once spent three hours tracking down an error that was being silently swallowed because a nested BEGIN...END block had its own exception handler that caught the exception and did nothing with it. The outer code never saw it. Checking your exception flow path is critical before you consider any PL/SQL block complete.
Advanced Topics That Separate Junior Candidates From Seniors
Collections and bulk operations are where PL/SQL interviews often shift direction. If you only know scalar variables and basic loops, you will struggle here. What is BULK COLLECT and when should you use it? BULK COLLECT retrieves multiple rows from a query into a collection in a single context switch rather than fetching row by row. The context switch between SQL and PL/SQL is expensive. Reducing the number of switches dramatically improves performance on large datasets. However, bulk collect loads everything into memory. If you run BULK COLLECT on a table with millions of rows without a LIMIT clause, you can exhaust your PGA memory and crash the session. Always pair BULK COLLECT with FETCH...BULK COLLECT...LIMIT in a loop. A limit of one thousand to five thousand rows per fetch is a reasonable starting point depending on your memory constraints. Here is a counter-intuitive point about collections. Nested tables and varrays behave differently when you try to update them after bulk fetching. Nested tables can be sparsely populated. Varrays must be dense. If you delete elements from a nested table, you get gaps in the index. Deleting elements from a varray after it has been populated will raise an error because varrays do not support element deletion after population. I learned this the hard way during a data migration project where I assumed I could trim a collection in place. It took a failed production run and an ORA-22160 error to figure out what went wrong.
Get the Full Details
Dynamic SQL Is Both Powerful and Dangerous
DBMS_SQL versus EXECUTE IMMEDIATE is another topic that separates people who have written dynamic SQL in anger from those who have only read about it. EXECUTE IMMEDIATE is simpler and sufficient for most use cases where you know the query structure at compile time or can construct it with bind variables. DBMS_SQL is necessary when the number of columns is not known until runtime or when you are building queries with a variable number of elements. I used DBMS_SQL once for a reporting tool where users could select any combination of columns from any table. The query structure was completely unknown until runtime. EXECUTE IMMEDIATE would have required an impossibly complex set of conditional branches. DBMS_SQL handled it cleanly through dynamic binding and column descriptor arrays. The danger with dynamic SQL is SQL injection. Bind variables solve this for most cases, but sometimes people concatenate strings into their queries instead of using binds. This is a career-limiting move. If an interviewer asks about dynamic SQL and you mention string concatenation without immediately following up with bind variables, they will note it. There is no excuse for constructing dynamic queries through string concatenation when the database gives you a direct mechanism to prevent injection.
Triggers Have Limitations You Need to Understand
Many candidates describe triggers as useful tools without understanding their constraints. Row-level triggers fire once per affected row. Statement-level triggers fire once per statement. The practical implication is that a row-level trigger executing a query against the same table it is defined on will raise a mutating table error. This happens because the row is currently being modified and Oracle cannot provide a consistent snapshot. The workaround involves compound triggers, which were introduced in Oracle 11g. A compound trigger lets you have both statement-level and row-level sections in a single trigger definition. You can collect data in the row-level section and process it in the statement-level section, avoiding the mutating table problem entirely. I implemented a compound trigger for an audit logging system that needed to track individual row changes and then generate a summary log entry after the entire statement completed. Before compound triggers, this required a two-trigger workaround with a temporary table, which was slower and harder to maintain.
Package State Is a Real Problem in Production
PL/SQL packages maintain state between calls within a session. This is convenient until it is not. If you store data in package variables and two concurrent sessions call the same package, they share the database but not the package state. Each session gets its own copy of the package state. This is usually fine. But if you are using package-level variables for caching or as a semaphore between procedures, you can get confused about why data from one session appears in another. It does not. Each session has its own isolated package state. If your code assumes shared state, it will produce incorrect results under concurrent load. I encountered this during a batch processing system where a package was used to track job progress across multiple procedures. The package variables reset between calls because different parts of the workflow were occasionally executed in different session contexts, particularly when connection pooling was involved. The fix was to move the state tracking into a database table instead of relying on package variables. Tables persist across all session types. Package variables do not, and assuming they do is a common source of subtle bugs.
Performance Tuning Within PL/SQL Requires a Different Mindset
Writing correct PL/SQL and writing fast PL/SQL are different skills. The most common performance mistake I see is row-by-row processing when a set-based operation would work. Looping through a cursor and updating one row at a time is slow. Oracle is optimized for set operations. An UPDATE statement with a subquery or a MERGE statement will almost always outperform a cursor loop with individual DML statements. That said, there are cases where loops are the right choice. When you need complex procedural logic that cannot be expressed in pure SQL, or when you are processing records conditionally based on values that require function calls with side effects, a loop is appropriate. The key is recognizing when you are using a loop out of habit rather than necessity. If your loop body contains a SELECT or DML statement, ask yourself whether a single set-based statement could replace the entire loop. The answer is often yes. Another performance consideration is the use of PRAGMA RESTRICT_REFERENCES. This pragma controls what a function can do in terms of reading and writing database state. In older Oracle versions, it was essential for ensuring functions could be called from SQL statements. In modern Oracle, it is mostly obsolete, but some legacy codebases still rely on it. Understanding when it matters and when it does not will save you from chasing phantom compilation errors.
Debugging PL/SQL Without Breaking Everything
Debugging in PL/SQL is less straightforward than in application languages. DBMS_OUTPUT.PUT_LINE is the default tool, but it has a buffer size limit. If you flood it with output, you will get errors or lose data. Setting SERVEROUTPUT to a large value like 1000000 helps, but it still buffers everything in memory. For heavy debugging sessions, I write to a temporary logging table instead. It is faster to query later and does not hit buffer limits. A simple procedure that inserts timestamp, procedure name, and message into a debug_log table handles most cases without cluttering the output stream. UTL_FILE is useful for writing debug output to flat files when table writes are not an option, but it requires directory object privileges and proper file handling. Many junior developers try to use UTL_FILE without realizing the Oracle user needs CREATE ANY DIRECTORY or the directory object must be explicitly granted. This causes a PRAGMA exception that is easy to miss if you are not checking your exception stack properly. Understanding PL/SQL well enough for an interview means going past the syntax and knowing how the engine actually executes your code. The questions that matter most are the ones that reveal whether you have dealt with real failures, not whether you can recite documentation. Performance, concurrency, exception handling, and memory management are where the real learning happens, and where candidates either impress or fall apart.
