Oracle Database / Basic: relational and transaction foundations / Lesson 3

Predicates, dates and deterministic results

Translate a shipment question into a stable row set with typed boundaries and explicit NULL handling.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Use three-valued logic and half-open temporal ranges.
Choose deterministic ordering and safe bind parameters.

Write the result contract first

A useful report specifies row eligibility, projection and order separately. “Recent shipments” is incomplete until the business names a time zone, date boundary, treatment of undispatched rows and tie-breaking key. SQL without ORDER BY does not promise insertion order, even when a small demonstration appears stable. SELECT * also makes the output change when a column is added. Use named columns for a durable application contract and review whether each field belongs in the response. Correctness starts with that question, before choosing an index or a row limit.

Unknown values do not satisfy equality

A predicate comparing a value to NULL evaluates to unknown. Therefore dispatched_at = NULL cannot select undispatched shipments. IS NULL asks the intended question. Likewise NOT IN is dangerous when its comparison set contains NULL: an apparent nonmember can become unknown and disappear. Prefer a correlated NOT EXISTS when the business asks whether no matching child exists. Parentheses matter when AND and OR combine; write the grouping that a reviewer can read directly rather than relying on remembered precedence. Test at least one absent value in every nullable business rule.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT CASE WHEN NULL = NULL THEN 'equal' ELSE 'not true' END AS comparison,
       CASE WHEN NULL IS NULL THEN 'missing' ELSE 'present' END AS absence
FROM dual;

Expected output

COMPARISON | ABSENCE
not true   | missing

CASE takes its THEN branch only for true; the first comparison is unknown, while IS NULL is true.

A day is a range, not a formatted string

For an Oracle DATE column storing local operational time, one day is commonly expressed as at-or-after midnight and before the next midnight. BETWEEN includes both endpoints, so using tomorrow’s midnight as its upper bound accidentally includes the next day. TO_CHAR on the stored column mixes presentation with filtering and may obstruct a useful ordinary index. DATE literals avoid session-dependent parsing. For timestamp-with-zone events, agree on the business zone and construct typed instant boundaries in the application or a reviewed expression; adding one calendar day and adding twenty-four hours can differ around daylight-saving changes.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT shipment_id, booked_at
FROM shipments
WHERE booked_at >= DATE '2026-10-01'
  AND booked_at <  DATE '2026-10-02'
ORDER BY booked_at, shipment_id;

Assumes booked_at is a DATE in the documented operational clock. All October 1 times qualify; October 2 midnight does not.

Make the last row reproducible

ORDER BY booked_at alone leaves tied timestamps unordered. Add the unique shipment_id before FETCH FIRST 20 ROWS ONLY, which is available from 12c. Oracle ascending order normally puts NULL last, while descending order normally puts NULL first; explicit NULLS LAST or NULLS FIRST removes ambiguity. OFFSET pagination can shift under concurrent inserts and can require increasingly large work. A continuation key based on the last ordered timestamp and unique identifier can be more stable, but its comparison must reproduce the exact sort direction and missing-value policy.

Bind values and allowlist syntax

Application values should travel through connector bind variables, not string concatenation. A bind represents a value, not a table name, an arbitrary ORDER BY expression or an entire SQL predicate. Choose identifiers and sort directions from a small application allowlist. Keep bound numeric and temporal types aligned with columns to avoid implicit conversions and inconsistent plans. When documenting a failure, preserve the SQL shape and redacted parameter types as well as the message. Replacing every parameter with a quoted string can conceal the actual conversion defect that happened in the application.

Common errors and fixes

ORA-01861

Why it happens: A string is implicitly parsed using a date format that does not match its contents.
How to fix it: Use typed binds, DATE literals or an explicit conversion format appropriate to the source.
Prevent it next time: Avoid depending on a session display format as an input protocol.

A NOT IN query unexpectedly returns no rows

Why it happens: The comparison set includes NULL and creates unknown comparisons.
How to fix it: Use the intended NOT EXISTS relationship or explicitly justify null exclusion.
Prevent it next time: Include a nullable child fixture when reviewing exclusion logic.

Pages repeat or omit shipments

Why it happens: Ordering lacks a unique tie-breaker or concurrent changes shift offsets.
How to fix it: Define a total order and a continuation contract for changing data.
Prevent it next time: Review boundary ties and inserts between page requests.

Check your understanding

1. Why does BETWEEN DATE '2026-10-01' AND DATE '2026-10-02' not precisely describe October 1?

Show worked solution

BETWEEN includes October 2 at midnight as well. Use >= the first midnight and < the second midnight for a DATE column with the stated clock convention. If the source stores instants, first derive those boundaries in the agreed business time zone instead of assuming the server zone is correct.

2. Can :sort_column safely replace an ORDER BY identifier?