Oracle 1Z0-183 common mistakes
Oracle 1Z0-183 · Understand the traps and how to avoid them.
Names a PL/SQL variable the same as a column referenced in an embedded SQL statement (e.g. declare `ename VARCHAR2(20)` and then write `WHERE ename = ename`), expecting the local variable to be used on both sides.
PL/SQL name resolution inside a SQL statement prefers the table column over the local variable, so the predicate silently becomes WHERE ename = ename and returns every row instead of filtering. It compiles cleanly, so the defect only shows up as wrong data.
What to do instead
Adopt a naming convention that separates identifiers (v_ename, p_ename for parameters), or qualify the variable explicitly with a block label (<<main>> DECLARE v_sal NUMBER; ... WHERE sal = main.v_sal). Always re-read embedded SQL predicates to confirm which side is the variable.
Declares a variable as `v_count NUMBER NOT NULL;` with no initializer and expects a runtime NULL-assignment error.
Candidates conflate NOT NULL on a variable with a NOT NULL table constraint. For a variable that is not a constant, Oracle requires it to be initialized at declaration time, so the block fails to compile with PLS-00218.
What to do instead
Either initialize at declaration (v_count NUMBER NOT NULL := 0;) or declare it as a constant (c_count CONSTANT NUMBER NOT NULL := 0;). Recognize PLS-00xxx errors as compile-time, not runtime, failures.
Assumes that returning from a stored procedure, or exiting a block normally, commits the transaction - and never issues an explicit COMMIT after an INSERT/UPDATE/DELETE executed inside PL/SQL.
PL/SQL has no implicit commit on block or subprogram exit; only DDL and certain statements commit implicitly. The session ends up holding uncommitted rows or rolling back on disconnect.
What to do instead
Place COMMIT/ROLLBACK deliberately in the calling layer (or at a documented transaction boundary inside the procedure). Remember that an unhandled exception rolls back only the current statement, not the whole transaction, so cleanup logic must roll back explicitly.
Writes `IF v_mgr_id <> 100 THEN ...` (or `IF v_flag != 'Y'`) believing the branch executes when the column is NULL.
Any comparison with NULL yields UNKNOWN, and IF treats UNKNOWN as false. The candidate is thinking in two-valued logic and misreads the requirement.
What to do instead
Test NULL explicitly (IF v_mgr_id IS NULL OR v_mgr_id <> 100), or use NVL/DECODE to collapse NULLs to a known value before the comparison. Also avoid relying on AND/OR short-circuit side effects, since evaluation order is not guaranteed.
Fills a nested table or associative array, deletes some elements, then loops `FOR i IN 1 .. my_tab.COUNT` and indexes `my_tab(i)` directly.
DELETE leaves holes, so COUNT no longer matches the highest subscript; the loop raises ORA-01403 (no data found) or hits a nonexistent element. Candidates treat collections as dense arrays like in other languages.
What to do instead
Iterate with the collection methods: i := my_tab.FIRST; WHILE i IS NOT NULL LOOP ... i := my_tab.NEXT(i); END LOOP;. Use EXISTS before referencing an element, and remember EXTEND is needed before assigning to a new index of a nested table/varray.
Tests cursor attributes such as %FOUND, %NOTFOUND or %ROWCOUNT before the first FETCH, or after a cursor FOR loop has finished, to decide how many rows were processed.
Before the first FETCH the attributes are undefined (typically NULL), and after a cursor FOR loop terminates the cursor is closed and its attributes are no longer reliable. Neither case raises an error, so the wrong branch silently executes.
What to do instead
Fetch first, then test; keep an explicit counter incremented inside the loop; or use SQL%ROWCOUNT immediately after an implicit DML/SELECT INTO statement, where it is well defined.
Ends exception sections with `WHEN OTHERS THEN NULL;` (or logs nothing) to make the block 'robust'.
This suppresses the error and the calling program continues with inconsistent state; DBMS_OUTPUT-based diagnostics are lost and the failure surfaces far from its cause.
What to do instead
Handle only the exceptions you can act on (NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, named user exceptions), and in WHEN OTHERS log SQLCODE/SQLERRM and the backtrace with DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and then re-RAISE. Remember WHEN OTHERS must be the last handler.
In a package body, calls a private procedure from a subprogram declared earlier in the body without providing a forward declaration.
PL/SQL compiles the body top-down, so a call to a subprogram that has not yet been declared fails with PLS-00313. Candidates assume the body is parsed as a whole like Java or C#.
What to do instead
Add a forward declaration (the subprogram specification terminated by a semicolon) above the caller, or reorder the body so callees precede callers. Also remember two overloads cannot differ only in return type or in parameter mode.
Calls DBMS_OUTPUT.PUT_LINE in a long loop and expects every line to appear in SQL*Plus / SQL Developer, or reads DBMS_OUTPUT output without having enabled the buffer.
DBMS_OUTPUT writes to a session buffer that must be enabled and sized; output beyond the default buffer is discarded with ORA-20000/ORU-10027 or simply never displayed.
What to do instead
SET SERVEROUTPUT ON SIZE UNLIMITED (or DBMS_OUTPUT.ENABLE(n)) before running, and call DBMS_OUTPUT.GET_LINES to drain the buffer if consuming it programmatically. For file output use UTL_FILE, which also requires a DIRECTORY object and the appropriate privileges.
Writes a row-level trigger on a table that queries or modifies that same table inside the trigger body.
This produces ORA-04091 (table is mutating) at runtime; the trigger compiles fine, so the error appears only on the first DML. Candidates assume triggers can freely read their own subject table.
What to do instead
Use a compound trigger (or the packaged-collection pattern) to capture :NEW/:OLD values in the BEFORE STATEMENT/FOR EACH ROW sections and apply the logic in AFTER STATEMENT. Also remember a trigger cannot issue COMMIT and that :OLD is NULL in an INSERT trigger.
Stores mutable state in package-level variables and is surprised when a session suddenly gets ORA-04068 or sees reset values after a dependent object is recompiled.
Package state is per session; recompiling the package spec (or a dependency) marks it invalid and discards instantiated state, so sessions holding state fail on next reference. Candidates mistake package variables for persistent or global storage.
What to do instead
Avoid package-level state for anything that must survive recompilation; persist in tables instead. Where state is genuinely needed, handle ORA-04068/ORA-06508 in the caller and re-invoke. Also check USER_DEPENDENCIES and recompile with DBMS_UTILITY.COMPILE_SCHEMA after changing base objects.