Oracle 1Z0-116 common mistakes
Oracle 1Z0-116 · Understand the traps and how to avoid them.
Uses a column alias inside WHERE (SELECT salary*12 AS ann_sal ... WHERE ann_sal > 100000) and, after ORA-00904, 'fixes' it by moving the predicate into HAVING; in the same session writes SELECT DISTINCT department_id, job_id FROM employees ORDER BY last_name and is baffled by ORA-01791.
ORDER BY is the one clause that can see a SELECT-list alias, so candidates generalise that aliases are globally visible. In fact the SELECT list is evaluated after WHERE/HAVING, and DISTINCT restricts ORDER BY to items that appear in the select list.
What to do instead
Repeat the expression in the predicate (WHERE salary*12 > 100000) or wrap the query in an inline view and filter the alias from outside. With DISTINCT, sort only on select-list columns. Memorise the logical order FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY; only ORDER BY can use aliases.
Treats NVL, COALESCE, NULLIF and DECODE as interchangeable: calls NVL(commission_pct,'None') on a NUMBER column, expects COALESCE(1,'x') to return a string, or expects DECODE to treat NULL as different from NULL.
NVL converts its second argument to the datatype of its first (raising ORA-01722 when that is impossible), whereas COALESCE returns the datatype with the highest precedence among all arguments. DECODE considers two NULLs equal, while CASE uses normal NULL comparison.
What to do instead
Choose by type rule: NVL(expr,default) forces the default into expr's type, so wrap expr in TO_CHAR when you need text output; use COALESCE or an explicit CAST for mixed types. For null-safe equality use DECODE or add explicit IS NULL branches to CASE. Remember NULLIF(a,b) returns NULL when a=b and a otherwise.
Compares dates to literal strings (WHERE hire_date >= '01-JUN-2007') or calls TO_DATE('12/06/07','DD/MM/YY'), then gets wrong rows, ORA-01858, or results that change when the same script is run on another machine.
This relies on implicit conversion through NLS_DATE_FORMAT, which is environment-dependent. YY assumes the current century while RR applies a 50-year sliding window, and a two-digit year paired with a mismatched format model simply fails.
What to do instead
Always give the full format model with a 4-digit year: TO_DATE('2007-06-01','YYYY-MM-DD'), or the ANSI literal DATE '2007-06-01'. Use TRUNC(hire_date) when comparing to a day, and avoid converting a DATE column to a string in a predicate (it can disable index use).
Writes AVG(commission_pct) or COUNT(commission_pct) and expects NULL rows to be included, filters groups with WHERE COUNT(*) > 5, or adds last_name to the SELECT list of a query that groups by department_id.
Every group function except COUNT(*) ignores NULLs, so the average denominator and the count silently exclude them. WHERE filters rows before grouping, so no aggregate is available there, and any non-aggregated column must appear in GROUP BY (ORA-00979).
What to do instead
Use COUNT(*) for row counts. Decide deliberately whether NULLs belong in the average: AVG(NVL(x,0)) and NVL(AVG(x),0) produce different numbers. Move aggregate predicates to HAVING, and group by every non-aggregated select-list column or wrap it in an aggregate such as MAX(last_name).
Writes ... FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Sales' and cannot see why departments with no employees vanish; with the old syntax, writes WHERE e.dept_id(+) = d.dept_id, putting (+) on the wrong side.
A WHERE predicate on the null-supplying table is applied after the outer join and throws away the NULL-extended rows, silently turning the outer join back into an inner join. The (+) marker belongs on the table that supplies the missing rows, which is the opposite of what most candidates expect.
What to do instead
Put the filter on the outer table in the ON clause (ON e.dept_id = d.dept_id AND d.dept_name = 'Sales') to keep the outer rows, or add OR d.dept_name IS NULL in WHERE. In old syntax use WHERE e.dept_id = d.dept_id(+) - (+) on the optional side, and never on both sides.
Writes WHERE department_id NOT IN (SELECT department_id FROM employees WHERE job_id = 'IT_PROG') and gets zero rows even though departments with no IT programmers exist; the candidate then blames the data or switches to <> by trial and error.
NOT IN expands to a chain of <> comparisons joined by AND, and any comparison with NULL evaluates to UNKNOWN, so a single NULL returned by the subquery makes the predicate impossible to satisfy. This is one of the few constructs that fails silently.
What to do instead
Either add WHERE department_id IS NOT NULL to the subquery, or use NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.job_id = 'IT_PROG'), which is NULL-safe and typically performs better. Apply the same thinking to IN versus EXISTS when the inner column is nullable.
Combines two queries with a set operator when the select lists differ in column count or datatype (ORA-01789/ORA-01790), attaches ORDER BY to the first branch, or assumes MINUS returns 'rows different in either direction'.
Set operators align columns by position, not by name, and require matching counts and compatible datatypes. Only one ORDER BY may appear, at the very end of the whole statement, and MINUS is directional: it subtracts the second query from the first, so swapping the branches changes the result.
What to do instead
Pad the shorter select list with NULL or CAST to align types and positions; the column names come from the first branch. Put a single ORDER BY after the last branch. Use UNION ALL unless duplicate removal is required, since UNION, INTERSECT and MINUS all force a sort, and check the precedence of INTERSECT over UNION/MINUS.
Runs UPDATE ...; COMMIT; and then tries ROLLBACK TO SAVEPOINT sp to undo it, or expects the whole transaction to be undone when the third of four statements fails with ORA-00001.
COMMIT ends the transaction and destroys every savepoint. A statement that fails is rolled back only to its own implicit statement-level savepoint, so earlier successful DML remains pending and later statements still see it.
What to do instead
Set the savepoint before the DML you may want to undo, then ROLLBACK TO sp (never COMMIT) and continue. Remember that DDL such as CREATE, ALTER, TRUNCATE, GRANT and even a normal exit from SQL*Plus issues an implicit commit of pending DML. For MERGE, note that WHEN MATCHED THEN UPDATE cannot modify a column referenced in the ON clause (ORA-38104).
Runs TRUNCATE TABLE to remove some rows and then ROLLBACK, adds a NOT NULL column with no default to a populated table, or drops a column that a constraint depends on and gets an error.
TRUNCATE is DDL: it commits implicitly, cannot be rolled back, resets the high-water mark and is not row-selective. A NOT NULL column with no default cannot be added to a table that already holds rows (ORA-01758), and a column referenced by a constraint cannot be dropped until the constraint is gone.
What to do instead
Use DELETE with a WHERE clause when you need selective or rollback-able removal. Add the column with a DEFAULT (on 11g and later this is stored in the dictionary, so it is fast) and then populate as needed. Drop dependent constraints first or use ALTER TABLE ... DROP COLUMN ... CASCADE CONSTRAINTS; for very large tables prefer DROP COLUMN ... UNUSED then drop unused columns. Use DROP TABLE ... PURGE deliberately - it bypasses the recycle bin and therefore FLASHBACK TABLE.
Creates a sequence and then selects seq.CURRVAL in a fresh session (ORA-08002), or expects sequence numbers to be contiguous; alternatively creates a view WITH CHECK OPTION and expects to be able to update a row so that it no longer satisfies the view's WHERE clause.
CURRVAL is session state, defined only after NEXTVAL has been called in that same session. Sequences are not transactional and use caching, so rollbacks, instance restarts and cache loss all leave gaps. CHECK OPTION exists precisely to reject DML that would make a row invisible through the view (ORA-01402).
What to do instead
Reference seq.NEXTVAL directly in the INSERT, or call NEXTVAL once in the session before CURRVAL, and never rely on gap-free numbering. Before writing DML against a view, confirm it is key-preserved and free of group functions, DISTINCT, GROUP BY, UNION and rownum; drop CHECK OPTION or write DML that keeps rows inside the view predicate. Also remember that IS NULL predicates cannot use a normal B-tree index.