20 PL/SQL Interview Questions and Answers

PL/SQL, or Procedural Language extensions to SQL, is a powerful tool for managing and manipulating Oracle databases. It combines the data manipulation power of SQL with the procedural capabilities of programming languages, making it an essential skill for database developers and administrators. PL/SQL allows for the creation of complex scripts, stored procedures, and triggers, enhancing the efficiency and functionality of database operations.

This article offers a curated selection of PL/SQL interview questions designed to test and expand your knowledge. By working through these questions, you will gain a deeper understanding of PL/SQL concepts and be better prepared to demonstrate your expertise in a professional setting.

PL/SQL Interview Questions and Answers

1. How do you declare and initialize variables?

In PL/SQL, variables are declared in the declaration section of a block, subprogram, or package. The syntax is:

variable_name datatype [NOT NULL] [:= value];

Example:

DECLARE
    v_employee_id NUMBER(6);
    v_employee_name VARCHAR2(50) := 'John Doe';
    v_salary NUMBER(8,2) NOT NULL := 50000.00;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;

2. How would you implement an IF-THEN-ELSE statement?

The IF-THEN-ELSE statement executes code conditionally. It performs actions based on whether a condition is true or false.

Example:

DECLARE
    v_number NUMBER := 10;
BEGIN
    IF v_number > 0 THEN
        DBMS_OUTPUT.PUT_LINE('The number is positive.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('The number is not positive.');
    END IF;
END;

3. Describe how to use a FOR loop.

A FOR loop iterates over a range of values or a cursor. It simplifies writing loops by automatically handling the loop variable and iteration process.

Numeric FOR Loop Example:

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
    END LOOP;
END;

Cursor FOR Loop Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name FROM employees;
BEGIN
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' || emp_record.first_name);
    END LOOP;
END;

4. How do you handle exceptions?

Exceptions in PL/SQL are handled using the EXCEPTION block. This block catches runtime errors, allowing the program to continue or fail gracefully. There are predefined and user-defined exceptions.

Example:

DECLARE
    v_number NUMBER;
    e_custom_exception EXCEPTION;
BEGIN
    SELECT some_column INTO v_number FROM some_table WHERE some_condition;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many rows found.');
    WHEN e_custom_exception THEN
        DBMS_OUTPUT.PUT_LINE('Custom exception occurred.');
END;

5. How do you declare and use an explicit cursor?

An explicit cursor is defined and controlled by the programmer, allowing individual row processing.

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name FROM employees;
    emp_record emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_record;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
    END LOOP;
    CLOSE emp_cursor;
END;

6. Explain the difference between implicit and explicit cursors.

Cursors handle query results. Implicit cursors are automatically created by Oracle for single-row queries, while explicit cursors are manually managed for multi-row queries.

7. How would you use BULK COLLECT to fetch multiple rows?

BULK COLLECT fetches multiple rows into a collection in a single context switch, improving performance.

Example:

DECLARE
    TYPE emp_table_type IS TABLE OF employees%ROWTYPE;
    emp_table emp_table_type;
BEGIN
    SELECT * BULK COLLECT INTO emp_table FROM employees WHERE department_id = 10;
    
    FOR i IN emp_table.FIRST .. emp_table.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_table(i).employee_id);
    END LOOP;
END;
/

8. How do you create and use a package?

A package groups related PL/SQL types, items, and subprograms. It has a specification and a body.

Example:

-- Package Specification
CREATE OR REPLACE PACKAGE my_package IS
  PROCEDURE my_procedure;
  FUNCTION my_function (p_param IN NUMBER) RETURN NUMBER;
END my_package;
/

-- Package Body
CREATE OR REPLACE PACKAGE BODY my_package IS
  PROCEDURE my_procedure IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello from my_procedure');
  END my_procedure;

  FUNCTION my_function (p_param IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_param * 2;
  END my_function;
END my_package;
/

To use the package:

BEGIN
  my_package.my_procedure;
  DBMS_OUTPUT.PUT_LINE(my_package.my_function(5));
END;
/

9. Describe how to execute a dynamic SQL statement.

Dynamic SQL executes SQL statements constructed at runtime, useful when the SQL statement depends on runtime conditions.

Example using EXECUTE IMMEDIATE:

DECLARE
    sql_stmt VARCHAR2(200);
    table_name VARCHAR2(50) := 'employees';
BEGIN
    sql_stmt := 'SELECT COUNT(*) FROM ' || table_name;
    EXECUTE IMMEDIATE sql_stmt INTO v_count;
    DBMS_OUTPUT.PUT_LINE('Number of rows in ' || table_name || ': ' || v_count);
END;

10. How do you handle user-defined exceptions?

User-defined exceptions are custom error conditions you define and handle in your PL/SQL code.

Example:

DECLARE
    insufficient_funds EXCEPTION;
    current_balance NUMBER := 500;
    withdraw_amount NUMBER := 600;
BEGIN
    IF withdraw_amount > current_balance THEN
        RAISE insufficient_funds;
    END IF;
    current_balance := current_balance - withdraw_amount;
    DBMS_OUTPUT.PUT_LINE('Withdrawal successful. Remaining balance: ' || current_balance);
EXCEPTION
    WHEN insufficient_funds THEN
        DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds for withdrawal.');
END;

11. What is an autonomous transaction and how do you implement it?

An autonomous transaction is independent of the main transaction, allowing SQL operations to commit or roll back independently.

Example:

CREATE OR REPLACE PROCEDURE log_error(p_message IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (message, log_date) VALUES (p_message, SYSDATE);
  COMMIT;
END;

12. Describe how to create and use a pipelined table function.

A pipelined table function returns a set of rows iteratively, allowing processing as they are produced.

Example:

CREATE TYPE emp_record AS OBJECT (
    emp_id NUMBER,
    emp_name VARCHAR2(100)
);

CREATE TYPE emp_table AS TABLE OF emp_record;

CREATE OR REPLACE FUNCTION get_employees RETURN emp_table PIPELINED IS
BEGIN
    FOR rec IN (SELECT emp_id, emp_name FROM employees) LOOP
        PIPE ROW(emp_record(rec.emp_id, rec.emp_name));
    END LOOP;
    RETURN;
END;

To use the function:

SELECT * FROM TABLE(get_employees);

13. How do you refresh a materialized view?

Materialized views store the result of a query physically and can be refreshed to reflect changes in the underlying data.

Example:

-- Complete Refresh
BEGIN
   DBMS_MVIEW.REFRESH('materialized_view_name', 'C');
END;

-- Fast Refresh
BEGIN
   DBMS_MVIEW.REFRESH('materialized_view_name', 'F');
END;

-- Force Refresh
BEGIN
   DBMS_MVIEW.REFRESH('materialized_view_name', 'FORCE');
END;

14. How do you declare and use nested tables?

Nested tables are collections that can store an unbounded number of elements and can be sparse.

Example:

CREATE OR REPLACE TYPE NumberTable AS TABLE OF NUMBER;

DECLARE
    my_numbers NumberTable := NumberTable();
BEGIN
    my_numbers.EXTEND;
    my_numbers(1) := 10;
    my_numbers.EXTEND;
    my_numbers(2) := 20;

    FOR i IN 1..my_numbers.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_numbers(i));
    END LOOP;
END;

15. Describe how to create and use an object type.

An object type is a user-defined composite datatype that encapsulates a data structure with functions and procedures.

Example:

CREATE TYPE Person AS OBJECT (
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    age NUMBER,
    MEMBER FUNCTION get_full_name RETURN VARCHAR2
);

CREATE TYPE BODY Person AS
    MEMBER FUNCTION get_full_name RETURN VARCHAR2 IS
    BEGIN
        RETURN first_name || ' ' || last_name;
    END;
END;
/

CREATE TABLE people OF Person;

INSERT INTO people VALUES (Person('John', 'Doe', 30));

DECLARE
    p Person;
BEGIN
    SELECT VALUE(p) INTO p FROM people p WHERE p.first_name = 'John';
    DBMS_OUTPUT.PUT_LINE(p.get_full_name);
END;
/

16. How do you use a REF CURSOR?

A REF CURSOR is a data type that allows defining a cursor that can be opened and associated with different query statements at runtime.

Example:

DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    my_cursor ref_cursor_type;
    my_record my_table%ROWTYPE;
BEGIN
    OPEN my_cursor FOR SELECT * FROM my_table WHERE column1 = 'value';
    
    LOOP
        FETCH my_cursor INTO my_record;
        EXIT WHEN my_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(my_record.column1 || ' ' || my_record.column2);
    END LOOP;
    
    CLOSE my_cursor;
END;

17. Explain the concept of mutating tables and how to avoid them.

Mutating tables occur when a trigger attempts to query or modify a table that is already undergoing a change due to the triggering statement. To avoid this, use compound triggers, temporary tables, or autonomous transactions.

Example:

CREATE OR REPLACE TRIGGER trg_before_update
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO temp_table (emp_id, old_salary, new_salary)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary);
    COMMIT;
END;

18. Describe the difference between IN, OUT, and IN OUT parameters.

Parameters in PL/SQL are used to pass values to and from stored procedures and functions. There are three types: IN, OUT, and IN OUT.

Example:

CREATE OR REPLACE PROCEDURE example_procedure (
    p_in IN NUMBER,
    p_out OUT NUMBER,
    p_inout IN OUT NUMBER
) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('IN parameter: ' || p_in);
    p_out := p_in * 2;
    p_inout := p_inout + p_in;
END;

19. How do you implement transaction control?

Transaction control in PL/SQL ensures data integrity and consistency. The primary statements are COMMIT, ROLLBACK, and SAVEPOINT.

Example:

BEGIN
    INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 50000);
    SAVEPOINT savepoint1;

    UPDATE employees SET salary = 55000 WHERE id = 1;
    SAVEPOINT savepoint2;

    DELETE FROM employees WHERE id = 1;

    ROLLBACK TO savepoint2;

    COMMIT;
END;

20. Explain the concept of pragma directives and provide examples.

Pragma directives in PL/SQL are special instructions to the compiler that affect code compilation. Common directives include PRAGMA EXCEPTION_INIT, PRAGMA AUTONOMOUS_TRANSACTION, and PRAGMA SERIALLY_REUSABLE.

Example:

-- PRAGMA EXCEPTION_INIT example
DECLARE
  e_custom_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_custom_exception, -20001);
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
EXCEPTION
  WHEN e_custom_exception THEN
    DBMS_OUTPUT.PUT_LINE('Caught custom exception');
END;
/

-- PRAGMA AUTONOMOUS_TRANSACTION example
CREATE OR REPLACE PROCEDURE log_error(p_message IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (message, log_date) VALUES (p_message, SYSDATE);
  COMMIT;
END;
/