Forum

Forums
Oracle
Interview Questions...
Oracle PL/SQL Real-...
 
Share:
Notifications
Clear all

Oracle PL/SQL Real-Time Scenario Questions — From HCL, Wipro & Capgemini (2025 Edition)

 
Interview Questions & Answers
Last Post by SQL Admin 4 months ago
1 Posts
1 Users
0 Reactions
388 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Nov 09, 2025 5:48 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

If you’re preparing for Oracle PL/SQL interviews at HCL, Wipro, or Capgemini, expect real-world problem-solving questions, not just theoretical syntax.

This guide compiles actual PL/SQL scenario-based questions asked by top IT companies in 2025 — focusing on procedures, triggers, exception handling, packages, and business logic implementation.

These questions will help you showcase hands-on expertise and analytical thinking, two key hiring factors in enterprise-level Oracle roles.

🔹 Top Oracle PL/SQL Real-Time Scenario Questions (2025 Edition)

1️⃣ Scenario: Calculate Employee Bonus Based on Performance

Question:
Write a PL/SQL block that calculates bonus as 10% of salary for employees with rating 'A', 5% for 'B', and 2% for others.

 
DECLARE
v_bonus NUMBER;
v_rating CHAR(1);
BEGIN
SELECT rating INTO v_rating FROM employees WHERE emp_id = 101;

IF v_rating = 'A' THEN
v_bonus := 0.1;
ELSIF v_rating = 'B' THEN
v_bonus := 0.05;
ELSE
v_bonus := 0.02;
END IF;

UPDATE employees SET bonus = salary * v_bonus WHERE emp_id = 101;
COMMIT;
END;

✅ Frequently asked in Wipro developer interviews.

2️⃣ Scenario: Log Every Employee Record Update Using a Trigger

Question:
Write a trigger to store old and new values whenever an employee’s salary changes.

 
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log(emp_id, old_salary, new_salary, changed_on)
VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

✅ Commonly asked in Capgemini support role interviews.

3️⃣ Scenario: Handle Divide-by-Zero Error in PL/SQL

Question:
Write a block to divide two numbers safely.

 
DECLARE
a NUMBER := 100;
b NUMBER := 0;
result NUMBER;
BEGIN
result := a / b;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero');
END;

📌 Tests your understanding of exception handling — a must-know for HCL interviews.

4️⃣ Scenario: Create a Package for Employee Operations

Question:
Write a PL/SQL package with procedures to add and fetch employee details.

 
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE add_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER);
PROCEDURE get_employee(p_id NUMBER);
END emp_pkg;
 
CREATE OR REPLACE PACKAGE BODY emp_pkg AS
PROCEDURE add_employee(p_id NUMBER, p_name VARCHAR2, p_salary NUMBER) IS
BEGIN
INSERT INTO employees VALUES(p_id, p_name, p_salary);
COMMIT;
END;

PROCEDURE get_employee(p_id NUMBER) IS
v_name employees.name%TYPE;
BEGIN
SELECT name INTO v_name FROM employees WHERE emp_id = p_id;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END;
END emp_pkg;

✅ Demonstrates modular coding — essential for enterprise Oracle projects.

5️⃣ Scenario: Identify Employees With Salary > Department Average

Question:
Write a PL/SQL block to list employees earning above their department’s average salary.

 
BEGIN
FOR rec IN (
SELECT e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > (
SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id
)
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.name || ' - ' || rec.department_name);
END LOOP;
END;

✅ Used in HCL’s real-time SQL-to-PL/SQL conversion tests.

6️⃣ Scenario: Create a Trigger to Prevent Negative Salary Entry

Question:
Prevent inserting or updating an employee with negative salary.

 
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
END IF;
END;

📌 Tests business rule enforcement logic.

7️⃣ Scenario: Use Cursor to Display Employee Data

 
DECLARE
CURSOR emp_cur IS SELECT emp_name, salary FROM employees WHERE department_id = 10;
v_name employees.emp_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_name, v_salary;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
END LOOP;
CLOSE emp_cur;
END;

✅ Commonly used to test basic cursor handling in Wipro interviews.

💡 Pro Tip:

When explaining PL/SQL scenario answers, always include:

  • The business case (why it’s used)

  • Code logic (how it solves the problem)

  • Optimization (e.g., using bulk operations or packages)

That combination helps you stand out as a real-world problem solver.

🧩 Conclusion

These Oracle PL/SQL Real-Time Scenario Questions (2025 Edition) are among the most frequently asked in HCL, Wipro, and Capgemini interviews.

They test your ability to translate business rules into efficient PL/SQL code, not just syntax recall.
Focus on mastering packages, triggers, exception handling, and logic flow — that’s where top candidates shine.


Topic Tags
Oracle PL/SQL Real-Time S Wipro PL/SQL Interview Q HCL Oracle Developer Int Capgemini PL/SQL Coding PL/SQL Triggers and Pack

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  Oracle PL/SQL Real-Time S (1) , Wipro PL/SQL Interview Q (1) , HCL Oracle Developer Int (1) , Capgemini PL/SQL Coding (1) , PL/SQL Triggers and Pack (1) ,
Share: