Top 20 PL/SQL Interview Questions and Answers in 2025

PL/SQL, which is the Oracle’s procedural language extension of SQL, is something you have to know if you are going to be working with Oracle databases. This is true for major roles such as database administrator, developer, and data analyst, all of whom need to handle complex business logic within the database.

I believe interviewers assess candidates on three key aspects:

• Conceptual Understanding: Core principles, data types, control structures, and exception handling.
• Practical Coding Ability: Writing efficient stored procedures, functions, triggers, and packages.
• Performance Considerations: Optimizing PL/SQL code to improve execution time, minimize resource consumption, and avoid mistakes such as excessive context switching between SQL and PL/SQL.

To help, I am providing a guide on PL/SQL interview questions. I will begin with the fundamental concepts and move forward to more advanced topics like bulk processing, dynamic SQL, and performance tuning.

If you want to understand the foundational concepts of the Oracle database, I recommend taking our Introduction to Oracle SQL course, which is a really important resource to learn how to interact with the Oracle database and how the database uses PL/SQL to process queries.

Beginner PL/SQL Interview Questions

In the initial interview phase, the interviewer can ask foundational questions to assess your knowledge of basic database and PL/SQL concepts. Try studying these questions and answer to prepare for the initial phase of the interview.

1. What is PL/SQL?

PL/SQL is Oracle’s procedural extension to SQL. Unlike SQL, which is declarative and focuses on data retrieval and manipulation, PL/SQL allows developers to implement procedural logic, making it a powerful tool for writing complex business rules within the database. It supports variables, loops, conditionals, exception handling, and modular programming through procedures, functions, and packages.

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

A PL/SQL block is the fundamental unit of execution in PL/SQL, and it consists of four main sections:

• DECLARE (Optional): Used to define variables, constants, cursors, and user-defined types.

• BEGIN: The executable section where SQL queries and procedural statements are written.

• EXCEPTION (Optional): Handles runtime errors and exceptions to ensure graceful error recovery.

• END;: Marks the end of the block.

3. What are the essential PL/SQL data types?

PL/SQL supports various data types, categorized as follows:

• Scalar Types: Single-value types like NUMBER, VARCHAR2, DATE, BOOLEAN.

• Composite Types: Collections such as RECORD (custom structures) and TABLE/VARRAY (arrays).

• Reference Types: Pointers to database objects, like REF CURSOR for dynamic query processing.

4. What are the basic control structures in PL/SQL?

PL/SQL includes several control structures that help manage the flow of a program:

• Loops: These include LOOP, FOR LOOP, and WHILE LOOP, allowing repetitive execution of statements.

• Conditional Statements: These include IF and CASE statements, which execute different blocks of code based on conditions. The DECODE() function is another good example of a conditional that is worth studying. 

Intermediate PL/SQL Interview Questions

Having covered the basic questions, now let's move on to some intermediate-level data structure interview questions. After testing your basic knowledge, interviewers are going to now test your technical proficiency in implementing and using PL/SQL concepts.

5. What is the difference between stored procedures and functions?

Stored procedures and functions are both reusable PL/SQL code blocks, but they serve rather different purposes.

Stored procedures are used to perform operations that do not return a value, such as inserting, updating, or deleting data. They are used for tasks that modify data or perform complex operations without returning a result.

For example, the procedure below updates the salary of the employee with the given employee_id by adding the specified p_increment such that the salary updates dynamically based on input parameters,

CREATE PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
BEGIN  
    -- Update the salary of the employee with the given ID  
    UPDATE employees  
    SET salary = salary + p_increment  
    WHERE employee_id = p_emp_id;  
END;

Functions, on the other hand, return a value after performing operations. They are suitable for calculations or data retrieval that need to return a result.

The function below simplifies fetching an employee's salary, making it reusable in SQL queries or other procedures.

CREATE FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER AS  
    v_salary NUMBER;  
BEGIN  
    -- Retrieve the salary for the given employee ID  
    SELECT salary INTO v_salary  
    FROM employees  
    WHERE employee_id = p_emp_id;  
    
    -- Return the retrieved salary  
    RETURN v_salary;  
END;

6. What are PL/SQL packages?

PL/SQL packages are collections of related procedures, functions, and variables that encapsulate code for better organization and reusability. They consist of two parts:

• Package Specification: Declares public elements (procedures, functions, variables).
• Package Body: Contains the implementation details of the procedures and functions declared in the specification.

For example, the query below creates a package employee_pkg that defines a procedure to raise an employee's salary and a function to retrieve the total number of employees, with their implementations to be provided in the package body.

-- Create a package named 'employee_pkg'
CREATE PACKAGE employee_pkg AS  

    -- Procedure to increase an employee's salary by a percentage  
    PROCEDURE raise_salary(p_emp_id NUMBER, p_percent NUMBER);  

    -- Function to return the total number of employees  
    FUNCTION get_total_employees RETURN NUMBER;  

END employee_pkg;

7. What are PL/SQL triggers?

Triggers are PL/SQL blocks that execute automatically in response to specific database events, such as insertions, updates, or deletions. Triggers are used to enforce business rules. They also are commonly used to perform auditing. They are categorized into:

• Row-Level Triggers: Execute once for each affected row.
• Statement-Level Triggers: Execute once per SQL statement, regardless of the number of rows affected.

The query below creates an AFTER UPDATE trigger trg_salary_audit on the employees table that logs salary changes into the salary_audit table, capturing the employee ID, old and new salary, and the update timestamp.

-- Create or replace a trigger 'trg_salary_audit'
CREATE OR REPLACE TRIGGER trg_salary_audit  
AFTER UPDATE OF salary ON employees  -- Fires after salary updates in 'employees' table
FOR EACH ROW  -- Executes for each updated row
BEGIN  
    -- Inserts old and new salary details into 'salary_audit' table
    INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)  
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);  
END;

Study our tutorial on SQL Triggers so that you will be ready in the event that triggers are brought up in the interview. 

8. What are the methods of exception handling in PL/SQL?

PL/SQL provides error-handling mechanisms for stability and to prevent crashes. The types of exceptions include the following:

• Predefined Exceptions: Built-in exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE.

• User-Defined Exceptions: Custom exceptions are declared using EXCEPTION and raised using RAISE.

For example, the following PL/SQL block retrieves the salary of an employee with ID 100, raises a custom exception if the salary is below 1000, and handles possible errors, including missing employee records and unexpected exceptions.

DECLARE  
    v_salary NUMBER;  -- Variable to store the employee's salary  
    e_low_salary EXCEPTION;  -- Custom exception for low salary  

BEGIN  
    -- Retrieve salary of employee with ID 100  
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;  

    -- Raise exception if salary is below 1000  
    IF v_salary < 1000 THEN  
        RAISE e_low_salary;  
    END IF;  

EXCEPTION  
    WHEN NO_DATA_FOUND THEN  -- Handle case where employee ID is not found  
        DBMS_OUTPUT.PUT_LINE('Employee not found.');  
    WHEN e_low_salary THEN  -- Handle custom low salary exception  
        DBMS_OUTPUT.PUT_LINE('Salary is below the allowed threshold.');  
    WHEN OTHERS THEN  -- Handle any other unexpected errors  
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);  

END;

The best practices for exception handling include the following:

• Always anticipate potential exceptions.
• Use meaningful error messages.
• Log exceptions for auditing.
• Keep exception handling code concise and focused on recovery.

9. How can you verify whether an UPDATE statement is executed or not?

The SQL %NOTFOUND attribute can be used to determine whether or not the UPDATE statement successfully changed any records. If the last SQL statement run did not affect any rows, this variable returns TRUE.

For example, the query below updates the salary of employees in department 10 by increasing it by 10%, and then checks whether any rows were affected by the UPDATE statement using the %NOTFOUND attribute. If no rows were updated, it outputs a message saying, "No rows were updated." If rows were updated, it outputs the number of rows that were affected using the SQL%ROWCOUNT attribute.

DECLARE
    -- Declare a variable to store the number of rows updated
    rows_updated INTEGER;
BEGIN
    -- Perform an UPDATE statement on the 'employees' table
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 10;

    -- Check if any rows were updated by using %NOTFOUND
    IF SQL%NOTFOUND THEN
        -- If no rows were updated, print a message
        DBMS_OUTPUT.PUT_LINE('No rows were updated.');
    ELSE
        -- If rows were updated, print how many rows were affected
        rows_updated := SQL%ROWCOUNT;  -- Store the number of rows updated
        DBMS_OUTPUT.PUT_LINE(rows_updated || ' rows were updated.');
    END IF;
END;
/

Advanced PL/SQL Interview Questions

Let's now explore some advanced interview questions in case you are applying for a more senior role that requires more experience.

10. What are the methods of optimizing performance in PL/SQL?

Minimizing context switches between SQL and PL/SQL is crucial for optimizing performance. Each switch incurs overhead, which can slow down execution times, especially in situations involving frequent transitions between the two.

11. How do you use bulk operations to minimize context switches?

PL/SQL provides bulk processing techniques to optimize SQL-to-PL/SQL interaction by fetching or modifying multiple rows at once.

For example, the PL/SQL block below retrieves all employees from department 10 using BULK COLLECT into a collection and iterates through it to print each employee's name, improving performance by minimizing context switches between SQL and PL/SQL.

DECLARE  
    -- Define a table-type collection based on the 'employees' table structure  
    TYPE emp_table IS TABLE OF employees%ROWTYPE;  
    v_emps emp_table;  -- Declare a variable of this type  

BEGIN  
    -- Bulk fetch employees from department 10 into the collection  
    SELECT * BULK COLLECT INTO v_emps FROM employees WHERE department_id = 10;  

    -- Loop through the collection and print employee names  
    FOR i IN 1..v_emps.COUNT LOOP  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emps(i).employee_name);  
    END LOOP;  

END;

Also, this PL/SQL block uses FORALL for bulk updating, increasing the salary by 10% for employees with IDs 101, 102, and 103.

DECLARE  
    -- Define a table-type collection for employee IDs  
    TYPE t_emp_ids IS TABLE OF employees.employee_id%TYPE;  
    -- Initialize collection with specific employee IDs  
    v_emp_ids t_emp_ids := t_emp_ids(101, 102, 103);  

BEGIN  
    -- Bulk update salaries by 10% for specified employee IDs  
    FORALL i IN 1..v_emp_ids.COUNT  
        UPDATE employees SET salary = salary * 1.10 WHERE employee_id = v_emp_ids(i);  

END;

12. What are dynamic SQL and Ref Cursors?

Dynamic SQL allows executing SQL statements dynamically at runtime, which is useful when dealing with variable table names, columns, or query structures.

The following PL/SQL block uses dynamic SQL to count the number of rows in the employees table and prints the result. I like this kind of approach because it allows flexibility.

DECLARE  
    v_table_name VARCHAR2(50) := 'employees';  -- Store table name  
    v_count NUMBER;  -- Variable to hold row count  

BEGIN  
    -- Dynamically count rows in the specified table  
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;  

    -- Print the total count  
    DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);  

END;  

Ref Cursors are dynamic cursors that can be opened, fetched, and closed at runtime, enabling the passing of query results between program units.

The PL/SQL block below uses a REF CURSOR to fetch and print employee names from department 20. The cursor is opened dynamically, iterated through using a loop, and closed after processing.

DECLARE  
    -- Define a REF CURSOR type  
    TYPE emp_ref_cursor IS REF CURSOR;  
    v_cursor emp_ref_cursor;  -- Declare a cursor variable  
    v_name employees.employee_name%TYPE;  -- Variable to store employee name  

BEGIN  
    -- Open the cursor for employees in department 20  
    OPEN v_cursor FOR SELECT employee_name FROM employees WHERE department_id = 20;  

    -- Fetch and print employee names in a loop  
    LOOP  
        FETCH v_cursor INTO v_name;  
        EXIT WHEN v_cursor%NOTFOUND;  
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);  
    END LOOP;  

    -- Close the cursor  
    CLOSE v_cursor;  

END;

13. How do you handle mutating table errors?

Mutating table errors occur when a trigger attempts to modify the table it is triggered on. A compound trigger allows breaking trigger execution into multiple phases such as BEFORE, AFTER, and FOR EACH ROW to prevent direct modification issues.

The compound trigger below logs salary changes in the salary_audit table efficiently by collecting data before each row update and performing a bulk insert after the statement, reducing context switches and improving performance.

CREATE OR REPLACE TRIGGER trg_salary_audit  
FOR UPDATE OF salary ON employees  -- Trigger fires on salary updates  
COMPOUND TRIGGER  

    -- Declare an associative array to store audit records  
    TYPE t_salary_audit IS TABLE OF salary_audit%ROWTYPE INDEX BY PLS_INTEGER;  
    v_audit_data t_salary_audit;  
    v_idx PLS_INTEGER := 0;  

    -- Before updating each row, store old and new salary details  
    BEFORE EACH ROW IS  
    BEGIN  
        v_idx := v_idx + 1;  
        v_audit_data(v_idx).employee_id := :OLD.employee_id;  
        v_audit_data(v_idx).old_salary := :OLD.salary;  
        v_audit_data(v_idx).new_salary := :NEW.salary;  
        v_audit_data(v_idx).change_date := SYSDATE;  
    END BEFORE EACH ROW;  

    -- After the statement, insert all audit records in bulk  
    AFTER STATEMENT IS  
    BEGIN  
        FORALL i IN 1..v_idx  
            INSERT INTO salary_audit VALUES v_audit_data(i);  
    END AFTER STATEMENT;  

END trg_salary_audit;

14. What is overloading in PL/SQL?

Overloading allows multiple procedures or functions with the same name but different parameters to be defined within a package. This enhances code readability and maintainability by providing multiple ways to perform similar operations.

In the query below, the package body implements two overloaded procedures named update_salary: One increases an employee's salary by a specified amount, while the other sets a new salary with an effective date, updating the employees table accordingly.

-- Create the package specification
CREATE OR REPLACE PACKAGE emp_pkg AS  
    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER);

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE);
END emp_pkg;
/

-- Create the package body
CREATE OR REPLACE PACKAGE BODY emp_pkg AS  

    -- Procedure to increment salary by a specified amount  
    PROCEDURE update_salary(p_emp_id NUMBER, p_increment NUMBER) AS  
    BEGIN  
        UPDATE employees  
        SET salary = salary + p_increment  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

    -- Overloaded procedure to set a new salary with an effective date  
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER, p_effective_date DATE) AS  
    BEGIN  
        UPDATE employees  
        SET salary = p_new_salary, last_update = p_effective_date  
        WHERE employee_id = p_emp_id;  
    END update_salary;  

END emp_pkg;
/

15. What are the compiler directives and pragmas in PL/SQL?

PL/SQL provides compiler directives (PRAGMA) to optimize code and handle exceptions. The common pragmas include:

• PRAGMA EXCEPTION_INIT: Associates a user-defined exception with an Oracle error code.

• PRAGMA SERIALLY_REUSABLE: Optimizes package memory usage for scalability.

The PL/SQL block below handles the insertion of an employee with an invalid salary by using a custom exception e_invalid_salary mapped to error code -20001. If the exception is raised, it prints an error message.

DECLARE  
    e_invalid_salary EXCEPTION;  -- Declare custom exception for invalid salary  
    PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);  -- Associate exception with error code -20001  
BEGIN  
    -- Attempt to insert an employee with invalid salary  
    INSERT INTO employees (employee_id, salary) VALUES (999, -1000);  
EXCEPTION  
    -- Handle the custom exception and print a message  
    WHEN e_invalid_salary THEN  
        DBMS_OUTPUT.PUT_LINE('Invalid salary detected!');  
END;  

16. What are the different methods to trace and debug PL/SQL code?

PL/SQL provides several built-in packages for tracing and debugging code performance. Common methods include using DBMS_TRACE to track execution flow, DBMS_APPLICATION_INFO to monitor session activity, and DBMS_SESSION to gather session-level diagnostic information.

The query below first enables SQL tracing using DBMS_SESSION.set_sql_trace(TRUE), then sets client-specific session information with DBMS_APPLICATION_INFO.set_client_info() for monitoring. The PL/SQL block is executed, which simulates an UPDATE operation. Finally, it disables the tracing after the execution using DBMS_SESSION.set_sql_trace(FALSE).

-- Enable tracing for the current session using DBMS_SESSION
BEGIN
   -- Start session-level tracing
   DBMS_SESSION.set_sql_trace(TRUE);
END;
/

-- Set application information using DBMS_APPLICATION_INFO
BEGIN
   -- Set the application name and action for session monitoring
   DBMS_APPLICATION_INFO.set_client_info('Trace Debug Session');
   DBMS_APPLICATION_INFO.set_action('Debugging PL/SQL Code');
END;
/

-- Example PL/SQL block that simulates a process for debugging
DECLARE
   v_employee_id NUMBER := 100;
BEGIN
   -- Example query to fetch employee details
   FOR rec IN (SELECT first_name, last_name FROM employees WHERE employee_id = v_employee_id) LOOP
      DBMS_OUTPUT.put_line('Employee: ' || rec.first_name || ' ' || rec.last_name);
   END LOOP;
   
   -- Simulate some logic that could be traced
   IF v_employee_id = 100 THEN
      DBMS_OUTPUT.put_line('Employee ID is 100');
   END IF;
END;
/

-- Disable tracing after the session is complete
BEGIN
   -- Stop session-level tracing
   DBMS_SESSION.set_sql_trace(FALSE);
END;
/

Scenario-Based PL/SQL Interview Questions

In real-world enterprise applications, PL/SQL is used to handle complex business logic. Interviewers often assess a candidate’s ability to apply PL/SQL in practical scenarios. Below are some key challenges, along with strategies to solve them.

For this next and final section, if you are interviewing, I would encourage you to think about how to answer questions following this template, but try, of course, to re-orient your answer for your specific industry or to reflect your own experience. The same goes if you are interviewing a candidate and want ideas on how to ask a question and judge how thorough the answer is, in which case I hope this section serves as inspiration for both your questions and evaluation criteria.