oracledb #

A pure Dart driver for Oracle Database. It speaks the thin-mode TNS/TTC wire protocol directly, so there is no Oracle Client, no FFI and no native code to install.

Stable API (semantic versioning), validated against Oracle 23ai and 21c. Connections, pooling, queries, DML, bulk DML, transactions, PL/SQL, result-set streaming, LOBs, and native JSON are all supported. See the Features list below, and the CHANGELOG for what changed in each release.

This is NOT an official Oracle product. It is an independent Dart port of the thin-client wire protocol as documented and implemented in Oracle's official node-oracledb driver. Oracle Corporation is not affiliated with this project.

Features #

Pure Dart — no FFI, no native code, no Oracle Instant Client required
Thin protocol — direct TNS/TTC wire protocol implementation
Oracle 23ai + Oracle 21c — tested against both; FAST_AUTH and classical auth paths both supported
Native Dart platforms — macOS, Windows, Linux, Android, and iOS (web unsupported — requires dart:io TCP sockets)
TLS/SSL — encrypted connections with certificate validation
Full query support — SELECT, INSERT, UPDATE, DELETE with positional and named bind parameters
Bulk DML — executeMany() runs one statement across many bind sets in a single round trip, with array RETURNING, per-iteration PL/SQL binds, batch errors, and per-row counts
PL/SQL — stored procedures and functions with OUT / IN OUT bind parameters
Result sets & streaming — consume large queries incrementally via OracleResultSet or queryStream() / executeStream() instead of materializing every row
REF CURSOR & implicit results — PL/SQL SYS_REFCURSOR OUT binds, DBMS_SQL.RETURN_RESULT implicit result sets (including rows that carry CLOB/NCLOB/BLOB/JSON columns), and nested CURSOR() columns (nested-cursor and REF CURSOR rows must not carry a CLOB/NCLOB/BLOB column — see Known Limitations)
LOB streaming & temporary LOBs — OracleExecuteOptions(fetchLobs: true) returns a server-backed OracleLob with bounded read()/write(), trim(), refresh() of the cached length/chunkSize, textStream()/byteStream()/writeStream() for piece-by-piece transfer, and createTemporaryClob()/createTemporaryNClob()/createTemporaryBlob() for session-duration temporary LOBs
Native JSON — JSON columns (and BLOB ... IS JSON) decode to Map / List, including Oracle DATE/TIMESTAMP/RAW members, and fetched documents bind back unchanged
Transactions — commit, rollback, and transaction helper
TIMESTAMP WITH TIME ZONE — decoded as UTC DateTime, or as OracleTimestampTz preserving the original offset (opt-in)
Statement caching — transparent prepared-statement cache
Async/await — modern Dart async API throughout

Platform Support #

Platform	Supported
macOS	✅
Windows	✅
Linux	✅
iOS	✅
Android	✅
Web	❌ (requires raw TCP sockets via `dart:io`)

All supported native platforms are declared in pubspec.yaml. Desktop/server targets are covered by CI. Android and iOS use the same dart:io TCP socket transport, which is available on native Dart targets; mobile-specific runtime validation is still expected before relying on them in production.

Supported Oracle Versions #

Oracle 23ai — FAST_AUTH protocol (single-round-trip authentication)
Oracle 21c — classical AUTH_PHASE_ONE / AUTH_PHASE_TWO

Older versions (19c, 12c) may work but have not been tested.

Installation #

Add to your pubspec.yaml:

dependencies:
  oracledb: ^1.5.0

Then run:

dart pub get

Quick Start #

import 'package:oracledb/oracledb.dart';

Future<void> main() async {
  final connection = await OracleConnection.connect(
    'localhost:1521/FREEPDB1',
    user: 'testuser',
    password: 'testpassword',
  );

  try {
    final result = await connection.execute(
      'SELECT employee_id, first_name FROM employees WHERE department_id = :dept',
      {'dept': 10},
    );

    for (final row in result.rows) {
      print('${row['EMPLOYEE_ID']}: ${row['FIRST_NAME']}');  // by name
      // or: row[0], row[1]                                  // by index
    }
  } finally {
    await connection.close();
  }
}

Usage #

Connecting #

connect takes an EZ Connect string (host:port/service) as the first argument:

final conn = await OracleConnection.connect(
  'dbhost.example.com:1521/ORCL',
  user: 'username',
  password: 'password',
  timeout: const Duration(seconds: 30),
);

Auto-closing with `withConnection` #

await OracleConnection.withConnection(
  'localhost:1521/FREEPDB1',
  user: 'testuser',
  password: 'testpassword',
  callback: (conn) async {
    final result = await conn.execute('SELECT SYSDATE FROM dual');
    print(result.rows.first[0]);
  },
);

Connection Pooling #

OraclePool keeps a bounded set of authenticated sessions and recycles them across borrowers. Acquire and release always belong in try/finally:

final pool = await OraclePool.create(
  'localhost:1521/FREEPDB1',
  user: 'testuser',
  password: 'testpassword',
  minConnections: 2,  // opened and authenticated up front
  maxConnections: 10, // hard upper bound; acquire() waits FIFO when exhausted
  acquireTimeout: const Duration(seconds: 30), // bound queued waits (null = wait forever)
  idleTimeout: const Duration(minutes: 5), // shrink surplus idle sessions (zero = never)
);

try {
  final conn = await pool.acquire();
  try {
    final result = await conn.execute('SELECT SYSDATE FROM dual');
    print(result.rows.first[0]);
  } finally {
    await pool.release(conn); // rolls back uncommitted work, recycles the session
  }

  // Or leak-safe by construction:
  final result = await pool.withConnection(
    (conn) => conn.execute('SELECT SYSDATE FROM dual'),
  );
  print(result.rows.first[0]);
} finally {
  // Optionally wait for borrowed sessions to come back before shutting down.
  await pool.close(drainTimeout: const Duration(seconds: 10));
}

release() rolls back any uncommitted transaction before the session is handed to the next borrower, and quietly destroys sessions that are no longer healthy.

Pool timeouts are opt-in and validated at create time:

acquireTimeout bounds how long a queued acquire() waits once the pool is exhausted; on expiry the acquire fails with an OracleException (ORA-12170). The default null waits indefinitely. It never bounds physical connection establishment — the timeout option does that.
idleTimeout closes surplus idle sessions (beyond minConnections) that sit unused longer than the configured duration; the pool never shrinks below minConnections, and retained sessions keep their statement cache warm. The default Duration.zero disables shrinking.
close(drainTimeout: ...) rejects new acquires immediately, then waits for checked-out sessions to be released (destroying each as it comes back) until the drain timeout expires. Omitting drainTimeout (or passing Duration.zero) closes without waiting — borrowed sessions are destroyed whenever they are eventually released. Repeated close() calls are idempotent and join a pending drain.

Session tagging

Pooled sessions can carry a tag — a client-side label for session state (NLS settings, time zone, …) that user code or a pool callback has applied — so that state is reused instead of reset on every borrow. Request a tag with acquire(tag: ...) / withConnection(tag: ...) and let an optional pool-wide sessionCallback apply the state:

final pool = await OraclePool.create(
  'localhost:1521/FREEPDB1',
  user: 'testuser',
  password: 'testpassword',
  maxConnections: 10,
  sessionCallback: (conn, requestedTag) async {
    if (requestedTag == 'TZ=UTC') {
      await conn.execute("ALTER SESSION SET TIME_ZONE = 'UTC'");
      conn.tag = requestedTag; // the callback records the state it applied
    }
  },
);

final conn = await pool.acquire(tag: 'TZ=UTC'); // session arrives with UTC applied

How it behaves:

Tags are client-side metadata. connection.tag (default '' = untagged) never applies database state by itself; it records state already applied. null and '' requests both mean untagged.
Selection order. For a requested tag the pool prefers an exact-match idle session, then an untagged one, then opening a new connection. A session carrying a different tag is used only when matchAnyTag: true is passed or the sessionCallback can repair its state first — the pool never claims a tag it did not observe.
The callback runs when needed. It is invoked before a borrower receives a session that is brand new or whose tag differs from the requested one, and it must set connection.tag itself once the state matches. If it throws, the candidate session is destroyed and the acquire fails with that error.
Tags survive release. A borrower may update conn.tag before release(); the automatic rollback does not clear it, so the next matching acquire reuses the session (statement cache intact). Without a callback, a tagged request may still return an untagged session — check conn.tag, apply the state yourself, and set the tag.
Queued waiters are tag-aware. A released session goes to the oldest waiter that can accept it; incompatible waiters keep their place in the queue.

Out of scope (by design, this is a pure-Dart thin driver): DRCP, heterogeneous credentials/proxy users, sharding keys, PL/SQL (server-side) session callbacks, pool reconfiguration, and Oracle thick-client tag-matching heuristics (tags are matched as exact strings, not parsed as property lists).

Queries #

// Named bind parameters (preferred)
final result = await connection.execute(
  'SELECT * FROM employees WHERE salary > :min_salary AND department_id = :dept',
  {'min_salary': 50000, 'dept': 10},
);

// Positional bind parameters
final result2 = await connection.execute(
  'SELECT * FROM employees WHERE salary > :1 AND department_id = :2',
  [50000, 10],
);

// Access rows and column metadata
print(result.columnNames);      // ['EMPLOYEE_ID', 'FIRST_NAME', ...]
for (final row in result.rows) {
  print(row['EMPLOYEE_ID']);    // by column name (case-insensitive)
  print(row[0]);                // or by zero-based index
  final map = row.toMap();      // {'EMPLOYEE_ID': 100, 'FIRST_NAME': 'Alice', ...}
}

DML — Insert, Update, Delete #

final result = await connection.execute(
  'UPDATE employees SET salary = salary * 1.1 WHERE department_id = :dept',
  {'dept': 10},
);
print('Rows affected: ${result.rowsAffected}');

Bulk DML — `executeMany()` #

executeMany() runs one statement many times in a single wire round trip: Oracle array DML for INSERT/UPDATE/DELETE/MERGE (including RETURNING ... INTO), and repeated PL/SQL execution with per-iteration binds (node-oracledb executeMany parity). Iterations run inside the current transaction and are not auto-committed — call commit() / rollback() exactly as after execute().

// Array DML — one INSERT executed once per row.
final result = await connection.executeMany(
  'INSERT INTO emp (id, name) VALUES (:1, :2)',
  [
    [1, 'Alice'],
    [2, 'Bob'],
    [3, null], // NULL name
  ],
);
print('Inserted ${result.rowsAffected} rows'); // 3
await connection.commit();

Rows are a List for positional (:1, :2) SQL or a Map for named (:name) SQL. Each slot's Oracle type is inferred from the first non-null value and must stay consistent across all rows; string/RAW slots are sized to the largest value. All row-shape and type validation happens before any wire round trip, and bind values never appear in error messages.

Batch errors and per-row counts. By default the first failing row throws. Pass batchErrors: true to apply every valid row and collect each failed row's data error instead, and dmlRowCounts: true for one affected-row count per input row (both DML-only):

final result = await connection.executeMany(
  'INSERT INTO emp (id, name) VALUES (:1, :2)',
  [[1, 'Alice'], [1, 'Dup'], [2, 'Bob']], // row 1 duplicates the PK
  OracleExecuteManyOptions(batchErrors: true, dmlRowCounts: true),
);
for (final e in result.batchErrors) {
  print('row ${e.offset} failed: ${e.code} ${e.message}'); // row 1: ORA-00001 ...
}
print(result.dmlRowCounts); // [1, 0, 1]
await connection.commit(); // applied rows stay pending until you commit

DML RETURNING ... INTO. Declare every placeholder once via bindDefs (required for RETURNING — the OUT targets carry no per-row value to infer a type from). Results are row-major: one entry per input row, each holding a list of returned values per OUT bind.

final result = await connection.executeMany(
  'INSERT INTO emp (name) VALUES (:1) RETURNING id INTO :2',
  [['Alice'], ['Bob']],
  OracleExecuteManyOptions(bindDefs: [
    OracleBindDef.input(type: OracleDbType.varchar, maxSize: 100),
    OracleBindDef.output(type: OracleDbType.number),
  ]),
);
print(result.outBinds.toList()); // [[[1]], [[2]]]

Single-statement RETURNING ... INTO. A plain execute() of an INSERT/UPDATE/DELETE with a RETURNING ... INTO clause accepts OracleBind.out(...) specs for the INTO targets (the same wire path as executeMany(), one iteration). Each OUT bind yields a List of the returned values, one per affected row (empty when no row matched). OracleBind specs on any other non-PL/SQL statement are still rejected (ORA-06502). Oracle 23ai also supports MERGE ... RETURNING; Oracle 21c does not.

final r = await connection.execute(
  'UPDATE emp SET sal = sal * 1.1 WHERE dept = :d RETURNING id INTO :ids',
  {'d': 10, 'ids': OracleBind.out(type: OracleDbType.number)},
);
print(r.outBinds['ids']); // [1, 4, 9]

Bulk PL/SQL. A BEGIN/DECLARE/CALL block runs once per row (or numIterations times). OUT / IN OUT placeholders are declared once via bindDefs; OUT results are row-major, one entry per iteration:

final result = await connection.executeMany(
  'BEGIN :2 := double_it(:1); END;',
  [[10], [20]],
  OracleExecuteManyOptions(bindDefs: [
    OracleBindDef.input(type: OracleDbType.number),
    OracleBindDef.output(type: OracleDbType.number),
  ]),
);
print(result.outBinds.toList()); // [[20], [40]]

// A positive int runs a bind-free (or all-OUT) PL/SQL block N times:
await connection.executeMany('BEGIN log_tick(); END;', 3);

SELECT / WITH ... SELECT queries, SYS_REFCURSOR OUT binds, PL/SQL collection binds, OracleBind value specs inside rows, RETURNING without bindDefs, and bindDefs on plain (non-returning) array DML all fail loudly before any wire round trip.

Transactions #

// Managed transaction (automatic rollback on exception)
await connection.runTransaction((conn) async {
  await conn.execute(
    'INSERT INTO orders (id, customer_id) VALUES (:id, :cust)',
    {'id': 1001, 'cust': 42},
  );
  await conn.execute(
    'UPDATE inventory SET quantity = quantity - 1 WHERE product_id = :pid',
    {'pid': 100},
  );
});

// Manual commit/rollback
try {
  await connection.execute(
    'INSERT INTO orders (id, total) VALUES (:id, :total)',
    {'id': 1001, 'total': 99.99},
  );
  await connection.commit();
} catch (e) {
  await connection.rollback();
  rethrow;
}

PL/SQL — Stored Procedures & Functions #

// Call a stored procedure
await connection.execute(
  'BEGIN raise_salary(:dept, :pct); END;',
  {'dept': 10, 'pct': 5},
);

// Function return value via an OUT bind
final result = await connection.execute(
  'BEGIN :ret := get_employee_name(:id); END;',
  {
    'ret': OracleBind.out(type: OracleDbType.varchar, maxSize: 100),
    'id': 100,
  },
);
print(result.outBinds['ret']);

// IN OUT parameter
final r = await connection.execute(
  'BEGIN increment(:value); END;',
  {'value': OracleBind.inOut(value: 41, type: OracleDbType.number)},
);
print(r.outBinds['value']); // 42

maxSize is required for varchar and raw OUT binds — size it for the largest value the procedure may return.

TIMESTAMP WITH TIME ZONE parameters bind with OracleDbType.timestampTz. OUT / IN OUT values follow the connection's decode contract: a UTC DateTime by default, or an OracleTimestampTz carrying the server-sent offset on a connection opened with preserveTimestampTimeZone: true. IN OUT input values may be an OracleTimestampTz (the offset travels on the wire) or a plain DateTime.

Result Sets & Streaming #

Large queries can be consumed incrementally instead of materializing every row up front. These paths have no 1,000-fetch safety cap — that bound applies only to eager execute().

Row stream. queryStream() / executeStream() return a single-subscription Stream<OracleRow>. The cursor opens when a subscriber listens and is closed automatically when the stream completes, is cancelled, or errors:

await for (final row in connection.queryStream('SELECT id, name FROM big_table')) {
  print(row['NAME']);
}
// executeStream() is identical; queryStream() is the node-oracledb-parity name.
// Pass a third argument to tune the FETCH batch size: queryStream(sql, binds, 100).

Result set handle. Pass OracleExecuteOptions(resultSet: true) to execute() to get an OracleResultSet in result.resultSet for manual, batched pulls. Column metadata is available before the first fetch. Always close() it (or fully drain it) to release the cursor and free the connection:

final result = await connection.execute(
  'SELECT id, name FROM big_table',
  null,
  OracleExecuteOptions(resultSet: true, fetchSize: 100),
);
final rs = result.resultSet!;
try {
  print(rs.columnNames);                 // available before the first row
  for (var row = await rs.getRow(); row != null; row = await rs.getRow()) {
    print(row.toMap());
  }
  // or pull in batches: final batch = await rs.getRows(50);
} finally {
  await rs.close();
}

A connection owns a single TTC byte stream, so only one result set or row stream may be open on it at a time — overlapping operations fail fast with a concurrent-operation OracleException. Close the result set before running the next statement.

REF CURSOR OUT binds

Bind a PL/SQL SYS_REFCURSOR OUT parameter with OracleBind.out(type: OracleDbType.cursor); the returned cursor arrives in result.outBinds as an OracleResultSet:

final result = await connection.execute(
  'BEGIN open_employees(:rc, :dept); END;',
  {
    'rc': OracleBind.out(type: OracleDbType.cursor),
    'dept': 10,
  },
);
final rs = result.outBinds['rc']! as OracleResultSet;
try {
  for (var row = await rs.getRow(); row != null; row = await rs.getRow()) {
    print(row['FIRST_NAME']);
  }
} finally {
  await rs.close();
}

IN OUT cursor binds are not supported — use OracleBind.out(type: OracleDbType.cursor).

Implicit result sets

Result sets a PL/SQL block returns via DBMS_SQL.RETURN_RESULT surface in result.implicitResults, in server-returned order. By default each element is a fully-drained List<OracleRow>:

final result = await connection.execute('BEGIN report(); END;');
for (final cursor in result.implicitResults) {
  for (final row in cursor as List<OracleRow>) {
    print(row.toMap());
  }
}

Under OracleExecuteOptions(resultSet: true), each element is a lazy OracleResultSet handle instead — close() (or fully drain) each one.

Implicit result rows may carry CLOB / NCLOB / BLOB and JSON columns: they decode exactly like a top-level SELECT's (String, Uint8List, Map/List, and a BLOB IS JSON column as JSON), and under fetchLobs: true (eager path) the LOB columns come back as OracleLob handles. As in node-oracledb, the driver sends the cursor's LOB-prefetch defines before its first fetch.

Nested cursor columns

A CURSOR(SELECT ...) column in a query projection materializes inline on the parent row as a List<OracleRow> (an empty nested cursor is [], a NULL cursor column is null), on the eager, streaming, and result-set paths alike:

final result = await connection.execute('''
  SELECT d.name,
         CURSOR(SELECT e.name FROM employees e WHERE e.dept_id = d.id) AS staff
  FROM departments d
''');
for (final row in result.rows) {
  final staff = row['STAFF']! as List<OracleRow>;
  print('${row['NAME']}: ${staff.map((r) => r['NAME']).join(', ')}');
}

Capping eager results

OracleExecuteOptions(maxRows: N) caps how many rows an eager execute() materializes (0 = unlimited; node-oracledb maxRows parity); check result.moreRowsAvailable to detect truncation. It is a no-op for resultSet: true and the streaming paths, which are already incremental.

final top = await connection.execute(
  'SELECT * FROM employees ORDER BY salary DESC',
  null,
  OracleExecuteOptions(maxRows: 10),
);

TLS/SSL Connections #

import 'package:oracledb/oracledb.dart';

// TLS with certificate validation (recommended for production)
final conn = await OracleConnection.connect(
  'dbhost.example.com:2484/ORCL',
  user: 'username',
  password: 'password',
  tls: TlsConfig.enabled(),
);

// TLS with self-signed certificates (development)
final devConn = await OracleConnection.connect(
  'localhost:2484/FREEPDB1',
  user: 'testuser',
  password: 'testpassword',
  tls: TlsConfig.enabled(verifyCertificate: false),
);

Oracle typically uses port 2484 for TLS connections. TLS is disabled by default.

Health Check #

final isAlive = await connection.ping();

API Reference #

Method	Description
`OracleConnection.connect(...)`	Open a connection
`OracleConnection.withConnection(...)`	Open, use, and auto-close a connection
`connection.execute(sql, [bindValues])`	Run a query, DML statement, or PL/SQL block
`connection.execute(sql, binds, OracleExecuteOptions(...))`	Eager (default), lazy result set (`resultSet: true`), or row-capped (`maxRows:`) execution
`connection.executeMany(sql, rows, [OracleExecuteManyOptions(...)])`	Run one statement across many bind sets in a single round trip (array DML, PL/SQL, RETURNING)
`OracleBindDef.input/output/inputOutput(type: ..., maxSize: ...)`	Declare a placeholder's type & direction for `executeMany` `bindDefs` (PL/SQL and DML RETURNING)
`result.batchErrors` / `result.dmlRowCounts`	Per-row errors / per-row affected-row counts from `executeMany` (opt-in via `OracleExecuteManyOptions`)
`connection.queryStream(sql, [binds])` / `executeStream(...)`	Stream a query's rows as `Stream<OracleRow>`
`OracleBind.out(type: ..., maxSize: ...)`	Declare a PL/SQL OUT bind parameter
`OracleBind.out(type: OracleDbType.cursor)`	Declare a PL/SQL REF CURSOR OUT bind (returned as an `OracleResultSet`)
`OracleBind.inOut(value: ..., type: ...)`	Declare a PL/SQL IN OUT bind parameter
`result.outBinds`	OUT / IN OUT values, by name or position
`result.resultSet`	The `OracleResultSet` when `OracleExecuteOptions(resultSet: true)` was used
`result.implicitResults`	PL/SQL `DBMS_SQL.RETURN_RESULT` result sets (eager `List<OracleRow>` / lazy `OracleResultSet`)
`result.moreRowsAvailable`	Whether an eager result was truncated by the fetch cap or `maxRows`
`resultSet.getRow()` / `getRows([n])`	Pull the next row / next batch from a result set
`resultSet.columnNames`	Result-set column names (available before the first fetch)
`resultSet.close()`	Close the cursor and free the connection
`connection.ping()`	Send a ping to verify the connection is alive
`connection.commit()`	Commit the current transaction
`connection.rollback()`	Roll back the current transaction
`connection.runTransaction(callback)`	Run a callback inside a managed transaction
`connection.close()`	Close the connection
`connection.isConnected`	Whether the connection is open
`connection.isHealthy`	Synchronous connection state check (no network I/O)
`connection.statementCacheSize`	Configured statement cache size

Supported Data Types #

Oracle Type	Dart Type
VARCHAR2, CHAR	`String` (see Character Set Support)
NCHAR, NVARCHAR2, NCLOB	`String` (national charset — see Character Set Support)
NUMBER	`num` / `int` / `double`
DATE	`DateTime`
TIMESTAMP	`DateTime`
TIMESTAMP WITH TIME ZONE	`DateTime` (UTC) — or `OracleTimestampTz` with `preserveTimestampTimeZone: true`
RAW	`Uint8List` (see RAW support)
CLOB	`String` (see CLOB support, Character Set Support)
BLOB	`Uint8List` (see BLOB support); a BLOB `IS JSON` column decodes like JSON (see JSON stored in a BLOB)
JSON (21c+)	`Map<String, Object?>` / `List<Object?>` (see JSON support)
CURSOR / REF CURSOR	`OracleResultSet` (OUT bind) / `List<OracleRow>` (nested `CURSOR()` column) — cursor-embedded CLOB/NCLOB/BLOB columns decode as materialized values; `fetchLobs: true` cannot wrap them as handles (see Result Sets & Streaming, Known Limitations)
NULL	`null`

RAW support #

RAW values round-trip as Dart Uint8Lists. RAW is a scalar binary type — not a LOB and not a BLOB alias: values travel inline on the wire (length-prefixed bytes, never a LOB locator), bytes are preserved exactly with no character-set conversion, and RAW queries keep normal statement-cache cursor reuse:

Queries — selecting a RAW column returns a Uint8List (null for SQL NULL). Oracle stores a zero-length RAW as SQL NULL — there is no empty-but-not-NULL RAW value, so an inserted Uint8List(0) reads back as null.
DML — bind an ordinary Uint8List into a RAW column with named or positional binds. A value longer than the column's declared byte size fails loudly with the Oracle error (ORA-12899) — never truncated or coerced. An empty Uint8List stores SQL NULL (Oracle's zero-length RAW convention); this differs from OracleBind(type: OracleDbType.blob), which can preserve an empty-but-not-NULL BLOB through a temporary LOB.
PL/SQL OUT / IN OUT — declare OracleBind.out(type: OracleDbType.raw, maxSize: ...) or OracleBind.inOut(value: ..., type: OracleDbType.raw, maxSize: ...); values decode through result.outBinds as Uint8List?. maxSize counts bytes and must hold the largest value the procedure may return — an undersized buffer fails loudly with the server's ORA-06502 instead of truncating.

RAW columns hold up to 2000 bytes with MAX_STRING_SIZE=STANDARD (32,767 with EXTENDED); use BLOB for anything larger. LONG RAW is not supported and fails with a clear OracleException.

CLOB support #

CLOB values round-trip as Dart Strings by default (opt into an incremental OracleLob handle with fetchLobs: true):

Queries — selecting a CLOB column returns a String (null for SQL NULL, '' for EMPTY_CLOB()). The driver reads LOB locators transparently in server-chunk-sized pieces; values above 64 KiB are covered by tests.
DML — bind an ordinary String into a CLOB column with named or positional binds. Strings above the 32,767-byte VARCHAR bind limit are handled automatically: Oracle's long-data path for SQL (covered by tests to 40,000 characters) and an internal temporary CLOB for PL/SQL (validated live to ~1 MB per value, ASCII and mixed multibyte/emoji, on both supported server lines).
PL/SQL OUT / IN OUT — declare OracleBind.out(type: OracleDbType.clob, maxSize: ...) or OracleBind.inOut(value: ..., type: OracleDbType.clob, maxSize: ...); values decode through result.outBinds as String?. maxSize counts characters (UTF-16 code units, the same as String.length) and bounds the value the driver will materialize — a longer value fails loudly instead of truncating. The empty string binds as SQL NULL, consistent with Oracle's '' IS NULL semantics.

Statement-cache note: result cursors for queries that select CLOB (or BLOB, or native JSON) columns are always re-parsed, never blind-reused from the statement cache — fresh defines keep the LOB-prefetch row shape intact — while RAW queries keep normal cursor reuse.

BLOB support #

BLOB values round-trip as Dart Uint8Lists by default (opt into an incremental OracleLob handle with fetchLobs: true):

Queries — selecting a BLOB column returns a Uint8List (null for SQL NULL, an empty Uint8List for EMPTY_BLOB()). The driver reads LOB locators transparently and byte-for-byte — no character set conversion ever touches the bytes; values above 64 KiB are covered by tests. The one exception is a BLOB column with an IS JSON check constraint, whose value decodes to a Map / List / scalar — see JSON stored in a BLOB.
DML — bind an ordinary Uint8List into a BLOB column with named or positional binds. Values above the 32,767-byte scalar bind limit are handled automatically: Oracle's long-data path for SQL (covered by tests to 40,000 bytes) and an internal temporary BLOB for PL/SQL (validated live to ~1 MB per value on both supported server lines). An empty Uint8List bound as a plain value in SQL DML stores SQL NULL (it travels as a zero-length RAW, and Oracle maps that to NULL — matching node-oracledb). To store an empty-but-not-NULL BLOB, bind through OracleBind(value: Uint8List(0), type: OracleDbType.blob), which routes the value through a temporary BLOB (see PL/SQL note below).
PL/SQL OUT / IN OUT — declare OracleBind.out(type: OracleDbType.blob, maxSize: ...) or OracleBind.inOut(value: ..., type: OracleDbType.blob, maxSize: ...); values decode through result.outBinds as Uint8List?. maxSize counts bytes and bounds the value the driver will materialize — a longer value fails loudly instead of truncating. An empty Uint8List binds as an empty BLOB value (length 0), which Oracle treats as distinct from SQL NULL — unlike CLOB's empty string.

Large LOB binds through the internal temporary-LOB path are validated live on both Oracle 23ai and 21c: a 1,000,000-byte BLOB and 500,000-character CLOBs (ASCII and mixed multibyte/emoji — ~1 MB as the on-wire UTF-16BE payload) as IN binds and IN OUT round trips, plus IN binds straddling the 64 KiB wire-chunk boundary exactly (BLOB at 65,535/65,536/65,537 payload bytes; CLOB at the nearest even UTF-16BE sizes 65,534/65,536/65,538). On this default path values are still materialized in memory as a single String / Uint8List, so unbounded/multi-gigabyte LOBs are not claimed for it — use fetchLobs and OracleLob.textStream() / byteStream() / writeStream() to move a value in bounded pieces instead. NCLOB columns are supported as Dart String (the national AL16UTF16 charset — see Character Set Support); BFILE columns are not supported and fail with a clear OracleException.

Incremental LOB access with `fetchLobs` #

By default a fetched CLOB/NCLOB/BLOB is materialized whole into a String / Uint8List. Pass OracleExecuteOptions(fetchLobs: true) to execute() instead and each CLOB/NCLOB/BLOB in result.rows / result.outBinds arrives as an OracleLob handle you read (and, for a writable locator, write) in bounded pieces by offset and amount:

final result = await connection.execute(
  'SELECT doc FROM articles WHERE id = :1',
  [42],
  OracleExecuteOptions(fetchLobs: true),
);
final lob = result.rows.single['DOC'] as OracleLob;
try {
  print(lob.length);                       // native units (see below)
  final head = await lob.read(offset: 1, amount: 100) as String;
  final all = await lob.read() as String;  // whole value (default args)
  // getData([offset, amount]) is a positional alias of read(), for
  // node-oracledb parity: lob.getData(1, 100).
} finally {
  await lob.close();                        // always close
}

Units and offsets — offsets are 1-based; offsets, amounts, and length are counted in characters (UCS-2 code units) for CLOB/NCLOB and in bytes for BLOB. A CLOB/NCLOB read returns a String, a BLOB read returns a Uint8List. An offset/amount that splits a UTF-16 surrogate pair (a supplemental/astral character occupies two UCS-2 code units) has a type-dependent outcome — choose boundaries carefully: an NCLOB / variable-length-charset CLOB (decoded UTF-16BE) returns a lone surrogate as a partial character, while a plain CLOB (decoded UTF-8) fails loud with an OracleException because the split leaves an incomplete UTF-8 sequence.
No size ceiling on read() — the requested range is fetched in one LOB READ round trip and, if one cannot carry it all, the read continues at the advanced offset until the range arrives. The bytes are assembled first and decoded exactly once, so a round-trip boundary can never corrupt multibyte or supplemental-plane text. (Measured: every value up to 32 MB on 23ai and 8 MB on 21c still arrives in a single READ.) Use the streams below when the point is to avoid holding the whole value in memory.
Writing — write(offset, data) (a String for CLOB/NCLOB, Uint8List / List<int> for BLOB) requires a writable locator: a LOB OUT/IN OUT bind or a SELECT ... FOR UPDATE locator. Writing through a read-only plain-SELECT locator surfaces the Oracle server error (it is not blocked client-side — measured: ORA-22920, with the handle and connection left usable). Writing past length + 1 extends the LOB and the server fills the gap; the local length advance matches the server's own DBMS_LOB.GETLENGTH.
Trimming — trim(newSize) shortens the LOB to newSize units (same units as length) in one round trip and sets length; trim(0) empties it (an empty LOB, not NULL). It needs a writable locator like write(), and on a persistent LOB the change is part of the current transaction. The server rejects a newSize past the end (ORA-22926), a read-only locator (ORA-22920) and a CLOB/NCLOB size that would split a surrogate pair (ORA-22831), leaving the handle, its length and the connection usable; a negative newSize throws ArgumentError before any round trip.
length and chunkSize are cached — seeded from the fetch, advanced by write(), set by trim(). refresh() re-reads both from the server (one GET_LENGTH and one GET_CHUNK_SIZE round trip), and a read() that starts past the cached length always asks the server instead of the cache — see Cached length below.
Lifecycle — each open handle holds the connection's single in-flight slot: the connection rejects other operations (and the pool force-closes any still-open handle on release) until every handle is close()d. close() is idempotent. A SQL NULL LOB is Dart null under both modes, never a handle.
Multi-row caveat — a multi-row SELECT with a LOB column yields one handle per row (200 rows → up to 200 open handles), and only one handle can be read/written at a time: the connection stays busy — rejecting every other statement, and logging a warning if the pool reclaims it — until you close every handle. Read each row's LOB fully and close() it before moving to the next, and don't leave any handle open. To close them all at once when you're done, call result.closeLobs() (it closes every open handle in rows / outBinds and returns the count):
```
final result = await connection.execute(
  'SELECT id, doc FROM articles',
  null,
  OracleExecuteOptions(fetchLobs: true),
);
try {
  for (final row in result.rows) {
    final lob = row['DOC'] as OracleLob;
    final text = await lob.read() as String; // read one at a time
    await lob.close();                        // ...and close before the next
    // ... use text ...
  }
} finally {
  await result.closeLobs(); // safety net: close any handle left open
}
```

fetchLobs wraps top-level execute() SELECT rows, scalar OUT / IN OUT binds, eager PL/SQL implicit-result rows, and a streaming resultSet: true cursor (see below). It does not reach a CLOB/NCLOB/BLOB carried by a nested CURSOR(...) column or a REF CURSOR OUT bind: those fail loud under fetchLobs (rather than silently materializing or wrapping an untracked handle). Without fetchLobs, such cursor rows decode normally — LOB columns arrive materialized as String / Uint8List and native JSON columns as decoded values. To get an OracleLob handle, project the LOB outside the cursor. See Known Limitations for the shape and the workaround. resultSet: true + fetchLobs: true on a PL/SQL block (whose implicit results come back as a group of lazy result sets) also throws before any wire round trip; use the eager execute() path there. Still not available: BFILE.

Streaming a LOB in and out

textStream() / byteStream() deliver a LOB in bounded pieces — one LOB READ round trip per piece (see the supplemental-character note below for the single exception) — and writeStream() feeds one in from a Stream, so a multi-megabyte value never has to exist whole in memory:

// Read: one round trip per piece, nothing materialized whole.
final sink = File('article.txt').openWrite();
try {
  await for (final piece in lob.textStream(pieceSize: 64 * 1024)) {
    sink.write(piece);              // Stream<Uint8List> for a BLOB (byteStream)
  }
} finally {
  await sink.close();
  await lob.close();
}

// Write: node-oracledb's canonical streamed insert. A DML RETURNING ... INTO
// statement takes OracleBind OUT specs directly; each OUT bind returns a List
// with one value per affected row (a PL/SQL BEGIN/END wrapper also works).
final result = await connection.execute(
  'INSERT INTO articles (id, doc) VALUES (:id, EMPTY_CLOB()) '
  'RETURNING doc INTO :out',
  {'id': 1, 'out': OracleBind.out(type: OracleDbType.clob, maxSize: 1)},
  OracleExecuteOptions(fetchLobs: true),
);
final lob = (result.outBinds['out'] as List).single as OracleLob;
try {
  final written = await lob.writeStream(pieces); // Stream<String>; returns units
  await connection.commit();
} finally {
  await lob.close();
}

Units are the type's native ones: characters (UCS-2 code units) for CLOB/NCLOB, bytes for BLOB — same as read / write / length.
pieceSize defaults to chunkSize when the server reported one and otherwise to OracleLob.defaultStreamPieceSize (32,768; the top-level defaultLobStreamPieceSize is a deprecated alias); a multiple of chunkSize is recommended. Note chunkSize is a byte count for every LOB type (measured: identical for a CLOB and a BLOB with the same CHUNK clause, and equal to DBMS_LOB.GETCHUNKSIZE), not characters like length. The concatenation of the emitted pieces equals read(offset: offset) exactly — for a LOB whose length has not changed since the handle was fetched or last refresh()ed (see the next bullet for the one case where they deliberately differ).
A read stream ends when a READ returns no data, not when the cached length is reached — so it is immune to a length that went stale after the fetch, and it never loops past the end. That terminating empty READ costs one extra round trip, and an empty LOB yields no pieces at all. This is also the one case where a stream and read() disagree, and the stream is the accurate one: for a LOB that grew server-side after the fetch, a read() starting inside the cached length caps its request there and returns a stale prefix, while the stream keeps going to the real end and so returns strictly more. Call refresh() before the read() to close that gap.
Emitted CLOB pieces are not guaranteed whole-character-aligned (the driver picks the boundaries), but they are never corrupt: a split encoding sequence or a trailing unpaired high surrogate is held back and joined to the next piece, so the concatenation is always exact. That carry-over is why a supplemental (astral) character straddling a boundary relaxes both piece-level guarantees: a piece may be one UCS-2 code unit longer than pieceSize, and a piece may be assembled from more than one READ (a READ whose payload is entirely held back emits nothing). A LOB that itself ends with an unpaired high surrogate emits it as the final piece, again to keep the concatenation exact. byteStream has no carry-over and so no exceptions.
writeStream issues one write per caller chunk at the running offset — your chunk size is the wire piece size. An empty chunk costs no round trip, and a surrogate pair split across two chunks is stitched back together (that boundary is the driver's, unlike the caller-chosen boundaries write() stays lenient about); a chunk stream ending on a still-unpaired high surrogate costs one extra write to flush it. It is not atomic: a bad chunk or a server error aborts the rest and leaves what was already written in place.
Cancelling a read stream mid-flight leaves the connection immediately reusable; the handle stays open, so close() it as usual. Liveness is re-checked before every piece, so closing the handle (or losing the connection) mid-stream fails the stream loud rather than truncating it silently.
executeStream() / queryStream() are unrelated — they stream rows and always materialize LOB columns; they take no fetchLobs argument.

Streaming result sets that yield LOB handles

Combine resultSet: true with fetchLobs: true and each batch's LOB columns are wrapped as handles as the batch is prefetched, so a huge result set never holds a handle per row up front:

final result = await connection.execute(
  'SELECT id, doc FROM articles',
  null,
  OracleExecuteOptions(resultSet: true, fetchLobs: true, fetchSize: 100),
);
final rs = result.resultSet!;
try {
  for (var row = await rs.getRow(); row != null; row = await rs.getRow()) {
    final lob = row['DOC'] as OracleLob?;
    if (lob == null) continue;               // SQL NULL LOB
    try {
      await for (final piece in lob.textStream()) { process(piece); }
    } finally {
      await lob.close();                     // close every handle you receive
    }
  }
} finally {
  await rs.close();      // reaps any handle prefetched but never delivered
}

Close every handle you receive — each holds the connection's in-flight slot, and only one can be read at a time. Reading a LOB between two getRow() calls is fine and does not disturb the cursor (verified on Oracle 23ai and 21c).
rs.close() is the safety net — it force-closes every handle the cursor wrapped for a prefetched row you never retrieved, so abandoning iteration early cannot wedge the connection. A handle you already received survives rs.close() and stays readable, which makes the "collect the rows, close the result set, then read the LOBs" pattern work.
result.closeLobs() also reaches a streaming result set's buffered handles, and the pool force-closes everything on release.

Temporary LOBs you create, fill, and bind

createTemporaryClob(), createTemporaryNClob() and createTemporaryBlob() create a new, empty session-duration temporary LOB on the server and return it as a caller-owned OracleLob. Fill it with write(), bind it into an INSERT or a PL/SQL block, then close() it — this is how you build a value above the 32 KB inline bind limit without materializing one giant String/Uint8List:

final lob = await connection.createTemporaryClob();
try {
  await lob.write(1, firstChunk);             // 1-based offsets
  await lob.write(lob.length + 1, nextChunk); // append after what you wrote
  print(lob.length);        // characters (UCS-2 code units); bytes for BLOB
  print(lob.isTemporary);   // true
  print(lob.chunkSize);     // real server chunk size

  await connection.execute(
    'INSERT INTO articles (id, doc) VALUES (:id, :doc)',
    {'id': 1, 'doc': lob},  // binds as the server-side LOB locator
  );
  await connection.commit(); // the temp LOB survives commit()
} finally {
  await lob.close();         // ALWAYS close
}

Units and offsets — identical to a fetched handle: 1-based offsets, counted in characters (UCS-2 code units) for CLOB/NCLOB and in bytes for BLOB. createTemporaryNClob() stores/transfers its data as UTF-16BE and requires the AL16UTF16 national character set (it throws before any wire round trip otherwise).
You must close() it — Oracle holds session resources for every unclosed temporary LOB (visible in V$TEMPORARY_LOBS). close() is idempotent and never throws. The actual server-side free travels as a piggyback on the next execute() on that session, matching node-oracledb: a close() followed only by connection.close() relies on session teardown, and a pooled connection's pending frees are flushed by the next borrower's first execute(). commit() / rollback() / ping() do not flush them.
Releasing a pooled connection closes it for you — if you release() a healthy, idle pooled connection while a temporary LOB you created is still open, the pool logs a warning, force-closes the handle and queues its server-side LOB for freeing on the next borrower's first execute() (or at session teardown). Later read/write operations on that handle fail with a pool-reclaim error; cached properties and another close() remain usable. close() every handle before release().
It does not block other statements — unlike a fetched handle, an open created-temporary handle leaves the connection free to run statements (binding it is the whole point). Individual LOB round trips are still serialized, so await each read() / write().
Session duration, not transaction duration — the LOB survives commit() and rollback().
An empty temporary LOB is not SQL NULL — binding a created-but-unwritten handle stores an empty LOB, deliberately unlike binding an empty String (which Oracle's '' IS NULL semantics store as NULL).
Binding — pass the handle positionally ([lob]), by name ({'doc': lob}), or as OracleBind.inOut(value: lob, type: OracleDbType.clob, maxSize: ...) in a PL/SQL block. Binding never frees the handle: its lifetime is yours. Binding a closed handle, or one belonging to a different connection, throws before any wire round trip. Only created-temporary handles can be bound. A handle obtained from fetchLobs: true holds the connection's single in-flight slot while it is open, so the statement that would bind it is rejected as a concurrent operation (with an error saying exactly that) — read the fetched value, close() the handle, then bind the value or a created-temporary LOB. executeMany() does not accept OracleLob values (it fails loud naming execute()); pass plain String / Uint8List values there and the driver creates a temporary LOB per iteration.
An IN OUT bind consumes the handle — validated on Oracle 23ai and 21c: the server replaces the LOB behind an IN OUT bind, so the returned value in result.outBinds is correct but a later read() on the bound handle raises ORA-22922. Read the returned value (a String / Uint8List, or a fresh OracleLob under fetchLobs: true) and treat the bound handle as spent; you still close() it. A plain IN bind leaves the handle fully usable.
Cached length — length (and chunkSize) are seeded from the locator's prefetch metadata, advanced locally by write() and set by trim(); only refresh() re-reads them from the server. How read() uses the cached length depends on where it starts:
- past the cached end (offset > length, including any read of an empty handle): the server is asked, never the cache. If the LOB really ends there the empty value comes back after one READ round trip; if it grew, the read continues piece by piece (like the streams) until the requested amount arrives or the server reports the end, returns the new data, and advances length. (1.4.0 returned the empty value here without asking — silently wrong for a LOB that had grown.)
- inside the cached range: the request is capped at the cached length, so for a LOB that grew the read returns the prefix up to the stale end (no error), and for one that shrank a read for the full cached length can fail loud (a length mismatch, or "LOB read made no progress") rather than return short data. The remedy for both is refresh() before the read (or a re-select).
textStream() / byteStream() never consult length and stop on the server's empty READ. Measured on Oracle 23ai and 21c: because a LOB locator is read-consistent, a handle fetched before another session's committed update keeps seeing the old value in full — correct data, never silently truncated — in both directions, its READ past the old end comes back empty, and refresh() reports the old length too, so length and read() always agree; a re-select is what surfaces the new value. refresh() and trim() fail with ORA-22275 on the locator of an EMPTY_CLOB() / EMPTY_BLOB() value that was never stored (SELECT EMPTY_CLOB() FROM dual, or a PL/SQL :out := EMPTY_CLOB()), which read() handles fine.

OracleLob.isTemporary reports whether a handle references a temporary LOB — always true for a created one, and true for a fetched locator that carries the server's temporary flag or its abstract (server-expression) LOB flag. Measured on 23ai and 21c: a plain table LOB, a SELECT ... FOR UPDATE locator and an EMPTY_CLOB() value report false (they allocate no session temp-LOB space). Every server expression LOB reports true — a SELECT TO_CLOB(...) / TO_BLOB(...) value, a CLOB concatenation, and a PL/SQL CLOB/BLOB OUT bind the body fills from a VARCHAR2 / RAW value or a TO_CLOB literal: each one holds a V$TEMPORARY_LOBS.ABSTRACT_LOBS entry until freed. The driver frees them for you when it materializes the value (fetchLobs: false), and a fetched handle frees its LOB on close(). An OUT locator the body assigns from a temporary input LOB (p_out := p_in) and an IN OUT bind's returned locator report true as well.

If two open handles reference the same server LOB — a created temporary LOB bound as a plain IN into a block that returns it as an OUT value (p_out := p_in) under fetchLobs: true — the free is queued only when the last of them closes, so closing one never destroys the LOB under the other.

JSON support #

Oracle's native JSON data type (introduced in Oracle Database 21c; requires database compatible >= 20) round-trips as ordinary Dart values — Map<String, Object?> for objects, List<Object?> for arrays, or a Dart scalar for a scalar JSON root. Members are null / bool / num / String and nested maps/lists, plus the Oracle-specific scalars listed below — in both directions: values within the bind limits can be bound back, subject to the date and number narrowings listed below. On the wire, values travel in Oracle's binary JSON format (OSON) under the native type — no jsonEncode()/jsonDecode() round-trip through text, no Oracle Client, no LOB read round trips:

Queries — selecting a JSON column returns a Map, List, or scalar (null for SQL NULL or a JSON null root). Document shape and member order are preserved and multi-batch fetches work normally. As with CLOB / BLOB queries, a repeated JSON SELECT keeps its statement-cache slot but its cursor is re-parsed with fresh defines rather than blind-reused.

Oracle-specific scalars — a JSON value may also hold Oracle scalars the server keeps in their native form (e.g. from JSON_SCALAR(SYSDATE) or JSON_OBJECT(... RETURNING JSON) over typed columns). They map as follows, wherever they appear in the document, when fetching and when binding:

Scalar inside the JSON value	Dart type (fetch)	Bound from Dart as
`DATE`, `TIMESTAMP`	`DateTime` — same as a `DATE` / `TIMESTAMP` column (local wall clock, fractional seconds truncated to microseconds)	A local `DateTime` → a `TIMESTAMP` (7 bytes with no sub-second part, else 11), written from its wall-clock fields exactly like a `DATE` / `TIMESTAMP` column bind. A UTC `DateTime` (`isUtc`) names an instant, so it travels as `TIMESTAMP WITH TIME ZONE` at `+00:00` instead and fetches back as an `OracleTimestampTz` for the same instant
`TIMESTAMP WITH TIME ZONE`	Always `OracleTimestampTz` (UTC instant + original offset), regardless of `preserveTimestampTimeZone` — unlike a column, which returns a UTC `DateTime` by default	`OracleTimestampTz` → `TIMESTAMP WITH TIME ZONE`, offset preserved
`BINARY_FLOAT`, `BINARY_DOUBLE`	`double` (NaN and ±Infinity included; `BINARY_FLOAT` widened from single precision)	Doubles outside Oracle `NUMBER`'s range (magnitude ≥ 1E126, or a non-zero magnitude below 1E-130) → `BINARY_DOUBLE`; every other finite `num`, including `0.0` / `-0.0`, stays `NUMBER`. NaN and ±Infinity are rejected by default (strict JSON has no such values) with an `ArgumentError` naming the member's path; opt in per bind with `OracleBind.inOut(..., type: OracleDbType.json, allowNonFinite: true)` to send them as `BINARY_DOUBLE`. node-oracledb sends NaN as `BINARY_DOUBLE` unasked and cannot bind ±Infinity or out-of-range doubles at all
`RAW` / binary	`Uint8List` — an empty binary JSON scalar is an empty list of bytes; an empty `RAW` column decodes to `null`	`Uint8List` → binary (bytes, never an array of numbers)

Any fetched document therefore binds back without conversion and fetches back equal — except that NaN / ±Infinity members re-bind only through the allowNonFinite opt-in (a PL/SQL IN OUT JSON parameter), and field names over 255 UTF-8 bytes only on Oracle 23ai — with these narrowings of the stored form:

a DATE node re-binds as a 7-byte TIMESTAMP (same DateTime);
a finite BINARY_FLOAT / BINARY_DOUBLE within NUMBER's range re-binds as NUMBER (equal value; an integral value then decodes as int, and a BINARY_FLOAT keeps its widened single-precision value, e.g. 3.140000104904175);
-0.0 re-binds as NUMBER zero and comes back as 0;
nanoseconds were already truncated to microseconds on fetch;
a fetched DateTime is a local wall-clock value (as from a DATE column), and re-binds from those same fields.

BCE dates and region-name time zones (e.g. US/Pacific) fail loudly with OracleException on fetch, as they do for columns; binding a DateTime / OracleTimestampTz outside years 1–9999 fails loudly at the call site. Fetched documents holding these Dart types are not plain JSON, so jsonEncode() on one can throw JsonUnsupportedObjectError.

DML — bind an ordinary Map<String, Object?> or List<Object?> into a JSON column with named or positional binds (execute() or executeMany()); the driver encodes it as OSON. Members may be null, bool, num, String, DateTime, OracleTimestampTz, Uint8List, and nested maps/lists; anything else (e.g. Set, a non-String key, an arbitrary object) fails loudly at the call site with ArgumentError (OracleException with oraBindTypeError from executeMany()) before any round trip, and the error never echoes the document — it names the offending member by position (list index, or 1-based object member ordinal such as [member #2]), never by value or key. The same applies to:
- NaN / ±Infinity — rejected by default; a DML or executeMany() bind has no opt-in, only OracleBind.inOut(..., allowNonFinite: true) does.
- Field names — up to 255 UTF-8 bytes on every server; up to 65535 bytes on Oracle 23ai (the document then travels as OSON version 3, and the server can query those names). An older server refuses longer names at the call site: Oracle 21c would store such a document but could not read it back.
- Nesting — objects and arrays may nest at most 1000 levels (Oracle's JSON limit); a deeper value, or a cyclic one (a map or list that contains itself), fails instead of overflowing the stack. A sub-document shared by two members is fine.
Each document is validated once per bind. Numeric members follow the same NUMBER contract as scalar columns: integers beyond 2⁵³ decode to double and lose precision (see Known limitations).
Scalar JSON values — a bare String, num, DateTime, Uint8List, … bind keeps its usual column type (VARCHAR2, NUMBER, DATE, RAW), so a String bound into a JSON column is parsed by the server as JSON text. A scalar JSON value binds only through PL/SQL, where the JSON type can be declared explicitly: an OracleBind.inOut(value: 'x', type: OracleDbType.json, maxSize: ...) bound to an IN OUT JSON parameter, or executeMany() of a PL/SQL block with OracleBindDef.input(type: OracleDbType.json) in bindDefs (e.g. BEGIN INSERT INTO t (doc) VALUES (:1); END;). Plain SQL DML cannot bind one: its binds always take their type from the value, and executeMany() rejects bindDefs for plain array DML. The root may be any supported scalar (String, num, bool, DateTime, OracleTimestampTz, Uint8List). A declared-JSON String is a JSON string scalar, never parsed: '{"a":1}' stores the string "{\"a\":1}" — bind the Map itself, not jsonEncode(map).
PL/SQL OUT / IN OUT — declare OracleBind.out(type: OracleDbType.json, maxSize: ...) or OracleBind.inOut(value: ..., type: OracleDbType.json, maxSize: ...); values decode through result.outBinds. maxSize counts OSON bytes (the binary wire encoding) and bounds the returned document — a larger return fails loudly instead of truncating. OracleBind.inOut(..., allowNonFinite: true) lets the value hold NaN / ±Infinity (sent as BINARY_DOUBLE); the flag is JSON-only and rejected on other types.

Creating JSON tables — tablespace requirement. A native JSON column requires a tablespace that uses Automatic Segment Space Management (ASSM). The default USERS tablespace qualifies; the SYSTEM tablespace does not, so CREATE TABLE ... (doc JSON) run as a user whose default tablespace is SYSTEM (e.g. the system account) fails with ORA-43853: JSON type columns are not allowed in this tablespace on every server version. Add an explicit TABLESPACE USERS (or any ASSM tablespace) to the CREATE TABLE, or grant the schema a default ASSM tablespace. This is an environment/DDL requirement, not a driver limitation.

Native vs. textual JSON. The sections above cover the dedicated JSON column type (OSON-backed, 21c+). JSON stored the pre-21c way in a BLOB column with an IS JSON check constraint decodes to the same Dart values — see JSON stored in a BLOB below. JSON text in VARCHAR2 / CLOB (or RAW) IS JSON columns keeps its ordinary String / Uint8List behavior; parse it with dart:convert or query it through SQL/JSON functions. JSON text inserted into a native JSON column is parsed by the server and reads back as Map/List.

Not in scope: SODA, JSON-relational duality views, and the Oracle-specific JSON scalars beyond date/time, binary and float/double — INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, JsonId, and VECTOR inside JSON documents. Documents containing such scalars fail loudly with OracleException (oraUnsupportedType, naming the scalar kind) rather than decoding silently or partially, and no Dart value binds as one.

JSON stored in a BLOB (`IS JSON`)

The Oracle 12c pattern — a BLOB column constrained IS JSON (or IS JSON FORMAT OSON) — reads back exactly like a native JSON column: Map<String, Object?>, List<Object?>, or a Dart scalar, with SQL NULL as null. It works for an eager execute(), resultSet: true, queryStream() / executeStream(), and implicit result sets (validated on Oracle 21c and 23ai; the decision comes from the column describe, never from the server version), so applications no longer hand-decode bytes per server version:

await connection.execute(
  'CREATE TABLE orders (id NUMBER, doc BLOB CHECK (doc IS JSON))',
);
await connection.execute(
  'INSERT INTO orders VALUES (1, :doc)',
  {'doc': {'item': 'rocket', 'qty': 2}}, // a Map binds as JSON (21c+)
);
final result = await connection.execute('SELECT doc FROM orders');
final doc = result.rows.first['DOC'] as Map<String, Object?>; // not bytes

Detection follows node-oracledb: the column describe carries an IS JSON (or OSON) flag, exposed as ColumnMetadata.isJson / ColumnMetadata.isOson (raw word: ColumnMetadata.udsFlags). The flag belongs to the table column — a view over the table, or an expression such as TO_BLOB(doc), describes without it and returns plain Uint8List.
Content — bytes starting with the OSON magic (IS JSON FORMAT OSON columns) decode as OSON, including the Oracle-specific scalars listed above. Anything else is JSON text: UTF-8 (with or without a byte-order mark), or UTF-16LE / UTF-16BE (with a byte-order mark, or detected from the zero-byte pattern without one — Oracle accepts both).
Writes need nothing new: bind a Map / List (it travels as native JSON, so the server needs native JSON support — 21c+ with compatible >= 20; it is stored as JSON text, or as OSON in a FORMAT OSON column), or bind the JSON text bytes as a Uint8List (utf8.encode(jsonEncode(doc))) on any server. Oracle-specific scalar members (DateTime, OracleTimestampTz, Uint8List, and NaN/±Infinity through the allowNonFinite opt-in) are converted by the server: a text IS JSON column stores Oracle's JSON text serialization — dates as ISO-8601 strings ("2024-03-15T10:20:30"), binary as a hex string ("DEAD"), NaN/±Infinity as "Nan" / "Inf" / "-Inf" — so they read back as Strings, while an IS JSON FORMAT OSON column keeps them and reads back the same Dart types (measured on 21c and 23ai).
Fails loud — a value the driver cannot decode throws OracleException (oraUnsupportedType, naming the column) for the whole call; it never returns raw bytes, null, or a partial result. The default IS JSON constraint is lax, so Oracle also accepts values that are not strict JSON and that therefore fail here: lax syntax (unquoted field names, single-quoted strings, upper-case TRUE), trailing NUL bytes, and a zero-length EMPTY_BLOB(). Use IS JSON STRICT to keep such values out, or read them as bytes with rawJsonBlobs: true (below).
Raw-bytes opt-out — OracleExecuteOptions(rawJsonBlobs: true) returns such a column as the stored Uint8List (its type before 1.5.0): no decode and no JSON validation (lax or empty values read without an error), SQL NULL still null. It applies to every row of that call — eager execute(), every batch of resultSet: true, implicit results (eager and resultSet: true), and REF CURSOR OUT bind / nested CURSOR() rows — and only to BLOB IS JSON columns: a native JSON column in the same row still decodes. queryStream() and executeStream() have no fetch options; executeMany() has options, but no rawJsonBlobs option for its implicit result rows. To read such a column as bytes, use a separate execute() SELECT with rawJsonBlobs: true (and resultSet: true for large results).
```
final raw = await connection.execute(
  'SELECT doc FROM orders',
  null,
  const OracleExecuteOptions(rawJsonBlobs: true),
);
final bytes = raw.rows.first['DOC'] as Uint8List;
```
OracleExecuteOptions(fetchLobs: true) still returns an OracleLob handle whose read() yields the stored bytes untouched, and wins when both options are set. It too affects only the LOB columns. For PL/SQL implicit results, use eager execute() with fetchLobs; the combination of resultSet: true and fetchLobs: true is unsupported for a PL/SQL block.
Behavior change (1.5.0): before this release such a column returned a Uint8List; code that decoded those bytes itself must now accept the decoded value or pass rawJsonBlobs: true. Decoding by default is this driver's choice — node-oracledb returns the raw Buffer unless oracledb.future.oldJsonColumnAsObj is enabled.
Not covered: fetchLobs: true for a REF CURSOR OUT bind or nested CURSOR() column carrying the BLOB — those rows decode without the option, but embedded LOB handles are not supported (see Known Limitations); and CLOB / VARCHAR2 / RAW IS JSON columns, which stay String / Uint8List; and a BLOB returned through a PL/SQL OUT bind or RETURNING ... INTO, which carries no column describe flag and so stays a Uint8List.

Character Set Support #

This driver follows node-oracledb's thin-mode character-set model: the client always negotiates UTF-8 (AL32UTF8) on the wire, and the Oracle server performs any conversion to and from the database's own character set. There is no configurable client-side database-charset codec, and the driver does not read the NLS_LANG environment variable — character handling is delegated to the server exactly as Oracle's own thin drivers do. connection.charsetInfo exposes the detected databaseCharset / nationalCharset for diagnostics (OracleCharsetInfo).

Database character set — `VARCHAR2`, `CHAR`, `CLOB` #

Because the client speaks UTF-8 and the server converts, the database character set is transparent to your code:

AL32UTF8 (the Oracle default since 12c) round-trips directly.
Single-byte / non-AL32UTF8 database character sets round-trip via Oracle's server-side conversion. This is validated end-to-end against WE8MSWIN1252 (Windows-1252 Western European), exercising characters that are specific to that code page — euro sign, smart quotes, en/em dashes, ellipsis, bullet — so the round-trip proves real UTF-8 ⇆ server conversion rather than a byte-identity accident.

You never select a client codec; the driver keeps primary text on the UTF-8 wire path regardless of the database character set.

National character set — `NCHAR`, `NVARCHAR2`, `NCLOB` #

National-character types are supported when the database's national character set is AL16UTF16 — the standard (and only non-deprecated) national charset, which both validated server lines (Oracle 23ai and 21c) use. National values travel the wire as UTF-16BE and map to Dart String:

Selecting an NCHAR / NVARCHAR2 / NCLOB column returns a String.
Bind national data with OracleDbType.nVarchar (NCHAR / NVARCHAR2) or OracleDbType.nClob (NCLOB). connection.charsetInfo.supportsNationalCharacterSet is true when the server's national charset is AL16UTF16.

Fail-loud on unsupported national character sets #

If a connection's national character set is not AL16UTF16 (for example the deprecated UTF8 national charset, which thin mode does not support), the driver fails loud with an OracleException on any NCHAR / NVARCHAR2 / NCLOB bind or column — it never silently produces mojibake. Ordinary VARCHAR2 / CHAR / CLOB columns are unaffected and keep working over the UTF-8 wire path regardless of the national character set.

Thin-model boundaries #

No NLS_LANG parsing — the environment variable is ignored (node-oracledb thin parity).
No configurable client-side database-charset codec — the server performs database-charset conversion.
One national character set is supported: AL16UTF16.

Project Status #

This package implements a subset of the full Oracle driver feature set. Below is the current roadmap:

Epic	Status
Core connection & authentication	✅ Done
Query execution & transactions	✅ Done
PL/SQL execution (stored procedures, functions)	✅ Done
Advanced data types (CLOB, BLOB, RAW, JSON)	✅ Done
Connection pooling	✅ Done
Result sets, streaming, REF CURSOR & implicit results	✅ Done
Character set support (`AL32UTF8` + non-`AL32UTF8` databases, national types)	✅ Done
Bulk DML (`executeMany()` — array DML, RETURNING, bulk PL/SQL, batch errors)	✅ Done
LOB streaming & temporary LOBs (`OracleLob`, chunked read/write, `textStream`/`byteStream`/`writeStream`, `createTemporary*`)	✅ Done

Planned After 1.0 #

After the 1.0 release, the project roadmap includes larger API and compatibility work. These items are planned candidates and may change as the APIs are designed and validated:

Priority	Planned enhancement
1	Extended JSON / OSON parity (interval/VECTOR/`JsonId` JSON scalars, OSON-in-BLOB helpers)
2	TIMESTAMP WITH TIME ZONE region-name compatibility and optional temporal fetch formatting
3	Type completeness for `INTERVAL`, `ROWID` / `UROWID`, and `VECTOR`

Tests #

The project has an extensive test suite:

Unit tests — covering protocol, crypto, transport, and connection layers
Integration tests — run against real Oracle instances via Docker

# Unit tests (no database required)
dart test test/src/

# Integration tests — Oracle 23ai
docker compose up -d
RUN_INTEGRATION_TESTS=true dart test test/integration/

# Integration tests — Oracle 21c
#   On Apple Silicon, 21c has no native ARM build — run it under a Colima
#   x86_64 VM (see CONTRIBUTING.md for full setup):
#     colima start --arch x86_64 --cpu 6 --memory 8 && docker context use colima
docker compose --profile oracle21c up -d oracle21c
RUN_INTEGRATION_TESTS=true ORACLE_PORT=1522 ORACLE_SERVICE=XEPDB1 dart test test/integration/

The non-AL32UTF8 character-set round-trip is proven by an optional, local-only fixture (a WE8MSWIN1252 database on port 1523). It is double-gated behind RUN_NON_AL32UTF8_TESTS and is not part of docker compose up — see CONTRIBUTING.md for the full walkthrough. In CI it runs as a manually-dispatched (workflow_dispatch) job rather than a blocking push/PR check (the gvenzl cold-start plus charset migration is slower and less proven than the standard service jobs):

# Optional: non-AL32UTF8 (WE8MSWIN1252) database charset round-trip
docker compose --profile non-al32utf8 up -d oracle-non-al32   # wait for healthy
RUN_INTEGRATION_TESTS=true RUN_NON_AL32UTF8_TESTS=true \
  dart test test/integration/charset_non_al32utf8_integration_test.dart

Known Limitations #

Character sets — primary character data (VARCHAR2/CHAR/CLOB) is handled via Oracle's server-side conversion while the client always negotiates UTF-8, so both AL32UTF8 and non-AL32UTF8 database character sets are supported (validated against WE8MSWIN1252). National types (NCHAR/NVARCHAR2/NCLOB) require the AL16UTF16 national charset and otherwise fail loud — there is no client-side codec and NLS_LANG is ignored. See Character Set Support for the full matrix.
Statement cache and external DDL — top-level DDL executed on the same connection clears the statement cache, but DDL issued from another session (or inside a PL/SQL block) can leave stale cached SELECT metadata. This matches node-oracledb thin-mode behavior.
CLOB/BLOB/JSON cursor re-parse — result cursors for queries selecting CLOB, BLOB or native JSON columns are always re-parsed, never blind-reused from the statement cache (fresh defines are required to keep the LOB-prefetch metadata); RAW queries keep normal cursor reuse.
NUMBER beyond 2⁵³ — integer-valued NUMBERs larger than 2⁵³ decode to double and lose precision (Dart double limit; matches node-oracledb).
Nested CURSOR() / REF CURSOR rows carrying a LOB with fetchLobs: true — a CLOB/NCLOB/BLOB column projected by a nested CURSOR(...) column or a SYS_REFCURSOR OUT bind is not wrapped as an OracleLob handle: with fetchLobs: true the call fails loud with an OracleException (the cursor-embedded handle lifecycle is not built), after the execute round trip and leaving the connection usable. Without fetchLobs these rows decode normally on Oracle 23ai and 21c — LOBs materialize as String / Uint8List, and native JSON columns decode to their Dart values (the driver sends a DEFINE call before the cursor's first FETCH, as node-oracledb does). Workaround for a handle: project the LOB column outside the cursor (join it into the outer SELECT, or return its key from the cursor and read the LOB in a second statement). DBMS_SQL.RETURN_RESULT implicit result sets behave the same way: their LOB / JSON columns decode normally.
One operation per connection — a connection supports one in-flight operation; overlapping execute() calls throw. Use a connection pool (OraclePool) or separate connections for concurrent work. As of 1.4 an open fetched OracleLob (from fetchLobs: true) also holds that slot — for the handle's whole lifetime, not just during a round trip, and including handles a streaming result set prefetched but you never asked for — so an unclosed LOB handle is the most likely cause of a concurrent-operation error; a created-temporary handle (createTemporaryClob() and friends) is deliberately exempt and can be read, written, and bound while open. See Incremental LOB access with fetchLobs.
Pre-12c authentication — password-verifier paths used by pre-12c servers are untested; the validated matrix is Oracle 21c (classical auth) and 23ai (FAST_AUTH).
Region-id time zones — TIMESTAMP WITH TIME ZONE values stored with a region id (e.g. Europe/Madrid) are rejected on decode; offset-based zones (e.g. +02:00) are supported. Region-name compatibility is planned as a post-1.0 temporal enhancement.
Very large result sets — a single execute() is bounded by a safety cap of 1,000 fetch round-trips (about 50,000 rows at the default fetch size); if the cap is hit, a warning is logged, the rows fetched so far are returned, and result.moreRowsAvailable is true so the truncation is detectable. The same flag is also set (with a logged warning) in the rarer case where the server reports more rows pending but the driver has no usable cursor id to continue fetching — in either case true means the rows are an incomplete prefix of the full result set. Streaming (queryStream() / executeStream()) and result-set (OracleExecuteOptions(resultSet: true)) consumption have no such cap — prefer them for very large result sets.

Thin Mode Limitations #

This driver implements Oracle's thin-mode protocol. The following features require Oracle Client (thick mode) and are not supported:

SODA (Simple Oracle Document Access)
Continuous Query Notification (CQN)
External/Kerberos authentication
Native Network Encryption (NNE)
Application Continuity
Client Result Cache

Requirements #

Dart SDK >= 3.12.0
Oracle Database 21c or later (older versions may work but are untested)

Contributing #

See CONTRIBUTING.md for development setup, test instructions, and contribution guidelines.

License #

Apache License 2.0 — see LICENSE for details.

Acknowledgments #

node-oracledb — Oracle's official Node.js driver, whose thin-client protocol implementation this project ports to Dart
python-oracledb — Oracle's official Python driver, used as a cross-reference for protocol details
Ian Redfern's TNS/TTC protocol documentation

oracledb 1.5.0 oracledb: ^1.5.0 copied to clipboard

Metadata