PL/SQL Developer with 3 years of experience | Interview Questions | Part 1

For a PL/SQL Developer with 3 years of experience, interview questions typically cover a range of basic, intermediate, and advanced topics. Here are the top 10 questions commonly asked, along with explanations and example answers.

---

1. What is PL/SQL and How Does it Differ from SQL?

Explanation: SQL is a language for querying databases and performing operations such as SELECT, INSERT, UPDATE, and DELETE. PL/SQL, which stands for Procedural Language/SQL, is Oracle's procedural extension to SQL. It includes programming constructs like loops, conditions, and exception handling, making it more powerful for complex operations.

Example:

DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || v_count);
END;

---

1. Explain Cursors in PL/SQL. What Types of Cursors Are There?

Explanation: Cursors are pointers to a result set, allowing row-by-row processing. Cursors can be Implicit (automatically created by Oracle for SELECT statements that return one row) or Explicit (defined by the programmer for multiple-row queries).

Example of Explicit Cursor:

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

---

1. What is the Purpose of a Trigger, and How is it Used in PL/SQL?

Explanation: A trigger is a stored procedure that executes automatically in response to certain events (e.g., INSERT, UPDATE, or DELETE) on a table. Triggers are often used for logging, enforcing rules, or validating data.

Example:

CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.hire_date := SYSDATE;
END;

---

1. Explain the Difference Between Procedures and Functions in PL/SQL.

Explanation: Both are subprograms in PL/SQL, but Procedures perform actions and don’t return values directly, while Functions must return a single value. Functions are typically used in queries, while procedures are used for executing tasks.

Example of a Function:

CREATE OR REPLACE FUNCTION get_employee_count RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;

Example of a Procedure:

CREATE OR REPLACE PROCEDURE increase_salary(p_emp_id IN NUMBER, p_amount IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_emp_id;
END;

---

1. What is Exception Handling in PL/SQL? Provide Examples of Predefined Exceptions.

Explanation: Exception handling in PL/SQL is used to manage errors gracefully. Predefined exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and ZERO_DIVIDE help handle specific issues that may arise during execution.

Example:

BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one employee found.');
END;

---

1. Explain PL/SQL Collections and the Different Types Available.

Explanation: Collections in PL/SQL allow the storage of multiple elements. The three main types are:

Associative Arrays (Index-By tables): Key-value pairs, similar to dictionaries.

Nested Tables: Unordered sets of elements, allowing duplicates.

VARRAYs (Variable-size Arrays): Fixed-size arrays.

Example of Associative Array:

DECLARE
TYPE EmpArray IS TABLE OF employees.first_name%TYPE INDEX BY PLS_INTEGER;
v_emps EmpArray;
BEGIN
v_emps(1) := 'John';
v_emps(2) := 'Jane';
DBMS_OUTPUT.PUT_LINE(v_emps(1) || ', ' || v_emps(2));
END;

---

1. How Do You Optimize PL/SQL Code?

Explanation: Optimizing PL/SQL code involves several practices:

Using BULK COLLECT and FORALL for bulk operations to reduce context switches between PL/SQL and SQL engines.

Avoiding unnecessary computations and SQL statements inside loops.

Using NOCOPY with OUT/IN OUT parameters to improve performance in procedures.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
emp_ids NumList;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees;
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_ids(i);
END;

---

1. Explain the Use of %TYPE and %ROWTYPE Attributes in PL/SQL.

Explanation: %TYPE and %ROWTYPE are used to declare variables that automatically adopt the datatype of a table column or the entire row structure of a table.

%TYPE is used for single column types.

%ROWTYPE is used for a whole row structure.

Example:

DECLARE
v_emp_id employees.employee_id%TYPE;
v_emp_record employees%ROWTYPE;
BEGIN
SELECT employee_id INTO v_emp_id FROM employees WHERE first_name = 'John';
SELECT * INTO v_emp_record FROM employees WHERE employee_id = v_emp_id;
END;

---

1. What are Packages in PL/SQL and Their Advantages?

Explanation: A package is a collection of PL/SQL objects like procedures, functions, variables, and types grouped together. Packages promote modularization, encapsulate logic, and allow for easier management and security.

Example:

CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER);
FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER;
END emp_pkg;

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE update_salary(p_emp_id NUMBER, p_amount NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_emp_id;
END;

FUNCTION get_employee_salary(p_emp_id NUMBER) RETURN NUMBER IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;
RETURN v_salary;
END;
END emp_pkg;

---

1. Describe the Use of Bulk Collect and Forall in PL/SQL.

Explanation: BULK COLLECT fetches multiple rows at once into a collection, while FORALL allows performing DML operations on collections, both reducing context switching between PL/SQL and SQL.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
emp_ids NumList;
salaries NumList;
BEGIN
SELECT employee_id, salary BULK COLLECT INTO emp_ids, salaries FROM employees;
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp_ids(i);
END;

These questions and examples test a candidate’s understanding of core PL/SQL concepts, performance optimization techniques, and experience with programming structures and error handling essential for PL/SQL development. Practicing these will strengthen one’s technical skills and enhance interview readiness.