Oracle PL/SQL Interview Questions

ROWID is a physical row address — a unique 18-character string representing datafile, block, and row position. It is the fastest way to access a specific row and is used in trigger logic (:NEW.ROWID) or post-query row updates. ROWNUM is a pseudo-column assigning sequential numbers to rows AS they are fetched — before ORDER BY. Therefore WHERE ROWNUM < 5 does not reliably give the top 5 sorted rows. Correct approach: SELECT * FROM (SELECT * FROM emp ORDER BY salary DESC) WHERE ROWNUM <= 5. In Oracle 12c+, use FETCH FIRST 5 ROWS ONLY — cleaner and ANSI-compliant.

Implicit cursor: automatically created by Oracle for every DML statement (SELECT INTO, INSERT, UPDATE, DELETE). Attributes: SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND. Explicit cursor: declared by the programmer for multi-row SELECT result sets: CURSOR emp_cursor IS SELECT employee_id, salary FROM employees; OPEN emp_cursor; LOOP FETCH emp_cursor INTO v_id, v_sal; EXIT WHEN emp_cursor%NOTFOUND; END LOOP; CLOSE emp_cursor; For large datasets, use BULK COLLECT to fetch all rows into a collection at once — 10-100x faster than row-by-row processing due to reduced context switches between SQL and PL/SQL engines.

An Oracle Package groups related procedures, functions, variables, cursors, and exceptions into a single logical unit. Structure: Package Specification (public interface — declarations visible to callers) and Package Body (private implementation). Advantages: Encapsulation (hide internal logic, expose only the API), Performance (entire package loaded into memory on first call — subsequent calls within the same session are faster), State management (package-level variables persist for session duration — useful for caching lookup data), Overloading (multiple procedures with the same name but different parameter signatures). Organize by business domain: SALES_PKG, INVENTORY_PKG, CUSTOMER_PKG.

Predefined exceptions: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE — Oracle raises these automatically. User-defined exceptions: DECLARE insufficient_stock EXCEPTION; PRAGMA EXCEPTION_INIT(insufficient_stock, -20001); BEGIN IF v_qty_available < v_qty_ordered THEN RAISE insufficient_stock; END IF; EXCEPTION WHEN insufficient_stock THEN RAISE_APPLICATION_ERROR(-20001, 'Stock insufficient: ' || v_item_id); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Order not found'); WHEN OTHERS THEN ROLLBACK; INSERT INTO error_log VALUES (SYSDATE, SQLERRM, SQLCODE); RAISE; END; Always include WHEN OTHERS with logging and RAISE to prevent silent exception swallowing.

An execution plan shows the sequence of operations Oracle performs to execute a query — retrieved with: EXPLAIN PLAN FOR query; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY). Key elements to analyze: Operation (TABLE ACCESS FULL = full table scan — bad for large tables, TABLE ACCESS BY INDEX ROWID = index lookup — good), Cost (optimizer's estimated cost — lower is better), Cardinality (estimated row count), Access Predicates (filter during index scan — good), Filter Predicates (applied after fetch — bad for large datasets). Red flags: TABLE ACCESS FULL on large tables, SORT without index on ORDER BY column, CARTESIAN JOIN (missing JOIN condition). Use Oracle's SQL Tuning Advisor for automated analysis and index recommendations.

MERGE combines INSERT and UPDATE in one atomic statement (upsert) — if source row matches target, UPDATE; if no match, INSERT. MERGE INTO dim_customers tgt USING staging_customers src ON (tgt.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET tgt.customer_name = src.customer_name, tgt.last_updated = SYSDATE WHERE tgt.customer_name != src.customer_name WHEN NOT MATCHED THEN INSERT (customer_id, customer_name, created_date) VALUES (src.customer_id, src.customer_name, SYSDATE); Advantages: one scan of target table, atomic (no partial updates), WHERE clause on WHEN MATCHED prevents unnecessary updates (improves redo log performance). Standard approach in Oracle ETL pipelines for dimension table maintenance.