1Z0-071 Exam Prep
Chapters 2, 3 & 5
Volume 1
Some of the most reliable exam marks come from topics that look minor but have precise rules. Implicit data type conversion, ROWNUM behaviour, the NVL function family, and Oracle-specific pseudocolumns each appear in exam questions that candidates lose to overconfidence — the rules seem obvious until the edge case arrives.
This post covers the conversion rules, ROWNUM traps, and several Oracle-specific features that appear across multiple chapters but are easy to overlook in preparation.
In this post
- Implicit data type conversion — what Oracle does automatically
- When implicit conversion fails
- Explicit conversion — always safer
- ROWNUM — how it works and its traps
- ROWNUM with ORDER BY — the ordering trap
- Top-N queries using ROWNUM correctly
- ROWID — the physical row address
- Substitution variables — & and &&
- Oracle date default format and NLS settings
- Quick reference
- Practice questions
01 — Implicit data type conversion — what Oracle does automatically
Oracle can automatically convert values between certain data types without you writing an explicit conversion function. The exam tests which conversions happen automatically and which require explicit functions.
| From | To | Implicit? | Example |
|---|---|---|---|
| VARCHAR2 | NUMBER | Yes — if the string is a valid number | WHERE salary = '5000' works |
| VARCHAR2 | DATE | Yes — if the string matches NLS date format | WHERE hire_date = '01-JAN-24' |
| NUMBER | VARCHAR2 | Yes | 'Emp: ' || 100 works |
| DATE | VARCHAR2 | Yes — uses NLS date format | 'Date: ' || SYSDATE works |
| VARCHAR2 | NUMBER | Fails if string is not a valid number | WHERE salary = 'abc' raises error |
-- Implicit conversion examples SELECT * FROM employees WHERE employee_id = '100'; -- Oracle converts '100' to NUMBER 100 — works fine SELECT * FROM employees WHERE hire_date = '01-JAN-20'; -- Oracle converts the string to a DATE using NLS_DATE_FORMAT -- Only works if the string format matches the session's NLS settings SELECT 'Salary: ' || salary FROM employees; -- salary (NUMBER) is implicitly converted to VARCHAR2 for concatenation
Implicit date conversion depends on NLS settings: When Oracle implicitly converts a string to a DATE, it uses the session's NLS_DATE_FORMAT. If the string does not match that format, the conversion fails with ORA-01861. This is why explicit TO_DATE with a format model is always safer than relying on implicit conversion.
02 — When implicit conversion fails
Implicit conversion is convenient but unreliable — it depends on the data. The exam presents queries that look valid but fail at runtime when the data does not match the expected format.
-- Fails at runtime — '2024/01/15' does not match NLS_DATE_FORMAT 'DD-MON-RR' SELECT * FROM employees WHERE hire_date > '2024/01/15'; -- ORA-01861: literal does not match format string -- Safe version — explicit TO_DATE with format model SELECT * FROM employees WHERE hire_date > TO_DATE('2024/01/15', 'YYYY/MM/DD'); -- Fails — 'twelve' is not a valid number SELECT * FROM employees WHERE salary = 'twelve'; -- ORA-01722: invalid number -- Performance issue — implicit conversion can prevent index use WHERE TO_CHAR(hire_date, 'YYYY') = '2020' -- Wrapping the indexed column in TO_CHAR prevents the index being used -- Rewrite as: WHERE hire_date BETWEEN TO_DATE('01-JAN-2020','DD-MON-YYYY') -- AND TO_DATE('31-DEC-2020','DD-MON-YYYY')
03 — Explicit conversion — always safer
Using TO_CHAR, TO_NUMBER, and TO_DATE explicitly removes any dependency on NLS settings and makes the intent of the query clear. The exam always accepts explicit conversion as correct — implicit conversion answers are sometimes traps.
-- Always use TO_DATE when comparing date strings WHERE hire_date = TO_DATE('15-03-2024', 'DD-MM-YYYY') -- Always use TO_CHAR when formatting dates for display SELECT TO_CHAR(hire_date, 'DD Month YYYY') FROM employees; -- TO_CHAR on numbers for formatted output SELECT TO_CHAR(salary, '$99,999.00') FROM employees; -- Returns: '$24,000.00' -- The 9 format element: digit, suppress leading zeros -- The 0 format element: digit, force leading zeros TO_CHAR(42, '099') -- Returns: '042' TO_CHAR(42, '999') -- Returns: ' 42'
TO_CHAR on a number wider than the format: If the number has more digits than the format allows, TO_CHAR returns a string of hash symbols (#####) instead of the number. For example,
TO_CHAR(12345, '999') returns '#####' because 12345 has 5 digits but the format only allows 3. The exam tests this behaviour.
04 — ROWNUM — how it works and its traps
ROWNUM is a pseudocolumn that assigns a sequential number to each row as Oracle retrieves it — before any ORDER BY is applied. It starts at 1 for the first row returned and increments by 1. The exam tests ROWNUM behaviour extensively because it has several non-obvious rules.
-- ROWNUM assigns numbers as rows are fetched — before ORDER BY SELECT ROWNUM, employee_id, last_name FROM employees WHERE ROWNUM <= 5; -- Returns the first 5 rows Oracle retrieves from the table -- NOT necessarily the first 5 in any meaningful order -- ROWNUM comparisons that WORK WHERE ROWNUM = 1 -- First row only — works WHERE ROWNUM <= 10 -- First 10 rows — works WHERE ROWNUM < 5 -- First 4 rows — works -- ROWNUM comparisons that return ZERO ROWS WHERE ROWNUM = 2 -- ZERO rows — never works WHERE ROWNUM > 5 -- ZERO rows — never works WHERE ROWNUM >= 3 -- ZERO rows — never works
ROWNUM = 2 always returns zero rows: ROWNUM starts at 1. The first row fetched gets ROWNUM = 1 and passes the filter. But for ROWNUM = 2, no row ever has ROWNUM = 2 when it is first fetched — ROWNUM only reaches 2 after a row has already passed the filter. Since no row can be the "second" row before the "first" row exists,
WHERE ROWNUM = 2 never returns anything. The same logic applies to ROWNUM > n for any n.
05 — ROWNUM with ORDER BY — the ordering trap
ROWNUM is assigned before ORDER BY runs. This means if you combine ROWNUM with ORDER BY, ROWNUM numbers the rows in their unordered retrieval order — then the ORDER BY sorts those already-numbered rows. The ROWNUM values in the result will appear out of sequence.
-- WRONG: intent is "top 3 highest salaries" -- But ROWNUM is assigned before ORDER BY runs SELECT ROWNUM, last_name, salary FROM employees WHERE ROWNUM <= 3 ORDER BY salary DESC; -- Result: 3 random rows (first 3 fetched), sorted by salary after the fact -- NOT the 3 highest-paid employees -- CORRECT: use an inline view to sort FIRST, then apply ROWNUM SELECT ROWNUM, last_name, salary FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 3; -- Inner query sorts by salary DESC first -- ROWNUM is then applied to the already-sorted result -- Returns the 3 highest-paid employees correctly
06 — Top-N queries using ROWNUM correctly
Getting the top N rows by some criteria is a common exam question. The correct pattern always uses an inline view to sort first, then applies the ROWNUM filter in the outer query.
-- Top 5 highest-paid employees SELECT last_name, salary FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5; -- 5 most recently hired employees SELECT last_name, hire_date FROM (SELECT last_name, hire_date FROM employees ORDER BY hire_date DESC) WHERE ROWNUM <= 5; -- Pagination: rows 6-10 (second page of 5) SELECT last_name, salary FROM (SELECT last_name, salary, ROWNUM AS rn FROM (SELECT last_name, salary FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 10) WHERE rn >= 6; -- Three levels: sort → cap at 10 → filter to 6-10 -- Cannot use ROWNUM > 5 in the outer query — use an alias instead
Pagination requires three levels: To get rows 6–10, you need to capture ROWNUM as an alias (rn) before filtering on it. You cannot use
WHERE ROWNUM > 5 directly — that always returns zero rows. The alias makes the already-assigned ROWNUM available as a regular column in the outer query.
07 — ROWID — the physical row address
ROWID is a pseudocolumn that contains the unique physical address of a row in the database. It is the fastest way to access a single row — Oracle can go directly to it without scanning. The exam tests what ROWID is and what it is not.
-- ROWID is the physical storage address of the row SELECT ROWID, employee_id, last_name FROM employees WHERE employee_id = 100; -- Returns something like: AAAE6NAAFAAAACXAAA -- Encodes: object number, file number, block number, row number -- ROWID is unique across the database -- No two rows in the database have the same ROWID -- ROWID can change after certain operations -- EXPORT/IMPORT, table move, partition operations can change ROWID -- Never store ROWID permanently as a reference — use PK instead
| ROWNUM | ROWID | |
|---|---|---|
| What it is | Sequential number assigned during query execution | Physical address of the row in storage |
| Permanent? | No — changes every query execution | Usually — but can change after table maintenance |
| Unique? | Within one query result — not across queries | Yes — unique across the entire database |
| Stored in table? | No — computed during SELECT | No — derived from physical storage location |
| Can it be used in WHERE? | Yes — with limitations | Yes — most efficient row lookup method |
08 — Substitution variables — & and &&
SQL*Plus and SQL Developer support substitution variables — placeholders in a query that prompt the user for a value at runtime. The exam tests the difference between single ampersand and double ampersand.
-- Single &: prompts for value every time the variable is referenced SELECT last_name, salary, department_id FROM employees WHERE department_id = &dept_id; -- User is prompted: Enter value for dept_id: 50 -- If &dept_id appears twice in the same query, user is prompted twice -- Double &&: prompts once and saves the value for reuse SELECT last_name, &&col_name FROM employees ORDER BY &col_name; -- User is prompted once: Enter value for col_name: salary -- The saved value 'salary' is reused for the second reference -- DEFINE and UNDEFINE commands DEFINE dept_id = 50 -- pre-assigns a value (no prompt needed) UNDEFINE dept_id -- clears the saved value -- SET VERIFY ON/OFF controls whether the substituted query is shown SET VERIFY OFF -- suppresses the "old/new" display after substitution
| Feature | Single & | Double && |
|---|---|---|
| Prompts user | Every time the variable appears | Once — value saved for subsequent references |
| Saves value | No | Yes — persists for the session |
| Use case | Single-use value needed once | Value referenced multiple times in same or later queries |
09 — Oracle date default format and NLS settings
Oracle's default date display format depends on the session's NLS_DATE_FORMAT setting. The standard Oracle sample database uses DD-MON-RR. The exam relies on this format in examples and tests what the RR year format element means.
-- Default Oracle date format: DD-MON-RR -- Example: 17-JUN-87 means June 17, 1987 -- RR vs YY year format — the exam tests this distinction -- RR: two-digit year interpreted based on current year -- If current year is 2000-2049: -- 00-49 in input → 2000-2049 (same century) -- 50-99 in input → 1950-1999 (previous century) -- YY: two-digit year always uses current century -- If current year is 2026: YY 87 → 2087 SELECT TO_DATE('87', 'RR') FROM dual; -- Returns year 1987 (87 >= 50, so previous century when current year is 2026) SELECT TO_DATE('87', 'YY') FROM dual; -- Returns year 2087 (always current century: 20xx) SELECT TO_DATE('27', 'RR') FROM dual; -- Returns year 2027 (27 < 50, so same century when current year is 2026)
RR format is designed to handle legacy two-digit years: When the Oracle HR sample data shows hire dates like '17-JUN-87', the RR format interprets 87 as 1987, not 2087. This is intentional — RR handles the Y2K-era problem of two-digit years spanning the century boundary. The exam tests the RR vs YY distinction directly.
10 — Quick reference
| Rule | Detail |
|---|---|
| Implicit VARCHAR2 → DATE | Depends on NLS_DATE_FORMAT — use TO_DATE explicitly to be safe |
| TO_CHAR overflow | Number wider than format returns '#####' |
| ROWNUM = 1 | Works — returns first row fetched |
| ROWNUM = 2 | Always zero rows — ROWNUM never reaches 2 before reaching 1 |
| ROWNUM > n | Always zero rows — same reason |
| ROWNUM with ORDER BY | ROWNUM assigned before ORDER BY — sort first in inline view |
| Top-N query pattern | Sort in inner query → apply ROWNUM <= N in outer query |
| Pagination pattern | Three levels — sort → alias ROWNUM → filter on alias |
| ROWID | Physical row address — unique in database — can change after maintenance |
| Single & | Prompts user every time variable appears |
| Double && | Prompts once — saves value for reuse |
| RR year format | 00–49 → current century; 50–99 → previous century (when in 2000–2049) |
| YY year format | Always applies current century — 87 becomes 2087 |
11 — Practice questions
Q1 — A developer wants the 3 employees with the highest salary. Which query correctly returns them?
Q2 — What does
TO_CHAR(1234567, '999,999') return?Q3 — The current year is 2026. What year does
TO_DATE('48', 'RR') return?Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
All four volumes cover implicit conversion, ROWNUM patterns, date format models, and Oracle-specific features — with practice sets designed around the exam traps on each topic. Instant PDF download.
Get the full guide — $25 →
All four volumes cover implicit conversion, ROWNUM patterns, date format models, and Oracle-specific features — with practice sets designed around the exam traps on each topic. Instant PDF download.
No comments:
Post a Comment