DEV Community

Pranav Bakare
Pranav Bakare

Posted on

PL/SQL Developer Interview Questions with 3 years of experience

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.

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay