NUMBER, Character, DATE/TIMESTAMP, INTERVAL, RAW, LOB, JSON, VECTOR, and Specialized Types

Learning outcomes

ServiceHub now has clear ownership, but a schema can still fail in production if its column types encode the wrong semantics. Porting datetime, text, arbitrary precision numbers, empty strings, JSON documents, or embeddings by name alone is dangerous because Oracle's type rules and client mappings are not identical to PostgreSQL, MySQL, or SQL Server.

1. Start from the value domain, not a cross-engine type name

A database type defines valid values, representation rules, comparison behavior, functions, and client bindings. Oracle NUMBER is a decimal numeric family with precision and scale; VARCHAR2 is variable-length text in the database character set; NVARCHAR2 uses the national character set; DATE stores date and time to seconds; timestamp variants add fractional seconds and different time-zone semantics. Treating type names as cosmetic labels produces silent conversion and boundary errors.

2. NUMBER precision/scale and character length semantics

For NUMBER(p,s), precision is the significant-digit budget and scale controls digits relative to the decimal point. Oracle may round to the declared scale, but a value that cannot fit the declared precision raises an error. Do not use an unconstrained numeric column when the business domain has a meaningful bound simply because Oracle can store a broad numeric range.

For character columns, explicitly choose byte or character semantics when it matters. VARCHAR2(80 CHAR) expresses an 80-character business limit; VARCHAR2(80 BYTE) expresses an 80-byte storage limit and can hold fewer than 80 characters in a multibyte database character set.

DROP TABLE IF EXISTS servicehub_type_probe PURGE;CREATE TABLE servicehub_type_probe (  probe_id        NUMBER(8) PRIMARY KEY,  labor_hours     NUMBER(6,2),  technician_name VARCHAR2(80 CHAR),  device_code     RAW(16));SELECT column_name, data_type, data_length,       data_precision, data_scale, char_used, char_lengthFROM   user_tab_columnsWHERE  table_name = 'SERVICEHUB_TYPE_PROBE'ORDER BY column_id;

3. Oracle DATE includes time; timestamp types answer different questions

Oracle DATE is not a date-only type: it stores year, month, day, hour, minute, and second. TIMESTAMP adds fractional-second precision. TIMESTAMP WITH TIME ZONE carries time-zone information with the value, while TIMESTAMP WITH LOCAL TIME ZONE normalizes storage to the database time zone and presents values in the session time zone. Application teams should decide whether a column represents a civil schedule, a globally ordered event instant, or a display-localized value before choosing the type.

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;SELECT TO_CHAR(CAST(TIMESTAMP '2026-08-25 01:02:03.456789' AS TIMESTAMP),               'YYYY-MM-DD HH24:MI:SS.FF6') AS ts_value,       TO_CHAR(CAST(TIMESTAMP '2026-08-25 01:02:03' AS DATE),               'YYYY-MM-DD HH24:MI:SS') AS date_valueFROM dual;

Explicit format models keep the observation independent of session display defaults. Avoid relying on implicit string-to-date conversion, because NLS settings can change the interpretation.

4. Empty string, NULL, RAW and LOB boundaries

Oracle SQL treats a zero-length VARCHAR2/NVARCHAR2 value as NULL. That surprises applications that distinguish “present but empty” from “missing.” A migration that requires that distinction needs an explicit representation, such as a status column, a sentinel governed by the domain, or a JSON/LOB representation whose semantics truly preserve the distinction.

TRUNCATE TABLE servicehub_type_probe;INSERT INTO servicehub_type_probe(probe_id, technician_name)VALUES (1, '');SELECT probe_id,       CASE WHEN technician_name IS NULL THEN 'NULL' ELSE 'NOT NULL' END AS state,       LENGTH(technician_name) AS character_lengthFROM servicehub_type_probe;-- Expected: STATE = NULL and CHARACTER_LENGTH is NULL.

RAW is byte-oriented scalar data, while BLOB and CLOB are large objects with locator/streaming implications for drivers. Do not base64-encode arbitrary binary data into VARCHAR2 merely to make it “look like text.”

5. Native JSON and VECTOR are typed database values

Native JSON uses Oracle's optimized binary JSON representation and is different from storing unvalidated JSON text in a generic character column. The native JSON type and constructor require COMPATIBLE of at least 20. Oracle AI Vector Search introduces the native VECTOR type; its documented minimum COMPATIBLE is 23.4.0. The client/driver must also support the way you intend to bind and fetch these values.

SELECT name, valueFROM   v$parameterWHERE  name = 'compatible';DROP TABLE IF EXISTS servicehub_ai_probe PURGE;CREATE TABLE servicehub_ai_probe (  probe_id  NUMBER PRIMARY KEY,  payload   JSON,  embedding VECTOR(3, FLOAT32));INSERT INTO servicehub_ai_probeVALUES (1,        JSON('{"kind":"temperature","value":21.5}'),        VECTOR('[0.10,0.20,0.30]', 3, FLOAT32));SELECT probe_id,       json_serialize(payload RETURNING VARCHAR2(200)) AS payload_text,       vector_dims(embedding) AS dimensionsFROM servicehub_ai_probe;

If the table creation fails because your Free build, COMPATIBLE, or client environment differs, record the evidence and use the relational/type exercises for the mandatory lab. Do not change COMPATIBLE casually; compatibility increases are upgrade decisions with rollback consequences.

6. Deliberately wrong approach: implicit conversion as an API contract

Suppose an application sends a localized string into a numeric column or a display-formatted date into a DATE column and relies on session defaults. The same text can fail or change meaning under a different NLS_DATE_FORMAT or numeric-character setting. The database is not obligated to guess the application's intent.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';-- Fragile: string interpretation depends on session settings.SELECT TO_DATE('08/09/2026') FROM dual;-- Likely ORA-01861 / ORA-01843 under this format.-- Explicit server-side conversion when text input is unavoidable:SELECT TO_DATE('2026-09-08','YYYY-MM-DD') AS explicit_date FROM dual;-- Preferred application path: bind a native date/timestamp value through the driver.

The repair is not “set one NLS format globally and forget it.” Use native bind types at application boundaries and explicit format models when parsing external text is genuinely part of the contract.

7. Hands-on lab: type a ServiceHub event correctly

Create a disposable event table that combines exact numeric, character, date/time, interval, JSON, and vector values only where your environment supports them. Query USER_TAB_COLUMNS afterward and write a one-line reason for each chosen type. Then attempt one precision overflow and one empty-string insert, capture the error/state, and repair the input rather than widening the type blindly.

DROP TABLE IF EXISTS servicehub_event_probe PURGE;CREATE TABLE servicehub_event_probe (  event_id       NUMBER(10) PRIMARY KEY,  asset_code     VARCHAR2(40 CHAR) NOT NULL,  labor_hours    NUMBER(6,2),  observed_at    TIMESTAMP WITH TIME ZONE NOT NULL,  service_window INTERVAL DAY TO SECOND,  payload_text   CLOB,  checksum_raw   RAW(16));INSERT INTO servicehub_event_probe(event_id, asset_code, labor_hours, observed_at, service_window, payload_text)VALUES(1, 'PUMP-042', 1.75, TO_TIMESTAMP_TZ('2026-08-25 01:00:00 +03:30','YYYY-MM-DD HH24:MI:SS TZH:TZM'), INTERVAL '0 02:30:00' DAY TO SECOND, '{"status":"ok"}');SELECT event_id, asset_code, labor_hours,       TO_CHAR(observed_at,'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS observed_at,       service_windowFROM servicehub_event_probe;

8. Production judgment and next step

Type design is an API decision. Record precision/scale, byte/character semantics, time-zone meaning, null/empty-string policy, LOB streaming expectations, and driver mappings in the schema contract. Gate native JSON and VECTOR usage on exact COMPATIBLE, RU, and client support rather than on marketing labels alone. No extra-cost option is required merely to use the basic native types in the Free learning environment, but AI features around vectors can have additional service/model/tool requirements.

Lesson 3 adds integrity constraints. Types constrain the shape of one value; constraints express relationships and row-level invariants that must remain true as transactions modify many values.

Authoritative references

• Oracle SQL data types — NUMBER, character, datetime, LOB, JSON, BOOLEAN, VECTOR, and type limits
• JSON data type — native JSON representation and COMPATIBLE requirement
• Create tables using VECTOR — VECTOR declarations, restrictions, and storage semantics
• Oracle AI Vector Search overview — VECTOR feature and COMPATIBLE >= 23.4.0 requirement
• Format models — explicit numeric and datetime conversion/display behavior