Forum

Forums
PL SQL
Interview Questions...
Advanced PL/SQL Cod...
 
Share:
Notifications
Clear all

Advanced PL/SQL Coding Interview Questions — Asked in Infosys, Accenture & Wipro (2025 Edition)

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

In 2025, Infosys, Accenture, and Wipro continue to test Oracle developers with complex PL/SQL coding questions based on real-time performance scenarios, not just syntax.

These questions are designed to check your ability to write optimized PL/SQL code, use collections, manage exceptions, and handle large data volumes efficiently.

If you’re preparing for mid-level or senior Oracle roles, this list of advanced PL/SQL interview questions with examples will help you ace both coding and technical discussions.

🔹 Top Advanced PL/SQL Coding Interview Questions (2025 Edition)

1️⃣ What is the difference between an Explicit and an Implicit Cursor?

Answer:

  • Implicit Cursor: Automatically created by Oracle for single SQL statements like INSERT, UPDATE, DELETE, SELECT INTO.

  • Explicit Cursor: Declared by the developer for queries returning multiple rows.
    Example:

 
DECLARE
CURSOR emp_cur IS SELECT emp_name, salary FROM employees WHERE dept_id = 10;
BEGIN
FOR rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_name || ' - ' || rec.salary);
END LOOP;
END;

✅ Common in Wipro and Accenture PL/SQL coding rounds.

2️⃣ What is Bulk Collect in PL/SQL and why is it used?

Used for performance improvement when fetching multiple rows.
Instead of fetching one row at a time, BULK COLLECT retrieves data in batches into PL/SQL collections.
Example:

 
DECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
v_emp emp_tab;
BEGIN
SELECT * BULK COLLECT INTO v_emp FROM employees;
FOR i IN 1..v_emp.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_emp(i).emp_name);
END LOOP;
END;

✅ Accenture often asks this for large data-load projects.

3️⃣ What is the difference between FORALL and Bulk Collect?

FeatureBULK COLLECTFORALL
PurposeFetch multiple rows at onceExecute DML operations in bulk
Used ForSELECT statementsINSERT, UPDATE, DELETE
Example:  
 
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET bonus = 0.1 * salary WHERE emp_id = v_ids(i);

📌 Infosys interviewers focus on this for optimization logic.

4️⃣ Explain Ref Cursors and their types.

Ref cursors allow passing cursor results between subprograms dynamically.

 
DECLARE
TYPE emp_ref IS REF CURSOR;
c1 emp_ref;
v_name employees.name%TYPE;
BEGIN
OPEN c1 FOR SELECT name FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE c1;
END;

✅ Common in Infosys advanced coding tests.

5️⃣ How can you handle multiple exceptions in PL/SQL efficiently?

Use named and generic exceptions with WHEN clauses.
Example:

 
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employees found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

📌 Accenture often gives a block with errors and asks you to correct it.

6️⃣ What are PL/SQL Collections? Explain their types.

  • Associative Arrays (INDEX BY tables): Key-value pairs.

  • Nested Tables: Similar to arrays, dynamic in size.

  • VARRAYs: Fixed-size arrays.

Example (Nested Table):

 
DECLARE
TYPE t_numbers IS TABLE OF NUMBER;
v_nums t_numbers := t_numbers(10, 20, 30);
BEGIN
FOR i IN 1..v_nums.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_nums(i));
END LOOP;
END;

✅ Infosys & Wipro focus on associative arrays and nested tables.

7️⃣ What is a Cursor FOR Loop and how is it different from explicit cursor handling?

Cursor FOR Loop simplifies cursor management — no need to explicitly open, fetch, or close.

 
FOR emp_rec IN (SELECT emp_name FROM employees WHERE dept_id = 50)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name);
END LOOP;

📌 Asked in Accenture intermediate rounds.

8️⃣ Real-Time Scenario: Bulk Update with Error Logging

Question: Update employee bonuses in bulk and log failed updates.

 
DECLARE
TYPE id_tab IS TABLE OF employees.emp_id%TYPE;
v_ids id_tab := id_tab(101, 102, 103);
BEGIN
FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
UPDATE employees SET bonus = bonus + 500 WHERE emp_id = v_ids(i);
EXCEPTION
WHEN OTHERS THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
INSERT INTO error_log VALUES(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX,
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE),
SYSDATE);
END LOOP;
END;

✅ Common in Wipro PL/SQL automation projects.

💡 Pro Tip:

When explaining PL/SQL logic in interviews, add context:

“We implemented bulk updates in a payroll module where performance improved by 60% after using FORALL with SAVE EXCEPTIONS.”

This gives a real-world impact — which impresses senior interviewers instantly.

🧩 Conclusion

These Advanced PL/SQL Coding Interview Questions (2025 Edition) from Infosys, Accenture & Wipro are drawn from actual project-based technical rounds.

Mastering these topics — bulk operations, ref cursors, autonomous transactions, and optimization techniques — will help you stand out as a top-performing Oracle developer.

Practice these scenarios regularly on Oracle LiveSQL or SQL Developer to strengthen your command and speed in PL/SQL interviews.


Topic Tags
Advanced PL/SQL Interview Infosys Oracle Developer Accenture PL/SQL Perform Wipro PL/SQL Coding Roun Bulk Collect and Cursors

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Topic Tags:  Advanced PL/SQL Interview (1) , Infosys Oracle Developer (1) , Accenture PL/SQL Perform (1) , Wipro PL/SQL Coding Roun (1) , Bulk Collect and Cursors (1) ,
Share: