Oracle-Specific SQL Features
Domain 14 tests Oracle extensions and idioms that appear throughout the exam: DUAL, ROWNUM, ROWID, NVL, DECODE, MERGE, and row-limiting behavior. You must know when Oracle-specific logic differs from standard SQL expectations.
Core rules
- DUAL is a one-row table used for expressions and function tests. SELECT SYSDATE FROM DUAL is valid Oracle syntax.
- ROWNUM is assigned to rows as they are returned by a query, before ORDER BY in a simple query. To get top-N by a sorted value, sort in an inline view and filter ROWNUM outside it, or use FETCH FIRST.
- ROWID is the physical address of a row. It is unique within a table and can change after operations such as table reorganization.
- NVL replaces NULL with a value. DECODE compares an expression to search values and returns a result; it treats NULLs as equal for matching. CASE is more flexible for ranges.
- MERGE combines insert and update logic based on a join condition. It is useful for upserts but must be written carefully to avoid unintended updates.
Worked example
SELECT employee_id, salary FROM (SELECT employee_id, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;
The inline view sorts employees by descending salary. The outer query then applies ROWNUM to the sorted result, so it returns the five highest salaries. Filtering ROWNUM in the same query as ORDER BY would assign numbers before the sort and produce the wrong top five.
Traps
ROWNUM = 2 in a WHERE clause returns no rows because numbering starts at 1 and is assigned only to rows that pass the predicate. NVL evaluates its arguments; COALESCE can short-circuit. DECODE is equality-based and has type conversion rules. MERGE can update more rows than intended if the join is not unique.
Key points
- ROWNUM is assigned before ORDER BY in a simple query.
- Use an inline view or FETCH FIRST for top-N.
- ROWID is a physical row address.
- DECODE handles equality and treats NULLs as equal.
- MERGE needs a carefully controlled join condition.