Conversion Functions and Conditional Expressions
Domain 4 tests explicit and implicit data conversion plus conditional logic with CASE, DECODE, NVL, NULLIF, and COALESCE. The exam rewards knowing when Oracle converts automatically and why relying on that is risky.
Core rules
- Explicit conversion uses TO_CHAR, TO_DATE, TO_NUMBER, TO_TIMESTAMP, and related functions. Format models control output and parsing.
- Implicit conversion depends on context and NLS settings. For example, comparing a character column to a number may convert the character column, which can affect indexes and produce errors for nonnumeric values.
- TO_CHAR with numeric formats uses elements such as 9, 0, comma, and decimal point. TO_CHAR with date formats uses YYYY, MM, DD, HH24, MI, and SS.
- The RR format model interprets two-digit years relative to the current century; YY uses the current century more directly. Prefer four-digit YYYY.
- CASE is an expression. Searched CASE evaluates conditions in order; simple CASE compares one expression to values. DECODE provides equality-based conditional conversion and treats NULLs as equal for matching.
- COALESCE returns the first non-NULL value. NULLIF returns NULL when two values are equal. NVL2 returns one value when the first argument is non-NULL and another when it is NULL.
Worked example
SELECT employee_id, TO_CHAR(hire_date,'YYYY-MM') AS hire_month, CASE WHEN salary >= 10000 THEN 'High' WHEN salary >= 5000 THEN 'Mid' ELSE 'Low' END AS band FROM employees;
TO_CHAR formats the date as year and month text. CASE checks the highest threshold first, so a salary of 12000 gets High, not Mid. The ELSE covers salaries below 5000 and NULL salaries, because NULL fails both comparisons.
Traps
Omitting a format in TO_DATE can depend on NLS_DATE_FORMAT. Using RR when the business meaning requires a fixed century can misdate records. DECODE cannot evaluate ranges; CASE can. A CASE without ELSE returns NULL for unmatched rows. Implicit conversion on a column can disable an index.
Key points
- Prefer explicit TO_CHAR, TO_DATE, TO_NUMBER.
- RR vs YY changes century interpretation.
- CASE supports ranges; DECODE supports equality.
- CASE without ELSE yields NULL.
- Implicit conversion can hurt indexes.