oracledb

A pure Dart Oracle Database driver implementing the thin-mode TNS/TTC wire protocol. No Oracle Client libraries required.

Pub Version License Dart SDK

Stable API. Connections, authentication, queries, DML, transactions, statement caching, PL/SQL (stored procedures, functions, OUT/IN OUT binds), CLOB-as-String, BLOB-as-Uint8List, RAW-as-Uint8List, and native JSON-as-Map/List are implemented and validated against Oracle 23ai and 21c. Connection pooling is complete: OraclePool.create() builds a pool of prewarmed authenticated sessions, acquire()/release() borrow and recycle them (with automatic rollback of uncommitted work on release), withConnection() wraps the pair leak-safely, queued acquires can be bounded with acquireTimeout, surplus idle sessions shrink back to minConnections with idleTimeout, close(drainTimeout: ...) waits for borrowed sessions on shutdown, and session tagging (acquire(tag: ...) with an optional sessionCallback) reuses session state such as NLS settings across borrowers. As of 1.1, query results can also be consumed incrementally — OracleResultSet and queryStream() / executeStream() stream rows without materializing them, PL/SQL REF CURSOR OUT binds and DBMS_SQL.RETURN_RESULT implicit result sets are consumable, and nested CURSOR() columns materialize inline (except when a cursor row carries a CLOB/NCLOB/BLOB column — see Known Limitations). As of 1.2, non-AL32UTF8 database character sets are supported: the client always negotiates UTF-8 and the server converts, so VARCHAR2/CHAR/CLOB text round-trips correctly on single-byte databases too (validated against WE8MSWIN1252), with national types (NCHAR/NVARCHAR2/NCLOB) supported on AL16UTF16. As of 1.3, bulk DML is available: executeMany() runs one statement across many bind sets in a single round trip — array INSERT/UPDATE/DELETE/MERGE (including RETURNING ... INTO), repeated PL/SQL with per-iteration binds, optional row-level batch errors, and per-row affected-row counts. As of 1.4, LOBs can be handled as server-backed OracleLob handles instead of whole values: OracleExecuteOptions(fetchLobs: true) returns a handle with bounded read()/write(), textStream()/byteStream()/writeStream() transfer a value piece by piece, createTemporaryClob()/createTemporaryNClob()/createTemporaryBlob() create session-duration temporary LOBs freed on close(), a created-temporary OracleLob can be bound as a value, and resultSet: true + fetchLobs: true streams a SELECT one handle per row. Depend on oracledb: ^1.4.0; the public API now follows semantic versioning (breaking changes bump the major version).

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, and nested CURSOR() columns (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(), textStream()/byteStream()/writeStream() for piece-by-piece transfer, and createTemporaryClob()/createTemporaryNClob()/createTemporaryBlob() for session-duration temporary LOBs
  • 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.4.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]]]

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.

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)
JSON (21c+) Map<String, Object?> / List<Object?> (see JSON support)
CURSOR / REF CURSOR OracleResultSet (OUT bind) / List<OracleRow> (nested CURSOR() column) — cursor rows carrying a CLOB/NCLOB/BLOB column are not decodable (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) 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.
  • 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.

  • 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), because a cursor row carrying a LOB cannot be decoded by this driver at all yet — such statements fail with a protocol error even without fetchLobs, and on Oracle 21c that failure also leaves the connection unusable. Note that fetchLobs: true only reaches this rejection for the REF CURSOR OUT bind half, and only after the statement has executed and its describe decoded (what it saves you is the row FETCH that poisons the session). For a nested CURSOR(...) column the EXECUTE response itself fails to decode, so the check never runs and fetchLobs makes no difference — the only remedy is to 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 and trim().

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. This driver accepts
// OracleBind specs only inside PL/SQL, so the DML RETURNING is wrapped in a block.
final result = await connection.execute(
  'BEGIN INSERT INTO articles (id, doc) VALUES (:id, EMPTY_CLOB()) '
  'RETURNING doc INTO :out; END;',
  {'id': 1, 'out': OracleBind.out(type: OracleDbType.clob, maxSize: 1)},
  OracleExecuteOptions(fetchLobs: true),
);
final lob = result.outBinds['out'] 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 defaultLobStreamPieceSize; a multiple of chunkSize is recommended. The concatenation of the emitted pieces equals read(offset: offset) exactly — for a LOB whose length has not changed since the handle was fetched (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, read() caps its request at the cached length and returns a stale prefix, while the stream keeps going to the real end and so returns strictly more.
  • 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.
  • 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 is seeded from the locator's prefetch metadata and advanced locally by write(); it is never re-read from the server (node-oracledb parity). So if the LOB grew server-side after the handle was fetched, read() stops at the cached length, and read(offset: length + 1) returns the empty value without any round trip. The remedy for that direction is to re-select the value: an explicit amount is still capped at the cached length (it can only make a read shorter), so it guards the opposite direction — a LOB that shrank, where a read for the full cached length can fail loud (a length mismatch, or "LOB read made no progress", depending on whether the server reports the units it actually delivered) and asking only for units you know exist avoids it. textStream() / byteStream() are immune by construction: they 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, so the shrunk-direction mismatch was not reproducible live on either fixture.

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. Measured on 23ai and 21c: a plain table LOB, a SELECT ... FOR UPDATE locator, a SELECT TO_CLOB(...) expression and a PL/SQL CLOB OUT bind the body fills from a TO_CLOB literal all report false (their locators carry no temporary flag and allocate no session temp-LOB space). "A PL/SQL LOB OUT bind" is not one answer, though — how the OUT value was produced decides: an OUT locator the body assigns from a temporary input LOB (p_out := p_in, where p_in arrived as the driver's temporary LOB for a String / Uint8List bind) is TEMP-flagged, and the driver queues its free. An IN OUT bind's returned locator likewise reports true and frees itself on close().

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, with members decoding as null / bool / num / String and nested maps/lists. 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 (null for SQL NULL). Document shape and member order are preserved; statement-cache cursor reuse and multi-batch fetches work normally.
  • DML — bind an ordinary Map<String, Object?> or List<Object?> into a JSON column with named or positional binds; the driver encodes it as OSON. Members may be null, bool, finite num, String, and nested maps/lists; anything else (e.g. DateTime, Uint8List, Set, NaN) fails loudly at the call site. Field names are limited to 255 UTF-8 bytes (the limit shared by all supported server versions). Numeric members follow the same NUMBER contract as scalar columns: integers beyond 2⁵³ decode to double and lose precision (see Known limitations).
  • 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.

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. This support covers the dedicated JSON column type (OSON-backed, 21c+). JSON text stored in VARCHAR2/CLOB/BLOB columns — the pre-21c pattern with IS JSON constraints — 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, JsonId, VECTOR, and Oracle-specific JSON scalar types (dates/timestamps/intervals/binary inside JSON documents). Documents containing such scalars fail loudly with OracleException rather than decoding silently.

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 (Oracle-specific 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 cursor re-parse — result cursors for queries selecting CLOB or BLOB 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 — a row returned by a nested CURSOR(...) column or a SYS_REFCURSOR OUT bind that projects a CLOB/NCLOB/BLOB column cannot be decoded by this driver, with or without fetchLobs. Measured on both Oracle 23ai and 21c, such a statement fails with a protocol error (ORA-12547 / buffer underrun) as the cursor's rows are decoded; on Oracle 21c the malformed stream also leaves the connection unusable, so the session is lost and must be replaced (via OraclePool this happens on reclaim). This is the one fail-loud boundary in this driver that does not leave the connection usable. fetchLobs: true does not rescue the two shapes equally, because every LOB pre-check runs on already-decoded rows: for a nested CURSOR(...) column the decode fails first (inside the EXECUTE response), so fetchLobs changes nothing and the 21c session is lost either way; for a SYS_REFCURSOR OUT bind the describe decodes fine, so with fetchLobs: true you get the driver's clear rejection — after the execute round trip, but before the row FETCH that would poison the connection — and the session stays healthy. Workaround (the only remedy for the nested-CURSOR() shape): 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).
  • 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

Libraries

oracledb
Pure Dart Oracle Database driver implementing thin-mode TNS/TTC wire protocol.