LEARN. PRACTICE. GROW.Concept and mechanism
SQL*Plus processes commands and substitutions before sending SQL to the database. Explicit DEFINE stores a textual variable; & and && substitute text rather than acting as bind variables. SET VERIFY controls before-and-after substitution echo without validating input or preventing changes to SQL structure. Define accepted parameters and script error handling. For temporal calculations, Oracle DATE includes time through seconds but no zone; subtracting two DATE values yields days including fractions. Multiplying by twenty-four converts that difference to hours. TIMESTAMP and INTERVAL have their own semantics. A local timestamp without offset can be ambiguous during clock changes and require source context. Preserve that context at ingestion where possible.
Guided application
In a fictional load, a global temporary table with ON COMMIT DELETE ROWS loses data at commit while retaining its definition. PRESERVE ROWS changes data scope to the session; both require understanding connections and cleanup. A classic ORACLE_LOADER external table reads files and cannot repair the source through UPDATE; use internal staging or an authorized feed-correction process. Marking a permanent column UNUSED makes it inaccessible and offers no simple return to USED, so recovery must be prepared beforehand. Conditional INSERT ALL can insert into several targets whose WHEN conditions are true; FIRST chooses the first. Rehearsing these boundaries prevents a valid script from implementing a different business rule than intended.
If end_date - start_date = 0.25, the DATE difference is six hours. Do not infer time zone from that number.
Common pitfalls
VERIFY as validation; DEFINE as bind; temporary as disposable definition; UNUSED as reversible; ALL as FIRST.
Related topics: Model, SELECT, and result population · Functions, conversions, and data contracts · Joins, grain, and aggregation
Document client context, data lifetime, and the effects of each boundary.
Reference: Temporary and external tables · 1Z0-071 public objectives inspected 2026-09-30; revision date not published