PL/SQL Interview Questions

 

Basic Level

1. What is PL/SQL, and how does it differ from SQL?
   Answer: PL/SQL (Procedural Language/Structured Query Language) extends SQL by adding procedural programming features like loops, conditions, and exception handling. While SQL is used for querying and manipulating data, PL/SQL is used for writing complex business logic and automating tasks within Oracle databases.

2. What are the advantages of using PL/SQL?
   Answer: Some advantages include:

   • Support for procedural programming (loops, conditions, etc.).
   • Better performance by reducing network traffic using blocks of code.
   • Robust error handling through exceptions.
   • Reusability via stored procedures and functions.

3. What is a PL/SQL block, and what are its components?
   Answer: A PL/SQL block is a unit of execution in PL/SQL. It consists of:

   • Declare: For defining variables, cursors, etc.
   • Begin: Contains the executable statements.
   • Exception: Handles errors.
   • End: Marks the end of the block.

4. How do you handle exceptions in PL/SQL?
   Answer: Exceptions are handled using the EXCEPTION block. Example:

   BEGIN
       -- Code
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
           -- Handle exception
       WHEN OTHERS THEN
           -- Handle other exceptions
   END;
   

5. What is the difference between a function and a procedure in PL/SQL?
   Answer: The main difference:

   • A function returns a single value and is used in SQL statements.
   • A procedure performs an action but does not return a value directly.

---

Intermediate Level

6. Explain the concept of cursors in PL/SQL and their types (implicit vs. explicit).
   Answer: Cursors are used to retrieve rows from a query.

   • Implicit cursors: Automatically created for single-row queries.
   • Explicit cursors: Defined by programmers for handling multiple rows.

7. What is a trigger in PL/SQL? Can you provide an example?
   Answer: A trigger is a stored program that automatically executes on specified events (e.g., INSERT, UPDATE). Example:

   CREATE OR REPLACE TRIGGER my_trigger
   AFTER INSERT ON my_table
   FOR EACH ROW
   BEGIN
       -- Logic here
   END;
   

8. How do packages work in PL/SQL? Why are they useful?
   Answer: Packages group related procedures, functions, and cursors. They help in modularizing code, improving reusability, and reducing dependencies.

9. What are collections in PL/SQL? Explain the difference between VARRAY, nested tables, and associative arrays.
   Answer: Collections allow grouping multiple elements of the same type.

   • VARRAY: Fixed-size arrays.
   • Nested tables: Dynamic arrays stored in the database.
   • Associative arrays: Key-value pairs.

10. How does dynamic SQL work in PL/SQL, and when would you use it?
    Answer: Dynamic SQL allows constructing and executing SQL statements at runtime using EXECUTE IMMEDIATE or DBMS_SQL. Useful for creating flexible, runtime-specific queries.

---

Advanced Level

11. How would you optimize the performance of PL/SQL code?
    Answer: Techniques include:

    • Using bulk binds (FORALL, BULK COLLECT).
    • Avoiding unnecessary loops.
    • Using efficient SQL queries.
    • Minimizing context switching between SQL and PL/SQL.

12. What are the differences between autonomous transactions and regular transactions in PL/SQL?
    Answer: Autonomous transactions are independent of the main transaction. They commit/rollback changes without affecting the parent transaction.

13. How do you debug and test PL/SQL code?
    Answer: Debugging tools include:

    • Using DBMS_OUTPUT.PUT_LINE for printing debug information.
    • Tools like Oracle SQL Developer's debugger.
    • Writing unit tests for procedures/functions.

14. What is a mutating table error, and how do you resolve it?
    Answer: It occurs when a trigger attempts to query or modify the table that caused the trigger. Resolve it by:

    • Using temporary tables.
    • Avoiding direct access within triggers.

15. Can you explain the difference between IN, OUT, and IN OUT parameters in PL/SQL?
    Answer:

    • IN: Input-only parameter (default).
    • OUT: Output-only parameter (returns a value).
    • IN OUT: Both input and output.

In PL/SQL, bulk binds are a performance optimization technique that allows you to move data between PL/SQL and the SQL engine in batches, instead of one row at a time. This significantly reduces context switching between SQL and PL/SQL engines, which is a common performance bottleneck.

Here are the 3 key concepts around bulk binds:

---

1. BULK COLLECT

Used for: Efficiently fetching multiple rows from a query into PL/SQL collections.

Instead of fetching one row at a time in a loop, BULK COLLECT fetches all rows at once into a collection (like a VARRAY, TABLE, or ASSOCIATIVE ARRAY).

Example:

DECLARE
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  names NameList;
BEGIN
  SELECT last_name BULK COLLECT INTO names
  FROM employees
  WHERE department_id = 10;

  FOR i IN names.FIRST .. names.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(names(i));
  END LOOP;
END;

---

2. FORALL

Used for: Sending batches of DML operations (INSERT, UPDATE, DELETE) to the SQL engine.

Instead of executing a DML statement inside a loop, FORALL allows sending the entire collection in one go.

Example:

DECLARE
  TYPE IdList IS TABLE OF employees.employee_id%TYPE;
  emp_ids IdList := IdList(101, 102, 103);
BEGIN
  FORALL i IN emp_ids.FIRST .. emp_ids.LAST
    DELETE FROM employees WHERE employee_id = emp_ids(i);
END;

---

3. Combining BULK COLLECT and FORALL

To maximize performance, you often BULK COLLECT data from the database into a collection, process it, and then FORALL it back for DML.

Example: Updating salaries for employees in a department

DECLARE
  TYPE IdList IS TABLE OF employees.employee_id%TYPE;
  TYPE SalList IS TABLE OF employees.salary%TYPE;

  emp_ids IdList;
  emp_sals SalList;
BEGIN
  SELECT employee_id, salary
  BULK COLLECT INTO emp_ids, emp_sals
  FROM employees
  WHERE department_id = 20;

  FOR i IN emp_sals.FIRST .. emp_sals.LAST LOOP
    emp_sals(i) := emp_sals(i) * 1.1; -- 10% raise
  END LOOP;

  FORALL i IN emp_ids.FIRST .. emp_ids.LAST
    UPDATE employees
    SET salary = emp_sals(i)
    WHERE employee_id = emp_ids(i);
END;

---

Summary

Concept	Purpose	Benefits
BULK COLLECT	Fetch multiple rows into PL/SQL	Reduces context switches
FORALL	Perform batch DML from PL/SQL	Improves DML performance
Combined	End-to-end efficient data handling	Scales better for large data sets

Let me know if you want performance comparison examples or exception handling with these!