view release on metacpan or  search on metacpan

docs/adr/0006-lob-chunked-equality-comparison.md  view on Meta::CPAN

- Date: 2026-06-22
- Tags: storage, lob, blob, clob, comparison, oracle

## Context

Oracle does not let you compare a LOB column directly to a bind value in SQL —
`WHERE blob_col = ?` against a BLOB/CLOB is not valid the way it is for ordinary
columns. But DBIO routinely generates exactly that shape: an `update` whose WHERE
clause references a LOB column, or a `select`/`delete` matching on a LOB. The
storage layer therefore has to rewrite any equality predicate on a LOB column
into something Oracle accepts, transparently, without the ORM caller knowing the
column is a LOB.

Oracle's portable building block for reading a LOB piecewise is
`DBMS_LOB.SUBSTR`, and the comparable VARCHAR2 form of a chunk is
`UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(...))`. Oracle also limits a VARCHAR2 to a few
thousand bytes, so a large LOB cannot be compared in one piece — it must be split
into bounded chunks compared with AND.

## Decision