Oracle and BLOB: the drawing refused, the view that weighs it, the PDF lost overnight

Goal Read a PLM's drawing register on Oracle without reading the drawings themselves, and know the same morning that a released drawing lost its file.

An engineering office keeps its drawings in a PLM on Oracle: one row per revision, the PDF in a BLOB.

the hub does not read a BLOB: the table is refused as it is, column named;
the DBA adds a view without the drawing, its size in bytes instead;
overnight, the archive migration copies two released revisions back without their PDF: two decisions.

lumnik Pro
Engineering
PLM
Oracle
BLOB
Connector
Process

$ lm secret set SCENARIO_PLM_PASSWORD_A -
Created secret "SCENARIO_PLM_PASSWORD_A"

$ lm source jdbc discover --password-stdin --url jdbc:oracle:thin:@//plm-oracle:1521/FREEPDB1 --user HUB_READER --schema PLM
SCHEMA     NAME                            ROWS  COLS PK                   WATERMARK       UNSUPPORTED
PLM        DOC_REVISION                      -1    10 ID                   MODIFIED_AT     CONTENT BLOB

$ lm source jdbc add scenario-plm-a --password-stdin --url jdbc:oracle:thin:@//plm-oracle:1521/FREEPDB1 --user HUB_READER --schema PLM --tables DOC_REVISION
Error: API error 400: table PLM.DOC_REVISION cannot be ingested: column CONTENT has unsupported Oracle type BLOB — v1 reads scalars and CLOB

view.sql

-- What the DBA adds: the register without the drawing itself, its size in bytes instead; REV without the padding of its CHAR(2).
CREATE VIEW V_DOC_REVISION AS
  SELECT ID, DOC_NO, RTRIM(REV) AS REV, TITLE, STATUS, RELEASED_AT, DESCRIPTION, FILE_NAME,
         NVL(DBMS_LOB.GETLENGTH(CONTENT), 0) AS CONTENT_BYTES, MODIFIED_AT
  FROM DOC_REVISION;
GRANT SELECT ON V_DOC_REVISION TO HUB_READER;

$ lm source jdbc discover --password-stdin --url jdbc:oracle:thin:@//plm-oracle:1521/FREEPDB1 --user HUB_READER --schema PLM --include-views
SCHEMA     NAME                            ROWS  COLS PK                   WATERMARK       UNSUPPORTED
PLM        DOC_REVISION                      -1    10 ID                   MODIFIED_AT     CONTENT BLOB
PLM        V_DOC_REVISION                     0    10                      MODIFIED_AT     -

manifests/10-drawings.yaml

apiVersion: connectors.lumnik.io/v1kind: Connectormetadata:  name: scenario-plm-drawings-a  connector_type: jdbc-generic  scopes: [engineering-a]spec:  connection:    url: jdbc:oracle:thin:@//plm-oracle:1521/FREEPDB1    user: HUB_READER    password_env: SCENARIO_PLM_PASSWORD_A  defaults:    schema: PLM  source:    tables:      - name: V_DOC_REVISION            # the view: DOC_REVISION holds a BLOB, refused        mode: watermark                 # full never reads an updated row again        watermark_column: MODIFIED_AT        pk_columns: [ID]        target: scenario_plm_drawings_a        rename:          ID: revision_id          DOC_NO: doc_no          REV: rev          TITLE: title          STATUS: status          RELEASED_AT: released_at          DESCRIPTION: description          FILE_NAME: file_name          CONTENT_BYTES: content_bytes          MODIFIED_AT: modified_at

$ lm apply -f manifests/10-drawings.yaml
Applied connector "scenario-plm-drawings-a" — 1 endpoint

$ lm connector run scenario-plm-drawings-a
run scheduled: 971be24a-9fd1-4de0-96b5-87fe605695f4

$ lm run get 971be24a-9fd1-4de0-96b5-87fe605695f4
ID        CONNECTOR                ENDPOINT        STATUS     IN  OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
971be24a  scenario-plm-drawings-a  V_DOC_REVISION  Completed  13  13   0        0             2026-11-08 10:23  2026-11-08 10:23  yes

manifests/20-file-lost.yaml

apiVersion: apps.lumnik.io/v1kind: Processmetadata:  name: scenario-plm-file-lost-aspec:  scope: engineering-a  on: { event: data.row.changed, table: connector.t_scenario_plm_drawings_a }  when:    - "status=RELEASED"    - "content_bytes=0"                # a released drawing whose PDF is empty  emit:    type: decision.drawing-file-lost-a    message: "{doc_no} rev {rev} ({title}) is released since {released_at} and its file {file_name} is empty — find the PDF before the workshop asks for it"    key: doc_no

$ lm process apply -f manifests/20-file-lost.yaml
Applied process "scenario-plm-file-lost-a" → table connector.t_scenario_plm_drawings_a, emits decision.drawing-file-lost-a

archive.sql

-- The night's archive migration: the PDFs move to the new document store, two of them never arrive,
-- and the rows are copied back with an empty BLOB. The PLM stamps MODIFIED_AT, as on every write.
UPDATE DOC_REVISION SET CONTENT = EMPTY_BLOB(), MODIFIED_AT = TIMESTAMP '2026-11-08 02:14:00' WHERE (DOC_NO, REV) IN (('PL-1004', 'C '), ('PL-1007', 'B '));
COMMIT;

$ lm connector run scenario-plm-drawings-a
run scheduled: 192ef43d-7375-4cd9-9f39-9b5224212f86

$ lm run get 192ef43d-7375-4cd9-9f39-9b5224212f86
ID        CONNECTOR                ENDPOINT        STATUS     IN  OUT  SKIPPED  DEAD-LETTERS  STARTED           ENDED             EVALUATED
192ef43d  scenario-plm-drawings-a  V_DOC_REVISION  Completed  2   2    0        0             2026-11-08 10:23  2026-11-08 10:23  yes

$ lm inbox --process scenario-plm-file-lost-a
ID     WHEN      PROCESS                   MESSAGE                                                                                                                                                         ENTITY_KEY
25844  just now  scenario-plm-file-lost-a  PL-1007 rev B (Pignon Z=23) is released since 2016-12-12T17:05:00Z and its file PL-1007-B.pdf is empty — find the PDF before the workshop asks for it           PL-1007
25843  just now  scenario-plm-file-lost-a  PL-1004 rev C (Bride d'accouplement) is released since 2018-04-30T10:00:00Z and its file PL-1004-C.pdf is empty — find the PDF before the workshop asks for it  PL-1004

Limits

The hub does not read a BLOB: not the PDF, not its content, not a checksum. A view gives its size; a corrupt but non-empty PDF passes.
The view is the Oracle DBA's gesture: the hub does not create it.
Watermark mode assumes the PLM stamps MODIFIED_AT. A write that does not touch it is not read again.
The rule only sees losses to come: a drawing already empty at the first run is read before the Process exists.
Replayed on Oracle 23ai Free. lumnik Pro only: the Open edition has no Oracle provider.
Drawings, revisions and dates synthetic; the PDFs are real one-page PDFs.

Engineering · PLM

The drawings live in Oracle.

One row per revision, the PDF in a BLOB.

CONTENT BLOB

The problem

The hub does not read a BLOB.

The table is refused, column named.
And an empty PDF shows nowhere.

What is not changed

The PLM's table stays as it is.

The PLM keeps writing its drawings.
No PDF leaves Oracle.

What had to be written

NVL(DBMS_LOB.GETLENGTH(CONTENT), 0)
  AS CONTENT_BYTES

A view: the drawing weighed, not read.

mode: watermark on MODIFIED_AT.
One rule: released and 0 bytes.

What was fixed

discover --include-views shows the view (#1890).

ROWS -1 for a table never analyzed (#1891).
PL-1004 C and PL-1007 B: two decisions.

Try it

The scenario replays locally.

Every step is published with its output.

This scenario needs lumnik Pro.

See the editions →