Forum

Forums
PL SQL
Interview Questions...
PL/SQL Triggers, Pa...
 
Share:
Notifications
Clear all

PL/SQL Triggers, Packages & Exception Handling — Real-Time Scenario Questions Asked in TCS, HCL & Capgemini (2025 Edition)

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

If you’re preparing for PL/SQL interviews at TCS, HCL, or Capgemini, expect questions that go beyond syntax.
Recruiters focus on real-time scenarios involving triggers, packages, and exception handling — key concepts in every enterprise Oracle system.

This post brings you practical interview questions directly from developer rounds in 2025, including project-based examples and error-handling logic you can use in production environments.

🔹 Top PL/SQL Triggers, Packages & Exception Handling Questions (2025 Edition)

1️⃣ What is a Trigger in PL/SQL? When do you use it?

Answer:
A trigger is a stored PL/SQL block that automatically executes in response to a database event such as INSERT, UPDATE, or DELETE.
Used for:

  • Auditing data changes

  • Enforcing business rules

  • Automating logging and calculations

Example (Audit Trigger):

 
CREATE OR REPLACE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, updated_on)
VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

✅ Frequently asked in Capgemini production support interviews.

2️⃣ What are the different types of Triggers in Oracle?

TypeDescriptionExample
BEFOREExecutes before the triggering statementData validation
AFTERExecutes after the triggering statementAuditing changes
INSTEAD OFUsed with viewsModifies data through views
COMPOUNDCombines multiple timing pointsUsed in bulk operations

📌 TCS often asks for “real use cases” of compound triggers.

3️⃣ Real-Time Scenario: Prevent Negative Salary Updates

Question: Create a trigger that prevents updating salary to a negative value.

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

✅ Common business rule scenario used in HCL ERP projects.

4️⃣ What is a Package in PL/SQL? Why use it?

A package is a collection of logically related PL/SQL objects — such as procedures, functions, variables, and cursors — grouped together for reusability and modularity.

Example:

 
CREATE OR REPLACE PACKAGE emp_pkg AS
PROCEDURE add_employee(p_id NUMBER, p_name VARCHAR2, p_sal 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_sal NUMBER) IS
BEGIN
INSERT INTO employees VALUES(p_id, p_name, p_sal);
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;

✅ Asked in TCS project design interviews.

5️⃣ What is the difference between a Procedure and a Function?

FeatureProcedureFunction
Returns a valueNoYes
Used in SQLNoYes
PurposePerform an actionCompute and return data
ExampleInsert recordCalculate bonus

6️⃣ What is Exception Handling in PL/SQL?

Exception handling lets you gracefully manage runtime errors.
Structure:

 
BEGIN
-- Your logic
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found!');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown error occurred!');
END;

✅ Capgemini often gives a code snippet and asks you to identify the correct exception block.

7️⃣ Real-Time Scenario: Log Errors in an Exception Table

Question: Write code to log errors instead of stopping execution.

 
BEGIN
INSERT INTO employees VALUES (101, 'Mark', NULL);
EXCEPTION
WHEN OTHERS THEN
INSERT INTO error_log(err_msg, log_date)
VALUES(SQLERRM, SYSDATE);
END;

📌 Used widely in HCL data migration projects.

8️⃣ What is RAISE_APPLICATION_ERROR and why is it used?

Used to define custom errors in PL/SQL.
Example:

 
IF v_salary < 10000 THEN
RAISE_APPLICATION_ERROR(-20005, 'Salary below company threshold.');
END IF;

✅ TCS often asks to differentiate between system exceptions and user-defined exceptions.

💡 Pro Tip:

When explaining PL/SQL logic in interviews, follow this structure:

“I’ll define the business rule → trigger event → package structure → exception handling → optimization.”

This stepwise explanation shows real-world thinking — exactly what TCS, HCL & Capgemini look for.

🧩 Conclusion

These PL/SQL triggers, packages & exception handling questions (2025 Edition) from TCS, HCL & Capgemini are based on actual developer rounds.
They test your ability to write efficient, modular, and error-proof code — crucial in enterprise systems.

Focus on explaining logic flow and performance improvements instead of just syntax — that’s what differentiates a senior Oracle developer from a beginner.


Topic Tags
PL/SQL Triggers Interview HCL PL/SQL Real-Time Que TCS Oracle Developer Int Capgemini PL/SQL Package PL/SQL Exception Handlin

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  PL/SQL Triggers Interview (1) , HCL PL/SQL Real-Time Que (1) , TCS Oracle Developer Int (1) , Capgemini PL/SQL Package (1) , PL/SQL Exception Handlin (1) ,
Share: