PL/SQL Interview Handbook - Comprehensive
Q&A;
Q1. What are PL/SQL cursors? Explain types with examples.
A cursor is a pointer to the context area where Oracle stores SQL execution results. Types:
- Implicit Cursors: Automatically created for single-row queries. Example:
BEGIN
UPDATE employees SET salary=salary*1.1 WHERE dept='IT';
IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Updated'); END IF;
END;
- Explicit Cursors: Manually declared for multi-row queries. Example:
DECLARE CURSOR emp_cur IS SELECT emp_id, name FROM employees;
BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO v_id, v_name; EXIT WHEN
emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_name); END LOOP; CLOSE emp_cur;
END;
Q2. What is a mutating table error and how do you handle it?
A mutating table error occurs when a row-level trigger tries to modify or query the same table that
caused the trigger to fire. This breaks Oracle’s read consistency.
Solutions:
- Use statement-level triggers instead of row-level.
- Use compound triggers (Oracle 11g+).
- Store affected row IDs in a collection, process them after trigger execution.
Q3. Difference between Procedures and Functions in PL/SQL?
- Procedures: Do not return a value directly, invoked using EXEC or within PL/SQL blocks. Used for
performing actions.
- Functions: Must return a single value, can be used inside SQL queries.
Example Procedure:
CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS BEGIN UPDATE
employees SET salary=salary*1.1 WHERE emp_id=p_id; END;
Example Function:
CREATE OR REPLACE FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS v_sal
NUMBER; BEGIN SELECT salary INTO v_sal FROM employees WHERE emp_id=p_id; RETURN
v_sal; END;
Q4. Explain PL/SQL Collections (Associative Arrays, Nested Tables,
VARRAYs).
- Associative Arrays: Key-value pairs, unbounded, indexed by string/number.
- Nested Tables: Unordered set, can be stored in DB columns.
- VARRAYs: Ordered collection with a fixed upper bound.
Example:
DECLARE TYPE num_list IS TABLE OF NUMBER; v_nums num_list := num_list(1,2,3); BEGIN
FOR i IN 1..v_nums.COUNT LOOP DBMS_OUTPUT.PUT_LINE(v_nums(i)); END LOOP; END;
Q5. How to improve performance in PL/SQL with BULK COLLECT and
FORALL?
- BULK COLLECT: Fetches multiple rows at once into a collection, reducing context switches.
- FORALL: Executes DML for all rows in a collection in one go.
Example:
DECLARE TYPE numlist IS TABLE OF employees.emp_id%TYPE; v_ids numlist;
BEGIN SELECT emp_id BULK COLLECT INTO v_ids FROM employees WHERE dept='IT';
FORALL i IN v_ids.FIRST..v_ids.LAST UPDATE employees SET salary=salary*1.1 WHERE
emp_id=v_ids(i);
END;
Q6. What are Triggers in PL/SQL? Types with example.
- Triggers are PL/SQL blocks that execute automatically in response to DML, DDL, or DB events.
Types: BEFORE/AFTER triggers, Row-level vs Statement-level, INSTEAD OF triggers (for views).
Example Audit Trigger:
CREATE OR REPLACE TRIGGER emp_audit AFTER UPDATE ON employees FOR EACH ROW
BEGIN INSERT INTO emp_audit_log(emp_id, old_sal, new_sal, updated_on)
VALUES(:OLD.emp_id, :[Link], :[Link], SYSDATE); END;
Q7. How do you handle exceptions in PL/SQL?
PL/SQL has predefined and user-defined exceptions.
Example:
BEGIN SELECT salary INTO v_sal FROM employees WHERE emp_id=999;
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such employee');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Multiple records found'); END;
Q8. Difference between Package, Procedure, and Trigger?
- Package: Collection of related procedures, functions, variables. Supports modularity.
- Procedure: Standalone PL/SQL subprogram to perform actions.
- Trigger: Fires automatically on DB events.
Q9. Explain Dynamic SQL in PL/SQL. Example?
- Dynamic SQL lets you build SQL statements at runtime using EXECUTE IMMEDIATE.
Example:
DECLARE v_sql VARCHAR2(200); BEGIN v_sql := 'UPDATE employees SET salary=salary*1.2
WHERE dept=''HR'''; EXECUTE IMMEDIATE v_sql; END;
Q10. Real-time optimization tips for PL/SQL queries?
- Use bind variables.
- Avoid unnecessary loops; prefer set-based SQL.
- Use bulk processing (BULK COLLECT, FORALL).
- Use indexes and partitions effectively.
- Avoid committing inside loops.