Oracle 1Z0-182 common mistakes
Oracle 1Z0-182 · Understand the traps and how to avoid them.
Writing WHERE to filter on an aggregate, e.g. SELECT dept_id, AVG(salary) FROM employees WHERE AVG(salary) > 5000 GROUP BY dept_id; and expecting it to run.
Candidates learn WHERE first and reach for it by reflex. WHERE is evaluated before rows are grouped, so no group value exists yet; Oracle raises ORA-00934 (group function is not allowed here). The mental model of clause evaluation order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY) is not yet automatic.
What to do instead
Memorise the rule: row-level conditions go in WHERE, group-level conditions go in HAVING. Rewrite the example as ... GROUP BY dept_id HAVING AVG(salary) > 5000. When a query mixes both, place the row filter in WHERE (so fewer rows are grouped) and the aggregate filter in HAVING. Practise classifying each predicate as 'per row' or 'per group' before writing SQL.
Assuming NULL behaves like an ordinary value: writing WHERE commission = NULL, expecting NULL to sort in the middle, or expecting COUNT(column) to include NULL rows.
Three-valued logic (TRUE/FALSE/UNKNOWN) is counter-intuitive and rarely practised. Any comparison with NULL yields UNKNOWN, so = NULL matches nothing silently instead of raising an error. Candidates also confuse COUNT(*) with COUNT(expr), and are surprised that AVG ignores NULLs, changing the denominator.
What to do instead
Use IS NULL / IS NOT NULL, never = NULL. Remember COUNT(*) counts rows, COUNT(col) counts non-NULL values, and AVG/SUM skip NULLs. Use NVL(col, 0) or COALESCE when NULLs must participate in arithmetic. For sorting, learn Oracle's default NULLS LAST for ASC and NULLS FIRST for DESC, and override explicitly with NULLS FIRST/LAST.
Writing an inner join and then filtering the outer-joined table in WHERE, e.g. employees e LEFT JOIN departments d ... WHERE d.dept_name = 'Sales', and reporting that the outer join 'does not work'.
Candidates do not realise that a WHERE predicate on the null-supplying side discards the NULL-extended rows, silently converting the outer join into an inner join. The syntax is correct, so the error is logical and produces no exception, only wrong row counts.
What to do instead
Put the restriction in the ON clause (ON e.dept_id = d.dept_id AND d.dept_name = 'Sales') when unmatched rows must be preserved, or use WHERE d.dept_name = 'Sales' OR d.dept_id IS NULL. Before running, ask: 'which table must keep its unmatched rows?' and ensure no WHERE predicate on that side rejects NULLs.
Confusing the order of operations in a correlated subquery versus a non-correlated one, or using NOT IN against a subquery that can return NULL, and getting zero rows.
NOT IN is equivalent to a chain of <> comparisons; if the subquery returns a single NULL, every comparison is UNKNOWN and the whole predicate is never TRUE, so the result is empty with no error. Candidates also assume a subquery runs once, missing the per-row correlation that makes it slow or logically dependent on the outer alias.
What to do instead
Prefer NOT EXISTS (or NOT IN with a WHERE col IS NOT NULL guard) when the subquery column is nullable. Verify correlation by checking whether the subquery references an outer-table alias; if it does, it executes per candidate row. Test with a deliberately NULL-containing subquery to confirm behaviour.
Mixing up TRUNCATE, DELETE and DROP: expecting TRUNCATE to be rollback-able, expecting DELETE to reset the high-water mark, or expecting DROP to leave the table structure intact.
All three remove data, so candidates treat them as interchangeable. The differences that matter (DML vs DDL, undo generation, WHERE support, implicit commit, segment release) are exactly what the exam tests.
What to do instead
Memorise a three-line contrast: DELETE is DML, can have WHERE, is transactional and rollback-able, fires triggers. TRUNCATE is DDL, removes all rows, cannot be rolled back, resets the high-water mark, no triggers. DROP is DDL and removes the object definition entirely (to the recycle bin unless PURGE). Choose based on whether partial removal, rollback, or object survival is required.
Writing a multi-table INSERT or MERGE that fails on duplicate keys, or expecting MERGE to update rows that the ON condition does not match.
Candidates assume MERGE is a generic upsert that handles any overlap. In fact MERGE requires the ON condition to match each target row at most once; if the source has duplicate rows for one target row, Oracle raises ORA-30926 (unable to get a stable set of rows). They also forget that MERGE cannot update a column named in the ON clause.
What to do instead
Deduplicate the source (via a subquery with GROUP BY, DISTINCT or ROW_NUMBER) before merging. Keep the ON clause limited to join keys, not columns you intend to update. Test with a source containing duplicates to see the error, then fix it. For multi-table INSERT, verify each WHEN condition is mutually exclusive or rely on unconditional ALL/FIRST semantics.
Creating a FOREIGN KEY on a column whose parent key has no unique constraint, or expecting a CHECK constraint to enforce a rule across rows or against another table.
The referential integrity rules (parent must have a PRIMARY KEY or UNIQUE constraint) are stated once and then forgotten. Candidates also over-trust CHECK, which is row-local and cannot query other tables; and they forget that adding a constraint to a populated table fails unless existing data satisfies it or the constraint is added with ENABLE NOVALIDATE.
What to do instead
Before adding a foreign key, confirm the parent column carries PRIMARY KEY or UNIQUE. For existing data, add the constraint with ENABLE NOVALIDATE or clean the data first. Use CHECK only for single-row predicates; use triggers or application logic for cross-row rules. Remember ON DELETE CASCADE vs SET NULL changes child rows automatically.
Assuming a view stores data, that a simple view is always updatable, or that WITH CHECK OPTION is the same as WITH READ ONLY.
Views are stored queries, not copies. Updatability depends on rules (no joins with certain features, no DISTINCT, no group functions, no set operators, no subqueries in the select list). WITH CHECK OPTION restricts DML through the view to rows the view can see; WITH READ ONLY blocks DML entirely. Candidates conflate the two because both are view options.
What to do instead
State the rule: a view is updatable only if it maps to one base table without DISTINCT, group functions, GROUP BY, set operators, or a select-list subquery. Use WITH CHECK OPTION when DML through the view must stay visible through the view; use WITH READ ONLY to forbid DML. To persist results, use a materialized view, not a regular view.
Granting privileges and then being unable to access the object, or forgetting that a role's privileges are unavailable inside a definer's-rights stored program.
Privileges can be granted directly or via a role, and roles are disabled inside definer's-rights PL/SQL. Candidates also confuse system privileges (CREATE TABLE) with object privileges (SELECT ON table), and forget that granting to a role does not help inside a procedure.
What to do instead
Distinguish system privileges (no ON clause) from object privileges (require ON object). Grant object privileges directly to the schema that owns the definer's-rights program, or make the program invoker's rights. Test access as the actual user, not as SYSDBA, and remember REVOKE ... FROM role does not affect direct grants.
Leaving a transaction open after DML, then being surprised that another session cannot see the change, or that a DDL statement committed it.
Oracle transactions begin implicitly with the first DML and end only with COMMIT, ROLLBACK, or an implicit commit from DDL. Candidates coming from autocommit environments assume each statement commits. They also miss that a failed statement rolls back only that statement, not the whole transaction.
What to do instead
Adopt the habit: after DML, explicitly COMMIT or ROLLBACK. Expect DDL (CREATE, ALTER, DROP, TRUNCATE, GRANT) to commit the pending transaction implicitly. Use SAVEPOINT plus ROLLBACK TO SAVEPOINT for partial rollback. Verify visibility from a second session before assuming data is lost.