Staged text data: ORA-01722, string sorting, and implicit date conversion
ORDER_STAGE holds 4,812 rows loaded from a flat file: ORDER_ID NUMBER, AMOUNT VARCHAR2(20) with values like '1250.00', ' 980 ', '$2,340.50', and 'N/A' for 37 unposted rows; ORDER_DT VARCHAR2(10) holds values like '2024-03-07'. The report query
SELECT order_id, amount, NVL(amount, 0) AS amt2 FROM order_stage WHERE amount > 500 ORDER BY amount;
fails with ORA-01722: invalid number. Removing the WHERE clause lets it run, but the output puts '1000.00' before '900.00', and ' 980 ' sorts last. A colleague proposes altering AMOUNT to NUMBER and reloading, pointing out that the '$' and 'N/A' values would still be a problem.
1. Explain what actually triggers ORA-01722 here, and write a query that returns only rows whose text genuinely converts to a number.
Read the worked answer
500 is numeric, so Oracle applies implicit conversion to the column: every VARCHAR2 value in scope is passed through TO_NUMBER. The first value that is not a valid number ('N/A', or '$2,340.50' once the currency symbol and comma are considered) raises ORA-01722.
The trap is assuming you can guard it with WHERE REGEXP_LIKE(amount,'^[0-9.]+$') AND amount > 500. Oracle does not guarantee predicate evaluation order, so the risky comparison can be pushed ahead of the guard. Convert in an inner query, filter outside:
SELECT order_id, amt
FROM (
SELECT order_id,
TO_NUMBER(REPLACE(REPLACE(amount,'$',''),',','')
DEFAULT NULL ON CONVERSION ERROR) AS amt
FROM order_stage
)
WHERE amt > 500;
VALIDATE_CONVERSION(...) = 1 or a CASE returning null on failure works as the inner expression instead of DEFAULT NULL ON CONVERSION ERROR on older releases. Note that comparing the column to the string '500' avoids the error but gives lexicographic comparisons - silently wrong answers instead of a loud failure.
2. Why does '1000.00' sort before '900.00', and what is the correct ORDER BY once the values are numeric?
Read the worked answer
The column is VARCHAR2, so ORDER BY amount is a character sort: '1' < '9' at the first byte. The space in ' 980 ' also sorts before every digit, which is why that row lands last only if nothing smaller exists - in fact blank-padded values sort earliest in ascending order.
The sort has to happen on the converted value, and only after the non-numeric rows have been removed:
SELECT order_id, amt
FROM ( ...conversion inline view... )
WHERE amt IS NOT NULL
ORDER BY amt DESC;
ORDER BY TO_NUMBER(amount) directly on the base table re-introduces ORA-01722, because ORDER BY is evaluated over the rows that survive the WHERE clause - the 'N/A' rows survive unless the filter already excluded them at the converted level.
3. The predicate WHERE order_dt > '2024-03-01' currently works. Is that acceptable in a scheduled report, and what should replace it?
Read the worked answer
It works only by accident. The column is VARCHAR2 and the literal is a string, so the comparison is a string comparison - which happens to be correct for zero-padded YYYY-MM-DD but silently wrong for formats like DD-MON-YY. If ORDER_DT is ever changed to a DATE column, the same predicate instead triggers implicit TO_DATE on the literal using the session's NLS_DATE_FORMAT, and fails with ORA-01861 in a session with a different setting.
Make the conversion explicit and independent of session settings:
WHERE TO_DATE(order_dt,'YYYY-MM-DD') > DATE '2024-03-01'
If ORDER_DT becomes a real DATE column, compare it to DATE '2024-03-01' with no function on the column, so an index on the column remains usable - a function on the column defeats a normal index.