Oracle Database / Advanced: secure and recoverable evolution / Lesson 15

JSON, LOBs and relational contracts

Store flexible shipment payloads without abandoning validation, typed extraction or version boundaries.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain textual JSON storage in the 19c baseline.
Use explicit SQL/JSON extraction and document array expansion.

A document has a contract even when fields vary

A carrier payload can contain variable scan metadata while shipment identity, ownership and booking charge remain relational facts. Keeping every field in one opaque document makes constraints, joins and audit questions harder. Choose which fields need stable types and keys, and keep a payload version for flexible extensions. The 19c baseline supports textual JSON in VARCHAR2, CLOB or BLOB with documented SQL/JSON functions. Do not copy examples using the native JSON type from later releases without checking compatibility. The 19c OSON storage capability has an Autonomous-specific boundary; cloud service behavior must not be generalized to every 19c server.

Validate syntax and presence separately

The IS JSON check establishes a syntax contract, while NOT NULL requires a supplied payload. It does not prove that a mandatory tracking field exists or that weight is positive. Add domain validation at ingestion and extract constrained relational facts where the application depends on them. CLOB accommodates large character documents but still requires a size policy at the application boundary. A small known maximum can justify VARCHAR2 instead. Avoid blindly selecting the largest storage type; network transfer, client memory and retrieval patterns matter alongside the declared capacity. All definitions here are illustrative and non-runnable.

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

CREATE TABLE carrier_payloads (
 payload_id NUMBER PRIMARY KEY,
 shipment_id NUMBER NOT NULL,
 payload CLOB NOT NULL CHECK (payload IS JSON)
);

This checks that a non-null document is well-formed under the chosen IS JSON rules. Required business fields need additional validation.

Declare the extracted SQL type

JSON_VALUE extracts a scalar into a SQL value. Specify RETURNING NUMBER when the destination is numeric, and choose an error policy rather than allowing malformed or wrongly typed data to vanish into an unexplained NULL. The fixture extracts three kilograms and a tracking string. JSON null, a missing path and an SQL NULL document can have different source meanings even when an extraction policy yields SQL NULL for several cases. If the distinction matters for reconciliation, preserve document presence and use an explicit existence test alongside the scalar value. Do not infer source quality from a successful cast alone.

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

SELECT JSON_VALUE('{"tracking":"LAB-101","weight_kg":3}',
                  '$.weight_kg' RETURNING NUMBER ERROR ON ERROR) AS weight_kg,
       JSON_VALUE('{"tracking":"LAB-101","weight_kg":3}',
                  '$.tracking' RETURNING VARCHAR2(24) ERROR ON ERROR) AS tracking
FROM dual;

Expected output

WEIGHT_KG | TRACKING
3         | LAB-101

The numeric result is 3 and the character result is LAB-101. The literals are synthetic valid documents.

Array expansion changes the row grain

JSON_TABLE projects document content into relational rows. Expanding a scans array produces one result row per selected array item, so a shipment-level charge can be duplicated just like a normal child-table join. Define paths, types, ordinality and empty-array behavior before combining the projection with totals. Nested arrays can multiply rows again. A document being well-formed says nothing about array size or schema consistency between revisions. Keep adversarial fixtures with an empty array, one scan, several scans and a wrongly typed item. Those cases reveal both business and extraction errors before performance tuning.

A locator is not an unlimited response budget

Oracle clients can expose LOB locators or streaming interfaces. Fetching every large document eagerly can move the bottleneck from SQL to application memory or network transfer. Request only required scalar fields for list views, and retrieve a full payload through a controlled detail path. Define maximum accepted size, retention, redaction and character-encoding expectations. LOB storage and compression options have version and licensing details, so review them separately from the basic CLOB type. The teaching course performs no database storage or throughput experiment and makes no claim that one storage format is universally fastest.

Common errors and fixes

JSON_VALUE returns unexpected NULL

Why it happens: A default extraction policy masks missing, non-scalar or incompatible source content.
How to fix it: Inspect the original synthetic document and specify the intended type and error policy.
Prevent it next time: Test missing, JSON null and wrong-type fields separately.

ORA-02290 on a JSON payload

Why it happens: The document violates its IS JSON check constraint.
How to fix it: Validate source syntax without disabling the stored integrity rule.
Prevent it next time: Quarantine malformed payloads with source identity and size limits.

Totals increase after JSON_TABLE is added

Why it happens: Array projection multiplied shipment-level facts.
How to fix it: Aggregate the expanded children to the required grain before joining totals.
Prevent it next time: Document the row unit at each JSON projection stage.

Check your understanding

1. Does payload IS JSON prove that weight_kg is a positive number?

Show worked solution

No. It validates document syntax according to the selected rules. Extract weight with an explicit numeric contract and apply the positive-domain requirement separately. Preserve a rejection reason for missing or invalid fields rather than treating all of them as zero-weight shipments.

2. A document has five scan items and one charge. How should a charge report avoid multiplying it?