| SGA vs PGA | SGA: shared memory for all sessions; PGA: private memory per server process |
| ROWID vs ROWNUM | ROWID: physical row address (stored); ROWNUM: pseudo-sequence assigned at fetch time |
| Cursor types | Implicit (SQL%), explicit (OPEN/FETCH/CLOSE), cursor FOR loop, REF CURSOR |
| %TYPE vs %ROWTYPE | %TYPE: single column's type; %ROWTYPE: record matching a whole row |
| Procedure vs Function | Function must RETURN a value and is callable from SQL; procedure performs an action |
| Package benefits | Encapsulation, overloading, session state, one-time loading, fewer invalidations |
| MERGE statement | Single-statement UPSERT: WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT |
| Sequence usage | seq.NEXTVAL generates next value; seq.CURRVAL valid only after NEXTVAL in session |
Oracle Architecture Basics
Q1. What is the difference between an Oracle instance and an Oracle database?
A: The instance is the set of memory structures (SGA, PGA) and background processes (DBWn, LGWR, SMON, PMON, CKPT) running on the server. The database is the physical set of files on disk: datafiles, control files, and redo log files. An instance mounts and opens a database; in RAC, multiple instances open the same database.
Q2. What is the SGA and what are its main components?
A: The System Global Area is shared memory allocated at instance startup and used by all sessions. Key components: the Database Buffer Cache (caches data blocks read from datafiles), the Shared Pool (library cache for parsed SQL/PL-SQL plus the data dictionary cache), the Redo Log Buffer (buffers change records before LGWR writes them), and optional Large/Java/Streams pools.
Q3. What is the PGA and how does it differ from the SGA?
A: The Program Global Area is private memory allocated per server process, holding session variables, cursor state, sort areas, and hash join work areas. Unlike the SGA it is not shared between sessions. Large sorts or hash joins that exceed the PGA work area spill to the TEMP tablespace, which is a common performance issue.
Q4. What is the role of the Oracle listener?
A: The listener is a separate server-side process that accepts incoming client connection requests on a port (default 1521), matches the requested service name, and hands the connection to a new dedicated server process (or a dispatcher in shared server mode). After the handoff, the client talks directly to the server process — the listener is not involved in query execution.
Q5. What do the key background processes DBWn, LGWR, CKPT, SMON, and PMON do?
A: DBWn writes dirty buffers from the buffer cache to datafiles. LGWR writes the redo log buffer to online redo logs (on commit, guaranteeing durability). CKPT signals checkpoints and updates file headers. SMON performs instance recovery and cleans up temporary segments. PMON cleans up after failed user processes and releases their locks.
Q6. What is the difference between a tablespace and a datafile?
A: A tablespace is a logical storage container that groups segments (tables, indexes); a datafile is the physical OS file that stores the actual blocks. One tablespace consists of one or more datafiles, but a datafile belongs to exactly one tablespace. Standard tablespaces include SYSTEM, SYSAUX, UNDO, TEMP, and USERS.
Q7. What is the difference between redo and undo?
A: Redo records how to redo changes and exists to recover committed work after a crash — it is written sequentially to redo log files. Undo records how to reverse changes and exists to roll back transactions and provide read consistency (other sessions see the pre-change image). Interview trap: undo generation itself also generates redo, because undo segments are protected like any other data.
Q8. What is the difference between ROWID and ROWNUM?
A: ROWID is a pseudocolumn giving the physical address of a stored row (file, block, slot) — it is the fastest possible access path. ROWNUM is a pseudo-sequence assigned to rows as they are returned, before ORDER BY is applied, which is why WHERE ROWNUM = 2 never returns rows. Use a subquery (or 12c's FETCH FIRST) for top-N queries.
-- WRONG: returns no rows (ROWNUM=1 fails first, so ROWNUM 2 never assigned)
SELECT * FROM emp WHERE ROWNUM = 2;
-- Correct top-5 by salary
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 5;
-- 12c+ syntax
SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 5 ROWS ONLY;Q9. What is the difference between dedicated server and shared server connections?
A: In dedicated mode, each client session gets its own server process — simple and best for long-running or heavy sessions. In shared server mode, dispatchers queue requests to a pool of shared server processes, reducing process count for thousands of mostly idle connections. Modern applications usually keep dedicated mode and pool connections in the middle tier (e.g., HikariCP/UCP in Java).
Q10. What is the data dictionary and what is the difference between USER_, ALL_, and DBA_ views?
A: The data dictionary is a set of read-only tables and views owned by SYS that describe every object in the database. USER_ views show objects you own, ALL_ views show objects you can access, and DBA_ views show everything (requires elevated privileges). Dynamic performance views (V$SESSION, V$SQL) expose live instance state.
SELECT table_name FROM user_tables; -- my tables
SELECT owner, object_type FROM all_objects
WHERE object_name = 'EMP'; -- what I can see
SELECT sid, username, status FROM v$session; -- live sessions (needs priv)PL/SQL Blocks & Variables
Q11. What is the structure of a PL/SQL block?
A: A PL/SQL block has an optional DECLARE section (variables, cursors, exceptions), a mandatory BEGIN...END executable section, and an optional EXCEPTION section for handlers. Blocks can be anonymous or stored (procedures, functions, packages, triggers), and can be nested inside each other.
DECLARE
v_count NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp;
DBMS_OUTPUT.PUT_LINE('Employees: ' || v_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/Q12. What is the difference between an anonymous block and a stored subprogram?
A: An anonymous block has no name, is compiled every time it is submitted, and is not stored in the data dictionary — useful for scripts and ad-hoc testing. Stored subprograms (procedures, functions, packages) are compiled once, stored in the database with dependency tracking and privileges, and their parsed form is cached in the shared pool, so repeated calls are much faster.
Q13. What is the difference between %TYPE and %ROWTYPE?
A: %TYPE anchors a variable to the datatype of a single column or another variable; %ROWTYPE declares a record whose fields match an entire row of a table or cursor. Both make code resilient to schema changes — if the column length changes, the code recompiles without edits.
DECLARE
v_name emp.ename%TYPE; -- matches ename column type
r_emp emp%ROWTYPE; -- record with all emp columns
BEGIN
SELECT * INTO r_emp FROM emp WHERE empno = 7839;
v_name := r_emp.ename;
DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || r_emp.sal);
END;
/Q14. How do you declare variables and constants in PL/SQL?
A: Variables are declared as name datatype [NOT NULL] [:= default] in the DECLARE section. Constants use the CONSTANT keyword and must be initialized at declaration; any later assignment is a compile error. DEFAULT is interchangeable with := for initialization.
DECLARE
v_salary NUMBER(8,2) := 50000;
v_hired DATE DEFAULT SYSDATE;
c_tax_rate CONSTANT NUMBER := 0.30; -- must initialize
v_active BOOLEAN NOT NULL := TRUE; -- NOT NULL needs a default
BEGIN
v_salary := v_salary * (1 - c_tax_rate);
END;
/Q15. What is the difference between SQL and PL/SQL?
A: SQL is a declarative set-based language for querying and manipulating data — one statement at a time. PL/SQL is Oracle's procedural extension that adds variables, control flow (IF, LOOP), cursors, exceptions, and modular units, and can embed SQL directly. SQL executes in the SQL engine, PL/SQL in the PL/SQL engine; every switch between them is a context switch, which is why bulk operations matter.
Q16. What are bind variables and why do they matter?
A: Bind variables are placeholders whose values are supplied at execution time instead of being concatenated into the SQL text. They let Oracle reuse one parsed cursor for many executions (soft parse instead of hard parse), dramatically reducing shared pool contention, and they prevent SQL injection. PL/SQL variables referenced inside embedded SQL are automatically bound; in JDBC use PreparedStatement parameters.
-- BAD: hard-parses a new statement per value, injectable
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || p_id;
-- GOOD: one shared cursor, safe
EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :id' USING p_id;Q17. How do NVL, NVL2, COALESCE, and NULLIF differ?
A: NVL(a, b) returns b when a is NULL. NVL2(a, b, c) returns b when a is NOT NULL, otherwise c. COALESCE(a, b, c, ...) returns the first non-NULL argument, short-circuits evaluation, and is ANSI standard — prefer it. NULLIF(a, b) returns NULL when a equals b, handy for avoiding divide-by-zero.
SELECT ename,
NVL(comm, 0) AS commission,
NVL2(comm, 'HAS COMM', 'NONE') AS comm_flag,
COALESCE(comm, bonus, 0) AS first_extra,
sal / NULLIF(quota, 0) AS safe_ratio
FROM emp;Q18. What is SELECT INTO and what exceptions can it raise?
A: SELECT ... INTO fetches exactly one row into PL/SQL variables. It raises NO_DATA_FOUND if zero rows match and TOO_MANY_ROWS if more than one matches — both must be anticipated. For zero-or-many result sets, use an explicit cursor, cursor FOR loop, or BULK COLLECT instead.
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN v_sal := NULL; -- 0 rows
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20001, 'Duplicate empno ' || p_id);
END;Q19. How does variable scope work in nested PL/SQL blocks?
A: An inner block sees the outer block's variables unless it declares one with the same name, which shadows the outer variable. You can still reach the outer one by qualifying with a block label. Variables declared in the inner block cease to exist when it ends.
<<outer>>
DECLARE
v_id NUMBER := 1;
BEGIN
DECLARE
v_id NUMBER := 2; -- shadows outer v_id
BEGIN
DBMS_OUTPUT.PUT_LINE(v_id); -- 2
DBMS_OUTPUT.PUT_LINE(outer.v_id); -- 1 (label-qualified)
END;
END;
/Q20. What is PLS_INTEGER and when should you use it over NUMBER?
A: PLS_INTEGER (and its subtype SIMPLE_INTEGER, which is NOT NULL and wraps on overflow) is a 32-bit machine integer that uses native hardware arithmetic, making it significantly faster and more memory-efficient than NUMBER for loop counters and integer math inside PL/SQL. It exists only in PL/SQL — table columns must still use NUMBER. Range is -2,147,483,648 to 2,147,483,647; exceeding it raises an overflow error.
Cursors: Implicit, Explicit & REF Cursors
Q21. What is a cursor and what is the difference between implicit and explicit cursors?
A: A cursor is a handle to the private SQL work area where Oracle processes a statement. Implicit cursors are created automatically for every DML statement and single-row SELECT INTO; you inspect them via SQL% attributes. Explicit cursors are declared by the programmer for multi-row queries and controlled with OPEN, FETCH, and CLOSE, giving row-by-row control.
Q22. Show the full lifecycle of an explicit cursor.
A: Declare the cursor with its query, OPEN it (binds and executes), FETCH rows one at a time into variables or a record, test %NOTFOUND to exit, and CLOSE to release the work area. Forgetting CLOSE leaks cursors and can hit ORA-01000: maximum open cursors exceeded.
DECLARE
CURSOR c_emp IS SELECT ename, sal FROM emp WHERE deptno = 10;
r_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(r_emp.ename || ': ' || r_emp.sal);
END LOOP;
CLOSE c_emp;
END;
/Q23. What are the cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN?
A: %FOUND is TRUE if the last fetch returned a row; %NOTFOUND is its negation and is the standard loop exit test. %ROWCOUNT counts rows fetched so far (or rows affected, for SQL%ROWCOUNT after DML). %ISOPEN tells whether the cursor is open — for implicit cursors it is always FALSE because Oracle closes them immediately.
UPDATE emp SET sal = sal * 1.1 WHERE deptno = 20;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No employees updated');
ELSE
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END IF;Q24. Why is a cursor FOR loop preferred over OPEN/FETCH/CLOSE?
A: The cursor FOR loop implicitly opens the cursor, declares the loop record, fetches each row, and closes the cursor even when an exception escapes — eliminating the leaked-cursor bug class entirely. Since Oracle 10g the optimizer silently array-fetches 100 rows at a time under the hood, so it also performs well.
BEGIN
FOR r IN (SELECT ename, sal FROM emp WHERE deptno = 10) LOOP
DBMS_OUTPUT.PUT_LINE(r.ename || ': ' || r.sal);
END LOOP; -- no OPEN/FETCH/CLOSE, no record declaration
END;
/Q25. What is a parameterized cursor?
A: A parameterized cursor accepts arguments at OPEN time, so one declaration serves many filter values without hard-coding session variables into the query. Parameters are IN-only and scoped to the cursor.
DECLARE
CURSOR c_dept (p_deptno NUMBER, p_min_sal NUMBER := 0) IS
SELECT ename, sal FROM emp
WHERE deptno = p_deptno AND sal >= p_min_sal;
BEGIN
FOR r IN c_dept(20, 2000) LOOP
DBMS_OUTPUT.PUT_LINE(r.ename);
END LOOP;
END;
/Q26. What is a REF CURSOR and when do you use SYS_REFCURSOR?
A: A REF CURSOR is a pointer to a query result set that can be opened for different queries at runtime and — critically — passed between programs, including out to Java/JDBC clients. SYS_REFCURSOR is the predefined weak REF CURSOR type, so you no longer need to define your own in a package for the common case.
CREATE OR REPLACE PROCEDURE get_emps (
p_deptno IN NUMBER,
p_rc OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_rc FOR
SELECT empno, ename, sal FROM emp WHERE deptno = p_deptno;
END;
/Q27. What is the difference between a strong and a weak REF CURSOR?
A: A strong REF CURSOR is declared with a RETURN clause (TYPE t IS REF CURSOR RETURN emp%ROWTYPE), so the compiler verifies at compile time that every query opened for it matches that row shape. A weak REF CURSOR (no RETURN clause, e.g., SYS_REFCURSOR) can be opened for any query, trading compile-time safety for flexibility; mismatches surface at runtime as ROWTYPE_MISMATCH.
Q28. What do FOR UPDATE and WHERE CURRENT OF do?
A: SELECT ... FOR UPDATE locks the selected rows at OPEN time so no other session can change them while you process the cursor. WHERE CURRENT OF cursor_name in an UPDATE/DELETE targets exactly the row just fetched, using its ROWID — faster and safer than re-specifying the key. The locks are released on COMMIT, which also invalidates the FOR UPDATE cursor.
DECLARE
CURSOR c IS SELECT sal FROM emp WHERE deptno = 30 FOR UPDATE OF sal NOWAIT;
BEGIN
FOR r IN c LOOP
UPDATE emp SET sal = r.sal * 1.05 WHERE CURRENT OF c;
END LOOP;
COMMIT;
END;
/Q29. What happens if you fetch after the last row, or open an already-open cursor?
A: Fetching past the last row does not raise an error — the fetch simply returns nothing, %NOTFOUND stays TRUE, and the target variables keep their previous values (a subtle bug source). Opening an already-open explicit cursor raises CURSOR_ALREADY_OPEN (ORA-06511), and fetching from or closing a closed cursor raises INVALID_CURSOR (ORA-01001).
Q30. How do you check how many rows a DML statement affected?
A: Read SQL%ROWCOUNT immediately after the INSERT, UPDATE, DELETE, or MERGE — any subsequent SQL statement (even an implicit one) resets it. It returns 0 when nothing matched, which is not an error in SQL, so if "no rows" is a business error you must test it explicitly and raise your own exception.
DELETE FROM orders WHERE order_id = p_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20010, 'Order ' || p_id || ' not found');
END IF;Procedures, Functions & Packages
Q31. What is the difference between a procedure and a function?
A: A function must have a RETURN clause and return exactly one value, which lets it be used inside SQL expressions; a procedure performs an action and returns data only through OUT parameters. Convention: use functions for computations with no side effects, procedures for operations that change state. Functions with OUT parameters cannot be called from SQL.
Q32. Write a stored procedure with IN, OUT, and IN OUT parameters.
A: IN parameters (the default) pass values in and are read-only inside the body; OUT parameters return values and start as NULL inside the procedure; IN OUT parameters do both. OUT/IN OUT parameters require variables (not literals) at the call site.
CREATE OR REPLACE PROCEDURE apply_raise (
p_empno IN NUMBER,
p_pct IN NUMBER DEFAULT 5,
p_new_sal OUT NUMBER,
p_message IN OUT VARCHAR2
) AS
BEGIN
UPDATE emp SET sal = sal * (1 + p_pct/100)
WHERE empno = p_empno
RETURNING sal INTO p_new_sal;
p_message := p_message || ' raised to ' || p_new_sal;
END;
/Q33. How are parameters passed — by value or by reference — and what is NOCOPY?
A: IN parameters are passed by reference (efficient, read-only). OUT and IN OUT parameters are passed by value: Oracle copies into a local, works on the copy, and copies back on success — so if the procedure fails, the caller's variable is untouched. The NOCOPY hint requests pass-by-reference for large OUT collections to avoid the copy, at the cost of losing that rollback-of-values guarantee on exceptions.
Q34. Write a function and call it from SQL.
A: A stored function with only IN parameters and a scalar return type can be called anywhere an expression is allowed — SELECT list, WHERE, ORDER BY. Remember each call may execute once per row, so keep SQL-callable functions cheap or mark them DETERMINISTIC / RESULT_CACHE where valid.
CREATE OR REPLACE FUNCTION annual_comp (
p_sal NUMBER,
p_comm NUMBER
) RETURN NUMBER DETERMINISTIC AS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 12;
END;
/
SELECT ename, annual_comp(sal, comm) AS yearly FROM emp;Q35. What restrictions apply to functions called from SQL?
A: The function must be stored (or in a package), take only IN parameters of SQL-compatible types, and return a SQL type — no BOOLEAN or PL/SQL records. It must not perform DML or DDL or issue COMMIT/ROLLBACK when called from a query (writes violate read consistency and raise ORA-14551), and it should not depend on session state, otherwise results are unpredictable.
Q36. What is a package and what are its benefits?
A: A package groups related procedures, functions, types, cursors, and variables into a specification (public interface) and a body (implementation). Benefits: encapsulation (private helpers hidden in the body), overloading, session-persistent state, better dependency management (callers depend only on the spec, so body changes don't invalidate them), and the whole package loads into the shared pool at first reference.
Q37. Show a package specification and body.
A: Anything declared in the spec is public; anything only in the body is private. Every subprogram declared in the spec must be implemented in the body with an exactly matching signature.
CREATE OR REPLACE PACKAGE emp_api AS
FUNCTION get_salary(p_empno NUMBER) RETURN NUMBER;
PROCEDURE hire(p_name VARCHAR2, p_deptno NUMBER);
END emp_api;
/
CREATE OR REPLACE PACKAGE BODY emp_api AS
FUNCTION validate_dept(p_deptno NUMBER) RETURN BOOLEAN IS -- private
BEGIN
RETURN p_deptno IN (10, 20, 30, 40);
END;
FUNCTION get_salary(p_empno NUMBER) RETURN NUMBER IS
v_sal emp.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
RETURN v_sal;
END;
PROCEDURE hire(p_name VARCHAR2, p_deptno NUMBER) IS
BEGIN
IF NOT validate_dept(p_deptno) THEN
RAISE_APPLICATION_ERROR(-20020, 'Bad dept');
END IF;
INSERT INTO emp(empno, ename, deptno)
VALUES (emp_seq.NEXTVAL, p_name, p_deptno);
END;
END emp_api;
/Q38. What is subprogram overloading and where is it allowed?
A: Overloading means declaring multiple subprograms with the same name but different parameter lists (differing in number, order, or type family of parameters — not just names or return type). It is allowed inside packages and PL/SQL blocks but not for standalone stored procedures. Overloads whose parameters differ only within the same type family (e.g., VARCHAR2 vs CHAR) are ambiguous and rejected at call time.
Q39. What is package state and what causes ORA-04068?
A: Variables declared at package level keep their values for the life of the session — that is package state. If the package body is recompiled while a session holds state, the session's next call raises ORA-04068: existing state of packages has been discarded; simply re-invoking succeeds. Avoid it by minimizing package-level variables or declaring the package PRAGMA SERIALLY_REUSABLE (state lasts only per call), and by deploying during quiet windows.
Q40. What do DETERMINISTIC and RESULT_CACHE do for functions?
A: DETERMINISTIC promises the function returns the same output for the same inputs, allowing Oracle to skip redundant calls within a fetch and enabling use in function-based indexes and materialized views. RESULT_CACHE stores results in a shared server-wide cache keyed by arguments, automatically invalidated when the tables it reads change — ideal for small, frequently-read lookup functions.
CREATE OR REPLACE FUNCTION country_name (p_code VARCHAR2)
RETURN VARCHAR2 RESULT_CACHE AS
v_name countries.name%TYPE;
BEGIN
SELECT name INTO v_name FROM countries WHERE code = p_code;
RETURN v_name; -- cached across ALL sessions until countries changes
END;
/Q41. What is the difference between definer rights and invoker rights (AUTHID)?
A: With AUTHID DEFINER (the default), the subprogram runs with the owner's privileges and name resolution — callers need only EXECUTE on it, which is how you grant controlled access to tables. With AUTHID CURRENT_USER, it runs with the caller's privileges and resolves objects in the caller's schema — right for generic utilities that must respect each user's own data and security.
Q42. How do you find the source code and errors of stored PL/SQL?
A: Source is in USER_SOURCE (columns NAME, TYPE, LINE, TEXT); compile errors are in USER_ERRORS or shown by SHOW ERRORS in SQL*Plus. Object status (VALID/INVALID) is in USER_OBJECTS, and dependencies in USER_DEPENDENCIES.
SELECT text FROM user_source
WHERE name = 'EMP_API' AND type = 'PACKAGE BODY'
ORDER BY line;
SELECT line, position, text FROM user_errors
WHERE name = 'EMP_API';Triggers
Q43. What is a trigger and what types does Oracle support?
A: A trigger is a stored PL/SQL block that fires automatically on an event. Types: DML triggers (BEFORE/AFTER, statement- or row-level, on INSERT/UPDATE/DELETE), compound triggers (all four timing points in one body), INSTEAD OF triggers (on views), DDL triggers (CREATE/ALTER/DROP), and database event triggers (LOGON, STARTUP, SERVERERROR).
Q44. What is the difference between BEFORE and AFTER triggers?
A: BEFORE triggers fire before the row is written, so they are the only place you can modify :NEW column values (derivations, defaults, normalization) or block the operation cheaply before work is done. AFTER triggers fire once constraints have been checked and the change applied — appropriate for auditing and propagating changes, since the data is guaranteed valid at that point.
Q45. What is the difference between row-level and statement-level triggers, and what are :NEW and :OLD?
A: A row-level trigger (FOR EACH ROW) fires once per affected row and can see that row's values via the :NEW and :OLD pseudo-records; a statement-level trigger fires once per statement regardless of row count and cannot see row values. For INSERT only :NEW is populated, for DELETE only :OLD, and for UPDATE both.
CREATE OR REPLACE TRIGGER trg_emp_biu
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
:NEW.ename := UPPER(:NEW.ename);
IF UPDATING AND :NEW.sal < :OLD.sal * 0.5 THEN
RAISE_APPLICATION_ERROR(-20030, 'Salary cut too large');
END IF;
END;
/Q46. Write an audit trigger.
A: A classic AFTER row-level trigger writes old and new values plus who/when into an audit table. Use the conditional predicates INSERTING, UPDATING, and DELETING to record the action in a single trigger.
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
v_action VARCHAR2(6) := CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
ELSE 'DELETE' END;
BEGIN
INSERT INTO emp_audit(action, empno, old_sal, new_sal, changed_by, changed_at)
VALUES (v_action, NVL(:NEW.empno, :OLD.empno),
:OLD.sal, :NEW.sal, USER, SYSTIMESTAMP);
END;
/Q47. What is the mutating table error (ORA-04091) and how do you solve it?
A: A row-level trigger cannot query or modify the table that fired it, because the table is mid-change and reading it would give inconsistent results — Oracle raises ORA-04091: table is mutating. Solutions: move the logic to a statement-level trigger, use a compound trigger that collects row data in the trigger's collection and processes it in the AFTER STATEMENT section, or redesign so the rule is enforced by a constraint or procedure API instead of a trigger.
Q48. What is a compound trigger?
A: Introduced in 11g, a compound trigger packs BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT sections into one trigger sharing common state (variables/collections declared at the top). Its main use is the mutating-table workaround: buffer row facts in AFTER EACH ROW and act on them safely in AFTER STATEMENT.
CREATE OR REPLACE TRIGGER trg_sal_cap
FOR UPDATE OF sal ON emp
COMPOUND TRIGGER
TYPE t_ids IS TABLE OF emp.deptno%TYPE;
g_depts t_ids := t_ids();
AFTER EACH ROW IS
BEGIN
g_depts.EXTEND;
g_depts(g_depts.COUNT) := :NEW.deptno;
END AFTER EACH ROW;
AFTER STATEMENT IS
v_total NUMBER;
BEGIN
FOR i IN 1 .. g_depts.COUNT LOOP
SELECT SUM(sal) INTO v_total FROM emp WHERE deptno = g_depts(i);
IF v_total > 1000000 THEN
RAISE_APPLICATION_ERROR(-20031, 'Dept budget exceeded');
END IF;
END LOOP;
END AFTER STATEMENT;
END trg_sal_cap;
/Q49. What is an INSTEAD OF trigger?
A: INSTEAD OF triggers are defined on views and replace the DML statement entirely — Oracle runs the trigger body instead of trying to modify the view. They make complex (join-based, aggregated) views updatable by translating the view-level DML into DML on the underlying base tables. They are always row-level.
CREATE OR REPLACE TRIGGER trg_v_emp_dept_ins
INSTEAD OF INSERT ON v_emp_dept -- view joining emp and dept
FOR EACH ROW
BEGIN
INSERT INTO emp(empno, ename, deptno)
VALUES (:NEW.empno, :NEW.ename, :NEW.deptno);
END;
/Q50. Can a trigger contain COMMIT? What is an autonomous transaction?
A: Ordinary triggers cannot COMMIT or ROLLBACK — they run inside the caller's transaction (ORA-04092 otherwise). PRAGMA AUTONOMOUS_TRANSACTION makes a block run in its own independent transaction that must commit or roll back before returning, and its work survives even if the parent rolls back. Legitimate use: persisting error/audit logs; abusing it to "commit inside a trigger" for business data breaks atomicity and is an anti-pattern.
CREATE OR REPLACE PROCEDURE log_error (p_msg VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_log(msg, logged_at) VALUES (p_msg, SYSTIMESTAMP);
COMMIT; -- commits ONLY the log row, not the caller's work
END;
/Q51. What are DDL and database event triggers?
A: DDL triggers fire on schema changes (BEFORE/AFTER CREATE, ALTER, DROP ON SCHEMA|DATABASE) and are used to block or audit DDL in controlled environments. Database event triggers fire on AFTER LOGON, BEFORE LOGOFF, AFTER STARTUP, AFTER SERVERERROR, etc. — commonly used to set session context (NLS settings, tracing) or record failed statements. Keep LOGON triggers fast and defensive: an unhandled error in one can lock out non-DBA users.
Q52. How do you enable, disable, and inspect triggers?
A: Use ALTER TRIGGER name DISABLE|ENABLE for one trigger or ALTER TABLE t DISABLE ALL TRIGGERS for bulk loads (re-enable and validate afterwards). Metadata lives in USER_TRIGGERS, including STATUS and the trigger body.
ALTER TABLE emp DISABLE ALL TRIGGERS;
-- ... bulk load ...
ALTER TABLE emp ENABLE ALL TRIGGERS;
SELECT trigger_name, triggering_event, status
FROM user_triggers WHERE table_name = 'EMP';Exception Handling
Q53. How does exception handling work in PL/SQL?
A: When an error occurs, normal execution stops and control jumps to the block's EXCEPTION section, where handlers are matched top-down; if no handler matches, the exception propagates to the enclosing block or caller. After a handler completes, control leaves the block — you cannot resume at the failing statement (retry requires a loop around a sub-block).
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_id;
v_ratio := v_sal / p_divisor;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_sal := 0;
WHEN ZERO_DIVIDE THEN
v_ratio := NULL;
WHEN OTHERS THEN
log_error(SQLERRM);
RAISE; -- never swallow silently
END;Q54. Name the most common predefined exceptions.
A: NO_DATA_FOUND (ORA-01403, SELECT INTO returns no rows), TOO_MANY_ROWS (ORA-01422), DUP_VAL_ON_INDEX (ORA-00001, unique constraint), ZERO_DIVIDE (ORA-01476), INVALID_NUMBER / VALUE_ERROR (conversion and assignment errors), CURSOR_ALREADY_OPEN, INVALID_CURSOR, and TIMEOUT_ON_RESOURCE. Interview nuance: plain SQL queries returning zero rows is not an error — only SELECT INTO raises NO_DATA_FOUND, and table functions swallow it.
Q55. How do you declare and raise a user-defined exception?
A: Declare it in the DECLARE section as type EXCEPTION, raise it with RAISE, and handle it by name. User-defined exceptions have no error code unless you associate one with PRAGMA EXCEPTION_INIT, and they exist only within their scope — callers outside the scope must catch OTHERS or you should use RAISE_APPLICATION_ERROR instead.
DECLARE
e_underage EXCEPTION;
BEGIN
IF p_age < 18 THEN
RAISE e_underage;
END IF;
enroll(p_id);
EXCEPTION
WHEN e_underage THEN
DBMS_OUTPUT.PUT_LINE('Applicant must be 18+');
END;
/Q56. What is RAISE_APPLICATION_ERROR?
A: RAISE_APPLICATION_ERROR(code, message) raises an error with a custom code in the reserved range -20000 to -20999 and a message up to 2048 bytes, which propagates to clients (Java sees it as SQLException with that code and text). It is the standard way to surface business-rule violations from stored code because the caller gets a stable, documented error number rather than a generic one.
IF v_balance < p_amount THEN
RAISE_APPLICATION_ERROR(
-20101,
'Insufficient funds: balance=' || v_balance || ', requested=' || p_amount);
END IF;Q57. What is PRAGMA EXCEPTION_INIT?
A: It binds a declared exception name to a specific Oracle error number so you can handle unnamed ORA- errors by name instead of catching OTHERS and inspecting SQLCODE. Common targets: ORA-00054 (resource busy with NOWAIT), ORA-02292 (child record found), ORA-01400 (cannot insert NULL).
DECLARE
e_child_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(e_child_exists, -2292);
BEGIN
DELETE FROM dept WHERE deptno = 10;
EXCEPTION
WHEN e_child_exists THEN
RAISE_APPLICATION_ERROR(-20040, 'Move employees before deleting dept');
END;
/Q58. What are SQLCODE and SQLERRM?
A: Inside an exception handler, SQLCODE returns the numeric error code of the current exception (negative for ORA- errors, +100 for NO_DATA_FOUND, 1 for user-defined) and SQLERRM returns the associated message. They cannot be used directly inside a SQL statement — assign them to local variables first if you need to log them via INSERT.
Q59. Why is WHEN OTHERS THEN NULL dangerous, and how do you capture the real error location?
A: WHEN OTHERS THEN NULL swallows every error — including bugs, deadlocks, and corruption signals — leaving data silently wrong; PL/SQL warning PLW-06009 flags OTHERS handlers that neither RAISE nor call RAISE_APPLICATION_ERROR. When you must catch OTHERS, log DBMS_UTILITY.FORMAT_ERROR_STACK plus FORMAT_ERROR_BACKTRACE (which gives the original line number, unlike SQLERRM) and re-raise.
EXCEPTION
WHEN OTHERS THEN
log_error(DBMS_UTILITY.FORMAT_ERROR_STACK ||
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); -- "at line 42"
RAISE;Q60. How do exceptions propagate through nested blocks?
A: An unhandled exception in an inner block propagates outward block by block until some EXCEPTION section handles it; if none does, it reaches the client as an unhandled ORA- error. Key subtlety: an exception raised in a block's DECLARE section (e.g., a failing default assignment) or in its EXCEPTION section is never caught by that same block's handlers — it always propagates to the enclosing block.
Q61. Does an exception automatically roll back the transaction?
A: An unhandled exception rolls back the changes of the failing statement, and when it escapes the outermost call the client/driver typically rolls back the whole transaction — but a handled exception rolls back nothing by itself: all work done before the error remains pending. That is why procedures often issue ROLLBACK (or roll back to a SAVEPOINT taken on entry) inside handlers to restore a known state before re-raising.
Q62. What is DML error logging (LOG ERRORS)?
A: The LOG ERRORS clause lets a set-based INSERT/UPDATE/MERGE/DELETE continue past bad rows, writing each failing row and its error to an error table created by DBMS_ERRLOG.CREATE_ERROR_LOG. It gives you bulk-load resilience without abandoning single-statement SQL for row-by-row PL/SQL.
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('EMP', 'ERR_EMP');
INSERT INTO emp SELECT * FROM emp_staging
LOG ERRORS INTO err_emp ('nightly_load')
REJECT LIMIT UNLIMITED; -- bad rows go to ERR_EMP, good rows commit-ableCollections & Bulk Operations
Q63. What are the three collection types in PL/SQL?
A: Associative arrays (INDEX BY tables): sparse, in-memory only, indexed by PLS_INTEGER or VARCHAR2 — the workhorse for PL/SQL processing. Nested tables: unbounded, can become sparse after DELETE, storable as table columns, usable with the TABLE() operator in SQL. Varrays: bounded, always dense, preserve order, also storable in columns. Nested tables and varrays must be initialized with a constructor and grown with EXTEND; associative arrays need neither.
Q64. How do associative arrays work?
A: An associative array maps keys to values like a Java HashMap: elements are created on assignment, keys can be sparse, and VARCHAR2 keys enable name-based lookup caches. They cannot be stored in tables or (before 12c) used directly in SQL, and their iteration uses FIRST/NEXT rather than 1..COUNT when sparse.
DECLARE
TYPE t_sal_map IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
v_sal t_sal_map;
v_key VARCHAR2(30);
BEGIN
v_sal('KING') := 5000;
v_sal('SCOTT') := 3000;
v_key := v_sal.FIRST; -- iterate sparse keys
WHILE v_key IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(v_key || ' = ' || v_sal(v_key));
v_key := v_sal.NEXT(v_key);
END LOOP;
END;
/Q65. What is the difference between nested tables and varrays?
A: Varrays have a fixed maximum size declared at creation, stay dense (no gaps), and preserve element order; when stored in a table they are kept inline (or as a LOB if large), making them good for small, stable lists like phone numbers. Nested tables are unbounded, can be made sparse by DELETE(n), do not guarantee stored order, and are stored in a separate storage table — better for larger or frequently queried sets, since they support the TABLE() operator and set operators like MULTISET UNION.
Q66. What is BULK COLLECT and why is it faster?
A: BULK COLLECT fetches an entire result set (or batches of it) into collections in one SQL-to-PL/SQL context switch, instead of one switch per row — routinely a 10x+ speedup on large fetches. It works with SELECT INTO, FETCH, and the RETURNING clause. Note it never raises NO_DATA_FOUND; check collection.COUNT = 0 instead.
DECLARE
TYPE t_emps IS TABLE OF emp%ROWTYPE;
v_emps t_emps;
BEGIN
SELECT * BULK COLLECT INTO v_emps FROM emp WHERE deptno = 20;
FOR i IN 1 .. v_emps.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_emps(i).ename);
END LOOP;
END;
/Q67. Why should you use the LIMIT clause with BULK COLLECT?
A: An unlimited BULK COLLECT loads every row into PGA memory at once — on a 50-million-row table that can exhaust memory. FETCH ... BULK COLLECT INTO ... LIMIT n processes the set in fixed-size batches (100–1000 is the usual sweet spot). Classic bug: exit on %NOTFOUND before processing — the final partial batch is skipped; exit when COUNT = 0 or test %NOTFOUND after processing.
DECLARE
CURSOR c IS SELECT * FROM big_table;
TYPE t_rows IS TABLE OF big_table%ROWTYPE;
v_rows t_rows;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_rows LIMIT 500;
EXIT WHEN v_rows.COUNT = 0; -- NOT "EXIT WHEN c%NOTFOUND" here
process_batch(v_rows);
END LOOP;
CLOSE c;
END;
/Q68. What is FORALL and how does it differ from a FOR loop?
A: FORALL is not a loop — it sends one DML statement with the whole collection of bind values to the SQL engine in a single context switch, executing it once per element server-side. A FOR loop issuing DML per iteration switches contexts every row. FORALL allows exactly one DML statement and supports INDICES OF / VALUES OF for sparse collections.
DECLARE
TYPE t_ids IS TABLE OF emp.empno%TYPE;
v_ids t_ids;
BEGIN
SELECT empno BULK COLLECT INTO v_ids FROM emp WHERE deptno = 40;
FORALL i IN 1 .. v_ids.COUNT
UPDATE emp SET sal = sal * 1.08 WHERE empno = v_ids(i);
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated in one round trip');
END;
/Q69. How do you handle individual row failures in FORALL?
A: Add SAVE EXCEPTIONS: FORALL then continues past failing elements and, at the end, raises ORA-24381 once if anything failed. The SQL%BULK_EXCEPTIONS pseudo-collection holds each failure's ERROR_INDEX (which element) and ERROR_CODE, so you can log or retry precisely the bad rows.
DECLARE
e_bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
FORALL i IN 1 .. v_rows.COUNT SAVE EXCEPTIONS
INSERT INTO emp VALUES v_rows(i);
EXCEPTION
WHEN e_bulk_errors THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Row ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX ||
' failed: ORA-' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
END LOOP;
END;
/Q70. What are the main collection methods?
A: COUNT (elements present), EXISTS(n) (safe test that never raises for missing elements), FIRST/LAST (lowest/highest index), NEXT(n)/PRIOR(n) (sparse navigation), EXTEND[(n)] (grow nested tables/varrays), TRIM[(n)] (remove from end), DELETE[(n)] (remove all, one, or a range — leaves gaps in nested tables), and LIMIT (varray max size).
DECLARE
TYPE t_list IS TABLE OF VARCHAR2(20);
v t_list := t_list('A', 'B', 'C', 'D');
BEGIN
v.DELETE(2); -- sparse now: 1,3,4
DBMS_OUTPUT.PUT_LINE(v.COUNT); -- 3
DBMS_OUTPUT.PUT_LINE(v.LAST); -- 4
IF v.EXISTS(2) THEN NULL; END IF; -- FALSE, no exception
v.EXTEND; v(v.LAST) := 'E'; -- grow and assign
END;
/Q71. How do you query a PL/SQL collection with SQL?
A: Wrap the collection in the TABLE() operator (implicit since 12.2 — you can put the collection straight in the FROM clause) to treat it as a row source, join it to real tables, or use it in an IN subquery. Before 12c the type had to be a SQL-level object type (CREATE TYPE); 12c+ also allows package-declared types.
CREATE TYPE t_num_list AS TABLE OF NUMBER;
/
DECLARE
v_ids t_num_list := t_num_list(7369, 7788, 7839);
v_total NUMBER;
BEGIN
SELECT SUM(sal) INTO v_total
FROM emp
WHERE empno IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
DBMS_OUTPUT.PUT_LINE(v_total);
END;
/Q72. Why are context switches the key to PL/SQL bulk performance?
A: Every time execution crosses from the PL/SQL engine to the SQL engine (or back), Oracle must package bind values, switch runtime state, and return results — cheap once, expensive a million times. Row-by-row loops pay that cost per row ("slow-by-slow" processing); BULK COLLECT and FORALL amortize it to once per batch. The design hierarchy: do it in a single SQL statement if possible, then bulk PL/SQL, and only then row-by-row.
Sequences, Synonyms, Views & Materialized Views
Q73. What is a sequence and how do NEXTVAL and CURRVAL work?
A: A sequence is a schema object that generates unique numbers, safe under any concurrency because generation never blocks. seq.NEXTVAL returns and advances the next value; seq.CURRVAL returns the last value this session obtained and raises ORA-08002 if NEXTVAL has not been called first in the session. Sequence values are never rolled back — rollback creates gaps.
CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1 CACHE 100;
INSERT INTO orders(order_id, created_at)
VALUES (order_seq.NEXTVAL, SYSDATE);
-- child rows in the same session reuse the value
INSERT INTO order_items(order_id, item)
VALUES (order_seq.CURRVAL, 'Widget');Q74. What are identity columns and how do they compare to sequence + trigger?
A: Oracle 12c identity columns (GENERATED [ALWAYS | BY DEFAULT [ON NULL]] AS IDENTITY) attach a system-managed sequence directly to the column, replacing the old pattern of a sequence plus a BEFORE INSERT trigger — same guarantees, less code, and no trigger overhead. ALWAYS forbids user-supplied values; BY DEFAULT ON NULL fills in only when NULL is inserted. 12c also allows seq.NEXTVAL as a column DEFAULT.
CREATE TABLE customers (
id NUMBER GENERATED ALWAYS AS IDENTITY,
name VARCHAR2(100) NOT NULL
);
INSERT INTO customers(name) VALUES ('Acme'); -- id assigned automaticallyQ75. Why do sequences have gaps and what does CACHE do?
A: CACHE n preallocates n values in memory per instance so NEXTVAL avoids a disk update every call — essential for insert-heavy tables. Cached but unused values are lost on instance shutdown/crash, and rolled-back transactions also discard values, so gaps are normal and expected. In RAC, each node caches its own range, so values are not globally ordered unless you pay for ORDER; never use sequences where gap-free numbering is a legal requirement — that needs a serialized counter table.
Q76. What is a synonym and what is the difference between private and public synonyms?
A: A synonym is an alias for another object (table, view, sequence, procedure — even across a database link), decoupling code from schema names: CREATE SYNONYM emp FOR hr.employees;. A private synonym belongs to one schema; a public synonym is visible to all users. Resolution order is: own object, then private synonym, then public synonym — a reason public synonyms can cause surprising name capture and are used sparingly.
Q77. When is a view updatable, and what does WITH CHECK OPTION do?
A: A view is inherently updatable if it has no DISTINCT, GROUP BY, aggregate, set operator, or expressions on the modified columns, and for joins only the key-preserved table's columns may be changed; otherwise you need an INSTEAD OF trigger. WITH CHECK OPTION forbids DML through the view that would create rows the view itself cannot see. WITH READ ONLY blocks DML entirely.
CREATE OR REPLACE VIEW v_dept10 AS
SELECT empno, ename, sal, deptno FROM emp WHERE deptno = 10
WITH CHECK OPTION;
UPDATE v_dept10 SET deptno = 20 WHERE empno = 7839;
-- ORA-01402: view WITH CHECK OPTION where-clause violationQ78. What is the difference between a view and a materialized view?
A: A regular view is a stored query — no data, always current, full query cost on every access. A materialized view physically stores the result set, so reads are fast but the data is only as fresh as the last refresh, and it consumes storage plus refresh overhead. Use MVs for expensive aggregations/joins in reporting, and for replicating data across database links.
Q79. What refresh options do materialized views support?
A: Method: COMPLETE (rebuild from scratch), FAST (apply only changes, requires a materialized view log on each base table), or FORCE (fast if possible, else complete). Timing: ON COMMIT (refresh inside the committing transaction — strong freshness, slower commits, single-DB only) or ON DEMAND (via DBMS_MVIEW.REFRESH or the scheduler).
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE
(product_id, amount) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW mv_sales_by_product
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT product_id, SUM(amount) total, COUNT(*) cnt
FROM sales GROUP BY product_id;
EXEC DBMS_MVIEW.REFRESH('MV_SALES_BY_PRODUCT', 'C'); -- manual completeQ80. What is a materialized view log?
A: An MV log (snapshot log) is a change-tracking table Oracle maintains on a base table, recording the ROWIDs/keys and optionally column values of every changed row. Fast (incremental) refresh reads the log instead of rescanning the base table, then purges entries once all dependent MVs have consumed them. One log per base table serves all its MVs; a lagging or broken MV can make the log grow unbounded.
Q81. What is the MERGE statement?
A: MERGE performs UPSERT logic in one statement: it joins a source to a target and applies WHEN MATCHED THEN UPDATE (optionally with DELETE) and WHEN NOT MATCHED THEN INSERT. One MERGE replaces the racy "SELECT then INSERT-or-UPDATE" pattern and is far faster than row-by-row logic for synchronizing tables.
MERGE INTO emp t
USING emp_staging s
ON (t.empno = s.empno)
WHEN MATCHED THEN
UPDATE SET t.sal = s.sal, t.deptno = s.deptno
DELETE WHERE s.status = 'TERMINATED'
WHEN NOT MATCHED THEN
INSERT (empno, ename, sal, deptno)
VALUES (s.empno, s.ename, s.sal, s.deptno);Q82. What is query rewrite with materialized views?
A: With ENABLE QUERY REWRITE on the MV and query_rewrite_enabled set, the optimizer can transparently answer a query against the base tables by reading the (much smaller) materialized view instead — applications need no code change. Whether stale MV results are acceptable is controlled by QUERY_REWRITE_INTEGRITY (ENFORCED, TRUSTED, STALE_TOLERATED). This is the classic data-warehouse aggregation acceleration technique.
Analytic & Hierarchical Queries
Q83. What is the difference between analytic functions and aggregate functions?
A: Aggregates with GROUP BY collapse many rows into one per group; analytic functions (the OVER() clause) compute the same kinds of results without collapsing rows — every detail row stays and carries its group-level value alongside. PARTITION BY defines the group, ORDER BY plus a window frame define running/sliding calculations. Analytics are evaluated after WHERE/GROUP BY/HAVING, so you cannot filter on them directly — wrap in a subquery.
SELECT ename, deptno, sal,
AVG(sal) OVER (PARTITION BY deptno) AS dept_avg,
sal - AVG(sal) OVER (PARTITION BY deptno) AS diff_from_avg
FROM emp; -- all rows kept, each annotated with its dept averageQ84. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?
A: All three number rows within an ordered partition, differing on ties: ROW_NUMBER assigns unique consecutive numbers (ties broken arbitrarily), RANK gives equal rows the same rank and then skips (1,2,2,4), DENSE_RANK gives ties the same rank without gaps (1,2,2,3). Choose ROW_NUMBER for deduplication and pagination, DENSE_RANK for "Nth distinct value" problems.
SELECT ename, sal,
ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn, -- 1,2,3,4
RANK() OVER (ORDER BY sal DESC) AS rnk, -- 1,2,2,4
DENSE_RANK() OVER (ORDER BY sal DESC) AS drnk -- 1,2,2,3
FROM emp;Q85. How do you find the Nth highest salary in each department?
A: Rank within each department with DENSE_RANK (so salary ties count as one level) and filter in an outer query — analytic results can't appear in the same query's WHERE clause.
SELECT deptno, ename, sal
FROM (
SELECT deptno, ename, sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rnk
FROM emp
)
WHERE rnk = 3; -- 3rd highest salary per departmentQ86. What do LAG and LEAD do?
A: LAG(col, n, default) reads a value from n rows before the current row in the window order; LEAD reads n rows after — no self-join needed. They are the standard tool for month-over-month deltas, gap detection in sequences, and session analysis.
SELECT month, revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_rev,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
LEAD(month) OVER (ORDER BY month) AS next_month
FROM monthly_sales;Q87. What is the windowing clause (ROWS/RANGE BETWEEN)?
A: The frame clause restricts an ordered analytic to a sliding subset: ROWS counts physical rows, RANGE uses logical value distance from the current row's sort key. Crucial default trap: with ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peers of the current value — running totals over duplicate keys jump unless you specify ROWS.
SELECT trade_date, amount,
SUM(amount) OVER (ORDER BY trade_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
AVG(amount) OVER (ORDER BY trade_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM trades;Q88. How do CONNECT BY hierarchical queries work?
A: START WITH picks the root rows, and CONNECT BY PRIOR defines the parent-child join that Oracle follows recursively. PRIOR marks which side refers to the parent row: PRIOR empno = mgr walks down from managers to reports; flipping it walks up. ORDER SIBLINGS BY sorts within each level without destroying the hierarchy.
SELECT LPAD(' ', 2*(LEVEL-1)) || ename AS org_chart, LEVEL
FROM emp
START WITH mgr IS NULL -- root: the president
CONNECT BY PRIOR empno = mgr -- child.mgr = parent.empno
ORDER SIBLINGS BY ename;Q89. What are LEVEL, SYS_CONNECT_BY_PATH, and CONNECT_BY_ROOT?
A: LEVEL is a pseudocolumn giving the depth (1 for roots). SYS_CONNECT_BY_PATH(col, '/') builds the full path from root to the current row ('/KING/JONES/SCOTT'). CONNECT_BY_ROOT col returns the root ancestor's value on every row, and CONNECT_BY_ISLEAF is 1 for rows with no children — together they answer most org-chart and bill-of-materials questions.
Q90. How do you handle cycles in hierarchical data?
A: If the data contains a loop (A manages B, B manages A), a plain CONNECT BY raises ORA-01436: CONNECT BY loop in user data. Adding NOCYCLE makes Oracle stop expanding the looping branch instead of failing, and the CONNECT_BY_ISCYCLE pseudocolumn flags (=1) the row whose child would close the loop, letting you locate and fix the bad data.
Q91. What is the recursive WITH clause and when would you prefer it over CONNECT BY?
A: Recursive subquery factoring (11gR2+) is the ANSI-standard alternative: an anchor member UNION ALL'd with a recursive member that references the CTE itself. Prefer it when you need portability to other databases, need to carry computed columns through the recursion, or need multi-column recursion logic awkward in CONNECT BY; CONNECT BY remains terser for simple trees.
WITH org (empno, ename, mgr, lvl) AS (
SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL -- anchor
UNION ALL
SELECT e.empno, e.ename, e.mgr, o.lvl + 1 -- recursive
FROM emp e JOIN org o ON e.mgr = o.empno
)
SEARCH DEPTH FIRST BY ename SET ord
SELECT LPAD(' ', 2*(lvl-1)) || ename FROM org ORDER BY ord;Performance Tuning & Java/JDBC Integration
Q92. How do you generate and read an execution plan?
A: EXPLAIN PLAN FOR shows the optimizer's predicted plan; DBMS_XPLAN.DISPLAY_CURSOR shows the plan actually used from the cursor cache (add hint GATHER_PLAN_STATISTICS and format 'ALLSTATS LAST' to compare estimated vs actual rows — the key to finding bad cardinality estimates). Read plans innermost-first; watch for unexpected TABLE ACCESS FULL on large tables, wrong join methods, and estimate/actual mismatches.
EXPLAIN PLAN FOR
SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- actual plan of the last executed statement
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));Q93. What are optimizer hints and when should you use them?
A: Hints are directives embedded in a special comment (/*+ ... */ right after the SQL verb) that constrain the optimizer: access path (INDEX, FULL), join method (USE_NL, USE_HASH), join order (LEADING), parallelism (PARALLEL), and more. Treat them as a last resort after fixing statistics and predicates — hard-coded hints go stale as data changes; invalid hints are silently ignored, which hides typos.
SELECT /*+ LEADING(d) USE_NL(e) INDEX(e emp_deptno_ix) */
e.ename, d.dname
FROM dept d JOIN emp e ON e.deptno = d.deptno
WHERE d.loc = 'DALLAS';Q94. What is the difference between B-tree and bitmap indexes?
A: B-tree indexes (the default) suit high-cardinality columns and OLTP: point lookups and range scans with row-level locking. Bitmap indexes store a bitmap per distinct value, excel at low-cardinality columns combined with AND/OR predicates in data warehouses, but a single row update can lock a whole bitmap range — deadly under concurrent DML, so never use them in OLTP. Also know: B-tree indexes do not store entries where all indexed columns are NULL, so WHERE col IS NULL can't use a normal single-column B-tree index.
Q95. Why might Oracle ignore an index, and what is a function-based index?
A: Common causes: applying a function or implicit type conversion to the indexed column (UPPER(ename), comparing VARCHAR2 to a number), leading-wildcard LIKE, stale statistics making the full scan look cheaper, or the query genuinely reading too large a fraction of the table. A function-based index indexes the expression itself, so the transformed predicate can use it.
-- this predicate cannot use a plain index on ename:
SELECT * FROM emp WHERE UPPER(ename) = 'SCOTT';
CREATE INDEX emp_upper_name_ix ON emp (UPPER(ename)); -- now it can
-- implicit conversion trap: empno is NUMBER, this disables its index
SELECT * FROM emp WHERE TO_CHAR(empno) = '7788'; -- avoidQ96. How do optimizer statistics work and how do you gather them?
A: The cost-based optimizer chooses plans using statistics: row counts, block counts, column distinct values, and histograms for skewed data. Oracle gathers them in an automatic maintenance window, but after large loads you should gather immediately with DBMS_STATS; stale or missing stats are the number one cause of bad plans. Never use the old ANALYZE command for optimizer stats.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'EMP',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE); -- indexes too
END;
/Q97. What is the difference between a hard parse and a soft parse, and how does JDBC affect it?
A: A hard parse builds a brand-new cursor: syntax/semantic check plus full optimization — CPU-heavy and serialized on shared pool latches. A soft parse finds an existing cursor for identical SQL text and reuses its plan. Java code that concatenates values into SQL strings makes every statement unique, forcing hard parses and bloating the shared pool; PreparedStatement with bind parameters produces one shared cursor, and enabling the driver's implicit statement cache skips even the soft-parse round trip.
Q98. How do you call a stored procedure from Java?
A: Use CallableStatement with the JDBC escape syntax {call proc(?, ?)}, set IN parameters, register OUT parameter types before execution, and read OUT values after execute(). Spring's SimpleJdbcCall or JPA's @NamedStoredProcedureQuery wrap the same mechanism.
try (CallableStatement cs =
conn.prepareCall("{call apply_raise(?, ?, ?, ?)}")) {
cs.setInt(1, 7788); // IN p_empno
cs.setInt(2, 10); // IN p_pct
cs.registerOutParameter(3, Types.NUMERIC); // OUT p_new_sal
cs.registerOutParameter(4, Types.VARCHAR); // IN OUT p_message
cs.setString(4, "SCOTT");
cs.execute();
BigDecimal newSal = cs.getBigDecimal(3);
String msg = cs.getString(4);
}Q99. How do you consume a REF CURSOR from JDBC?
A: Register the OUT parameter as OracleTypes.CURSOR, execute, and cast getObject() to a ResultSet — the standard pattern for returning query results from PL/SQL to Java without shipping SQL text to the client. Close the ResultSet (and statement) promptly, since each open ref cursor counts against OPEN_CURSORS.
try (CallableStatement cs = conn.prepareCall("{call get_emps(?, ?)}")) {
cs.setInt(1, 20);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
try (ResultSet rs = (ResultSet) cs.getObject(2)) {
while (rs.next()) {
System.out.println(rs.getString("ename") + ": " + rs.getInt("sal"));
}
}
}Q100. What are the key techniques for high-throughput inserts/updates from Java into Oracle?
A: Use PreparedStatement with addBatch()/executeBatch() (batches of 100–1000) so many rows travel in one round trip — the JDBC counterpart of FORALL; also set an appropriate fetchSize for reads (the default 10 cripples large result sets). Pool connections (HikariCP/UCP), commit per batch rather than per row, and for very heavy transformation logic consider passing arrays to a stored procedure that uses FORALL server-side.
conn.setAutoCommit(false);
try (PreparedStatement ps =
conn.prepareStatement("INSERT INTO emp(empno, ename, sal) VALUES (?, ?, ?)")) {
int n = 0;
for (Employee e : employees) {
ps.setInt(1, e.getId());
ps.setString(2, e.getName());
ps.setBigDecimal(3, e.getSalary());
ps.addBatch();
if (++n % 500 == 0) { ps.executeBatch(); conn.commit(); }
}
ps.executeBatch();
conn.commit();
}
Post a Comment
Add