oracle.md

Oracle Provider

Oracle Database support for LibreDB Studio, built on the oracledb driver in Thin mode (pure JavaScript — no Oracle Instant Client required). This document is the single reference point for the Oracle provider: design, architecture, usage, and tests. Oracle is a SQL-family provider sharing SQLBaseProvider; read the PostgreSQL doc first for the canonical SQL walkthrough, then this doc for the Oracle-specific deltas.

Status	✅ Implemented & shipped
Database type id	oracle
Family	SQL (relational)
Driver	oracledb — Thin mode (no Instant Client)
Query language	sql
Default port	1521
Connection pooling	Yes — oracledb pool (poolMin/poolMax/poolTimeout)
Connection string	Supported — EZConnect host:port/service or a TNS string (passed straight to the driver's connectString)
Transactions	Yes: explicit begin/commit/rollback (5-minute auto-rollback on an owned provider only, see §3.4)
Query cancellation	Yes — tracked connection + connection.break()
SSL	Yes — connection.ssl selects TCPS, the DN match and the wallet (§4.3)
Source	src/lib/db/providers/sql/oracle/index.ts
Base	src/lib/db/providers/sql/sql-base.ts
Tests	tests/integration/db/oracle-provider.test.ts

---

1. Overview

Oracle is a relational database that maps onto the DatabaseProvider interface like the other SQL providers, with several Oracle-isms that are worth knowing before reading the code. Read this as a diff against the PostgreSQL provider (the SQL reference implementation):

Aspect	PostgreSQL	Oracle
Driver mode	pg	oracledb Thin by default (Thick opt-in via ORACLE_CLIENT_LIB_DIR)
Pagination	LIMIT … OFFSET	FETCH FIRST n ROWS ONLY / OFFSET m ROWS FETCH NEXT n
Schema scope	all non-system schemas	every owner in ALL_USERS (§7); the deleted flat reading saw only the connecting user's
Schema queries	1 MATERIALIZED-CTE round-trip	5 bulk ALL_* queries grouped in memory
Maintenance	vacuum / analyze / reindex / kill	analyze (DBMS_STATS) / optimize (rebuild one table's indexes, or the schema's) / kill
Transaction timeout	5-minute auto-rollback	5-minute auto-rollback on an owned provider (governance), none otherwise
Cancellation	pg_cancel_backend(pid)	connection.break() (tracked connection)
SSL	buildSSLConfig() + cloud auto-detect	tcps:// + sslServerDNMatch + walletContent (no cloud auto-detect)
Monitoring source	pg_stat_*	V$ views (privilege-gated, each guarded)
UI labels	default SQL	overridden (Gather Statistics / Rebuild Indexes)

Thin mode

The constructor (oracle/index.ts) uses pure-JS Thin mode by default, sets outFormat = OUT_FORMAT_OBJECT (rows as objects), and autoCommit = true globally. Thin mode means no native Oracle client has to be installed in the container — a real deployment win.

⚠️ Thin mode only supports Oracle Database 12.1 and later. Connecting to an older server (11.2 and earlier) fails with the driver's NJS-138 error. For those servers, opt into Thick mode via ORACLE_CLIENT_LIB_DIR — see §4.4.

---

2. Architecture

Same Strategy-Pattern hierarchy as the other SQL providers:

DatabaseProvider (interface) → BaseDatabaseProvider → SQLBaseProvider → OracleProvider

OracleProvider inherits the shared SQL helpers from sql-base.ts — see the PostgreSQL doc §2.2. It overrides three of them: getCapabilities(), getLabels(), and prepareQuery() (Oracle pagination). Note escapeIdentifier() from the base produces "ident" quoting, but Oracle maintenance largely uses inline-escaped literals instead (see §9).

Registration

Loaded on demand by the factory (factory.ts):

case 'oracle': {
  const { OracleProvider } = await import('./providers/sql/oracle/index');
  return new OracleProvider(connection, options);
}

---

3. Design decisions

3.1 EZConnect connect string (service name, not database)

Oracle connects to a service, not a database name. getConnectString() (oracle/index.ts) returns the raw connectionString if given, otherwise builds host:port/serviceName where serviceName = config.serviceName ?? config.database ?? 'ORCL'. Accordingly, validate() requires only host (not database) when no connection string is present.

3.2 FETCH FIRST instead of LIMIT

Oracle has no LIMIT, so prepareQuery() (oracle/index.ts) overrides the base and appends FETCH FIRST n ROWS ONLY (or OFFSET m ROWS FETCH NEXT n ROWS ONLY when an offset is set) to bare SELECTs that don't already have a limit. Default page size DEFAULT_QUERY_LIMIT = 500; unlimited caps at MAX_UNLIMITED_ROWS = 100000.

A limit or an offset that is not a non-negative safe integer is refused with a QueryError before either branch runs, by the check the shared limiter runs first (assertRowBounds in query-limiter.ts).

Both branches append at the end of the statement, which src/lib/sql/statement-end.ts delimits — before any trailing comment and before the terminating ;, both of which are then re-attached verbatim. Appending after them instead put the clause inside a trailing -- note while this method still reported wasLimited: true, so the statement reached Oracle unbounded and the UI called the result capped. A statement with no trailing comment is emitted exactly as it was before. The same reading answers whether the statement already carries a FETCH FIRST, so SELECT … FETCH FIRST 10 ROWS ONLY -- deliberate is still honoured and never gets a second clause. A statement whose end may not be cut is returned untouched with wasLimited: false rather than bounded on a guess. One shape reaches that on Oracle: a literal Oracle and MySQL would close in different places (a quote behind an odd backslash run). It is a deliberate loss of a bound — appending after the whole text, as this method used to, happened to be valid Oracle there, and is what puts the clause inside a trailing comment everywhere else — so that statement returns every row rather than being bounded on a guess. Since #297 the same unresolvable run also costs that statement a confirmation prompt, because the safety gate cannot read it either; the general rule and its accepted costs are in query-optimization.md.

# inside an identifier (ID#, common in legacy schemas) used to reach the same refusal and no longer does. prepareQuery() passes its own type to the shared readers (#292), and Oracle's grammar says # opens no comment: node-oracledb's own SQL tokenizer (node_modules/oracledb/lib/thin/statement.js) accepts # as an identifier character and starts comments on -- and /* … */ only. SELECT * FROM EMP WHERE ID# = 1 is therefore bounded, emitted as … ID# = 1 FETCH FIRST 500 ROWS ONLY. See Which dialect the readers are reading.

Alternate quoting (q'{it's}') is read as the literal it is — the second half of the same fix, and Oracle is the only dialect that has the form. The delimiter after the tag opens the body and its partner followed by ' closes it ([ ] { } ( ) < > pair up, any other character closes with itself, q or Q, and nq'…' / NQ'…' is the same form for NCHAR/NVARCHAR2), so the body carries apostrophes with nothing escaped. That is precisely what made reading it as code costly, and it cost two different things:

• An apostrophe in the body opened a string, so everything after it was read one construct out of step: a ) inside the literal closed a CTE body early and the statement was typed by a keyword written inside the literal. WITH T AS (SELECT q'{it's}' AS S FROM DUAL) SELECT * FROM T lost its bound entirely.
• A -- in the body made the rest of the literal look like a trailing comment, and the insert-before-trivia rule above then placed the clause inside the literal: SELECT q'[it's a -- note )]' AS S FROM DUAL was emitted as SELECT q'[it's a FETCH FIRST 500 ROWS ONLY -- note )]' AS S FROM DUAL with wasLimited: true — a statement Oracle rejects, reported as capped.

Both are gone for either spelling of the tag; the clause now lands after the literal. A body whose closing delimiter never arrives is undeterminable, so that statement is returned untouched rather than bounded on a guess. The tag must also start a word: in SELECT FREQ'{it's}' … the reader takes FREQ for a name and the apostrophe after it for an ordinary string, so that statement reaches the same refusal rather than a bound placed inside something that may not be a literal at all. That is deliberately stricter than node-oracledb's tokenizer, which opens a q-string at any ' preceded by q/Q whatever comes before it; the strict side is the one whose mistake costs a bound — and, since #297, a confirmation prompt on that statement — rather than a misplaced clause.

3.2a A generated statement carries no terminator

getCapabilities() declares statementTerminator: 'none', so the two statements src/lib/query-generators.ts writes on the user's behalf - "Select Top 50" and "Generate Query" - end without a ;.

; is a SQL*Plus convention rather than Oracle SQL. node-oracledb sends ONE statement and the terminator is not part of it, so the generated form was rejected outright.

Measured through this provider on Oracle AI Database 26ai Free on 2026-09-12, by clicking a table in the object browser:

SELECT * FROM APP.APP_CUSTOMERS FETCH FIRST 50 ROWS ONLY;   -> ORA-00933: SQL command not properly ended
SELECT * FROM APP.APP_CUSTOMERS FETCH FIRST 50 ROWS ONLY    -> rows

The generator carried that ; from the day its Oracle branch was written, so clicking a table on Oracle had never once worked. This is a declaration rather than a branch in the generator: nothing in src/lib/query-generators.ts needs to know which engine it is writing for (#789).

The row bound in the statement above has since gone: #816 moved the preview cap into the limit execution option, so what the generator writes today is SELECT * FROM APP.APP_CUSTOMERS — still with no ;, which is the half of that measurement the engine cares about. The Oracle branch itself was removed with the bound, because once there is no FETCH FIRST to spell it did nothing the shared return does not already do.

It bounds the generators, and it also tells the editor to read a one-statement selection through the statement reader (#1414). A ; a user types after a plain statement is stripped by that reader before the statement is sent: with the caret in the statement on every engine, and, because of this declaration, also when the statement is selected; a multi-statement selection is still sent as selected. The raw API passes text through untouched. The one ; the reader keeps is the one after a PL/SQL unit's END, which is part of the unit (§5.1).

3.3 Schema introspection reads the ALL_* views, and is not owner-scoped

Every reading of this engine's objects goes through the object surface (§7). The flat reading that came before it ran five bulk queries over the ALL_* data-dictionary views, all filtered by OWNER = :1 (the connecting user, upper-cased) and grouped in memory by table, so the app showed exactly one schema on Oracle with no way to reach another. It is deleted (#765). Row counts still come from NUM_ROWS, an optimizer estimate that can be stale or NULL.

3.4 Transaction auto-rollback timeout on an owned provider only

beginTransaction() (oracle/index.ts) checks out a connection and marks the transaction active. A provider created with an owner in its execution context, which only governance does (ProviderExecutionContext.owner, see Per-user providers), also arms the 5-minute auto-rollback timer of the Postgres and MySQL providers there. createDatabaseProvider hands the execution context to OracleProvider for this. When the timer fires, expireTransaction() rolls the transaction back and closes the held connection, which releases it to the pool; a rollback or a close that fails is logged, and the transaction is released all the same. commitTransaction(), rollbackTransaction() and disconnect() clear the timer. A provider without an owner, which is every install without governance, arms no timer: an abandoned transaction holds its connection (and locks) until explicitly committed or rolled back, or the connection is reclaimed by the pool. Arming the timer in every mode would change the transaction lifetime of every install without governance, so it waits for a product decision (GOV3).

3.5 SSL through the connect string, not a buildSSLConfig()

The Oracle provider has no buildSSLConfig() and no cloud auto-detect: TLS is not an option object here but a protocol in the connect string. The Thin driver calls tls.connect only when the resolved address protocol is TCPS (audited in oracledb/lib/thin/sqlnet/ntTcp.js), so honouring connection.ssl means composing tcps://host:port/service — see §4.3 for the full mapping, and for the two Oracle-specific consequences: the chain is always verified (there is no rejectUnauthorized to turn off), and the CA and client certificates travel as one walletContent PEM rather than three options.

3.6 A failed connect closes the pool it created

createPool() resolves before anything is dialled in Thin mode, so a connect that fails on its test borrow already holds a live pool. factory.getOrCreateProvider() never caches a provider whose connect() threw, which means nothing can call disconnect() on it afterwards, and node-oracledb's background creator keeps reaching for poolMin connections with no delay between attempts. One failed connect to an unreachable host therefore pins a CPU core and floods that host until the process exits — measured at roughly 14,000 TCP connect attempts and one full core per second.

The catch in connect() closes that pool with close(0) (force close, the oracledb equivalent of end()) and clears this.pool before rethrowing, so a retried connect() creates a fresh pool instead of returning silently through the if (this.pool) guard while connected to nothing. A close() that itself rejects is swallowed rather than replacing the connect error. This is the same contract PostgreSQL and SQL Server already follow (#1102).

3.7 Privilege-resilient monitoring

Oracle monitoring reads V$ dynamic-performance views, which require privileges a typical app user may lack. Every V$ sub-query is wrapped in its own try/catch and degrades rather than failing the whole call — so the dashboard still renders for a low-privilege user, just with gaps. The default it degrades to is N/A or [] where the shape has a place to say "not measured", and — in the health and overview readings, where a number would otherwise be invented — nothing at all: getHealth().activeConnections and getOverview().activeConnections are both omitted rather than reported as 0 (§7.2).

The guard covers the V$ reads, the ones a privilege decides. getTableStats() and getIndexStats() read only the USER_* / ALL_* dictionary views, which every user can read, so they are not guarded: a failure there is a defect or a dead connection, never a missing grant, and it rejects with the engine's sentence. The inherited getMonitoringData() records it under errors.tables / errors.indexes, and the Tables, Storage and admin Operations tabs show that refusal instead of "No table statistics available". An empty catch used to turn exactly such a failure into an empty list (§7.4).

---

4. Connection

4.1 Configuration

// Discrete fields — host required; service comes from serviceName ?? database ?? 'ORCL'
const a = { id: 'or-1', name: 'XE', type: 'oracle',
  host: 'localhost', port: 1521, serviceName: 'XEPDB1',
  user: 'app', password: 'secret', createdAt: new Date() };

// Connection string — EZConnect host:port/service (or a TNS string); passed
// straight to oracledb's connectString. (An `oracle://…` URL is NOT a valid
// driver connect string — it's only decomposed into discrete fields by the UI
// paste-parser before it ever reaches the provider.)
const b = { id: 'or-1', name: 'XE', type: 'oracle',
  connectionString: 'localhost:1521/XEPDB1',
  user: 'app', password: 'secret', createdAt: new Date() };

validate() (oracle/index.ts) requires host only when no connectionString is given; database is not required (Oracle uses the service name).

4.2 Connection pooling

connect() builds an oracledb pool (oracle/index.ts):

oracledb pool option	Value	Source
poolMin	2	ProviderOptions.pool.min
poolMax	10	ProviderOptions.pool.max
poolTimeout	30 (s)	ProviderOptions.pool.idleTimeout ÷ 1000

⚠️ acquireTimeout (from DEFAULT_POOL_CONFIG) and queryTimeout (a separate ProviderOptions option, defaulting to DEFAULT_QUERY_TIMEOUT) are not mapped — there is no provider-driven server-side query timeout (cancellation is explicit, §5.2).

connect() is idempotent; getPoolStats() (oracle/index.ts) exposes { total: connectionsOpen, idle, active: connectionsInUse, waiting: 0 }.

4.3 SSL / TLS

getConnectString() and buildTLSAttributes() (oracle/index.ts) map connection.ssl onto the three things the driver understands:

ssl.mode	Connect string	sslServerDNMatch	Chain verified
absent / disable	host:port/service	not set	— (plaintext)
require	tcps://host:port/service	false	yes (unavoidable)
verify-system	tcps://host:port/service	true	yes, against the runtime's own roots
verify-ca	tcps://host:port/service	false	yes
verify-full	tcps://host:port/service	true	yes

caCert, clientCert and clientKey are concatenated, in that order and newline-separated, into a single walletContent attribute. That is the driver's own shape, not a convenience: Thin mode hands the same string to tls.createSecureContext() as cert, key and ca.

Note: require is not "encrypt without verifying" on Oracle. Thin mode calls tls.connect with rejectUnauthorized: true unconditionally, so every TCPS connection checks the chain and ssl.rejectUnauthorized: false has nothing to map to. A server with a self-signed certificate is reachable only by supplying its CA in caCert. require and verify-ca therefore differ from verify-full only in the DN/hostname match, which is the one check Oracle does expose.

verify-system (D26) asks for that same match. What separates it from verify-full here is what it does NOT send: with no PEM pasted there is no walletContent, so tls.connect falls back to Node's bundled roots for the chain — which is exactly what the mode means. Audited in the installed driver: oracledb/lib/thin/sqlnet/ntTcp.js runs tls.checkServerIdentity(hostName, cert) when sslServerDNMatch is on and no sslServerCertDN is configured. Not exercised against a TLS listener (the probe instance speaks TCP), so this is the driver's audited shape and no claim about a verified handshake.

Note: a pasted connectionString is returned verbatim, so its own protocol (or full TNS descriptor) decides whether the transport is encrypted — a require selected alongside a tcp connect string cannot upgrade it. sslServerDNMatch and walletContent are separate pool attributes and still apply.

4.4 Thick-mode opt-in (ORACLE_CLIENT_LIB_DIR)

Env var	Required	Effect
ORACLE_CLIENT_LIB_DIR	No (default: unset, Thin mode)	Absolute path to an installed Oracle Instant Client directory. When set, the constructor calls oracledb.initOracleClient({ libDir }) and the driver runs in Thick mode instead of Thin. On Linux this variable alone is not enough — see below.

# Only needed against a pre-12.1 Oracle server (see the Thin-mode caveat above).
# The version matters: use Instant Client 19c for an Oracle 11.2 server (see below).
ORACLE_CLIENT_LIB_DIR=/opt/oracle/instantclient_19_28

Thick mode is reachable on the default image only. Oracle publishes no musl build of Instant Client, so the -alpine and -alpine-slim tags (issue #840, DISTRIBUTION.md) can never load it whatever an operator layers on top — Dockerfile.alpine therefore does not ship node-oracledb's native addons at all, since nothing in those images could load them. Thin mode is unaffected and measured working on both: the driver is pure JavaScript there, Next's output file tracing carries it into the standalone payload, and oracledb.thin === true inside the running container. An attempted initOracleClient() fails with the driver's own NJS-045, whose text already tells the operator to use Thin mode. So an Oracle 12.1+ server works on every tag; an 11.2 server needs the default one.

node-oracledb's Thin/Thick choice is a process-wide singleton — initOracleClient() throws if called more than once, or after any connection/pool already exists. This is why the setting is a process-level env var rather than a per-connection config field: every OracleProvider in the process shares one driver mode. The constructor guards the call with a module-level flag so it runs at most once regardless of how many OracleProvider instances (i.e. connections) are created. The Oracle Instant Client itself must already be installed at the given path — this provider does not download or bundle it. If the client cannot be loaded, the constructor fails fast with a DatabaseConfigError that names ORACLE_CLIENT_LIB_DIR and says which of the three failures it is (describeOracleClientLoadFailure() in oracle-driver-errors.ts decides the wording; §11 lists the codes).

On Linux the variable alone is not enough

Setting ORACLE_CLIENT_LIB_DIR makes the driver call initOracleClient({ libDir }), and on Linux that call cannot load Instant Client on its own. libclntsh.so.19.1 is built with no RUNPATH, so the dynamic loader has no way to find the libraries it depends on (libnnz19.so, libclntshcore.so.19.1) even though they sit right next to it. The call fails with:

DPI-1047: Cannot locate a 64-bit Oracle Client library: "libnnz19.so: cannot open shared object file"

node-oracledb's own documentation is explicit about this: "Never set libDir on Linux and related platforms. Instead you must configure the system library search path to include the directory before starting Node.js." This provider still passes libDir on every platform (it is the documented mechanism on Windows and macOS, and it is harmless on Linux once the directory is on the loader path), so on Linux you need both:

1. ORACLE_CLIENT_LIB_DIR=<dir>, and
2. <dir> on the system library search path — either a file under /etc/ld.so.conf.d/ followed by ldconfig, or LD_LIBRARY_PATH=<dir> exported before Node starts (the loader reads it at process start; setting it from inside the app is too late).

Measured with Instant Client 19.28 against Oracle Free 23ai: with either of those two in place, initOracleClient({ libDir }) succeeds (oracledb.thin === false, client 19.28.0.0.0) and v$session_connect_info.client_driver reports node-oracledb : 6.10.0 thk. Without them, only DPI-1047.

There is a second Linux prerequisite on Debian 13 (the runtime base): the client links against libaio.so.1, but trixie's libaio1t64 package installs only libaio.so.1t64, so ldd libclntsh.so.19.1 reports libaio.so.1 => not found. A symlink fixes it, and the recipe below creates one.

Before this fix, Thick mode could not be entered at all in a real build. Measured on the published images 0.13.4 and 0.13.7: with ORACLE_CLIENT_LIB_DIR set, POST /api/db/test-connection returned HTTP 400 CONFIG_ERROR quoting NJS-045 and paths under /ROOT/node_modules/oracledb. The driver was bundled into the server chunk, which rewrote its __dirname, so it looked for its own native addon at a path that does not exist and never got as far as reading the Instant Client. The recipes in this section could not have worked on those images. oracledb is now in serverExternalPackages (next.config.ts) and the package is copied explicitly by the Dockerfile runner stage and scripts/build-standalone-payload.sh, the same way the other native drivers are.

Pick the right Instant Client version

Thick mode delegates to Oracle's native client, whose ability to reach an older server is bounded by Oracle client/server interoperability (My Oracle Support Doc ID 207303.1). For the common "connect to Oracle 11g" case:

Target server	Instant Client to install
Oracle 11.2 (11g)	19c — the newest client that still reaches 11.2 (11.2.0.3 / 11.2.0.4). 21c and 23ai cannot connect to 11.2.
Oracle 12.1+	Any current Instant Client (19c / 21c / 23ai). Thin mode already covers these, so Thick is rarely needed.

A 12.1+ server can still force Thick mode

The server version is not the only reason to leave Thin mode, and "my DBA says I need Thick mode on 12.2" is a real and common case (#538). Thin mode covers 12.1 and later as a version, but it refuses a connection when the server demands something it does not implement:

The codes below are read out of node-oracledb 6.10.0's own source (lib/thin/, lib/errors.js), not inferred from the symptom, because the driver does not report every one of these as a Thin mode refusal and some are not the driver's error at all:

What the server or connection requires	What you actually see
Oracle Native Network Encryption or data integrity checksumming	NJS-533
An account whose only password verifier is the 10G one	NJS-116
A wallet available only as cwallet.sso	NJS-529 — Invalid wallet content format. Supported format is PEM
Kerberos, RADIUS, operating-system or other external authentication	No single driver code. Thin mode implements externalAuth for token-based authentication (lib/thin/protocol/messages/auth.js) but has no Kerberos, RADIUS or OS-auth implementation, and raises nothing of its own for them — the server refuses the logon, so what arrives is an ORA- logon error. Thick mode only.
LDAP (directory) naming for the connect identifier	Not a Thin-mode refusal. There is no LDAP code anywhere under lib/thin, so an ldap:// identifier fails during connect-string resolution instead, typically NJS-516 (no configuration directory set or available to search for tnsnames.ora) or a connect-string parse error. Thick mode only.

The three rows that are driver codes map to a non-retryable DatabaseConfigError naming ORACLE_CLIENT_LIB_DIR (§11) rather than to a retryable connection failure. The last two rows do not, and cannot: nothing in their text identifies the cause.

Two of them have a way out that needs no Instant Client, and the provider's error message says so in both cases:

• NJS-116 — the account simply has no 12C verifier. A DBA resetting its password writes one (given a server SQLNET.ALLOWED_LOGON_VERSION_SERVER that permits it).
• NJS-529 — Thin mode reads only PEM. Converting the wallet to ewallet.pem (orapki wallet pkcs12_to_pem, or openssl against the PKCS#12) is enough; Thick mode reads the cwallet.sso as it stands.

NJS-089 is not in the table on purpose. In Thin mode the driver raises it for client-side features it has not implemented — heterogeneous pooling (lib/thin/pool.js), some database object types (lib/thin/dbObject.js), Advanced Queuing (lib/thin/protocol/messages/aqArray.js, aqBase.js) and a few protocol features — none of which is something a server can demand at connect time. It is still mapped (§11), because the remedy is the same.

Building an image with Instant Client (works for both cases above)

The published image (ghcr.io/libredb/libredb-studio) ships Thin only — it does not bundle the Oracle Instant Client, because the native client is ~100 MB and only a minority of deployments need it. What the image does carry (since #538) is the driver's own Thick-mode addon, so layering a client on top is all that is left to do. The runtime base is Debian 13 (node:*-trixie-slim), so the recipe installs libaio1t64 (trixie's renamed libaio1), symlinks the SONAME the client actually asks for, unpacks the Basic package, and puts the directory on the loader path:

FROM ghcr.io/libredb/libredb-studio:latest

USER root
# Instant Client 19c — reaches Oracle 11.2; 21c/23ai do not. Pin to a specific
# 19.x build; check https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html
# for the current file name and update the version folder in ORACLE_CLIENT_LIB_DIR to match.
# ca-certificates is not optional: the base image does not have it (and has no
# curl either), and --no-install-recommends will not pull it in, so without it
# the download below fails with curl (77) setting the certificate file.
RUN apt-get update && apt-get install -y --no-install-recommends libaio1t64 unzip curl ca-certificates \
    && mkdir -p /opt/oracle && cd /opt/oracle \
    && curl -fsSLO https://download.oracle.com/otn_software/linux/instantclient/1928000/instantclient-basic-linux.x64-19.28.0.0.0dbru.zip \
    && unzip -q instantclient-basic-linux.x64-*.zip \
    && rm instantclient-basic-linux.x64-*.zip \
    # Debian 13 ships libaio.so.1t64; the client links against libaio.so.1.
    && ln -sf libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1 \
    # Required, not optional: libclntsh has no RUNPATH, so without this the
    # driver fails with DPI-1047 no matter what ORACLE_CLIENT_LIB_DIR says.
    && echo /opt/oracle/instantclient_19_28 > /etc/ld.so.conf.d/oracle-instantclient.conf \
    && ldconfig \
    && rm -rf /var/lib/apt/lists/*
ENV ORACLE_CLIENT_LIB_DIR=/opt/oracle/instantclient_19_28
USER nextjs

Instead of rebuilding, you can mount an Instant Client directory from the host into the stock image and point ORACLE_CLIENT_LIB_DIR at the mount — but then the loader-path step has to be done on the container too, and ldconfig is not available to you at that point, so set LD_LIBRARY_PATH to the same directory in the container's environment (docker run -e, or env: in the pod spec). The libaio.so.1 symlink is needed either way; on the stock image that means the mount has to supply it or the container has to run as root long enough to create it, which is why the derived image above is the supported path. A first-class, separately-published Thick-mode image variant is a possible future addition.

Connection string

The scheme shown for Oracle is oracle://. Its row in CONNECTION_STRING is declared in oracle-connection-string.ts. Its paste claims oracle://, with port 1521 by default. It has no readTls, so a pasted oracle:// URL carries no SSL mode into the form, and it runs no seed load check.

Execution UI

The rolled-back notice names COMMIT as the keyword that keeps an open transaction, and oracle has no consent deadline, so the agent's consent card shows no deadline chip or sentence for it. Its row in EXECUTION_UI is declared in oracle-execution-ui.ts. What a tree click and Generate Query write is ORACLE_SQL_GENERATORS, its row of SQL_PORT_GENERATORS keyed by its default port, declared in oracle-generators.ts and read by the engine-agnostic dispatcher in src/lib/query-generators.ts. How mapDatabaseError classifies its driver's errors, its Thin-mode refusals, its statement and access-refusal error numbers and its ORA- connection and missing-table words, is the oracledb row of DRIVER_ERROR_CLASSIFIERS, declared in oracle-driver-errors.ts.

---

5. Query interface

5.1 Execution

query(sql, params?, queryId?) (oracle/index.ts) checks out a pooled connection, optionally stores the connection object under queryId for cancellation, runs conn.execute(sql, binds, { outFormat: OUT_FORMAT_OBJECT, autoCommit: true, fetchTypeHandler }) (§5.3 says what the handler is for), and returns:

{ rows, fields: metaData.map(m => m.name), rowCount: rows.length, executionTime, columnTypes? }

Column names. Every column reaches the grid named, and under a name no other column of the result has, by the driver's own doing. An unaliased expression is named by its text (SELECT 1+1 FROM dual answers 1+1), and an empty alias is refused by the server (SELECT 1 AS "" FROM dual fails with ORA-01741: illegal zero-length identifier). A repeated name is numbered by node-oracledb itself, in metaData and before any row is keyed (_setup in oracledb/lib/impl/resultset.js, 6.10.0): the first column keeps the name, a repeat takes NAME_1, NAME_2, and a number skips a name the statement declares. Measured on 2026-10-07 against Oracle XE with that driver, the same in OUT_FORMAT_OBJECT and OUT_FORMAT_ARRAY:

statement	names	row
SELECT 1 AS a, 2 AS a FROM dual	A, A_1	{ A: 1, A_1: 2 }
SELECT 1 AS a, 2 AS a, 3 AS a_1 FROM dual	A, A_2, A_1	{ A: 1, A_2: 2, A_1: 3 }
a join projecting user_id from both sides of all_users	USER_ID, USER_ID_1	both values
SELECT 1+1, 1+1 FROM dual	1+1, 1+1_1	both values

So no value is lost, and the provider keys rows by the names the driver gives rather than through uniqueFieldNames. The declared names are not gone: the driver hands each column to the call's fetchTypeHandler under its declared name before it renames it, though only for a statement it does not serve from its statement cache. Rebuilding the shared name (2) form from them was tried and left out: it would take a statement-cache miss on every editor statement, and a CURSOR(...) column read as arrays loses the names of its nested columns. So a repeat reads NAME_1, which data masking and the inline-edit refusal do not read as a repeat of NAME.

A SELECT answers with a rows array and rowCount is rows.length. A non-SELECT (INSERT/UPDATE/DELETE/DDL/PL/SQL) carries no rows array at all, and that absence is what selects the other branch of buildQueryResult(): the grid is empty (rows: [], fields: [], no columnTypes, because there is no metadata to state them from) and rowCount is the driver's own result.rowsAffected, 0 when the driver states none. Same shape as the MySQL provider's buildQueryResult() (#469).

Until 2026-08-24 the count was rows.length on both branches, so every statement that wrote something reported 0 for work it had done. Measured 2026-08-24 through createDatabaseProvider({type:"oracle"}) against Oracle AI Database 26ai Free, with an interleaved SELECT proving each statement had landed:

statement	rowsAffected on the wire	rowCount before	after
CREATE TABLE d13_probe (…)	0	0	0
INSERT INTO d13_probe VALUES (1, 'a')	1	0	1
INSERT INTO d13_probe SELECT … ROWNUM <= 3	3	0	3
UPDATE d13_probe SET note = 'z' (4 rows)	4	0	4
DELETE FROM d13_probe WHERE id = 9 (3 rows)	3	0	3
DELETE FROM d13_probe WHERE id = 4242	0	0	0
BEGIN NULL; END;	unset	0	0
TRUNCATE TABLE d13_probe	0	0	0

autoCommit: true on the call is load-bearing, not decoration: oracledb.autoCommit defaults to false, and measured without it the INSERT still reported rowsAffected: 1 while a second session saw COUNT(*) = 0, and the row was gone for good once the writing connection went back to the pool. Bind parameters use Oracle's :1-style placeholders. Native errors are normalised through mapDatabaseError() (see §11).

PL/SQL from the editor (#1312). The grammar's script fact for this dialect (src/lib/db/providers/sql/oracle-sql-grammar.ts) makes the editor's statement reader treat a PL/SQL unit as ONE statement: an anonymous block that starts with DECLARE or BEGIN, and CREATE [OR REPLACE] [EDITIONABLE | NONEDITIONABLE] PROCEDURE | FUNCTION | PACKAGE [BODY] | TRIGGER | TYPE BODY. The unit is read to the END that closes its outermost block, and the ; after that END is kept, because Oracle refuses the unit without it (PLS-00103, "Encountered the symbol end-of-file"). A line holding only / ends the statement in progress and is never sent, as in SQLPlus, so a script written for SQLPlus or SQL Developer runs unchanged. A compound trigger is read to its last END with each timing point a block of its own, a <<label>> before a block is read past, and a call spec (AS LANGUAGE JAVA …, AS LANGUAGE C …, AS EXTERNAL …, AS MLE MODULE …) has no body, so the ; after it ends it. A unit the reader still cannot close on its own needs that /, as it does there. A WITH FUNCTION read is not a unit and is still cut at its inner ;.

A write inside a block still asks for confirmation: the gate reads into a statement led by BEGIN, DECLARE, IF, THEN and the other control-flow words, so BEGIN DELETE FROM emp; END; asks, and so does a block that runs dynamic SQL (EXECUTE IMMEDIATE, DBMS_SQL), whose text the gate cannot read (docs/editor/query-optimization.md, multi-statement runs).

Measured before this on 26ai Free 23.26.3: the editor cut CREATE OR REPLACE PROCEDURE raise_sal(p_pct IN NUMBER) AS BEGIN UPDATE emp SET salary = salary * (1 + p_pct/100); END; at its inner ;, the first fragment answered success while user_objects showed the procedure INVALID, the second was ORA-00900, and a trigger cut the same way stayed INVALID on its table so that every later INSERT failed with ORA-04098.

5.2 Query cancellation

A query issued with a queryId stores its connection in a Map. cancelQuery(queryId) (oracle/index.ts) calls connection.break() on it — interrupting the in-flight OCI call — and returns true on success (it does not verify a query was actually running). Exposed via POST /api/db/cancel.

5.3 What each Oracle type arrives as

Every row below was measured on 2026-08-24 through createDatabaseProvider({type:"oracle"}) against Oracle AI Database 26ai Free with oracledb 6.10.0 in Thin mode, over a probe table holding one populated row and one all-NULL row. The JSON.stringify column is what POST /api/db/query puts on the wire, and therefore what the grid, the row detail sheet, the CSV, the SQL export and the agent's result summary all read.

Oracle type	Arrives as	JSON.stringify gives	Reported as
CLOB / NCLOB	string (see below)	"the quick brown fox"	the text
BLOB	Buffer (see below)	{"type":"Buffer","data":[222,173,190,239,1,2]}	\xdeadbeef0102
RAW	Buffer	{"type":"Buffer","data":[10,11,12]}	\x0a0b0c
NUMBER	number	1.2345678901234568e+37 — digits lost	the double, see below
BINARY_DOUBLE	number	3.5	the number
DATE	string, the stored wall clock (see below)	"2026-09-01 10:11:12"	the text
TIMESTAMP	string, the stored wall clock (see below)	"2026-09-01 10:30:00.345", sub-ms dropped	the text
TIMESTAMP WITH TIME ZONE	Date	"2026-08-24T07:11:12.345Z" — offset folded to UTC, sub-ms dropped	the formatted date — §5.5
TIMESTAMP WITH LOCAL TIME ZONE	Date	"2026-08-24T07:11:12.345Z" — same, normalized to the session time zone first	the formatted date — §5.5
INTERVAL YEAR TO MONTH	IntervalYM	"+03-07"	its Oracle literal — §5.5
INTERVAL DAY TO SECOND	IntervalDS	"+05 06:07:08.9"	its Oracle literal — §5.5
XMLTYPE	string	"<r>\n <a>1</a>\n</r>\n"	the serialized document
JSON	plain object	{"k":[1,2]}	that object, as JSON
any of the above, NULL	null	null	empty

A LOB used to fail the whole query, not just the cell

query() and queryInTransaction() pass a per-call fetchTypeHandler (oracle/index.ts) that maps CLOB and NCLOB to oracledb.STRING and BLOB to oracledb.BUFFER. Apart from DATE and TIMESTAMP, which it gives a converter (below), every other column keeps the driver's own default: RAW is already a Buffer and VARCHAR2 already a string, and restating them would put this provider in charge of types it has no reason to touch.

Without it oracledb answers a LOB with a Lob stream object, and the row cannot be serialized at all. Measured over four LOB columns, each arriving with constructor.name === "Lob":

TypeError: Converting circular structure to JSON
    --> starting at object with constructor 'NVPair'
    |     property 'list' -> object with constructor 'Array'
    |     index 0 -> object with constructor 'NVPair'
    --- property 'parent' closes the circle          (Node 24.14.0)

TypeError: JSON.stringify cannot serialize cyclic structures   (Bun 1.3.14)

POST /api/db/query builds its answer with NextResponse.json, so the whole SELECT failed — no grid, no CSV, no export, nothing for the agent to summarize. The in-process path (StudioWorkspace, the agent's tools) got further and was worse: the cell classified as JSON and the export wrote the stream's internals, measured verbatim as

INSERT INTO r6_lob ("ID", "C", "B") VALUES (1, '{"_events":{"finish":[null]},"_readableState":{...

A BLOB as a Buffer needs nothing further: asBytes in src/lib/export/binary.ts accepts both a live Uint8Array and the {"type":"Buffer","data":[…]} JSON it serializes to, which is the same contract a Postgres bytea and a MySQL BLOB already reach the binary cell renderer, the row detail sheet, the CSV, the JSON export's \x… string (#1381) and the SQL export's binary literal through. Verified by exporting a row and replaying it into Oracle itself:

SOURCE   {"ID":1,"C":"the quick brown fox","NC":"ncl-value-unicode-café","B":{"type":"Buffer","data":[222,173,190,239,1,2]}}
EXPORT   INSERT INTO r6_replay ("ID", "C", "NC", "B") VALUES (1, 'the quick brown fox', 'ncl-value-unicode-café', HEXTORAW('deadbeef0102'));
REPLAYED {"ID":1,"C":"the quick brown fox","NC":"ncl-value-unicode-café","B":{"type":"Buffer","data":[222,173,190,239,1,2]}}

A LOB is fetched whole, with no length cap. That is the same contract every other provider here already has for a large value — a Postgres text/bytea and a MySQL BLOB arrive whole too, and DEFAULT_QUERY_LIMIT bounds the row count, not the cell. A cap was considered and rejected: a truncated CLOB looks exactly like a complete one in the grid, and the SQL export would write the truncation into the target as though it were the value. The cost is linear and measured — a 16,384,000-character CLOB fetched as a string took 66 ms and serialized to 16.4 MB of JSON in 18 ms — and the ceiling is the runtime's own and fails loudly: a string past V8's 536,870,888-character maximum throws RangeError: Invalid string length, which reaches the user as a failed query rather than as a value that has quietly lost its tail.

The handler is deliberately per-call, not the process-wide oracledb.fetchAsString / fetchAsBuffer globals: those would also change every schema and monitoring read (the catalog reads read ALL_TAB_COLUMNS.DATA_DEFAULT, a LONG), and they outlive the provider — the embeddable library surface runs inside a host application that may have its own oracledb consumers.

A DATE and a TIMESTAMP read as the stored wall clock, in every server time zone (#1131)

Neither type holds a zone. oracledb, in Thin and Thick mode alike, builds the Date for both by reading the stored fields as local time of the Node process (makeDate(useLocal) in oracledb/lib/util.js), and every row path then serialized that Date as ISO UTC, so the value moved with the server's TZ. The published image runs in UTC, which hid it; npx @libredb/studio on a machine east or west of UTC did not. Measured on 2026-10-02 with tests/live/oracle-zoneless-values.ts against gvenzl/oracle-free:slim (Oracle AI Database 26ai Free Release 23.26.3.0.0, oracledb 6.10.0, Bun 1.4.2), through this provider, for DATE '2026-09-01' and TIMESTAMP '2026-09-01 10:30:00', before and after this change:

process TZ	DATE before	TIMESTAMP before	DATE now	TIMESTAMP now
UTC	2026-09-01T00:00:00.000Z	2026-09-01T10:30:00.000Z	2026-09-01 00:00:00	2026-09-01 10:30:00
Europe/Istanbul	2026-08-31T21:00:00.000Z (the previous day)	2026-09-01T07:30:00.000Z (3 hours early)	2026-09-01 00:00:00	2026-09-01 10:30:00
America/Los_Angeles	2026-09-01T07:00:00.000Z	2026-09-01T17:30:00.000Z (7 hours late)	2026-09-01 00:00:00	2026-09-01 10:30:00

The server's own TO_CHAR printed the "now" column in every zone, and the same held for the other rows of the run: TO_DATE('2026-08-24 10:11:12'), TIMESTAMP '2026-09-01 10:30:00.345' and .5, NULL, and the BC date -0044-03-15 00:00:00, which before read as -000044-03-15T00:00:00.000Z under UTC and as -000044-03-14T22:04:08.000Z under Istanbul, the zone's local mean time for that year. TIMESTAMP '2026-09-01 10:30:00 +03:00' WITH TIME ZONE read as 2026-09-01T07:30:00.000Z throughout, before and after.

The shifted text reached the grid, copy, CSV, JSON, the SQL INSERT export and the MCP and agent reads. Over HTTP, where the export receives the row as JSON, the old SQL INSERT export quoted that ISO text (VALUES (1, '2026-08-31T21:00:00.000Z', '2026-09-01T07:30:00.000Z')) and Oracle refused it on replay with ORA-01861: literal does not match format string. The in-process export still had the driver's Date and wrote its local fields, which replayed to the right values. The export of the provider's text now replays to values the server calls equal, for all four rows, under Istanbul.

The provider tests pin the reading for all three zones over the Date the driver builds, and the live script (§12.4) holds it against the server.

• The spelling. A DATE is YYYY-MM-DD HH24:MI:SS, always with its time, because an Oracle DATE always holds one. A TIMESTAMP adds the fraction the Date kept, with trailing zeros trimmed, and a whole second carries none: '2026-09-01 10:30:00.345', '2026-09-01 10:30:00.5', '2026-09-01 10:30:00'. A BC year keeps one leading sign, '-0044-03-15 00:00:00', as TO_CHAR(d, 'SYYYY-MM-DD HH24:MI:SS') prints it.

• How. The handler gives both types a converter that reads the Date back through its local getters, the inverse of what the driver did, so the stored fields come back in every zone. TIMESTAMP WITH TIME ZONE and WITH LOCAL TIME ZONE are left alone: each names an instant, the driver hands over that instant, and its ISO form is the same in every zone.

• Why not the NLS formats. Setting the session's NLS_DATE_FORMAT / NLS_TIMESTAMP_FORMAT and fetching as a string does nothing in Thin mode, the default, which never asks the server for text for these types: a fetch type of oracledb.STRING is the same Date put through toString() (oracledb/lib/impl/resultset.js), in the Node process's zone and without milliseconds, the finding §5.5 records for the zoned type. Moving the session TIME_ZONE to UTC was not taken either: Thin sets it at connect to the process's offset, and CURRENT_DATE and LOCALTIMESTAMP read it, so changing it would change what a user's own query answers.

• What the Date lost stays lost, because it is gone before the converter runs:

  • Digits past the millisecond: .345678 still reads .345.
  • A wall clock inside the Node zone's spring-forward gap. It exists in the table but not in that zone, so the driver's Date is an hour later: new Date(2026, 2, 8, 2, 30) reads back as 03:30 under America/Los_Angeles. UTC has no gap, so the published image is exact.
  • A year below 100, which the Date constructor reads as 19xx.

  TO_CHAR in the query is exact for all three.

• Library surface: an in-process consumer now receives strings, not Date objects, for these two types. The SQL export reads the text back into an Oracle literal, and inline row editing binds :n through the same conversion functions (§5.5).

NUMBER still loses digits, and that is a separate defect

NUMBER arrives as a JS double and the loss is silent: measured, 12345678901234567890123456789012345678 (a NUMBER(38,0)) came back as 1.2345678901234568e+37, and NUMBER(20,4) 1234567890123456.7891 as 1234567890123456.8. Fetching NUMBER as a string would keep the digits — the way docs/providers/cassandra.md §3.8 keeps a bigint's — but it is not part of that change: it changes every numeric cell Oracle produces, including the ones the grid right-aligns and the agent arithmetics over, so it is tracked separately rather than smuggled in with the LOB fix. JSON is lossless as an object and is left as it is; XMLTYPE needs nothing, it is already a string. The two INTERVAL types were left alone by that change too and are handled now — §5.5.

5.4 Declared column types

Oracle is the one engine of the four whose driver hands over a NAME rather than a wire code: result.metaData[].dbTypeName. It is passed through into QueryResult.columnTypes verbatim (column-types.ts), keyed by the column name in fields, by both query() and queryInTransaction(), and it is uppercase. It is not the schema tree's type, which is a column's full declaration, such as VARCHAR2(20 BYTE) (§7). For most types it is the ALL_TAB_COLUMNS.DATA_TYPE spelling, which the tree reports as baseType ?? type. The exceptions, measured on 21c XE, are FLOAT, which reads NUMBER here, and the TIMESTAMP and INTERVAL types, which lose the precision DATA_TYPE carries: a TIMESTAMP(3) column reads TIMESTAMP here and TIMESTAMP(3) in the tree.

Measured on Oracle AI Database 26ai Free over the probe table, verbatim from oracledb:

declared	dbTypeName	also reported
NUMBER(19)	NUMBER	precision: 19, scale: 0
NUMBER(10,2)	NUMBER	precision: 10, scale: 2
BINARY_DOUBLE	BINARY_DOUBLE
VARCHAR2(40)	VARCHAR2	byteSize: 40
CLOB / BLOB	CLOB / BLOB
TIMESTAMP / DATE	TIMESTAMP / DATE	precision: 6 on the timestamp
SYSTIMESTAMP (computed)	TIMESTAMP WITH TIME ZONE	precision: 6
COUNT(*) (computed)	NUMBER	precision: 0, scale: 0
1/3 (computed)	NUMBER	precision: 0, scale: -127

The precision and scale sit right beside the name and are deliberately not spelled into it. The last two rows are why: a computed column reports precision 0 or scale -127, and a NUMBER(p,s) built from those would claim something Oracle did not. DATA_TYPE is the type; the declaration channel carries the type.

This is the only source of a type for a computed column or an ad-hoc projection - the schema tree has no catalog entry to answer with - and it is what stops the SQL-DDL export from guessing. Measured before this existed, the probe table's NUMBER(10,2) column exported as BINARY_DOUBLE and its BLOB as VARCHAR2(4000), both inferred from a value.

5.5 An interval is normalized to its Oracle literal; a time zone cannot be

Four Oracle types "lose or hide what they carry", and the answers are not the same for both pairs: the two intervals are normalized at the driver boundary, the two time-zone timestamps cannot be and this section says so plainly instead of implying otherwise. This is the decision docs/providers/cassandra.md §3.8 already took for a CQL duration, applied to the one other engine here that has the same shape of problem.

Measured 2026-08-24 against Oracle AI Database 26ai Free with oracledb 6.10.0 in Thin mode, through createDatabaseProvider({type:"oracle"}):

Oracle type	stored	before	after
INTERVAL YEAR TO MONTH	INTERVAL '3-7' YEAR TO MONTH	{"months":7,"years":3}	"+03-07"
INTERVAL DAY TO SECOND	INTERVAL '5 6:7:8.9' DAY TO SECOND	{"fseconds":900000000,"seconds":8,"minutes":7,"hours":6,"days":5}	"+05 06:07:08.9"
TIMESTAMP WITH TIME ZONE	TIMESTAMP '2026-08-24 10:11:12.345678 +03:00'	"2026-08-24T07:11:12.345Z"	unchanged — see below
TIMESTAMP WITH LOCAL TIME ZONE	the same value	"2026-08-24T07:11:12.345Z"	unchanged — see below

The intervals

The old objects were lossless and unreadable: nothing in the product reconstructs either one, the grid showed a JSON blob where a duration belongs, and the SQL export wrote that blob into an INTERVAL column — which Oracle refuses (ORA-01867: the interval is invalid), so the row was lost rather than silently wrong.

The literal is composed in the provider, not asked of the driver, because the driver refuses to produce it: a fetchTypeHandler returning {type: oracledb.STRING} for either type fails the whole statement with NJS-119: conversion from type DB_TYPE_INTERVAL_YM to type DB_TYPE_VARCHAR is not supported, and the process-wide oracledb.fetchAsString rejects both identities up front with NJS-021: invalid type for conversion specified.

The spelling is Oracle's own signed form rather than the INTERVAL '3-7' YEAR TO MONTH keyword form, and that is a measured choice, not a preference. A cell reaches the SQL export as a value, so the keyword form would be exported quoted — 'INTERVAL ''3-7'' YEAR TO MONTH' — and Oracle answers ORA-01867. The signed form is accepted as a plain string in exactly the position the export puts it. Every form below was replayed against the live server:

ACCEPTED   INSERT INTO d19_cand (tag, iym) VALUES ('a', '+03-07')
ACCEPTED   INSERT INTO d19_cand (tag, iym) VALUES ('b', '-03-07')
ACCEPTED   INSERT INTO d19_cand (tag, iym) VALUES ('c', '+00-00')
ACCEPTED   INSERT INTO d19_cand (tag, ids) VALUES ('d', '+05 06:07:08.9')
ACCEPTED   INSERT INTO d19_cand (tag, ids) VALUES ('e', '+09 08:07:06')
ACCEPTED   INSERT INTO d19_cand (tag, ids9) VALUES ('h', '+123456789 23:59:59.123456789')
REFUSED    INSERT INTO d19_replay (tag, iym) VALUES ('ym-quoted-keyword', 'INTERVAL ''3-7'' YEAR TO MONTH')
           -> ORA-01867: the interval is invalid

Details that follow from the measurements:

• One leading sign. A negative interval arrives with every field negative (INTERVAL '-3-7' → {"months":-7,"years":-3}), so the sign is taken once and the fields are printed absolute: -03-07, not -03--07.
• Two digits is a minimum, not a width. INTERVAL '123456789-11' YEAR(9) TO MONTH arrives as {"months":11,"years":123456789} and is spelled +123456789-11, which Oracle takes back into the same column. The two-digit padding matches what TO_CHAR prints at Oracle's default leading precision; the declared precision is not in the value, so a YEAR(4) column reads +03-07 here where the server's own TO_CHAR says +0003-07. Same value, different padding.
• fseconds is nanoseconds, so the fraction is nine digits with trailing zeros trimmed — exact for a SECOND(9) column, and no fractional part at all for a whole-second interval (+09 08:07:06).
• A NULL interval stays null, not a zero interval.

Verified end to end — read through the provider, exported, replayed into a fresh table, and compared by the server, not by re-reading our own spelling:

PROVIDER ROWS  [{"K":1,"IYM":"+03-07","IDS":"+05 06:07:08.9"},{"K":2,"IYM":"-03-07","IDS":"+09 08:07:06"},{"K":3,"IYM":null,"IDS":null}]
EXPORT DDL     CREATE TABLE d19_replay ("K" NUMBER, "IYM" INTERVAL YEAR TO MONTH, "IDS" INTERVAL DAY TO SECOND);
EXPORT INSERT  INSERT INTO d19_replay ("K", "IYM", "IDS") VALUES (1, '+03-07', '+05 06:07:08.9');
               INSERT INTO d19_replay ("K", "IYM", "IDS") VALUES (2, '-03-07', '+09 08:07:06');
               INSERT INTO d19_replay ("K", "IYM", "IDS") VALUES (3, NULL, NULL);
REPLAYED       all four statements accepted; rows read back identical
SERVER SAYS    SELECT ... CASE WHEN s.iym = r.iym AND s.ids = r.ids THEN 'EQUAL' ...  ->  EQUAL, EQUAL, EQUAL
               (source TO_CHAR '+0003-07' / '+0005 06:07:08.900000' vs replayed '+03-07' /
                '+05 06:07:08.900000' — the difference is the declared leading precision of the
                exported column, not the value)

The columns are found once per result from metaData[].dbType, so a query with no interval column does no per-cell work and keeps the driver's own rows array untouched.

The time zones, and why the offset is not recoverable

A TIMESTAMP WITH TIME ZONE loses its stored offset, and this provider cannot keep it. The driver has already reduced the value to a UTC instant by the time any code here sees it: it hands over a JS Date, which holds no zone and no sub-millisecond digits.

The obvious candidate was measured and is worse than the Date. Asking for the column as a string (fetchTypeHandler → {type: oracledb.STRING}) is accepted, but what the driver returns is that same Date put through toString() — in the Node process's time zone, with the milliseconds gone. Three rows with three different stored offsets, read by a process running in +03:00:

SERVER TEXT  plus3  2026-08-24 10:11:12.345678 +03:00
             minus7 2026-08-24 10:11:12.345678 -07:00
             named  2026-08-24 10:11:12.345678 ASIA/TOKYO

DEFAULT      plus3  "2026-08-24T07:11:12.345Z"
             minus7 "2026-08-24T17:11:12.345Z"
             named  "2026-08-24T01:11:12.345Z"

AS STRING    plus3  "Mon Aug 24 2026 10:11:12 GMT+0300 (Türkiye Standard Time)"
             minus7 "Mon Aug 24 2026 20:11:12 GMT+0300 (Türkiye Standard Time)"
             named  "Mon Aug 24 2026 04:11:12 GMT+0300 (Türkiye Standard Time)"

Every row reports GMT+0300 — the reader's zone, not the stored one — and .345 is gone. That would replace a correct instant with a wrong-looking local rendering, and would break the ordinary DATE/TIMESTAMP path the grid formats, so it was rejected. oracledb.fetchAsString refuses both identities outright (NJS-021), and the driver exposes no offset beside the Date (Object.keys(date) is empty).

So the instant is right and the offset is gone. A user who needs the stored zone must ask the server for it, which is the one place that still has it:

SELECT TO_CHAR(ttz, 'YYYY-MM-DD HH24:MI:SS.FF6 TZR') FROM t;   -- 2026-08-24 10:11:12.345678 -07:00

The same TO_CHAR recovers the sub-millisecond digits that a Date cannot hold — for a plain TIMESTAMP(6) too, where .345678 is likewise truncated to .345. A TIMESTAMP WITH LOCAL TIME ZONE has no stored offset to lose (Oracle normalizes it on write and renders it in the session's zone), so for that type only the sub-millisecond truncation applies.

The SQL export writes an Oracle date literal, not an ISO string

A Date cell used not to replay at all. The shared export wrote it as its ISO string, and every one of the four types refuses that — measured 2026-08-25 against the Oracle Free image (Oracle AI Database 26ai Free Release 23.26.2.0.0) by replaying the exported file:

D     REFUSED  ORA-01861: literal does not match format string
TS    REFUSED  ORA-01843: An invalid month was specified.
TTZ   REFUSED  ORA-01843: An invalid month was specified.
TLTZ  REFUSED  ORA-01843: An invalid month was specified.
        (all four from INSERT ... VALUES ('2026-08-24T07:11:12.345Z'))

So a DDL+INSERT export of any ordinary Oracle table with a date column was unreplayable. The fix is in this engine's SQL writing module (oracle-sql-writing.ts, export.columnWriters), not in this provider, and the conversion function IS the literal — the way HEXTORAW already is for a RAW. Which function comes from the declared type (columnTypes, §5.4), because the two shapes disagree about which fields of the Date are the value:

declared	written as
DATE	TO_DATE('2026-08-24 10:11:12', 'YYYY-MM-DD HH24:MI:SS') — the local fields
TIMESTAMP (and anything else, and no declared type)	TO_TIMESTAMP('2026-08-24 10:11:12.345', 'YYYY-MM-DD HH24:MI:SS.FF3') — the local fields
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE	FROM_TZ(TO_TIMESTAMP('2026-08-24 17:11:12.345', 'YYYY-MM-DD HH24:MI:SS.FF3'), 'UTC') — the UTC instant
DATE, holding the provider's text (#1131)	TO_DATE('2026-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), the text itself
TIMESTAMP or TIMESTAMP(n), holding the provider's text (#1131)	TO_TIMESTAMP('2026-09-01 10:30:00.345', 'YYYY-MM-DD HH24:MI:SS.FF'), the text itself, .FF only when it has a fraction
TIMESTAMP[(n)] WITH [LOCAL] TIME ZONE, holding a Date's ISO text, as over HTTP (#1224)	FROM_TZ(TO_TIMESTAMP('2026-08-24 17:11:12.345', 'YYYY-MM-DD HH24:MI:SS.FF3'), 'UTC'), the same literal the Date of that instant gets

• The provider's own text for a naive column (#1131). The provider reads a DATE and a TIMESTAMP as their wall clock (§5.3), and that text is what reaches the export, over HTTP and in-process alike. Quoted as it is, it would be read through the session's NLS_DATE_FORMAT (DD-MON-RR by default) and refused, so the export writes it through the function of the declared type, with a mask that spells the text's own form: SYYYY only for a BC year, and FF, which takes one to nine digits, only when there is a fraction. No getter is read, so the zone the export runs in moves nothing. Two things stay quoted as text:

  • A cell in a column not declared DATE or TIMESTAMP: a VARCHAR2 holding the same characters is text, and converting it would store the NLS rendering of a timestamp in its place.
  • A value not in that form, a DATE text with a fraction included.

  So the naive Date rows above now apply only to a host that builds its rows itself.

• A BC year (#1498). A Date whose year is negative, such as a zoned Date the driver reads for -0044-03-15 10:30:00 +00:00 or a naive one a host built, is written with the sign in front of the zeros and the signed mask: FROM_TZ(TO_TIMESTAMP('-0044-03-15 10:30:00.000', 'SYYYY-MM-DD HH24:MI:SS.FF3'), 'UTC'), and TO_DATE('-0044-03-15 10:30:00', 'SYYYY-MM-DD HH24:MI:SS') for a DATE. Before, the year was padded as 0-44, which Oracle refused (ORA-01843, ORA-01850). A year of 0 to 9999 keeps YYYY and no sign. #1498 measured the signed literals on Oracle AI Database 26ai Free 23.26.3: replayed into a copy of the source table, the server compares the rows equal.

• A zoned column's ISO text, over HTTP (#1224). The provider leaves TIMESTAMP WITH TIME ZONE and WITH LOCAL TIME ZONE as the driver's Date, so in-process the export gets the zoned Date row above. Over HTTP the row has been through JSON (POST /api/db/query), and the cell is that Date's toISOString text, 2026-09-01T07:30:00.000Z. Quoted, Oracle reads it through the session's NLS_TIMESTAMP_TZ_FORMAT and refuses it: ORA-01843: An invalid month was specified. So for a column declared TIMESTAMP[(n)] WITH [LOCAL] TIME ZONE, text in exactly that form (YYYY-MM-DDTHH:MM:SS.sssZ) is parsed back to its Date and written by the same code as the Date, but only when that Date writes the same text back. The literal is built from the parsed fields, never from the text. The live script (§12.4) replays both zoned types this way under Europe/Istanbul, and the server calls every row equal. Three things stay quoted as text:

  • The same text in a column not declared a zoned timestamp: TIMESTAMP, DATE, VARCHAR2, or no declared type.
  • Other text in a zoned column: +00:00 in place of Z, no fraction or more than three digits of one, a space in place of T, and text that has the form but is not what toISOString writes for any instant, such as 2026-02-30T00:00:00.000Z or T24:00:00.000Z.
  • A year outside 0000–9999, which toISOString writes with a sign and six digits (-000044-03-15T10:30:00.000Z, +012026-…). Oracle has no year after 9999, and the BC text is left for a separate change, so it stays quoted and Oracle still refuses it (ORA-01847). The Date of a BC instant is written with its sign (above), so only this text form is affected.

• Local fields for a naive column, because that is the inverse of what the driver did: it built the Date by reading the stored wall clock in the Node process's zone. Measured above, a DATE holding 2026-08-24 10:11:12 arrives as 2026-08-24T07:11:12.000Z from a process at +03:00, so writing the ISO text would move every naive value by the exporter's own offset — and it would parse, which is worse than being refused.

• FROM_TZ(..., 'UTC') for a zoned column, because the Date there is the true instant and the stored offset is already gone (above). No offset is invented; the instant is preserved whatever zone the replaying session runs in, which a plain TO_TIMESTAMP is not — it is read in the session's zone. Measured by replaying the same instant into a session at -07:00 and letting the server compare it against the source row:

  fromtz              1999-01-01 18:04:05.006 UTC       EQUAL
  plain-utc-fields    1999-01-01 18:04:05.006 -07:00    DIFF
  plain-local-fields  1999-01-01 21:04:05.006 -07:00    DIFF
  

• What a zoned column loses: its original zone, and only its zone. A TIMESTAMP WITH TIME ZONE that read 2026-08-24 10:11:12.345 -07:00 on the source comes back as 2026-08-24 17:11:12.345 UTC on the target — the same moment, rendered as UTC, because the offset was gone before the export saw the value. TIMESTAMP WITH LOCAL TIME ZONE loses nothing: it has no stored offset, and it renders in the reader's session zone on both sides. A user who needs the original zone must take it from the server with the TO_CHAR ... TZR above, in the same result.

• Milliseconds are kept (FF3), and a declared DATE gets TO_DATE because a DATE has no fractional second at all. Both were measured: a TO_TIMESTAMP literal inserted into a DATE column is accepted and silently truncated to the whole second, so the explicit function only says what the column already is.

• Inline grid editing (use-inline-editing.ts, #1807) opens its editor on those same three text forms: the wall-clock text of a DATE or TIMESTAMP (#1131) and the ISO instant text of a TIMESTAMP WITH [LOCAL] TIME ZONE over HTTP (#1224). And oracledb binds a string parameter as DB_TYPE_VARCHAR, which Oracle reads into a date or timestamp column through the session's NLS_DATE_FORMAT (DD-MON-RR), NLS_TIMESTAMP_FORMAT (DD-MON-RR HH.MI.SSXFF AM), or NLS_TIMESTAMP_TZ_FORMAT (DD-MON-RR HH.MI.SSXFF AM TZR). Measured on 26ai Free 23.26.3.0.0 (oracledb 26.0.0, TZ=Europe/Istanbul): a bare SET "COL" = :1 is refused with ORA-01861: literal does not match format string on DATE and ORA-01843: An invalid month was specified. on TIMESTAMP and both zoned timestamp types. So an edit still in the column's form (inlineEdit.boundEdit in oracle-sql-writing.ts, sharing the masks and regexes the SQL INSERT export reads there) binds :n inside TO_DATE(:n, '[S]YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP(:n, '[S]YYYY-MM-DD HH24:MI:SS[.FF]'), or FROM_TZ(TO_TIMESTAMP(:n, 'YYYY-MM-DD HH24:MI:SS.FF3'), 'UTC') (binding YYYY-MM-DD HH:MM:SS.sss). Text in any other shape, or in a column declared as another type, is bound as :n untouched.

Verified end to end — read through the provider, exported, replayed into a fresh table, compared by the server. This run predates #1131, so D and TS are still the driver's Date in it, read by a process at +03:00:

PROVIDER ROWS  [{"K":1,"D":"2026-08-24T07:11:12.000Z","TS":"2026-08-24T07:11:12.345Z","TTZ":"2026-08-24T17:11:12.345Z","TLTZ":"2026-08-24T17:11:12.345Z"},
                {"K":2,"D":"1999-01-01T22:00:00.000Z","TS":"1999-01-02T01:04:05.006Z","TTZ":"1999-01-01T18:04:05.006Z","TLTZ":"1999-01-01T18:04:05.006Z"},
                {"K":3,"D":null,"TS":null,"TTZ":null,"TLTZ":null}]
COLUMN TYPES   {"K":"NUMBER","D":"DATE","TS":"TIMESTAMP","TTZ":"TIMESTAMP WITH TIME ZONE","TLTZ":"TIMESTAMP WITH LOCAL TIME ZONE"}
EXPORT DDL     CREATE TABLE d23_replay ("K" NUMBER, "D" DATE, "TS" TIMESTAMP,
                 "TTZ" TIMESTAMP WITH TIME ZONE, "TLTZ" TIMESTAMP WITH LOCAL TIME ZONE);
EXPORT INSERT  INSERT INTO d23_replay ("K", "D", "TS", "TTZ", "TLTZ") VALUES (2,
                 TO_DATE('1999-01-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                 TO_TIMESTAMP('1999-01-02 03:04:05.006', 'YYYY-MM-DD HH24:MI:SS.FF3'),
                 FROM_TZ(TO_TIMESTAMP('1999-01-01 18:04:05.006', 'YYYY-MM-DD HH24:MI:SS.FF3'), 'UTC'),
                 FROM_TZ(TO_TIMESTAMP('1999-01-01 18:04:05.006', 'YYYY-MM-DD HH24:MI:SS.FF3'), 'UTC'));
REPLAYED       CREATE TABLE and all three INSERTs accepted
SERVER SAYS    K=2  D_EQ EQUAL  TS_EQ EQUAL  TTZ_EQ EQUAL  TLTZ_EQ EQUAL
               K=3  (the all-NULL row)       EQUAL on all four
               K=1  DIFF on the three timestamps, by exactly 00:00:00.000678

That last row is the truncation this section is about, not an export defect: K=1 was stored with .345678 and a Date holds milliseconds, so the server measures the difference as the 678 microseconds the driver dropped (TO_CHAR(s."TS" - r."TS") → +000000000 00:00:00.000678, and +000000000 00:00:00.000000 for the millisecond-exact row). A sub-millisecond digit does not survive an export, because it did not survive the driver.

---

5.6 SQL grammar

How this engine's statement text is read is declared once, in src/lib/db/providers/sql/oracle-sql-grammar.ts, the oracle row of the SQL grammar registry in src/lib/db/sql-grammar/. Its grammar is ORACLE_GRAMMAR, the one reading the statement splitter, the row limiter, the confirmation gate and query-event redaction share, so they cannot disagree about where a literal or a comment ends. # is an identifier character and comments open on -- and /* only, from node-oracledb's own SQL tokenizer (lib/thin/statement.js). Alternate quoting is on, and Oracle is the only row with it: the same tokenizer's _parseQstring opens a q-string at a ' after q or Q, nq'...' included, with [ ] { } ( ) < > as paired delimiters, as Oracle's SQL Language Reference (checked 2026-08-05) gives it. Block comments are flat: the tokenizer stops at the first */, and the PL/SQL Language Reference says one multiline comment cannot contain another. // is code, probed 2026-08-25 on Oracle Free through sqlplus: SELECT 1 AS a // note FROM dual is ORA-00923. A PL/SQL unit is one statement, ended by SQL*Plus's / line: measured on 26ai Free 23.26.3, a procedure cut at its inner ; was stored INVALID with PLS-00103, and the unit is refused without the ; after its END, so the splitter keeps that one. [...] is not established and stays at the fail-safe default. The update-target reader takes MINUS as a set operator here alone, since it is an ordinary column name on every other engine. readsSqlText is true: the editor text is SQL, and the provider extends SQLBaseProvider. Redaction is sql with " as its name quote: a redacted statement keeps its words and every name in that quote, and every literal becomes ?.

5.7 Agent read-only execution profile

Agent mode executes statements on Oracle through queryReadOnly in index.ts, on Oracle Database 23ai and 26ai. The boundary is credential-enforced: the agent signs in as a local user set ALTER USER ... READ ONLY, which the server refuses every write with ORA-28194: Can perform read operations only. What READ ONLY does not stop is what the user can reach through others, so before every statement the user is checked, as itself, to reach none of it (agent-read.ts). Studio's agent admission in front of the provider is defence in depth on top of those two.

The factory passes the execution context as the constructor's third argument. queryReadOnly answers only when that context sets both readOnly and sendsStatements, the agent and hand-over profiles; it refuses on any other handle, an ordinary editor handle and the operations profile's included, before it sends anything. The operations profile and plan mode send no statement of the model's, so they open unchecked, and plan mode keeps grounding on a privileged connection.

What READ ONLY refuses, and what it does not

Measured on Oracle AI Database 26ai Free Release 23.26.3.0.0 (gvenzl/oracle-free:slim, digest pinned in run.sh), with the scripts and their answers in tests/fixtures/oracle/23.26.3-free/agent-boundary/:

• A READ ONLY user is refused INSERT, UPDATE, DELETE and MERGE on a table it holds those grants on, CREATE TABLE, a global and a private temporary table, SELECT ... FOR UPDATE, LOCK TABLE, a write through DBMS_SQL, a write in a PL/SQL block after a read, a write over a public database link, and a granted definer's-rights function that writes in an autonomous transaction, each with ORA-28194 (captures 03 and 04).
• ALTER SESSION SET READ_ONLY = FALSE answers "Session altered" and every write after it is still refused the same way (capture 03); inside the read-only transaction each agent call opens, the same statement answers ORA-65023 instead.
• The SQL statements COMMIT and ROLLBACK are refused with ORA-28194 too; the node-oracledb Thin calls connection.commit() and connection.rollback() are not, and end the read-only transaction (capture 10).
• A READ ONLY user that holds ALTER SYSTEM still runs ALTER SYSTEM SET, inside SET TRANSACTION READ ONLY as well (capture 05).
• SELECT <sequence>.NEXTVAL advances the sequence, inside SET TRANSACTION READ ONLY too (capture 06).
• A granted definer's-rights function runs with its owner's privileges: a user holding only CREATE SESSION advanced a sequence ALL_SEQUENCES does not list for it, and ran ALTER SYSTEM SET OPEN_CURSORS, through two granted functions (captures 08 and 09).
• A SELECT runs code of the object's owner in five more places, each measured as a READ ONLY user holding only CREATE SESSION and SELECT grants, with a sequence of its own (captures 11 to 13): a virtual column over a function, selected by name, the hidden virtual column of a function-based index over a function, selected by its SYS_NC name, a row-level security policy function, on the table, on a private synonym and on a PUBLIC synonym, and on a table the user reads only through a granted view, a fine-grained auditing handler, and a granted view over a view the user may not read, replaced to call a function after the outer view was created. ALL_TAB_COLS lists the user both virtual columns, a hidden one included, and ALL_POLICIES the three policies on what it may read, but not the one behind the view; ALL_AUDIT_POLICIES answers ORA-00942; ALL_DEPENDENCIES lists the outer view's dependency on the inner view only. Granted SELECT on DBA_POLICIES and DBA_AUDIT_POLICIES by SYS (SYSTEM is refused that grant with ORA-01031, capture 14), the same user reads the policy behind the view in the first and the handler in the second (captures 14 and 15).
• A row-level policy whose function the schema owner owns, put on SYSTEM.HELP, which PUBLIC may read, ran for the READ ONLY user's plain SELECT and advanced the owner's sequence; so did a fine-grained auditing policy with no handler whose audit condition calls the owner's function, once the policy's creator could execute that function (without that, the read answers ORA-28112 and runs nothing) (measured, tests/live/oracle-agent-boundary.ts).
• A fresh database holds twelve row-level policies, on four XDB tables, every one calling XDB.DBMS_XDBZ0, which PUBLIC may execute and ALL_OBJECTS marks ORACLE_MAINTAINED, and no fine-grained auditing policy (capture 00). Every object a PUBLIC grant names in a schema Oracle maintains is itself marked ORACLE_MAINTAINED, and no unmarked table there has a virtual column (measured).
• A function an administrator creates in SYSTEM, a schema ALL_USERS marks Oracle-maintained, is not marked itself in DBA_OBJECTS, and it runs with SYSTEM's rights: a row-level policy calling it on SYSTEM.HELP, and a fine-grained auditing handler made there on the same table, each advanced a SYSTEM sequence for the READ ONLY user's plain SELECT (measured).
• A fresh database keeps 5,913 objects it marks ORACLE_MAINTAINED local to the pluggable database, SHARING reading NONE, against 23,257 linked from the root container; PUBLIC may execute 133 of the local ones: 109 domains, 14 operators, 3 job classes, 2 indextypes, 2 consumer groups, an evaluation context, a procedure and a package body (measured).
• An object an administrator makes under _oracle_script is marked ORACLE_MAINTAINED, and SYSTEM.PRODUCT_PRIVS, a local view Oracle marks that PUBLIC may read, keeps its flag and that grant when SYSTEM replaces it; each reads SHARING NONE. A function and an operator made that way and granted to PUBLIC, and the replaced view calling a SYSTEM function, each advanced a SYSTEM sequence for the READ ONLY user's plain SELECT (measured).
• A domain's check, display and order expressions refuse a user function with ORA-00904, and its default with ORA-04044 (measured).
• An unqualified DBA_POLICIES read by the user resolves to a private synonym of that name in its own schema before Oracle's PUBLIC synonym: an administrator's synonym over an empty table hid a policy from it, while SYS.DBA_POLICIES still listed it (measured).
• ALL_OBJECTS and ALL_DEPENDENCIES show the user a view, and that view's own dependencies, for any grant that makes ALL_OBJECTS list it, INSERT alone included, and for none of DEBUG, FLASHBACK and MERGE VIEW alone (measured).
• A view over a table the user may not read, which has a virtual column over a function, lists that function in ALL_DEPENDENCIES whether it selects the column, selects * or leaves the column out (measured).
• A column group made by DBMS_STATS.CREATE_EXTENDED_STATS adds a hidden virtual column named SYS_STU... to a plain table, and ALL_TAB_COLS lists it to the user (captures 14 and 15).
• A granted view left invalid, by a column it reads dropped and added again as a virtual column over a function, lists its old dependencies, and the READ ONLY user's SELECT compiled it and ran the function (captures 11 to 13).
• ALTER SESSION SET EDITION inside SET TRANSACTION READ ONLY is refused with ORA-38814, so a call cannot switch the edition its views are read in (capture 12).
• The fresh database grants PUBLIC EXECUTE on DBMS_LDAP, DBMS_SCHEDULER, DBMS_XSLPROCESSOR, HTTPURITYPE, UTL_FILE, UTL_HTTP, UTL_INADDR, UTL_SMTP and UTL_TCP among the packages that reach the network or the server's files, and SELECT on four sequences Oracle maintains (captures 02 and 07).

The check before each statement

Every queryReadOnly call runs the check on the statement's own session, as the agent user, inside its SET TRANSACTION READ ONLY and before the statement, and keeps no verdict: a grant or a policy added after one call refuses the next call on the same handle, and a refusal lifted between two calls admits the second (measured). What remains is a window from SET TRANSACTION READ ONLY to the statement, the whole check included: the check reads the dictionary as of the transaction's start, so a grant or a policy added after SET TRANSACTION is not seen by that call's check, and a policy added there ran for the statement (measured); the next call sees it. The check costs more as the database holds more grants and objects the user can see. On the measured 26ai Free a call with its check and a SELECT 1 FROM DUAL took about 200 ms with nothing granted beyond the two catalog views, about 20 ms of it opening and closing the call's own session, about 250 ms with 3,000 extra views granted to the user, and 315 to 370 ms with 7,000. The first call after the server last optimized the check's statements took about 0.8 s. Each finding is collected, and one PROFILE_PRIVILEGES_TOO_BROAD refusal names them all, as the statements that lift them or, for a policy, as the policy. A revoke can uncover the next finding: a granted view over a view the refusal names is refused once the inner grant is gone, so revoke the outer grant as well; the next call's check reads the database as it is then. Every dictionary view the check and the catalog read name is written SYS.<view>: an unqualified name resolves first to an object or a private synonym in the user's own schema, then to a PUBLIC synonym, and either could stand in for the view. An object is Oracle's only when ALL_OBJECTS shows the user every object of its owner and name as ORACLE_MAINTAINED and linked from the root container, SHARING other than NONE; one the user cannot see is not. The schema's flag in ALL_USERS proves nothing: an administrator can create objects in SYSTEM or SYS, which are not marked. Nor does the object's own flag alone: an object made under _oracle_script is marked, and a local object Oracle marked keeps its flag when replaced; the SHARING rule refuses both, since each is local. Below, "not Oracle's" means that; item 3 alone admits some local objects by their kind.

1. USER_USERS.READ_ONLY must read YES. A server without the column (ORA-00904) is older than 23ai and is refused PROFILE_UNSUPPORTED_TARGET.
2. Every role granted to the user, directly or through PUBLIC (USER_ROLE_PRIVS), must be a default role. SET ROLE is permitted under READ ONLY, while ALL_TAB_PRIVS lists only the grants of enabled roles, so a role whose grants the check cannot see is refused.
3. The user, its enabled roles and PUBLIC must hold EXECUTE on no unit (PL/SQL, type, Java class, library) that is not Oracle's, an administrator's function in SYSTEM included. Here alone a local object Oracle marks is admitted by its kind: a domain, a job class or a consumer group, which runs no function (a domain's expressions refuse one, and a job class or a consumer group names no code, measured; a job class is used only through DBMS_SCHEDULER, item 10); an operator whose every binding's function and implementation type are linked (ALL_OPBINDINGS); an indextype whose implementation type is linked (ALL_INDEXTYPES). An evaluation context names a function that DBMS_RULE, which PUBLIC may execute, runs, so it is held to the link; the refusal names DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE for it, since a REVOKE statement answers ORA-04042 (measured).
4. They may read no view that is not Oracle's whose own definition calls code rather than a table or view (ALL_DEPENDENCIES): a plain SELECT on such a view runs that code with its owner's rights, and advanced a sequence for a READ ONLY user (measured). A view that calls the code through a synonym, private or PUBLIC, lists only the synonym, and ALL_SYNONYMS does not show the user another schema's private synonym (measured), so a synonym is admitted only when ALL_SYNONYMS resolves it to a table or view the check starts from, or to an object that is Oracle's, as PUBLIC.DUAL and PUBLIC.DBMS_LOB are; any other is refused, a private synonym for a table the user is not granted, and one for another synonym, included. The check reads dependencies for the objects it starts from: those that are not Oracle's granted to the user, an enabled role or PUBLIC that ALL_OBJECTS shows the user, and the tables and views the user owns. A view over another view lists only the inner view, and ALL_DEPENDENCIES lists the inner view's own dependencies only when ALL_OBJECTS shows the user the inner view, any object grant on it that does so included, so a view over a view that is not Oracle's is admitted only when the inner view is one of the objects the check starts from, held to this same item; a synonym that does not resolve to an object that is Oracle's is admitted only when it resolves to one of them.
5. They may read no such view that is not valid (ALL_OBJECTS.STATUS): an invalid view lists the dependencies it had, and the user's SELECT compiles it and runs what it calls now (measured). The refusal names the revoke; compiling the view lifts it as well, since the next call's check then reads what the view calls now.
6. No table or view that is not Oracle's that ALL_TAB_COLS shows the user has a virtual column, hidden or not: selecting one evaluates its expression, which can call a function with its owner's rights, and the hidden column of a function-based index can be selected by its SYS_NC name (measured). The expression is not parsed to prove it built-in, so every one is refused, UPPER(NAME) and the hidden SYS_STU column of extended statistics (a column group or an expression statistic) included.
7. SYS.DBA_POLICIES must list no row-level security policy anywhere in the database, enabled or not, whose function is not Oracle's, on any object, one Oracle maintains included: a SELECT of what it guards runs the function with its owner's rights, and the user reaches guarded objects the check cannot follow it to, a table behind a view or Oracle's own SYSTEM.HELP (measured). ALL_POLICIES does not list the user a policy behind a view, so the rule is database wide rather than per object.
8. SYS.DBA_AUDIT_POLICIES must list no fine-grained auditing policy anywhere, enabled or not, with an audit condition, which a read evaluates and which can call a function with its owner's rights (measured), or with a handler that is not Oracle's, which a read runs; ALL_AUDIT_POLICIES answers the user ORA-00942. The condition is not parsed to prove it built-in, so every one is refused, ID > 0 included. A user that may not read either view is refused, naming its grant, which only SYS may run. The fresh database's own rows, all calling XDB.DBMS_XDBZ0, are admitted (capture 00); a policy function or handler an administrator made in SYSTEM is refused (measured).
9. They must hold no privilege on a sequence that is not Oracle's, and the user must own no sequence.
10. They must hold EXECUTE on none of UTL_HTTP, UTL_TCP, UTL_SMTP, UTL_FILE, UTL_INADDR, HTTPURITYPE, DBMS_LDAP, DBMS_SCHEDULER, DBMS_PIPE and DBMS_XSLPROCESSOR, in any schema, and no privilege on a directory, which BFILENAME reads server files through.
11. SESSION_PRIVS must hold no system privilege but CREATE SESSION; the grantee is read from USER_SYS_PRIVS and ROLE_SYS_PRIVS where they list it.
12. ALL_DB_LINKS must list no link, PUBLIC or the user's own, since a link reaches another database under its stored credential. Only its owner drops a private link, and READ ONLY refuses the owner that DROP with ORA-28194 (measured), so the refusal names it between ALTER USER ... READ WRITE and ALTER USER ... READ ONLY.

A check read that fails, past the deadline included, is reported as a connection failure rather than as a failure of the model's statement, so the agent does not offer it to the model to repair.

Each statement

index.ts runs each call on a session opened for it alone, with the profile's user, connect string and TLS settings, never one from the pool:

• the driver's callTimeout is set to the statement budget, so a round trip past it ends with NJS-123, refused in the shared time sentence, and the session is signed off; that ends the wait at the deadline, and this record does not measure when the server stops the statement;
• SET TRANSACTION READ ONLY opens the transaction, the check above runs on the same session, and the statement follows in one execute, with autoCommit: false on all of them, because the constructor sets the process-wide default to true for the editor;
• the statement is executed as a result set, so its column types are read before any row is fetched: without one the driver fetches every row, and expands every nested cursor in full, before the execute returns;
• a column of type BFILE is then refused unread, since its locator reads a server file, and so are an object and a cursor column, whose values the byte budget cannot measure: measured, three nested CURSOR() levels of 1,000 rows each were refused with no growth of the process;
• otherwise at most one row past the row budget is fetched, and a result past it is refused rather than cut;
• CLOB and NCLOB are fetched as strings and BLOB as buffers by the call's own fetchTypeHandler, never the process-wide settings, so no LOB locator outlives the call, and every value is counted against the byte budget;
• the session is then rolled back with the driver's rollback() and closed, which signs it off, on every path, an answered call included. A session keeps what a statement set on it, and the USER_* views the check reads follow CURRENT_SCHEMA: measured, an agent's ALTER SESSION SET CURRENT_SCHEMA = APP_OWNER ran, and on a pooled session the next calls read APP_OWNER as their schema. The Thin driver of node-oracledb 6.10 returns a pooled session to its pool even when close({ drop: true }) asks it to drop it (measured), so the session is never taken from the pool at all.

Every refusal is a sentence of read-only-budget.ts or the server's own error. The estimate-plan session mode is refused: EXPLAIN PLAN writes its rows into PLAN_TABLE, a write the READ ONLY user is refused, so the agent composes no estimating plan here (B147). The run read allowance is not charged: Oracle does not bill per read.

The agent user

The agent credential takes a user and a password, both required, the form every engine without an agentCredential declaration takes. An agent credential beside a connection string is refused (AGENT_CREDENTIAL_WITH_CONNECTION_STRING); with no agent credential the agent signs in as the connection's own user, which the check must admit. Create the user in the pluggable database, grant it SELECT on the tables it may read and, as SYS, on the two catalog views of items 7 and 8, and set it READ ONLY:

CREATE USER libredb_agent IDENTIFIED BY "<secret>";
GRANT CREATE SESSION TO libredb_agent;
GRANT SELECT ON app.orders TO libredb_agent;
GRANT SELECT ON app.customers TO libredb_agent;
GRANT SELECT ON SYS.DBA_POLICIES TO libredb_agent;        -- as SYS
GRANT SELECT ON SYS.DBA_AUDIT_POLICIES TO libredb_agent;  -- as SYS
ALTER USER libredb_agent READ ONLY;

The two grants stay on the agent credential, since each seed carries one credential and a non-default role is refused: so the agent's own statements can read, through them, every row-level policy's function name and every fine-grained auditing policy's handler and audit condition text in the database. That is metadata, not table data, and it is what proving that no such code runs on the agent's reads costs.

A fresh database refuses that user, because PUBLIC holds EXECUTE on the network and file packages and on four local objects of Oracle's that the check cannot prove. Run the revoke the refusal names, as SYS; on the measured 26ai Free it is:

REVOKE EXECUTE ON SYS.DBMS_LDAP FROM PUBLIC;
REVOKE EXECUTE ON SYS.DBMS_SCHEDULER FROM PUBLIC;
REVOKE EXECUTE ON XDB.DBMS_XSLPROCESSOR FROM PUBLIC;
REVOKE EXECUTE ON SYS.HTTPURITYPE FROM PUBLIC;
REVOKE EXECUTE ON SYS.UTL_FILE FROM PUBLIC;
REVOKE EXECUTE ON SYS.UTL_HTTP FROM PUBLIC;
REVOKE EXECUTE ON SYS.UTL_INADDR FROM PUBLIC;
REVOKE EXECUTE ON SYS.UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON SYS.UTL_TCP FROM PUBLIC;
REVOKE EXECUTE ON SYS.SCHEDULER$NTFY_SVC_METRICS FROM PUBLIC;
REVOKE EXECUTE ON XDB.XMLINDEX FROM PUBLIC;
REVOKE EXECUTE ON XDB.XMLINDEX_NOOP FROM PUBLIC;
BEGIN DBMS_RULE_ADM.REVOKE_OBJECT_PRIVILEGE(DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT, '"SYS"."STREAMS$_EVALUATION_CONTEXT"', 'PUBLIC'); END;

The last four are objects Oracle keeps local to the database that item 3 cannot prove: a procedure, an evaluation context, and an indextype and an operator whose implementation type, XDB.XMLINDEXMETHODS, the user cannot see.

Revoking from PUBLIC reaches every user of the database: grant EXECUTE back to each user or role that calls one of these packages, before revoking, and recompile what the revoke invalidates. Grant the SELECT on tables, or on views whose definitions read only tables and views: a view that calls a function refuses the user, unless the view is Oracle's.

The check refuses some grants that run no code, since it cannot prove that they do not; each refusal names a statement that lifts it, and each has a remedy that keeps the read:

• A table with a virtual column, with a function-based index, whose hidden column is a virtual column, even over a built-in such as UPPER, or with extended statistics, whose column group or expression adds a hidden SYS_STU virtual column: grant a view that leaves the column out instead of the table, or drop statistics that are not needed with DBMS_STATS.DROP_EXTENDED_STATS. The view does not show the user the table's columns, and a view over a table with a virtual column over a function lists that function and is refused under item 4.
• A view over a view the check does not start from: grant the inner view as well, so the check reads its dependencies, or define the outer view over the tables directly; once a refusal names the inner view, revoke the outer one with it. A DEBUG, FLASHBACK or MERGE VIEW grant on the inner view is not enough, since ALL_OBJECTS then does not show it.
• A view that is not valid: revoke the grant the refusal names, or compile it with ALTER VIEW ... COMPILE as its owner or an administrator, so the dictionary lists what it reads now and the next call's check reads that.
• A user that may not read SYS.DBA_POLICIES or SYS.DBA_AUDIT_POLICIES: grant both as SYS.
• An object the agent user owns: no revoke takes its own object from it, so the refusal names the object's move to another schema.

One refusal has no remedy short of changing the database: a row-level security policy whose function is not Oracle's, or a fine-grained auditing policy with an audit condition or with such a handler, anywhere in the database, on any object, disabled ones included. Oracle runs that code with its owner's rights when any user reads what it guards, the agent user included, and the check cannot prove the agent user outside every object it guards, so agent mode is not offered on such a database; the refusal names the policies rather than a statement. Dropping the policy, or for auditing replacing its condition and handler with none, lifts it, at the cost of what the policy protects; a fine-grained auditing policy with neither condition nor handler runs no code and is admitted.

What the agent reads beyond the tables needs no grant: the catalog reads below and plan-mode grounding read the ALL_* views, which list what the user may see. Measured with the agent credential set, under the operations profile: the READ ONLY user listed and described the four tables it held SELECT on. Its operations readings come back degraded, as section 3.7 describes for a user that cannot read the V$ and DBA_ views: the database size read 0 MB and the cache hit ratio N/A.

What only the operator stops

• A sequence of Oracle's that PUBLIC may select is advanced by NEXTVAL, measured: SYS.SCHEDULER$_JOBSUFFIX_S answered for the admitted user. Four are granted on a fresh database (capture 07); advancing one moves a counter and changes no data. Oracle's own definer's-rights packages granted to PUBLIC, and Oracle's own views, are trusted as the vendor's boundary in the same way: the check trusts an object Oracle marks ORACLE_MAINTAINED and links from the root container, and, for what a grant may execute, a local one of a kind item 3 admits.
• ALL_OBJECTS does not list a package body to the agent user, so the check reads only a package's specification: on a fresh database SYS.DBMS_MEMOPTIMIZE's specification is linked and its body local, the one such package PUBLIC may execute (measured), and a body an administrator replaced there would not be seen.
• LOCK TABLE is not a residual: the READ ONLY user is refused it (capture 03).

Boundary record

Measured on 26ai Free 23.26.3 (the digest run.sh pins, 2026-10-29) by tests/live/oracle-agent-boundary.ts, with Studio's agent admission bypassed: 168 checks, all passing.

Probe	Admitted READ ONLY user, through queryReadOnly	Writable schema owner, positive control
INSERT, UPDATE, DELETE, MERGE on a table it holds those grants on	ORA-28194	ran
CREATE TABLE, a global and a private temporary table	ORA-28194	ran
SELECT ... FOR UPDATE, LOCK TABLE	ORA-28194	ran
a write in a PL/SQL block after a read, a write through DBMS_SQL	ORA-28194	ran
ALTER SESSION SET READ_ONLY = FALSE then a write, in one block	ORA-65023	ran
COMMIT, ROLLBACK	ORA-28194	ran
GRANT	ORA-28194	not sent
ALTER SYSTEM SET	ORA-01031	not sent
a second statement	ORA-03405	not sent
ALTER SESSION SET EDITION	ORA-38814	not sent

The table rows, the sequence, open_cursors, the owner's objects and the database links read back the same before and after the refused statements. The fresh database refused its READ ONLY user, naming the thirteen revokes above and the two catalog grants, and none of its local domains, job classes, consumer groups or operators bound to linked code, and admitted it once they ran. The admitted user then read SYSTEM.PRODUCT_PRIVS and XMLSEQUENCE, Oracle's local view and operator, was refused DBMS_SCHEDULER.CREATE_JOB naming a job class with PLS-00201, and a domain's check, display and order expressions each refused a user function with ORA-00904. The check refused, and admitted again once the grant was gone: a user set READ WRITE; EXECUTE on a definer's-rights function granted to the user, to PUBLIC and to a default role; a role set DEFAULT ROLE NONE; SELECT on a view over that function, on a view that calls it through a private synonym, through a PUBLIC synonym and through a chain of two private synonyms, on a view over a private synonym for a table the user is not granted, on a view over an invoker's-rights function, and on a view over a view the user may not read, one that calls that function and one that calls nothing; SELECT on a table with a virtual column over that function and on one with a function-based index over UPPER; SELECT on a view left invalid by a dropped column; SELECT on a view over a view granted only DEBUG; a table the user owns with a virtual column, lifted by dropping the table; each lifted by dropping the policy: a row-level policy of the owner's on a granted table, on a table the user is not granted at all, on a table read only through a granted view, and on SYSTEM.HELP, a policy on a private and on a PUBLIC synonym, lifted by dropping the synonym, a fine-grained auditing handler of the owner's on a granted table, behind a granted view and on SYSTEM.SQLPLUS_PRODUCT_PROFILE, and an audit condition calling the owner's function and one of built-ins only, ID > 0; a row-level policy and a fine-grained auditing handler on SYSTEM.HELP whose function an administrator made in SYSTEM, EXECUTE through PUBLIC on such a function, a granted view made in SYSTEM that calls it, and SELECT on a sequence made in SYSTEM; EXECUTE through PUBLIC on a function made in SYSTEM under _oracle_script, and on an operator made there bound to a SYSTEM function, which Oracle marks its own, and SYSTEM.PRODUCT_PRIVS replaced to call a SYSTEM function, lifted by restoring it, each measured to run its function for the READ ONLY user; a row-level policy while the user's own schema held a private synonym DBA_POLICIES over an empty table, which hid the policy from an unqualified read; the grant on DBA_POLICIES and on DBA_AUDIT_POLICIES, each revoked, lifted by granting it again; SELECT on a sequence; a private database link the user owns, which the user, READ ONLY, was refused the DROP of with ORA-28194 and dropped once set READ WRITE; a PUBLIC database link; READ on a directory. It refused a READ ONLY user holding ALTER SYSTEM, and admitted the fresh database's twelve XDB row-level policies, a fine-grained auditing policy with no condition and no handler, a view over DBMS_LOB through Oracle's PUBLIC synonym, a view over a private synonym for a table the user is granted, a view that leaves out the function-based index's hidden column, a view over a view once both were granted, and a view over a view granted only INSERT. On one handle, a call ran, a row-level policy added on SYSTEM.HELP refused the next call without running its function, and the call after the policy was dropped ran. On the same handle the agent's ALTER SESSION SET CURRENT_SCHEMA = APP_OWNER ran, and each of the four calls after it read the agent user's own schema and its own empty USER_TABLES. The operations profile opened the writable schema owner unchecked and listed its tables; it and an editor handle refused queryReadOnly before sending anything. The four catalog kinds answered as the admitted user. The budgets: 60 rows under a budget of 50 were refused at the 51st, a 30,000-character CLOB under a byte budget of 10,000 was refused, a CLOB under the budgets came back as a string, a BFILE column was refused, three nested CURSOR() levels of 1,000 rows each were refused in 2 ms with no growth of the process, and DBMS_SESSION.SLEEP(5) under a one-second deadline was refused, the next statement answering.

The script revokes PUBLIC's grants and does not grant them back, so run it on a disposable database such as the container run.sh names:

LIBREDB_LIVE_ORACLE_PORT=15217 LIBREDB_LIVE_ORACLE_PASSWORD=<SYS and SYSTEM password> bun tests/live/oracle-agent-boundary.ts

Agent language pack

Statement-language family: sql. Overlay: src/lib/agent/languages/sql/engines/oracle.ts, with its client row in src/lib/agent/languages/sql/engines/client.ts. The agent reads it through runServerPack and agentEnginePack, and the dialect through engineServerDialect.

Member	Kind	Value
catalogRead	Dialect-bound	Supplied
planEstimate	Dialect-bound	"unsupported"
profileComposition	Dialect-bound	"unsupported"
statistics	Dialect-bound	"unsupported"
catalogCapture	Dialect-bound	"unsupported"

This overlay overrides no Shared member, so every Shared member is the sql statement-language family's own. The catalog read takes ALL_TAB_COLUMNS for the column kind, ALL_CONSTRAINTS and ALL_CONS_COLUMNS for the foreign keys, ALL_INDEXES and ALL_IND_COLUMNS for the indexes, and ALL_TABLES.NUM_ROWS for the row estimates, which the last statistics gathering recorded, each named SYS.<view> so nothing in the user's schema stands in for it; every kind leaves out the schemas Oracle maintains, and a selector's schema and table are compared as string literals, exactly as the dictionary spells them, an unquoted name in upper case. Each kind was run as the admitted READ ONLY user. Investigation, database assessment and data analysis open on Oracle; assessment cannot pass there, because its verdict needs a table profile and the engine composes none (B146); query optimization stays closed, and inspect_plan and profile_table answer UNSUPPORTED_DIALECT, because the engine composes no estimating plan and no table profile (B147). A pack does not open agent mode: execution is gated by AGENT_EXECUTION_ENGINES and the execution-profile check. MCP's statement read stays closed on Oracle (MCP_STATEMENT_READ.oracle is false) until a change of its own opens it.

6. Transactions

Explicit lifecycle on a dedicated connection checked out from the pool (beginTransaction(), oracle/index.ts). Oracle starts a transaction implicitly on the first DML, so beginTransaction() just holds the connection. A 5-minute auto-rollback on an owned provider only (see §3.4). Surfaced via POST /api/db/transaction.

Method	Behaviour
beginTransaction()	Checks out a connection, marks active. Throws if one is active.
queryInTransaction(sql, params?)	Runs on that connection with autoCommit: false, through the same buildQueryResult() — so a DML statement reports its own rowsAffected here too. Throws if none active.
commitTransaction() / rollbackTransaction()	commit()/rollback(), then closes the connection. Throws if none active.
isInTransaction()	Current state.

6.1 endOpenQueryTransaction() is NOT implemented here, because the engine has no transaction to leave open

The providers that implement endOpenQueryTransaction() (types.ts) end a transaction a statement run through query() left behind on the session the next request borrows; the set is read from the type rather than listed here, because a list repeated across provider docs goes stale the moment it grows. This provider does not, and the reason is that on this path the engine has no transaction to leave open.

Two independent facts in query() (oracle/index.ts) make that true, and neither is an inference about the engine's name:

• Every statement executes with autoCommit: true, so Oracle ends the transaction that statement implicitly started, at that statement. A statement that FAILS ends it too: Oracle rolls a failed statement back to its own implicit savepoint, and the statement before it was already committed. BEGIN does not change this — in Oracle it opens a PL/SQL block, not a transaction.
• The pooled connection is closed in the finally of every call, so nothing survives the statement for a later caller to inherit. oracledb 6.10.0 in Thin mode also rolls back inside that close: ThinConnectionImpl.close() issues a rollback when _protocol.txnInProgress is set, before the session goes back to the pool.

The ask exists on this driver, unlike mysql and mssql: oracledb publishes connection.transactionInProgress, read from the server's own end-of-call TXN_IN_PROGRESS status flag. It is not usable here, because by the time a caller could ask, the connection the statement ran on has been closed. That is also why implementing the surface to answer "none" would be wrong rather than harmless: it would certify an absence on a session that no longer exists.

The interactive lifecycle above is the only transaction this provider holds open, and it is not what the surface names: it runs on a connection of its own with autoCommit: false, which query() never borrows.

---

7. Schema introspection

The dictionary views every object read draws on:

Data	Source view(s)
Tables + row estimate	ALL_TABLES (NUM_ROWS)
Columns	ALL_TAB_COLUMNS (type built from DATA_TYPE and its length, precision and scale columns, #1139, and from VECTOR_INFO where the server has it, #1209; isPrimary derived from PK set; nullable = NULLABLE = 'Y')
Primary keys	ALL_CONSTRAINTS + ALL_CONS_COLUMNS (CONSTRAINT_TYPE = 'P')
Foreign keys	ALL_CONSTRAINTS (type 'R') joined to the referenced constraint's columns
Indexes	ALL_INDEXES + ALL_IND_COLUMNS (unique = UNIQUENESS = 'UNIQUE')

The object surface (#789), and the confinement it lifts (#765)

The flat reading this replaced was bound to OWNER = <connecting user> on every one of its five reads, so the app showed exactly one schema on Oracle with no way to reach another. The object surface is five container-aware methods (listContainers, countObjects, listObjects, describeObject, describeObjects) declared in types.ts and implemented in oracle/index.ts. Both surfaces are live through Phase 1; the flat reading it replaced is deleted.

Nine kinds, and the dictionary that answers for each

Kind	role	ALL_OBJECTS.OBJECT_TYPE	Listing read	Note
table	relation	TABLE	ALL_OBJECTS, minus materialized-view containers	acceptsRowWrites: true
view	relation	VIEW	ALL_OBJECTS	not a row-write target
materialized_view	relation	MATERIALIZED VIEW	ALL_OBJECTS	not a row-write target
synonym	config	SYNONYM	ALL_OBJECTS
sequence	config	SEQUENCE	ALL_OBJECTS
package	group	PACKAGE (+ PACKAGE BODY)	ALL_OBJECTS, two rows collapsed	childKinds: ['procedure', 'function']
procedure	routine	PROCEDURE	ALL_OBJECTS
function	routine	FUNCTION	ALL_OBJECTS
trigger	attached	TRIGGER	ALL_OBJECTS, with ALL_TRIGGERS OUTER joined for the parent	attachedTo: 'table'

containerLevels is one level, schema, and on Oracle that level IS a user: a schema is not a thing created beside a user, it is what a user owns. No catalog level is declared, because a pool is opened against one service and nothing in the product can switch the pluggable database on a live connection.

No index kind, on the same line PostgreSQL is read against. Oracle's own dictionary models an index as an attribute of the table it is on: ALL_INDEXES is keyed by TABLE_OWNER and TABLE_NAME, and an index cannot exist apart from them. So an index stays in describeObject()'s output beside that object's columns rather than becoming a container-level folder.

A package's members are declared but not browsable yet. childKinds is a true statement about the engine and Phase 2 renders it, but Phase 1's provider surface is container-scoped end to end: countObjects(container) and listObjects(container, kind) both take a container, and nothing lists an object's children. A Procedures folder under a package would therefore render, never badge, and expand to nothing. listObjects(container, 'package') returns the packages themselves.

Two dictionary vocabularies for the same nine kinds

ALL_OBJECTS.OBJECT_TYPE writes the type names with spaces; the object_type argument DBMS_METADATA.GET_DDL takes writes them with underscores. They are not interchangeable, and Phase 2 (the Source tab) reads the second column, so both are written once in ORACLE_OBJECT_TYPES in oracle/index.ts rather than typed out twice:

Kind	ALL_OBJECTS.OBJECT_TYPE	DBMS_METADATA.GET_DDL
table	TABLE	TABLE
view	VIEW	VIEW
materialized_view	MATERIALIZED VIEW	MATERIALIZED_VIEW
synonym	SYNONYM	SYNONYM
sequence	SEQUENCE	SEQUENCE
package	PACKAGE	PACKAGE
(a package body)	PACKAGE BODY	PACKAGE_BODY
procedure	PROCEDURE	PROCEDURE
function	FUNCTION	FUNCTION
trigger	TRIGGER	TRIGGER

The package body is deliberately outside the kind table and kept beside it: it is not a kind, it is the second dictionary row of the one package node, and putting it in the table would add it to the counting statement and report every package twice.

listContainers() reads ALL_USERS, and that is what ends the single-schema confinement

One statement, bound to nothing. ORACLE_MAINTAINED = 'N' is the dictionary's own answer to "is this schema Oracle's", so no hand-written name denylist exists here and none should be added: measured on Oracle Database 21c XE, 29 of the 33 rows in ALL_USERS are Oracle's own, and a list written by hand would be wrong on the next release. ALL_USERS itself is not privilege filtered, so every user sees every owner.

The cost of that filter is a known limitation, and SYSTEM is the name to know. An Oracle-maintained owner other than the session user is not in the container list at all, so it cannot be browsed, and SYSTEM is one of them while genuinely holding user-visible objects: measured, the fixture's APP user counts 4 tables and 1 view in SYSTEM. MDSYS and XDB are in the same position. This is the same trade PostgreSQL's system-schema exclusion makes, for the same reason, and it is why the session's own owner is exempted: connecting as SYSTEM still browses SYSTEM.

The session's own owner is kept whatever ORACLE_MAINTAINED says (OR USERNAME = SYS_CONTEXT('USERENV','SESSION_USER')), because connecting as SYSTEM must not hide SYSTEM. That same SYS_CONTEXT call, not connection.user, is what marks Container.isSessionDefault: it is Oracle's own answer for who is connected, so it is right under external authentication and right for an owner created with a quoted lower-case name.

ORACLE_MAINTAINED costs the filter, never the list. The column arrived in Oracle Database 12.1. Thin mode refuses anything older with NJS-138, but Thick mode is an explicit opt-in for exactly those servers (§4.4), so an 11.2 instance answering ORA-00904 here is a supported configuration; listContainers() re-runs the statement without the filter. The retry is keyed on the COLUMN NAME as well as on ORA-00904, because 00904 is "invalid identifier" generally and re-running without the filter repairs nothing when the missing column was USERNAME. Both halves of that key survive a non-English NLS_LANGUAGE: Oracle translates the sentence, never the ORA- prefix and never the quoted identifier.

A container path is passed to the other three methods verbatim and is never upper-cased. The deleted flat reading upper-cased connection.user because it was reading what a person typed into a form; a container segment came out of ALL_USERS, so it is already the dictionary's own spelling, and CREATE USER "app" is legal.

countObjects() is one statement that reads no column of any table

This is what #765 is. Measured on 21c XE against the SYS owner (1,672 tables, 113,264 columns), which is the nearest thing on a laptop to the reporter's PeopleSoft instance:

	statements	rows materialised	wall clock
the deleted flat reading, on connect	5	121,462	1,490 ms
listContainers() + countObjects()	2	12	72 ms

Verified from the server side rather than from the client: after a flushed shared pool, a connect plus first paint leaves exactly three statements in V$SQL for the app user (the container read, the counting read, and node-oracledb's own BEGIN NULL; END; connection test) and zero touching ALL_TAB_COLUMNS or ALL_IND_COLUMNS. One describeObject() call then puts two of them there, which is the control that makes the zero a measurement rather than an empty log.

Two exclusions inside that one statement, and both turn a badge into a lie if they are missed.

PACKAGE BODY is not counted. A body is not a separate tree node, so counting it would double the Packages badge.

A materialized view's container table is not counted as a table. Measured: CREATE MATERIALIZED VIEW app_revenue_mv writes TWO rows into ALL_OBJECTS, the materialized view and a TABLE of the same name for its container, with GENERATED = 'N' on both, so nothing about the table row says it is not a table somebody created. Left in, an owner with 100 materialized views reports 100 tables nobody wrote, and each one opens onto the materialized view's own columns. The deleted flat reading had that defect, and it was visible on the fixture: it returned three tables for an owner holding two.

The rule needs no second dictionary view, and that is measured rather than assumed. A table and a materialized view cannot share a name in one owner, because they share Oracle's schema-object namespace: CREATE TABLE app.app_revenue_mv against the fixture's materialized view answered ORA-00955. So a same-named TABLE/MATERIALIZED VIEW pair is always the container, and one analytic window over the rows already being read finds it. Reading ALL_MVIEWS instead answers the same and costs more: against the 15,636-object SYS owner, the window form takes 10,385 consistent gets and a correlated NOT EXISTS over ALL_OBJECTS takes 18,285.

A refused read is { unavailable }, never 0, carrying Oracle's own sentence unmapped, and every declared kind is seeded at { count: 0 } before the read so a folder the owner holds none of renders a zero rather than disappearing. There is no partial outcome to report here and no retry that could produce one: ALL_OBJECTS is the single source for all nine kinds, so Oracle refuses it whole or not at all. Worth knowing when reading a small number: ALL_* views FILTER by privilege rather than refusing, so an owner you can see only part of answers a real count of the part you can see, not a refusal. Measured: APP counting SYSTEM gets 4 tables and 1 view.

A package's specification and body are one object carrying both statuses

Oracle stores them as two dictionary rows with two statuses, and a user wrote one package. listObjects(container, 'package') reads both and collapses them by name; the collapsed object's status is INVALID when EITHER half is, because "the package works" is false if either half does not compile. Neither row is privileged: a body can outlive its specification, and a specification usually exists with no body while it is being written. All four combinations are real, and the fixture ships the second one because it is the state Oracle is in most often:

Specification	Body	Rendered
VALID	VALID	nothing
VALID	INVALID	INVALID
INVALID	VALID	INVALID
VALID	absent	nothing

A successful CREATE OR REPLACE PACKAGE BODY can leave an INVALID body behind rather than failing, which is why docker/oracle-init/01-object-fixture.sql contains a package whose body deliberately does not compile. Do not "fix" it.

status is set only where ALL_OBJECTS says INVALID, for every kind. Until #789 this provider published STATUS on every object, which put a VALID badge beside every table in the tree: VALID is what nearly every row in a real schema says, so the badge carried no information and taught a reader to skip the field. The field's contract is now that its PRESENCE is the signal and Oracle's own word is the content, and absence means ordinary rather than unknown. The decision stays in this provider because only it knows which of Oracle's words is the ordinary one; a renderer that knew the string VALID would be a branch on the engine moved up a layer.

STATUS is ALL_OBJECTS's VALID/INVALID for every kind, triggers included. ALL_TRIGGERS has a STATUS column of its own that says ENABLED/DISABLED, which is a different fact about a different thing, so the trigger listing joins back to ALL_OBJECTS rather than putting two vocabularies in one field. A trigger's enabled state is a Phase 2 detail-tab fact.

Object identity: Oracle needs no disambiguator on the last path segment

DatabaseObject.path's last segment must be unique within its parent, and on PostgreSQL that forces a routine's segment to carry its argument types. Oracle does not, and this was measured rather than reasoned:

• CREATE OR REPLACE FUNCTION app.app_order_total(p_a NUMBER, p_b NUMBER) against the fixture's existing single-argument APP_ORDER_TOTAL replaced it. ALL_OBJECTS still held one row. Oracle has no routine overloading at schema level.
• A function cannot even share a name with a table: CREATE OR REPLACE FUNCTION app.app_orders answered ORA-00955. Tables, views, materialized views, sequences, private synonyms, packages, standalone procedures and functions all share ONE namespace per owner, so Oracle paths happen to be unique across those kinds as well as within each of them. The shared conformance helper only requires the second, and nothing here should be tightened to depend on the first.

ALL_ARGUMENTS does carry an OVERLOAD column, and it is the right column: a PACKAGE can hold overloaded members. Phase 1 lists packages and not their members, so nothing needs it yet, and OVERLOAD is what Phase 2 should reach for rather than a format invented here.

Triggers are the one kind in a different namespace, and that is measured too: CREATE TRIGGER app.app_orders ... ON app.app_orders succeeds beside the table of that name. It costs nothing, because a trigger's path has three segments where a table's has two.

Where a trigger hangs, including the cases that are not a table

attachedTo: 'table', so a trigger is [owner, table, trigger] and not [owner, trigger]. Oracle would in fact allow the shorter form, since a trigger name is unique within its owner rather than within its table, but the nesting is what the tree renders and it is the same rule every engine in #789 follows. Four cases, all measured:

• Base table in another owner. ALL_TRIGGERS separates OWNER from TABLE_OWNER, and a trigger APP owns on REPORTING's table is real. It is listed in APP's container, because APP is what owns it, and its path is ['APP', 'REPORT_DAILY', 'REPORT_DAILY_TRG']. The middle segment then names a table that APP's own Tables folder does not list. That is the honest answer of the three available: putting it in REPORTING's container would list an object that owner does not own, and joining the owner into the segment (REPORTING.REPORT_DAILY) would put a qualified name back into a path, which is the exact defect DatabaseObject.path exists to prevent.
• Base object is a view. An INSTEAD OF trigger gives BASE_OBJECT_TYPE = 'VIEW' with the view in TABLE_NAME, so it nests under the view. The attachedTo declaration names one kind and the path names the object, which is what addresses it.
• The base table is invisible to this user. ALL_TRIGGERS is outer joined, so there is no row to read a parent from and the trigger lists at two segments, exactly like the case below. The two are deliberately indistinguishable: what the tree needs is an address, and the object is counted either way.
• No base object at all. A SCHEMA or DATABASE trigger (AFTER LOGON ON SCHEMA) leaves TABLE_NAME NULL. It hangs off the container itself, so its path is two segments, ['APP', 'APP_LOGON_TRG']. Filtering it out of the listing instead would have made the folder disagree with the badge, since the count comes from ALL_OBJECTS, which counts every trigger. describeObject() accepts both depths for an attached kind for the same reason.

The count and the listing read ONE catalog, and for triggers that took fixing. Standing ruling 5f requires the listing to contain exactly what the count counted, and the count reads ALL_OBJECTS. The two dictionary views do not expose the same population, which is measured rather than argued: a user holding nothing but CREATE SESSION and one SELECT grant sees 83 rows in ALL_TRIGGERS and 0 in ALL_OBJECTS, because ALL_OBJECTS answers by privilege on the object while ALL_TRIGGERS also answers by accessibility of the BASE TABLE. Driving the listing from ALL_TRIGGERS with an inner join therefore computes an INTERSECTION, which can only ever be a SUBSET of what the badge counted: the badge can outrun the folder, never the reverse. On 21c XE no reader could be constructed where that subset was strictly smaller, so the divergence is structural rather than exhibited, and the outer join removes the question at no cost. Verified live on the fixture: 4 counted, 4 listed.

The shared conformance helper still does not assert list.length === count, because a count and a listing remain two reads at two instants against a live engine.

describeObject() reads four statements, each bound to one owner and one object

Columns from ALL_TAB_COLUMNS, the primary key and the foreign keys from ALL_CONSTRAINTS + ALL_CONS_COLUMNS, indexes from ALL_INDEXES + ALL_IND_COLUMNS. They run in sequence on one pooled connection, because a single oracledb connection serialises its statements anyway. ALL_TAB_COLUMNS answers for a view and for a materialized view's container table as well as for a table, so none of the three relation kinds needs a dictionary of its own.

All four bind [path[0], path[path.length - 1]]. The object's own name is the LAST segment and never path[1]: the two are the same string on a one-level engine like this one and different on the five two-level engines that copy this file, where path[1] is a container segment and the read would narrow to nothing.

The KIND decides whether they run at all: only the three kinds whose role is relation have columns, so a package, a routine, a synonym, a sequence and a trigger answer three empty arrays without a round trip. That is a true fact about those kinds rather than a failed read. Deciding it from the NAME instead would be correct only by coincidence, and Oracle is where the coincidence breaks: these statements key the last path segment against TABLE_NAME, and a trigger named APP_ORDERS on table APP_CUSTOMERS is legal, so it would have been handed APP_ORDERS's columns as if they were its own.

Those same three kinds, table, view and materialized_view, are the ones that declare hasColumns, which is what gives an object row a twisty in the object tree; a synonym, a sequence, a package, a procedure, a function and a trigger declare nothing and are leaves, because describeObject() answers them three empty arrays. sequence is why the declaration is per provider rather than derived from the role or the kind id: this provider gates on the role, so an Oracle sequence has no columns at all, while PostgreSQL's sequence answers last_value, log_cnt and is_called under the same kind id. An object dropped between the listing and the expand is not an error here: the four reads answer no row and describeObject() returns three empty arrays, which the tree reports on the open row as No columns reported rather than as a refusal.

Two of the four statements differ from the deleted flat reading's counterparts on purpose:

• The foreign-key read pairs columns with rcc.POSITION = acc.POSITION. Without it a two-column foreign key joins every referencing column to every referenced one and reports four pairs for two. The flat foreign-key statement had that defect and is gone with it.
• The index read is keyed by ai.TABLE_OWNER, not ai.OWNER. An index one user owns on another user's table belongs to the table when a person is looking at the table, and an index this owner holds on somebody else's table does not.

ForeignKeySchema.referencedTable is one string, so it is spelled bare within the same owner and OWNER.TABLE outside it. Qualifying the cross-owner case is not cosmetic, since a bare name there addresses a table in the wrong schema. Turning that string into a path is Phase 2's.

describeObjects() describes a whole folder in five statements (#789)

describeObjects(container, kind, limit?) answers columns, indexes and foreign keys for EVERY object of one kind in one owner, in FIVE round trips whatever the folder holds, against four per object for the single read. On this engine that is also the other half of #765: the same four dictionary views scoped to an owner alone answered 910,000 column rows on the reporter's instance.

The timing is the one in this family that does not simply favour the bulk read, and it is measured rather than assumed. On Oracle Database 21c XE against a 202-table owner, three consecutive runs in one connection:

Run	describeObjects()	202 × describeObject()
first	1025 ms	447 ms
second	53 ms	206 ms
third	50 ms	220 ms

The first call pays a hard parse of five large statements the shared pool has never seen; every call after it is about four times faster than the N+1. That is why all five carry BINDS rather than interpolated values: a statement whose text changes per owner would hard-parse every time and never reach the second row of that table.

The five decisions this engine had to make for itself, each measured rather than reasoned:

Which catalog. The same four ALL_* dictionary views describeObject() reads, and not USER_*, which answers only for the connecting user and is what #765 was about. The five statements share one described CTE and the four detail reads join it by NAME, which is the only key these views carry: none of ALL_TAB_COLUMNS, ALL_CONSTRAINTS, ALL_CONS_COLUMNS or ALL_INDEXES publishes an OBJECT_ID. Joining on a name is safe here for a measured reason: tables, views, materialized views, synonyms, sequences, packages, procedures and functions share ONE namespace inside an owner, and a second CREATE of any of them answers ORA-00955.

Which kinds have no columns. Everything but table, view and materialized_view: a package, a procedure, a function, a synonym, a sequence and a trigger answer { details: [] } with no round trip. A MATERIALIZED VIEW is NOT one of them, measured on 21c XE: ALL_TAB_COLUMNS answers for one, because it has a container table underneath, and that container is also why the table target has to drop it.

What bounds the read on the wire. ORDER BY o.OBJECT_NAME FETCH FIRST :3 ROWS ONLY, bound at limit + 1, and not ROWNUM: ROWNUM is assigned BEFORE the sort, so WHERE ROWNUM <= n ORDER BY OBJECT_NAME keeps an arbitrary set and then orders it, while FETCH FIRST cuts the ordered set. The extra object is dropped and truncated carries the CALLER's limit; an unbounded call runs a statement with no row bound and can never report truncation. Nothing here caps a column list.

What orders the cut, and under whose collation. The target's ORDER BY OBJECT_NAME, which runs under the database's own NLS_SORT and is therefore the SERVER's order rather than ours. It decides WHICH objects a bound keeps and nothing else: the answer is re-sorted by path in code, one rule everywhere, because callers join the two readings on path rather than on position.

Mixed path depth (ruling 5f). Not in this engine's relation set. table, view and materialized_view are all addressed [owner, name]; trigger is the kind that sits at two depths here - a SCHEMA or DATABASE trigger has no base object - and it has no columns.

A bind trap worth carrying forward. Each detail statement names the owner a SECOND time, for its own join, and that reference takes the next free placeholder (:3 unbounded, :4 bounded) with the owner repeated in the bind array. A repeated :1 looks right and is not: measured against a live 21c XE, oracledb maps a bind ARRAY by the order the placeholders APPEAR rather than by the number they carry, so a statement naming :1 twice answers NJS-098: 3 bind placeholders were used in the SQL statement but 2 bind values were provided. The unit suite could not see that, because its fake dispatches on statement text and never counted the binds, so the arity is asserted from the arguments now.

An empty owner costs ONE round trip rather than five.

Rebuilding the 202-table owner the timings above were measured on, as APP on XEPDB1, so the numbers are re-runnable rather than asserted:

BEGIN
  FOR i IN 0 .. 199 LOOP
    EXECUTE IMMEDIATE 'CREATE TABLE bulk_t' || LPAD(i,3,'0') || ' (id NUMBER PRIMARY KEY, a VARCHAR2(20), b NUMBER)';
    EXECUTE IMMEDIATE 'CREATE INDEX bulk_ix' || LPAD(i,3,'0') || ' ON bulk_t' || LPAD(i,3,'0') || ' (a)';
  END LOOP;
END;
/

A column's type is its declaration, built from the dictionary (#1139)

ALL_TAB_COLUMNS.DATA_TYPE has no length, precision or scale: a VARCHAR2(20) reads VARCHAR2 and a NUMBER(12,2) reads NUMBER. The schema-diff migration generator writes ColumnSchema.type into CREATE TABLE and ALTER TABLE ... ADD (...) as it is, so a type read from DATA_TYPE alone breaks the migration. A bare VARCHAR2, NVARCHAR2 or RAW is refused with ORA-00906. A bare CHAR or NUMBER is worse, because Oracle accepts it and creates a different column: CHAR(1), or a NUMBER with no precision or scale.

Both column reads therefore also select DATA_LENGTH, DATA_PRECISION, DATA_SCALE, CHAR_LENGTH and CHAR_USED, and declaredType() in oracle/index.ts builds the declaration with this rule:

DATA_TYPE	Declaration
VARCHAR2, CHAR	<DATA_TYPE>(<CHAR_LENGTH> BYTE) when CHAR_USED is B, <DATA_TYPE>(<CHAR_LENGTH> CHAR) when it is C
NVARCHAR2, NCHAR	<DATA_TYPE>(<CHAR_LENGTH>)
RAW	RAW(<DATA_LENGTH>)
NUMBER	NUMBER when precision and scale are both null, NUMBER(*,<s>) when only precision is null, NUMBER(<p>) when scale is 0, otherwise NUMBER(<p>,<s>)
FLOAT	FLOAT(<DATA_PRECISION>)
UROWID	UROWID(<DATA_LENGTH>) (#1209)
VECTOR	VECTOR_INFO as it is, where the server has that column (#1209)
anything else	DATA_TYPE as it is

ColumnSchema.type is that declaration, and ColumnSchema.baseType is DATA_TYPE. baseType is OMITTED where the two are equal, which is the rule the MySQL and SQL Server providers follow too. type is what a reader sees and what DDL writes. baseType is what a reader that decides on a type family matches against, and the code generator and the test data generator already read baseType ?? type.

Measured on Oracle Database 21c Express Edition over APP.COLUMN_TYPES, which the fixture creates:

Declared	DATA_TYPE	type	baseType
VARCHAR2(20), VARCHAR2(20 BYTE)	VARCHAR2	VARCHAR2(20 BYTE)	VARCHAR2
VARCHAR2(20 CHAR)	VARCHAR2	VARCHAR2(20 CHAR)	VARCHAR2
NVARCHAR2(10)	NVARCHAR2	NVARCHAR2(10)	NVARCHAR2
CHAR(2)	CHAR	CHAR(2 BYTE)	CHAR
CHAR(3 CHAR)	CHAR	CHAR(3 CHAR)	CHAR
NCHAR(3)	NCHAR	NCHAR(3)	NCHAR
RAW(16)	RAW	RAW(16)	RAW
NUMBER	NUMBER	NUMBER	absent
NUMBER(10)	NUMBER	NUMBER(10)	NUMBER
NUMBER(12,2)	NUMBER	NUMBER(12,2)	NUMBER
NUMBER(*,2)	NUMBER	NUMBER(*,2)	NUMBER
NUMBER(5,-2)	NUMBER	NUMBER(5,-2)	NUMBER
INTEGER	NUMBER	NUMBER(*,0)	NUMBER
FLOAT(10)	FLOAT	FLOAT(10)	FLOAT
FLOAT	FLOAT	FLOAT(126)	FLOAT
TIMESTAMP(3)	TIMESTAMP(3)	TIMESTAMP(3)	absent
TIMESTAMP(6) WITH TIME ZONE	the same	the same	absent
INTERVAL DAY(3) TO SECOND(2)	the same	the same	absent
DATE, CLOB, BLOB, BINARY_DOUBLE	the same	the same	absent
UROWID(100)	UROWID	UROWID(100)	UROWID
UROWID	UROWID	UROWID(4000)	UROWID

A table created from the built declarations has ALL_TAB_COLUMNS rows identical to the fixture's, 25 of 25, on 21c XE and on Oracle AI Database 26ai Free 23.26.3. The TIMESTAMP and INTERVAL types need no rule, because DATA_TYPE already carries their precision.

UROWID and VECTOR (#1209)

Both types carry a size that DATA_TYPE leaves out, and a bare DATA_TYPE is accepted for both. So before #1209 a migration created a different column without an error, the same class of defect as a CHAR(2) read back as CHAR. A bare UROWID is created with DATA_LENGTH 4000, so UROWID(100) needs its DATA_LENGTH. A bare UROWID reads as UROWID(4000), and that declaration creates the same column.

A vector's dimension count and format are in no length, precision or scale column. CHAR_LENGTH holds the dimension count, and nothing but VECTOR_INFO holds the format. So the declaration of a vector column is VECTOR_INFO as it is. Measured on Oracle AI Database 26ai Free 23.26.3. The fixture cannot hold these columns because the compose image is 21c, so the live guard creates them:

Declared	CHAR_LENGTH	VECTOR_INFO, which is type	Bare VECTOR creates
VECTOR	0	VECTOR(*,*,DENSE)	the same
VECTOR(3, FLOAT32)	3	VECTOR(3,FLOAT32,DENSE)	VECTOR(*,*,DENSE)
VECTOR(*, FLOAT64)	0	VECTOR(*,FLOAT64,DENSE)	VECTOR(*,*,DENSE)
VECTOR(16, BINARY)	16	VECTOR(16,BINARY,DENSE)	VECTOR(*,*,DENSE)
VECTOR(100, FLOAT32, SPARSE)	100	VECTOR(100,FLOAT32,SPARSE)	VECTOR(*,*,DENSE)

baseType is VECTOR for each of them. Each VECTOR_INFO spelling, replayed on the same server, creates a column with the same VECTOR_INFO, CHAR_LENGTH and DATA_LENGTH. VECTOR(5, INT8) and VECTOR(3) read as VECTOR(5,INT8,DENSE) and VECTOR(3,*,DENSE), and they replay the same too.

VECTOR_INFO is a VARCHAR2 column on 26ai, and it does not exist on 21c XE: a read that names it is ORA-00904: "VECTOR_INFO": invalid identifier, or "C"."VECTOR_INFO" in the bulk read. Both column reads therefore name it only where the server has it. readColumns() asks for it first, and on an ORA-00904 that names VECTOR_INFO it reads the columns again without it. This is the repair listContainers() makes for ALL_USERS.ORACLE_MAINTAINED. The provider instance also remembers the refusal, so a server without the column pays for one refused read for each provider instance, not one for each describe. Nothing resets that memory, a reconnect of the same instance included. So a server upgraded to 23ai under an open provider instance keeps the read without VECTOR_INFO, and its VECTOR columns read as DATA_TYPE until the instance is recreated. An ORA-00904 that names another column is raised, because reading without VECTOR_INFO repairs nothing there. A VECTOR row without VECTOR_INFO keeps DATA_TYPE, which is what the provider reported before #1209.

BYTE is written even though DBMS_METADATA.GET_DDL leaves it out. Under NLS_LENGTH_SEMANTICS = CHAR, a bare VARCHAR2(20) is created with CHAR_USED = C, measured, so a migration without BYTE would change the column on such a target. A computed view column is safe with this rule: COUNT(*), 1/3 and N * 2 report a null precision and scale and read as NUMBER, and SUBSTR(A, 1, 3) reads as VARCHAR2(12 BYTE).

QueryResult.columnTypes does NOT change. It comes from the driver (§5.4), and there a computed result column reports precision 0 or scale -127, so it stays DATA_TYPE's spelling on purpose.

tests/live/oracle-column-type.ts (§12.4) holds this against a real server. It replays the generated CREATE TABLE under NLS_LENGTH_SEMANTICS = CHAR, requires an accept, and compares the new table's ALL_TAB_COLUMNS rows with the fixture's. It then replays the DATA_TYPE-only definition that this replaces and requires ORA-00906.

What the server does with a MODIFY (#1240)

The migration generator writes a modified column as one ALTER TABLE <t> MODIFY (<col> <type><default><nullability>). It writes the nullability clause, NULL or NOT NULL, only when the nullability changes. Oracle refuses a nullability the column already has, measured on 21c XE and 26ai Free 23.26.3:

Statement	Result
MODIFY ("S" VARCHAR2(50 BYTE) NULL), S nullable	ORA-01451: column to be modified to NULL cannot be modified to NULL, and S keeps its type
MODIFY ("N" VARCHAR2(50 BYTE) NOT NULL), N NOT NULL	ORA-01442: column to be modified to NOT NULL is already NOT NULL, and N keeps its type
MODIFY ("S" VARCHAR2(50 BYTE)), S nullable	accepted: VARCHAR2(50 BYTE), nullable
MODIFY ("U" UROWID(4000)), U a nullable UROWID(100)	accepted: DATA_LENGTH 4000

Before #1240 the clause was always there, so a type or default change on a column whose nullability did not change was refused, and the migration changed nothing. The live guard (§12.4) replays a type change on a nullable and on a NOT NULL column, and a nullability change in each direction, and requires the target's ALL_TAB_COLUMNS rows, NULLABLE included.

One consequence for the schema diff, measured and accepted rather than repaired. diffColumns() compares type, and a snapshot saved before #1139 stored DATA_TYPE. So every column whose declaration now differs from DATA_TYPE reports one false Type changed: VARCHAR2 → VARCHAR2(20 BYTE) and one MODIFY. Its nullability did not change, so the MODIFY has no nullability clause. What the server does with it depends on the side of the diff the old snapshot is on. Measured on an empty table on 21c XE and 26ai Free 23.26.3:

• The old snapshot is the source. The MODIFY writes the current declaration, for example MODIFY ("V" VARCHAR2(20 BYTE)). The server accepts it and the column does not change. This holds for VARCHAR2, CHAR, NUMBER, FLOAT, RAW and UROWID.
• The old snapshot is the target. The MODIFY writes the bare DATA_TYPE. A bare VARCHAR2 or RAW is refused with ORA-00906. A bare CHAR, NUMBER, FLOAT or UROWID is accepted and changes the column: CHAR(2) becomes CHAR(1), NUMBER(12,2) loses its precision and scale, FLOAT(10) becomes FLOAT(126), and UROWID(100) becomes UROWID(4000).

A column whose declaration is DATA_TYPE, such as DATE, CLOB or a bare NUMBER, compares equal and reports nothing, and a new snapshot clears the rest. Comparing baseType instead would also hide a real VARCHAR2(20) → VARCHAR2(40), which is the change this section exists to carry.

Object source (#789)

readObjectSource(path, kind, limit?) answers ONE object's definition text as a document of named parts, each part either readable text or Oracle's own reason there is none. All NINE declared kinds have one, so this provider has no "declares nothing" list.

Kind	Statement	What the text IS	Parts
table	DBMS_METADATA.GET_DDL('TABLE', :name, :owner)	complete, regenerated	1, definition
view	GET_DDL('VIEW', ...)	complete, regenerated	1, definition
materialized_view	GET_DDL('MATERIALIZED_VIEW', ...)	complete, regenerated	1, definition
synonym	GET_DDL('SYNONYM', ...)	complete, regenerated	1, definition
sequence	GET_DDL('SEQUENCE', ...)	complete, regenerated	1, definition
package	GET_DDL('PACKAGE_SPEC', ...) then GET_DDL('PACKAGE_BODY', ...)	complete, regenerated	1 or 2, spec then body
procedure	GET_DDL('PROCEDURE', ...)	complete, regenerated	1, definition
function	GET_DDL('FUNCTION', ...)	complete, regenerated	1, definition
trigger	GET_DDL('TRIGGER', ...)	complete, regenerated	1, definition

The metadata type comes from the translation table the provider already ships for the count and the listings, so the two vocabularies cannot drift: ALL_OBJECTS.OBJECT_TYPE writes them with spaces (MATERIALIZED VIEW, PACKAGE BODY) and GET_DDL takes them with underscores (MATERIALIZED_VIEW, PACKAGE_BODY).

That shared table is what makes the two vocabularies drift-proof, and it is also what makes ONE edit able to break nine reads at once, so the nine spellings above are pinned in tests/integration/db/oracle-provider.test.ts as a LITERAL list captured from the live server rather than read back out of the provider's own table. A wrong spelling is not a soft failure: GET_DDL answers an unknown object_type with ORA-31600: invalid input value BOGUS_TYPE for parameter OBJECT_TYPE in function GET_DDL, so the Source tab for every object of that kind fails.

form is complete and origin is regenerated, on every kind, and both are measurements. GET_DDL answers a statement that runs as given, never a body or a bare SELECT. It is not the author's bytes either: the fixture's CREATE OR REPLACE FUNCTION app.app_order_total(p_id NUMBER) comes back as CREATE OR REPLACE EDITIONABLE FUNCTION "APP"."APP_ORDER_TOTAL" (p_id NUMBER), with a keyword the author never typed and a qualification the author never wrote, so a reader is never shown a reconstruction as an original.

Nothing is interpolated and no identifier escaper is involved. The metadata type, the object name and the owner are all three BINDS (:1, :2, :3), so a caller-supplied name never reaches statement text on this path. The owner is the segment the DECLARATION assigns to the schema container level and the object name is the LAST path segment; neither is read by a literal index.

The Monaco language id is sql, and that is a compromise this provider states rather than hides. MEASURED on the installed monaco-editor 0.57.0: plsql is not among the 89 language ids the bundle registers, and an unregistered id degrades to plain text SILENTLY, with no throw and nothing observable. A PL/SQL body therefore renders under the SQL grammar, which highlights the DML and misses IS, BEGIN, EXCEPTION and the block structure.

A package is TWO parts, and a package with no body is ONE

GET_DDL('PACKAGE', ...) retrieves the specification AND the body together in one CLOB, and this provider deliberately does not use it. Two round trips are paid instead, for three reasons that one concatenated CLOB cannot express:

• a body may be ABSENT, and a concatenation cannot say so. APP_SPEC_ONLY_PKG in the fixture is that state, and it emits ONE part. The missing body is neither a refusal nor an error: nobody ever wrote it, so there is nothing to refuse.
• a body may be WRAPPED while the specification is not, and one CLOB gives the reader neither honestly. APP_WRAPPED_PKG in the fixture is that state: a readable spec part beside a refused body part.
• the two halves carry independent STATUS values, which APP_BROKEN_PKG exhibits.

A kind declaring source and no sourceLanguage RAISES

readObjectSource refuses with Oracle declares readable source for the kind "<kind>" and no sourceLanguage to render it with, before a connection is taken from the pool. There is no fallback to a literal sql: an unregistered or absent Monaco id degrades to plain text with no throw and nothing observable, so a fallback would hide a deleted declaration behind a Source tab that had quietly stopped highlighting. All nine declared languages are pinned by the isolated census (tests/isolated/object-source-declarations.test.ts), so the only way to reach this arm is a declaration somebody removed.

ORA-31603 says "not found in schema" for an object you merely cannot read

This is the single most important thing to know about this surface, because since #765 the tree lists EVERY owner, so opening another schema's object is the ordinary path rather than an edge case.

MEASURED on Oracle XE 21.3.0.0.0 as APP, which holds SELECT on REPORTING.REPORT_DAILY and no catalog role:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','REPORT_DAILY','REPORTING') FROM DUAL;
ORA-31603: object "REPORT_DAILY" of type TABLE not found in schema "REPORTING"

The identical error, word for word, comes back for REPORTING.NO_SUCH_TABLE, which really does not exist. Oracle's own message cannot tell the two apart, and shipping it unqualified tells a user their objects are gone.

The code is read off errorNum, not off the message. node-oracledb carries the Oracle error number as a numeric errorNum on the error it rejects with, in both modes: thin assigns it in lib/thin/protocol/protocol.js (err.errorNum = message.errorInfo.num) and every prebuilt thick addon under build/Release exports the same property name (strings oracledb-6.10.0-linux-x64.node | grep -x errorNum), both checked against oracledb 6.10.0. Scanning the MESSAGE for ORA-31603 would make a failure that merely quotes that text, a wrapped error or a logged one, look like a missing object, and the second question would then be asked about the wrong fact. An error carrying a different errorNum raises even when its text quotes this code. An error carrying no numeric errorNum at all, which is what a rejection composed outside the driver looks like, still falls to the text scan, and that arm is asked second.

So on ORA-31603 the provider asks a SECOND question:

SELECT 1 FROM ALL_OBJECTS WHERE OWNER = :1 AND OBJECT_NAME = :2 AND OBJECT_TYPE = :3

• a row EXISTS: the object is there and the READ was refused. The part carries an unavailable holding Oracle's own ORA-31603 sentence whole and first, then the fact that settles which of the two it is.
• no row: the object is genuinely absent to this session, and the read RAISES a QueryError naming the object. It never answers a document and never answers a refusal.

ALL_OBJECTS by name, and never DBA_OBJECTS. ALL_OBJECTS is privilege-filtered, so it answers "can THIS caller see it", which is the question being asked. DBA_OBJECTS would answer "does it exist anywhere", turning the disambiguation into a cross-schema existence oracle over objects the caller has no grant on at all, and it needs a catalog role most callers do not hold, so it would also fail for the very sessions this path exists to serve.

The type is bound in the DICTIONARY spelling, which is the column ALL_OBJECTS publishes. Binding the metadata spelling would answer NO ROW for every object of the three kinds whose two spellings differ, turning every privilege refusal on a materialized view or a package body into a false claim of absence.

What the refusal drops, and why that is a selection rather than a rewrite. node-oracledb composes that error as ten lines: the ORA-31603 line, then nine ORA-06512: at "SYS.DBMS_METADATA", line 6781 frames, then a Help: link. The frames are a PL/SQL backtrace of line numbers inside Oracle's own package and say nothing about the object, and the refusal pane renders the engine's sentence in full, so leaving them in would bury the disambiguation under nine lines of SYS internals. Not one word of Oracle's is changed, reordered or paraphrased; the ORA-06512 frames are dropped and everything else, the help link included, is kept. Only the ORA-31603 path is filtered: every other failure raises through mapDatabaseError with its message untouched.

Wrapped PL/SQL: the detection rule, and why it is a POSITION

A unit created through DBMS_DDL.CREATE_WRAPPED is stored as the encoder's output, and GET_DDL hands that output over with no error at all. A provider that passed it to an editor would show something that is not a definition and could not say so.

THE RULE, established by probe 7 on Oracle XE 21.3.0.0.0 and documented nowhere Oracle publishes: a unit's definition text is WRAPPED if and only if the token immediately following the CLOSING DOUBLE QUOTE of its quoted name in the DBMS_METADATA header is the bare keyword wrapped, case-insensitive, AND the next physical line is the wrap format marker, matching ^[a-z][0-9]{6}$ (a000000 on 21.3.0.0.0). The pattern is wider than the one literal on purpose: the marker names the encoder's format version, and pinning a000000 would report a later Oracle's wrapped unit as plain text.

  CREATE OR REPLACE EDITIONABLE FUNCTION "APP"."APP_WRAPPED_MULTI" wrapped
a000000
369
...

The rule was mutation-tested rather than asserted. The fixture commits four plain, VALID, COMPILING functions, three of them built to defeat the naive TEXTUAL rule and the fourth the control for the header position itself, and none of the four may be tidied away:

Fixture unit	Built to defeat	First source line
APP_FIRST_LINE_WRAPPED	"the line ends with wrapped"	... RETURN NUMBER IS -- wrapped
APP_SECOND_LINE_MARKER	"line 2 is the format marker"	... RETURN NUMBER IS /*, then a000000
APP_CONJ_DEFEATER	both halves at once	... RETURN NUMBER IS /* wrapped, then a000000
APP_ZERO_ARG	the control: the closest PLAIN shape to a wrapped header	... "APP_ZERO_ARG" RETURN NUMBER IS

All four fail the predicate, because GET_DDL writes the object name inside double quotes and what follows it is decided by the PARSER: a plain unit admits only (, a RETURN clause, IS or AS in that position. If a future Oracle ever admits wrapped there for a plain unit, the assertion over these units fails by name.

The fifth unit attacks the OTHER conjunct, and it is the one that does not compile. All four above defeat the keyword half of the rule; until APP_MARKERLESS_HEADER was added, the MARKER half was asserted by nothing at all, because a real wrapped unit always carries its marker. MEASURED on Oracle XE 21.3.0.0.0, and it is not what the parser rule above would lead you to expect: wrapped after a function name is ACCEPTED, because it is the wrap keyword.

CREATE OR REPLACE FUNCTION app.app_markerless_header wrapped
BEGIN
  RETURN 1;
END;

That leaves one row in USER_ERRORS, PLS-00753: malformed or corrupted wrapped unit, the object is created FUNCTION / INVALID, and DBMS_METADATA.GET_DDL answers its source verbatim anyway: the header carries the keyword and the next line is BEGIN. So the predicate must answer NOT WRAPPED for it, and a reader gets a readable text rather than a manufactured refusal. Deleting the marker conjunct from the rule fails exactly the test over this unit.

A second, independent signal exists and is deliberately not used. ALL_SOURCE holds a whole wrapped unit in ONE row with embedded newlines, where a plain unit is one row per line, and that shape is not forgeable from source text at all. It is not used because it costs a second round trip on every PL/SQL read and answers nothing for the five kinds that are not PL/SQL. It is written down here so a future defect in the header predicate has a measured alternative rather than a research problem.

EXECUTE ON DBMS_DDL is already granted to PUBLIC on gvenzl/oracle-xe, so the fixture needs no grant to create a wrapped unit.

Other refusals, and what raises instead

• an EMPTY or whitespace-only definition is a REFUSAL carrying that fact, never a readable part. An empty definition is not a definition, and an empty editor over one is the hazard this whole surface exists to remove.
• a CLOB that arrives as something other than a string RAISES. GET_DDL answers a CLOB, oracledb answers a CLOB with a Lob stream by default, and serialising one throws TypeError: Converting circular structure to JSON. The fix, lobFetchTypeHandler, is a PER-CALL option and the object surface's ordinary reader passes none, so this read has its own execute; a value that still comes back as a stream is a defect of ours and is reported as one rather than coerced into an editor.
• a path whose shape this kind cannot take is refused by the same rule describeObject uses, which the two methods share so they cannot come to disagree about one engine.
• a kind the provider declares no source for is refused by name.

Reproducing every one of these

The whole fixture is applied by the mount, so no command below creates anything:

The container name and the host port below are a PRIVATE pair, not the defaults, and that is deliberate: a machine already running an Oracle on 1521, or a container already called oracle, is the ordinary case rather than the exception, and the recipe must not collide with one. Every measurement in this section was taken on exactly this pair. Remove only what you created.

docker run -d --name src-task07-oracle -e ORACLE_PASSWORD='Password123!' -p 15217:1521 \
  -v "$PWD/docker/oracle-init:/container-entrypoint-initdb.d:ro" gvenzl/oracle-xe
# wait for "DATABASE IS READY TO USE!" in `docker logs src-task07-oracle`, about four minutes
docker exec -i src-task07-oracle sqlplus -s app/'Password123!'@localhost:1521/XEPDB1
# 15217 is the HOST port, for a connection from the Studio UI; the exec above is already
# inside the container, where the listener is on 1521.
docker rm -f src-task07-oracle   # when you are done, and nothing else

SET LONG 200000 PAGESIZE 0 LINESIZE 32767 LONGCHUNKSIZE 200000
SELECT DBMS_METADATA.GET_DDL('FUNCTION','APP_WRAPPED_MULTI','APP') FROM DUAL;   -- wrapped
SELECT DBMS_METADATA.GET_DDL('FUNCTION','APP_CONJ_DEFEATER','APP') FROM DUAL;   -- plain, and imitates it
SELECT DBMS_METADATA.GET_DDL('FUNCTION','APP_MARKERLESS_HEADER','APP') FROM DUAL; -- keyword, no marker
SELECT LINE, POSITION, TEXT FROM ALL_ERRORS WHERE NAME = 'APP_MARKERLESS_HEADER'; -- PLS-00753
SELECT DBMS_METADATA.GET_DDL('BOGUS_TYPE','APP_ORDERS','APP') FROM DUAL;        -- ORA-31600
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED VIEW','APP_REVENUE_MV','APP') FROM DUAL; -- ORA-31600 again
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','APP_WRAPPED_PKG','APP') FROM DUAL; -- wrapped body
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','APP_SPEC_ONLY_PKG','APP') FROM DUAL; -- ORA-31603, absent
SELECT DBMS_METADATA.GET_DDL('TABLE','REPORT_DAILY','REPORTING') FROM DUAL;     -- ORA-31603, refused
SELECT DBMS_METADATA.GET_DDL('TABLE','NO_SUCH_TABLE','REPORTING') FROM DUAL;    -- ORA-31603, absent
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE OWNER = 'REPORTING';

The last statement is the second question by hand: it answers a row for REPORT_DAILY and none for NO_SUCH_TABLE, which is the entire difference between a refusal and a raise. The two ORA-31600 lines are the other vocabulary's failure mode: the dictionary spelling MATERIALIZED VIEW is as invalid an object_type as BOGUS_TYPE is, which is why the nine metadata spellings are pinned in the suite rather than left to a table two consumers read differently.

The fixture

docker/oracle-init/01-object-fixture.sql, mounted at /container-entrypoint-initdb.d by the oracle service in database-compose.yml. It creates two owners so the lifted confinement is observable, one object of every declared kind, the three trigger cases above, the package whose body does not compile, and the wrapped-PL/SQL block with the four plain units that imitate it and the fifth, INVALID one that carries the keyword without the marker (Object source). APP.COLUMN_TYPES has one column per row of the column type rule (§7). Connect as APP / Password123! on service XEPDB1.

It also seeds ROWS, two in APP.APP_CUSTOMERS and two in REPORTING.REPORT_DAILY, and those are part of the fixture rather than decoration: the terminator measurement in §3.2a reads -> rows, and a table with none answers the accepted statement and the rejected one alike. One is inside the connecting user's own schema and one is outside it, which is the pair the generated statement's qualification needs.

Three things about it are load-bearing:

• The / statement terminators are a SQLPlus convention and are correct in a mounted init script, which SQLPlus runs. They must never be sent through node-oracledb, which takes one statement per execute() and answers ORA-00911 for a trailing terminator.
• GRANT CREATE TABLE TO app looks redundant beside RESOURCE, and is not: creating a materialized view in another user's schema checks that the owner holds CREATE TABLE directly, and a privilege held through a role does not satisfy that check. Without the line, CREATE MATERIALIZED VIEW answered ORA-01031 while SYS held CREATE ANY MATERIALIZED VIEW.
• The scripts run once and only on a fresh data directory, so an existing container has to be recreated before an edit takes effect.

---

Object edit (#789)

This engine is a REFUSAL of the strongest class in the fleet: the FAILURE destroys the object. A CREATE OR REPLACE that fails to COMPILE overwrites the stored source with the broken text, marks the object INVALID, and THE DRIVER DOES NOT THROW, measured on FUNCTION, PROCEDURE, PACKAGE BODY, PACKAGE SPEC, TRIGGER and a FORCE VIEW. For a TRIGGER that is a table outage rather than one broken object: every INSERT then answers ORA-04098 while USER_TRIGGERS.STATUS still reads ENABLED. Detecting that success-that-is-not-a-success means reading the driver's warning, and it is unwritable here until an ambient declaration grows: db-drivers.d.ts declares oracledb's Result with no warning member at all. No kind here declares acceptsSourceEdits, and tests/isolated/object-edit-declarations.test.ts is what holds that absence and this section together.

8. Monitoring & health

All from V$/USER_* views; getMonitoringData() (inherited) fans them out in parallel. Each V$ sub-query is independently privilege-guarded; the dictionary-only table and index statistics are not (§3.7).

Method	Primary source	Notes / degradation
getHealth()	V$SESSION, USER_SEGMENTS, V$SYSSTAT, V$SQL	each block guarded → absent/N/A/[] if no privilege; activeConnections is omitted, never 0 (§7.2); cacheHitRatio is N/A, never 0% (§7.1)
getOverview()	V$VERSION, V$INSTANCE, V$SESSION, V$PARAMETER, USER_SEGMENTS, USER_TABLES/USER_INDEXES	each guarded; activeConnections is omitted, never 0 (§7.2), while maxConnections stays 0 because 0 there means "no limit published"; databaseSizeBytes is omitted and databaseSize stays N/A, never a 0, when USER_SEGMENTS does not answer (§7.3)
getPerformanceMetrics()	V$SYSSTAT	only cacheHitRatio, and it is omitted when V$SYSSTAT cannot be read (no QPS/deadlocks/buffer-pool) — §7.1
getSlowQueries()	V$SQL (top-N by ELAPSED_TIME)	sharedBlksHit=BUFFER_GETS, sharedBlksRead=DISK_READS; [] on failure
getActiveSessions()	V$SESSION ⋈ V$SQL	pid = "SID,SERIAL#"; wait class/event; [] on failure. activity classifies STATUS and STATE: INACTIVE is idle even when WAIT_CLASS is set, ACTIVE is waiting only when STATE is WAITING and the wait class is other than Idle (EVENT and WAIT_CLASS keep the last wait once the session is on CPU, and there is no CPU wait class), and any other status is other. The raw status stays in state
getTableStats()	USER_TABLES + USER_LOBS + USER_INDEXES + USER_SEGMENTS	one row per table the connected user owns; sizes + lastAnalyze (§7.4); no live/dead tuples, no bloat; rejects with the engine's sentence on failure (§3.7)
getIndexStats()	ALL_INDEXES + USER_SEGMENTS + ALL_IND_COLUMNS	size summed over every segment of the index, partitions included; scans always 0 (no usage counter exposed); isPrimary always false; rejects on failure
getStorageStats()	DBA_DATA_FILES → fallback USER_SEGMENTS	per-tablespace size; DBA view falls back to user segments without privilege

7.1 When the cache hit ratio is not measurable

Two states, both ordinary:

• The connected user cannot read V$SYSSTAT. Measured 2026-08-23 on Oracle AI Database 26ai Free against a user granted only CREATE SESSION:

  ORA-00942: table or view "SYS"."V_$SYSSTAT" does not exist
  

• The counter denominator is zero. NULLIF(..., 0) guards the division, so the statement returns one row whose single column is NULL. Measured 2026-08-23 on the same instance:

   HIT_RATIO
  ----------
  <NULL>
  

In both cases getHealth().cacheHitRatio is "N/A" and getPerformanceMetrics() omits cacheHitRatio (returning {} when nothing else was read), and the Overview and Performance tabs render "Not measured". A ratio measured as 0 is kept and shown as 0.0%.

getHealth() previously published "0%" for an unreadable ratio and getPerformanceMetrics() defaulted to 100. The 0% was worse than the 100: the Overview card rates a low ratio "Needs tuning", so a least-privilege application user saw a cache fault Oracle never reported.

bufferPoolUsage is no longer reported. It was assigned cacheHitRatio itself — the same number under a second name, which the Performance tab drew and rated as an independent gauge. Oracle does publish pool occupancy, in V$BUFFER_POOL_STATISTICS/V$SGASTAT, but this method does not query them.

7.2 When the connection count is not measurable

Two methods read a connection count, and both can be refused:

Method	Statement	Field
getHealth()	SELECT COUNT(*) FROM V$SESSION WHERE STATUS = 'ACTIVE'	HealthInfo.activeConnections
getOverview()	SELECT COUNT(*) FROM V$SESSION WHERE TYPE = 'USER'	DatabaseOverview.activeConnections

Both need the same V_$ grant everything else here does, and lacking it is the ORDINARY case rather than an exotic one. Oracle's own Database Reference is explicit: "After installation, only user SYS or anyone with SYSDBA privilege has access to the dynamic performance tables"; the views themselves carry the V_$ prefix and what an application queries is the V$ public synonym over them, until a DBA grants a wider set of users access. A plain schema user therefore reads nothing from V$SESSION at all. The refusal measured 2026-08-23 on Oracle AI Database 26ai Free, against a user granted only CREATE SESSION, was on the cache-ratio view (§7.1) - V_$SESSION answers in the same shape, naming the underlying view rather than the synonym:

ORA-00942: table or view "SYS"."V_$SYSSTAT" does not exist

activeConnections is optional on both shapes for this case, so a refused count is omitted rather than reported:

• From getHealth() the key is absent from the object and from the POST /api/db/health body, and the admin fleet-health row drops its N conn figure rather than printing 0 conn (src/components/admin/tabs/OverviewTab.tsx).
• From getOverview() the key is absent from the monitoring payload, and the Overview tab's Connections card draws N/A / "not published" instead of the figure 0; the connections trend chart drops that sample rather than plotting it at zero (src/components/monitoring/tabs/OverviewTab.tsx). The card's threshold rating is not among the things this changes: the V$PARAMETER ceiling is read inside the same try as the count, so a refusal leaves maxConnections at its 0 initialiser too, connectionPercent is null on both the old and the new path, and the rating is taken from connectionPercent ?? 0 either way - the same score and the same card border. The drawn figure and the dropped trend sample are the whole of it.

Both used to be initialised to 0 with the guard leaving that 0 standing, so ORA-00942 arrived as a measured "no active sessions" about an instance Oracle had said nothing about. For the health figure that reached the model: the agent's curated health reading forwards this field (src/lib/agent/tools.ts), so the fabrication was a claim about a server it could not measure. The agent does not read getOverview(); that count's readers are the monitoring card and its trend chart (the threshold rating reads it too, to the same result either way, as above).

An instance that really has no such session measures 0, and that 0 is a reading: it is kept and reported as 0. The absence is spelled measuredNumber(...) plus a conditional spread, never || undefined.

maxConnections is deliberately not optional alongside it. It is a published ceiling (V$PARAMETER sessions), and there 0 MEANS "no limit published" - the same fact as absence - so a refused V$PARAMETER leaves 0 and the card says "no limit published". The count is read first in that shared block precisely so a refused ceiling cannot carry a measured count away with it.

7.3 When the database size is not measurable

getOverview() sizes the schema with one statement over a data dictionary view of the connected user's own segments:

SELECT SUM(BYTES) AS TOTAL FROM USER_SEGMENTS

That is a different story from §7.1 and §7.2, and the difference is the point. USER_* views describe objects the current user owns, so this statement needs none of the V_$ access those sections turn on - the ORA-00942 measured there against a CREATE SESSION-only user is not what fails here, and no failure of this statement has been measured on a live instance at all. That is precisely why the guard names no cause: a dictionary the DBA has locked down, a connection lost between this statement and the one before it, and an overrunning query that nothing here cuts short - this provider wires no server-side query timeout at all (§4.2) - all arrive at the same catch in the same shape. A catch cannot tell them apart. It knows only that no figure arrived.

So the figure is omitted, not zeroed. DatabaseOverview.databaseSizeBytes is optional exactly so this can be said - "absence and zero are different facts", its docblock in src/lib/db/types.ts - and until #565 this method could not say it: the local was initialised to 0 and the catch was empty, so a statement that never answered published a measured-looking zero, indistinguishable from a schema that owns nothing.

The monitoring Storage tab (src/components/monitoring/tabs/StorageTab.tsx) is what the difference buys: it keys its entire breakdown off databaseSizeBytes !== undefined, so on the absence it renders "No storage size information available." On the fabricated 0 it drew the breakdown instead - and drew it against a total that contradicted its own rows. The Tables and Indexes figures come from getTableStats(), a separate read that does not share the size statement's failure, so real per-table bytes sat under a schema reported as 0 B: every share is gated on totalSize > 0, so all three bars stayed empty, and 0 - tables - indexes went negative, which the remainder row refuses as N/A. What the tab presented as a measurement was therefore a breakdown whose every element either disagreed with the total or declined to answer.

databaseSize, the formatted string, moves with the figure. It is initialised to "N/A" and only formatBytes() replaces it, so an unanswered statement now leaves "N/A" where it used to leave "0 bytes". That is not cosmetic: both the monitoring Overview card and the Storage tab's own header render this string as the headline size (overview?.databaseSize || "N/A"), so the old initialiser printed a confident 0 bytes directly above "No storage size information available." getHealth() in this same file initialises its own databaseSize to "N/A", so getOverview() was the odd one out; #569 (libSQL) and #517 (the search provider) merged the same pairing.

A schema that really measures 0 is a reading and is kept, and here that case is ordinary rather than hypothetical: a freshly created user owns no segment, so SUM(BYTES) answers one row of NULL, which the provider maps to 0; the tab then formats the 0 B it was given. If the driver returns no row, no expected column, or a non-finite value, the measurement is absent and the string stays N/A. The shared measuredNullableAggregate() (measured-aggregate.ts) boundary preserves those states without a falsy test that would erase a genuine zero.

7.4 What a table's size counts

USER_SEGMENTS names each segment for its own object (SEGMENT_NAME, with SEGMENT_TYPE saying what kind) and has no TABLE_NAME column. From the provider's first version through 0.17.0 the table-stats statement selected one from it anyway, so on every Oracle connection the read answered ORA-00904: "TABLE_NAME": invalid identifier, the empty catch returned [], and Monitoring > Tables, the Storage tab's table figures and the admin per-table maintenance list all read "no tables" (measured on Oracle AI Database 26ai Free 23.26.3.0.0, 2026-10-04). Which table owns an index or a LOB is a fact of USER_INDEXES and USER_LOBS, so the statement now lists every segment a table owns through those two and joins USER_SEGMENTS by name and kind:

Counted toward	Segments
tableSize	the table's own (TABLE, TABLE PARTITION, TABLE SUBPARTITION); its LOB segments (LOBSEGMENT, LOB PARTITION, ...); its LOB indexes (LOBINDEX); an index-organized table's top index, where that table's rows live
indexSize	every other index on the table, one segment per partition summed

Everything that stores the rows is the table, the way PostgreSQL's table size includes TOAST. The join matches a segment's kind as well as its name because indexes have their own namespace and may share a table's name. Measured on the same server with a seeded schema:

Table	Shape	tableSize	indexSize
PART_T	two range partitions, one LOCAL index	16 MB (2 x 8 MB partitions)	128 KB (2 x 64 KB)
DOCS	CLOB + BLOB, primary key	7.69 MB (64 KB table + 7.25 MB and 256 KB LOB segments + 2 x 64 KB LOB indexes)	64 KB
EMP	heap, primary key + one index, analyzed	64 KB, 2000 rows, lastAnalyze set	256 KB
IOT_T	index-organized	64 KB (the top index)	0 B
HEAPY	no row ever inserted (deferred segment creation)	0 B	0 B

A table never analyzed reads rowCount 0 and no lastAnalyze: both are the optimizer statistics, filled by Gather Statistics. DROPPED = 'NO' keeps a recycle-bin BIN$ table out of the list wherever the dictionary shows one. An index the user owns on another schema's table is listed in USER_INDEXES under that table's bare name, so TABLE_OWNER = USER keeps it off a same-named table here. schemaName is the statement's own USER, the account the views answer for, which is the owner the per-row Gather Statistics and Rebuild Indexes then act on (§9). It is not the configured login name: a proxy login (app[report]) or a quoted lower-case user differ from it.

A table stored in a CLUSTER has no segment of its own (the cluster holds it), so it reads 0 B.

---

9. Maintenance

runMaintenance(type, target?, container?) (oracle/index.ts):

A container is the OWNER the row carries as schemaName (#772). It moves every read off the USER_* catalogs and onto their ALL_* twins with the owner bound, passes that owner as the first GATHER_TABLE_STATS / GATHER_SCHEMA_STATS argument in place of USER, and qualifies each ALTER INDEX "<owner>"."<index>" REBUILD. Without one the connected user is the owner, which is the reading every column below used before the parameter existed.

Type	With target	Without target
analyze	DBMS_STATS.GATHER_TABLE_STATS(<owner>, '<t>')	DBMS_STATS.GATHER_SCHEMA_STATS(<owner>)
optimize	rebuild the indexes THAT TABLE owns: SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = :t AND INDEX_TYPE = 'NORMAL' (or ALL_INDEXES with OWNER = :owner), then ALTER INDEX "<i>" REBUILD for each (own try/catch)	rebuild every normal user index (USER_INDEXES / ALL_INDEXES, each in its own try/catch)
kill	ALTER SYSTEM KILL SESSION '<SID,SERIAL#>'	throws (SID,SERIAL# required)

getCapabilities().maintenanceOperations = ['analyze', 'optimize', 'kill']. Targets are inline-escaped (single quotes doubled for the PL/SQL string literal; double quotes doubled for the quoted index identifier) rather than routed through escapeIdentifier(), because they sit inside DBMS_STATS arguments / ALTER identifiers that can't take bind parameters. The optimize catalog read is the exception: TABLE_NAME = :tableName sits in a WHERE clause, which does take a bind.

Where each operation may be offered (maintenanceOperationSpecs)

Declaring that an operation EXISTS is not enough to put a button on it: two engines that declare the same MaintenanceType take different kinds of target, so each provider also declares what its own operations may be pointed at. The monitoring Tables tab renders a per-row control only where perEntity is true, the admin Operations tab a whole-database card only where global is true, and both take the wording from label (#496).

POST /api/db/maintenance reads the same declaration since #U20, and it is the one reader that REFUSES rather than hides: it takes the placement from whether the request carries a target (absent or empty means whole-database) and answers 400 when this provider marks that placement unavailable while the other one is available. On Oracle it never speaks: every declaration above is either both placements or neither. kill declaring neither is not "takes no target" - SID,SERIAL# comes from the Sessions panel, which this field says nothing about - so those requests pass through.

Operation	Control label	Per-row	Global	Why
analyze	Gather Statistics	yes	yes	GATHER_TABLE_STATS / GATHER_SCHEMA_STATS
optimize	Rebuild Indexes	yes	yes	the target is a TABLE, and its own indexes are rebuilt - the shape SQL Server's identically worded ALTER INDEX ALL ON [<t>] REBUILD has
kill	Kill Session	no	no	the target is SID,SERIAL# from the Sessions panel

optimize used to take an INDEX name, so the per-table button #427 wired up sent a table and every click answered ORA-01418: specified index does not exist - reproduced against ldb-oracle-r5 on 2026-08-25 and re-run after the fix, which brought an UNUSABLE index on the named table back to VALID both with a target and without one. That container's SELECT BANNER_FULL FROM V$VERSION answers "Oracle AI Database 26ai Free Release 23.26.2.0.0", which is the product name used throughout this document. INDEX_TYPE = 'NORMAL' excludes what ALTER INDEX ... REBUILD cannot take (the LOB index a CLOB column creates was present in that probe) and keeps the B-tree indexes that back UNIQUE and PRIMARY KEY constraints. A table with no rebuildable index succeeds having rebuilt nothing: "nothing to do" is not a failure, and neither is a heap table.

An empty index list has two causes, and they are not the same fact. A target the schema does not own answered {"success": true} in ~1 ms having done nothing at all - measured through the provider on 2026-08-25 for U9MISSING (no such table) and for u9real (a real table spelled in the wrong case, which Oracle stores folded to upper case). Where TABLE_NAME = :t returns no index, SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = :t is asked as well, and a target that catalog does not know is reported as a failed operation:

Target	Result
U9REAL (one index)	success: true · "OPTIMIZE: rebuilt 1 of 1 indexes."
U9HEAP (no index, real table)	success: true · "OPTIMIZE: rebuilt 0 of 0 indexes."
u9real (case mismatch)	success: false · "this schema owns no TABLE named u9real …"
U9MISSING (absent)	success: false · "this schema owns no TABLE named U9MISSING …"
a plain VIEW	success: false · the same sentence, which is why it names the view case too
no target (whole schema)	success: true · "OPTIMIZE: rebuilt 27 of 30 indexes."
every index of the table refused	success: false · "rebuilt 0 of 2 indexes. ORA-01647 …"

The existence question is asked ONLY when the index list came back empty, so the ordinary path stays at one catalog read. USER_TABLES is the catalog that answers it because it is not narrower than USER_INDEXES: measured on the same container, a MATERIALIZED VIEW's container appears there under the view's own name and its indexes are keyed to that name, while a plain VIEW appears in neither - and a view owns no index for "Rebuild Indexes" to have rebuilt. The count in the message is there because tolerating one failed index means success: true alone cannot distinguish 2 of 2 from 1 of 2.

None of them rebuilding is a third fact. One index failing leaves the run completed - an offline tablespace or an unusable partition stops that index alone - but a table where EVERY rebuild is refused had nothing it was asked to do happen. Measured on 2026-08-25 with the table's tablespace put READ ONLY, so every ALTER INDEX ... REBUILD answers ORA-01647: this reported {"success": true, "message": "OPTIMIZE: rebuilt 0 of 2 indexes."} in 14 ms with the ORA text discarded in an empty catch. It now reports success: false and carries the engine's first refusal, because the count says how many and only the ORA text says why. A table with no index at all keeps its success: nothing to do is still not a failure.

vacuumAction has said "Rebuild Indexes" since this provider shipped, and that is optimize, not a vacuum Oracle has no statement for: vacuumActionOperation: 'optimize' is what lets the Operations tab render those words and send an operation Oracle declares.

---

10. Capabilities & labels

getCapabilities() (oracle/index.ts)

Capability	Value
queryLanguage	sql
supportsExplain	false (intentionally disabled — see Known limitations)
supportsExternalQueryLimiting	true (from base)
createTable	declared, see Create Table form
supportsInlineRowEdit	true — UPDATE t SET c = v WHERE pk = v is core Oracle DML; an edit of a DATE, TIMESTAMP, or TIMESTAMP WITH [LOCAL] TIME ZONE cell still in the provider's text form binds :n through TO_DATE, TO_TIMESTAMP, or FROM_TZ(TO_TIMESTAMP(:n, …), 'UTC') (§5.5)
supportsTestDataGeneration	true - the row menus offer Generate Test Data on tables, which writes one multi-row INSERT INTO ... VALUES. A DATE value is written as TO_DATE('2026-01-27', 'YYYY-MM-DD') and a TIMESTAMP value as TO_TIMESTAMP('2026-01-27 14:30:00', 'YYYY-MM-DD HH24:MI:SS'), the form the SQL export writes, because the quoted ISO text is read through NLS_DATE_FORMAT and refused with ORA-01861; a NUMBER column is generated as an integer and written unquoted (#1400)
supportsResultPagination	true — OFFSET m ROWS FETCH NEXT n ROWS ONLY from this provider's own prepareQuery override; page one is FETCH FIRST n ROWS ONLY (#816)
supportsTransactions	true — Oracle is always in a transaction and the held connection commits or rolls back, so the trio and the SANDBOX toggle are offered (#464)
implicitCommitStatements	ALTER, ANALYZE, ASSOCIATE, AUDIT, COMMENT, CREATE, DISASSOCIATE, DROP, FLASHBACK, GRANT, NOAUDIT, PURGE, RENAME, REVOKE, TRUNCATE: Oracle's DDL, which "implicitly commits the current transaction before and after every DDL statement" (SQL Language Reference, "Types of SQL Statements"). Plus BEGIN, DECLARE and CALL: a PL/SQL block or a procedure can commit through EXECUTE IMMEDIATE or its own COMMIT. SANDBOX refuses all of these before sending, because a ROLLBACK after one answers success and can undo nothing, and this provider reads no transaction state back from the server to notice afterwards, so the declaration is the only guard here. COMMIT, ROLLBACK and ABORT are refused on every engine
implicitCommitExceptions	ALTER SESSION, ALTER SYSTEM: session and system control, not DDL, so SANDBOX lets them through
declaresForeignKeys	true — inherited from the base capabilities; read from ALL_CONSTRAINTS, so an empty list is about the schema or the owner, not the engine
supportsMaintenance	true
maintenanceOperations	['analyze', 'optimize', 'kill']
supportsConnectionString	true
defaultPort	1521
statementTerminator	'none' - node-oracledb sends one statement and ; is not part of it (see §3.2a)
schemaRefreshPattern	(CREATE|DROP|ALTER|TRUNCATE)\b (from base)
containerLevels	one level, schema - and on Oracle that level is a USER (§7)
containerPathShapes	exact: only [schema] addresses a container, so a shorter or a longer path is refused, by the object routes over HTTP and by this provider for a caller that reaches it directly (#1147)
objectKinds	nine: table, view, materialized view, synonym, sequence, package, procedure, function, trigger. No index kind (§7)

Create Table form

createTable is ORACLE_CREATE_TABLE in oracle-create-table.ts, a module that loads no driver, so the Create Table form and its tests read the real declaration (#1822). It is the grammar the form writes; the form itself knows no engine.

Field	Value
Auto-increment clause	NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, not sequence-backed
Column constraints (PRIMARY KEY, UNIQUE)	Yes
Key type	INTEGER
Types, in dropdown order	VARCHAR2(255), CLOB, INTEGER, NUMBER(19), NUMBER(1), TIMESTAMP, DATE, RAW(16), NUMBER(10,2)

The auto-increment clause is the identity column, the spelling Oracle documents as the current one. VARCHAR2 is the string type Oracle documents; VARCHAR is reserved for a future meaning. There is no TEXT, BIGINT or UUID type, so long text is CLOB, a 64-bit integer is NUMBER(19) and a GUID is RAW(16). A BOOLEAN table column is 23ai and later, so a true/false column is NUMBER(1), the spelling that works on the versions before it. JSON is CLOB for the same reason: the native JSON type is 21c and later. A row that reads like the old shared list is Oracle agreeing with it, not a row nobody looked at.

Labels — overridden (getLabels(), oracle/index.ts)

Oracle overrides the default SQL labels so the UI uses Oracle vocabulary: analyzeAction → "Gather Statistics", vacuumAction → "Rebuild Indexes", and the matching global labels ("Gather Stats", "Rebuild All Indexes").

slowQueriesEmptyState → "Query stats come from V$SQL, which this user needs SELECT on to read." The monitoring Queries panel's empty state was hardcoded to PostgreSQL's pg_stat_statements advice on every engine (#463); getSlowQueries() here reads V$SQL (§8) and returns [] when that read is refused, so the grant is the thing a DBA can act on.

SQL writing

sql/oracle-sql-writing.ts declares how a generated statement spells a literal, a name and a bind for this engine, and it is this engine's row of SQL_WRITING (src/lib/db/sql-writing/index.ts), which the readers in src/lib/sql/values.ts and src/lib/sql/identifier.ts look up. A literal is written by literalStandard and read back by unquoteStandard: the quote is doubled and a backslash is data. A name is quoted by identifierDoubleQuoted, the SQL standard form: a double-quoted name with an inner double quote doubled. A bind is spelled by placeholderColon, as :1, :2. oracledb binds an array to :n. The module also declares a temporalLiteral form. Oracle reads a quoted string in a date position through the session's NLS_DATE_FORMAT, DD-MON-RR by default, and refuses the ISO text with ORA-01861: literal does not match format string (#1400, on Oracle 23.26.3.0.0; the SQL export measured the same refusal for all four date types). So a YYYY-MM-DD date goes through TO_DATE and a YYYY-MM-DD HH24:MI:SS timestamp through TO_TIMESTAMP, with a mask that spells the text's own form, as the export writes the same types, and the session's format moves nothing. Text that is not in that form is quoted as it is: the mask would refuse it, and a plain literal says what the caller had. Inline row editing reads a key column's kind through the shared INSTANT_TYPE_NAMES, FLOAT64_TYPE_NAMES and EXACT_DECIMAL_TYPE_NAMES of the module's inlineEdit. Its boundEdit wraps the :n bind of a DATE, TIMESTAMP or TIMESTAMP WITH [LOCAL] TIME ZONE edit still in the provider's text in TO_DATE, TO_TIMESTAMP or FROM_TZ(TO_TIMESTAMP(…), 'UTC'), with the masks the SQL INSERT export uses (#1818). oracledb binds a string as DB_TYPE_VARCHAR, which Oracle reads into those columns through the session's NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT. Measured 2026-10-18 on Oracle AI Database 26ai Free 23.26.3.0.0 (oracledb 26.0.0, TZ=Europe/Istanbul): a bare :1 was refused with ORA-01861: literal does not match format string on DATE and ORA-01843: An invalid month was specified. on the three timestamp types, while the wrapped bind stored each value equal on the server, BC dates included. Every other edit is bound as :n untouched.

The module's export field spells a result export's column types through ddlTypes: VARCHAR2(4000), NUMBER(19), BINARY_DOUBLE, NUMBER(1), TIMESTAMP and BLOB, since Oracle has no TEXT and no BOOLEAN before 23c. Each name in standsAlone was measured, one CREATE TABLE probe (c <name>) per name on Oracle Free 23ai, read back out of USER_TAB_COLUMNS, and is listed only because Oracle both accepted it and stored it unnarrowed. NUMBER there is the full 38 digits. A binary cell is written by binaryHexToRaw, because Oracle parses neither X'…' nor 0x… as a binary literal (SELECT rawtohex(x'0102') FROM dual is ORA-00907), so the conversion function is the literal. HEXTORAW('') is NULL, which is also what Oracle stores for a zero-length RAW, so the empty case needs no special spelling. nonFinite writes BINARY_DOUBLE_NAN, BINARY_DOUBLE_INFINITY and -BINARY_DOUBLE_INFINITY: replayed into Oracle AI Database 23.26.3 (2026-10-04), its own constants read back into BINARY_DOUBLE and BINARY_FLOAT. columnWriters writes a date or timestamp cell through a conversion function chosen by the column's declared type, because Oracle parses none of the literal forms a Date stringifies to. Measured through the real export path on Oracle AI Database 26ai Free 23.26.2.0.0, an exported '2026-08-24T07:11:12.345Z' is ORA-01843: An invalid month was specified for all three timestamp types and ORA-01861: literal does not match format string for a DATE. A naive DATE or TIMESTAMP is written from the local fields of the Date: a DATE holding 2026-08-24 10:11:12 arrived as 2026-08-24T07:11:12.000Z from a process at +03:00, so writing the ISO text would move every such value by the exporter's own offset. A zoned column is written through FROM_TZ(..., 'UTC'), which keeps the instant whatever zone the replaying session runs in, and a declared DATE through TO_DATE, since a TO_TIMESTAMP literal inserted into a DATE is accepted and silently truncated to the whole second. NO_BARE_SPELLING completes it.

---

11. Error handling

mapDatabaseError() (map-database-error.ts) reads the oracledb row of DRIVER_ERROR_CLASSIFIERS (oracle-driver-errors.ts) for the Oracle-specific cases:

Situation	Error
Missing host (no connection string)	DatabaseConfigError
Operation before connect()	DatabaseConfigError (via ensureConnected())
connect() fails	ConnectionError (carries host/port)
A privilege the user lacks, by errorNum: ORA-01031 insufficient privileges, ORA-01045 no CREATE SESSION	PermissionDeniedError, answered 403 PERMISSION_DENIED with Permission denied: before the server's sentence
A refused credential, by errorNum: ORA-01017 an invalid credential, ORA-01005 a null password, ORA-28000 a locked account, ORA-28001 an expired one	AuthenticationError, answered 401 AUTH_ERROR; a message with no errorNum is still read by its words (ORA-01017, password)
ORA-12541 / ORA-12154 / TNS:	ConnectionError
ORA-00942 (table or view does not exist)	QueryError
NJS-138 (server predates Oracle 12.1, Thin-mode incompatible)	DatabaseConfigError, not retryable — see §4.4
NJS-116 (account has only a 10G password verifier)	DatabaseConfigError, not retryable. Checked before the generic password branch, which would otherwise report it as an authentication failure. The message also names the DBA-side fix (a password reset writes a 12C verifier)
NJS-533 (server requires Native Network Encryption or checksumming)	DatabaseConfigError, not retryable
NJS-529 (wallet is not PEM, typically an sso-only cwallet.sso)	DatabaseConfigError, not retryable. Caught by code, not by substring: the driver's text says nothing about Thin mode. The message names both ways out — convert the wallet to ewallet.pem, or use Thick mode
NJS-089 (a client-side feature Thin mode does not implement: heterogeneous pooling, some database object types, Advanced Queuing)	DatabaseConfigError, not retryable
Any other message containing not supported by node-oracledb in Thin mode	DatabaseConfigError, not retryable
NJS-045 from initOracleClient() (no Thick-mode addon in this build)	DatabaseConfigError from the constructor, worded as a packaging defect naming the platform and arch, not as a bad path
DPI-1047 from initOracleClient() (client libraries not loadable)	DatabaseConfigError from the constructor, pointing at the loader path (/etc/ld.so.conf.d/ + ldconfig, or LD_LIBRARY_PATH) and libaio.so.1
Driver message contains timeout / timed out	TimeoutError
connection.break()-interrupted query	maps via the generic path (the driver's ORA-01013 / "user requested cancel"); other ORA-* codes fall through to QueryError/DatabaseError with the original message

There is no provider-driven server-side query timeout (no queryTimeout wiring), so a TimeoutError only arises from a driver-level timeout message.

---

12. Testing

12.1 How the tests work

Integration tests live in tests/integration/db/oracle-provider.test.ts. The oracledb module is replaced with an in-process mock via mock.module('oracledb', …) before the provider is imported — there is no live Oracle in the suite. The mock pool/connection returns canned { rows, metaData } results, exercising the same code paths as the real driver.

The mock is why this went unnoticed for as long as it did. It answered every column with a plain JS value, so no test could produce the Lob stream object oracledb really returns for a CLOB, an NCLOB or a BLOB — a defect that made the whole query fail against a real Oracle was invisible to a suite that never saw the driver's own value shape. The mock now carries the DB_TYPE_* / STRING / BUFFER identities a fetch type handler is written against, and records the options each execute() received, so the handler itself is asserted over each type; the value shapes it produces are pinned from live measurements (§5.3). The same holds for the two INTERVAL identities and the IntervalYM/IntervalDS field shapes (§5.5) — and those constants are typed as the driver's DbType from src/types/db-drivers.d.ts, which is what keeps the mock and the provider reading the same declaration. That declaration is hand-written because oracledb publishes none (verified on 6.10.0: no types/typings field, no .d.ts in the package, and no @types/oracledb dependency here), so a driver upgrade that changes a shape is caught by a live probe, not by tsc.

The mock refuses a USER_SEGMENTS column the view does not have. It answered every statement it was sent, so SELECT TABLE_NAME ... FROM USER_SEGMENTS passed the suite for as long as it shipped while the engine refused it with ORA-00904 (§7.4). Worse, the table-stats statement fell through to the mock's database-size answer, and the old test asserted only the field types of that wrong row. The default handler now answers ORA-00904 for any column outside the 27 the view has on 23.26.3.0.0, a test pins that the guard refuses the 0.17.0 statement, and the table and index statistics are matched before the USER_SEGMENTS answers and asserted value by value.

⚠️ Mock isolation: bun's mock.module() is process-wide; files mocking different drivers would cross-contaminate if they shared one. They never do: bun run test gives every test file its own bun process, so a single file is safe and so is the whole suite, which is the same command CI runs. bun run test:coverage is that runner with coverage on. See CLAUDE.md.

12.2 Coverage

The suite covers: validation, connect/disconnect, query, capabilities, labels override, prepareQuery FETCH FIRST / OFFSET-FETCH, the object surface (columns/PKs/FKs/indexes), health, maintenance (analyze/optimize/kill), pool stats, the transaction lifecycle, query cancellation (break()), overview, performance metrics, slow queries, active sessions, table/index/storage stats, the LOB fetch type handler (per type, plus that the catalog reads are left alone and that a BLOB reaches asBytes in both its live and its serialized shape), the INTERVAL literals (both types, positive/negative/zero, a nine-digit year count, nanosecond precision, NULL, both query paths, and that a result with no interval column keeps the driver's own rows array), the DATE/TIMESTAMP wall clock (#1131: the same row read under UTC, Europe/Istanbul and America/Los_Angeles, the fraction, padding and a BC year, NULL, both query paths, the two zoned types as untouched controls, and the SQL INSERT export of the read), error mapping, and every ssl.mode branch (the TCPS switch, the DN-match flag, the concatenated walletContent, and a pasted connect string keeping its own protocol) asserted against the attributes createPool received.

It also covers the object surface (#789): the nine declared kinds and their roles, the shared assertObjectSurface contract, the container list and its ORACLE_MAINTAINED fallback, the one-statement count and its two exclusions, the package spec-and-body collapse across all three row orderings, the three trigger shapes, the four narrow detail reads, and every refusal. Each of those invariants was mutation-checked: the logic behind it was deleted and the suite confirmed to go red, which caught two assertions that were passing vacuously.

12.3 Run it

bun test tests/integration/db/oracle-provider.test.ts   # just this file (single process — safe)
bun run test                                             # the whole suite, one process per file, what CI runs
bun run test:coverage                                    # CI coverage workflow: the same runner, with coverage

12.4 Optional: verifying against a live Oracle

docker run --rm -e ORACLE_PASSWORD=secret -p 1521:1521 gvenzl/oracle-free:slim
# then connect to localhost:1521 / FREEPDB1 (user system, password secret) in the Studio UI

tests/live/oracle-zoneless-values.ts (#1131, §5.3) holds the DATE/TIMESTAMP reading against the server itself. It reads a throwaway table through the provider under UTC, Europe/Istanbul and America/Los_Angeles AND as the server's own TO_CHAR, and requires the two to agree. It also checks that TIMESTAMP WITH TIME ZONE stays an instant, that the raw driver value is still the shifted Date the conversion compensates for, and that the SQL INSERT export of the read, put through JSON as over HTTP, replays to values the server calls equal, TIMESTAMP WITH TIME ZONE and WITH LOCAL TIME ZONE included (#1224). Supply the password configured on the container:

ORACLE_TEST_PORT=1521 ORACLE_TEST_PASSWORD="$PROBE_PASSWORD" bun tests/live/oracle-zoneless-values.ts

For the object surface, use the compose service instead, which mounts the fixture (§7). Connecting as SYSTEM is a weaker test than connecting as APP: SYSTEM reads every owner, so a read that was still owner-scoped would look correct.

docker compose -f database-compose.yml up -d oracle
# then connect to localhost:1521 / XEPDB1 as APP / Password123!

The live guard for the column type rule (§7) reads the same fixture. Its subject is what the ENGINE creates from a declaration, which a mock cannot settle. It CREATES and DROPS throwaway tables in the connecting user's schema, so point it at a disposable server:

LIBREDB_LIVE_ORACLE_URL='oracle://app:Password123!@127.0.0.1:1521/XEPDB1' \
  bun tests/live/oracle-column-type.ts

On 21c the guard prints that it skipped the VECTOR case (#1209). To run that case, use 26ai Free. The fixture names the 21c service XEPDB1, and 26ai Free names it FREEPDB1. So mount a copy of the fixture with the service name replaced, measured on 26ai Free 23.26.3. The init scripts run only on a fresh container, so wait for DATABASE IS READY TO USE! before you connect:

mkdir -p /tmp/oracle-init-free
sed 's/XEPDB1/FREEPDB1/g' docker/oracle-init/01-object-fixture.sql > /tmp/oracle-init-free/01-object-fixture.sql
docker run -d --name libredb-oracle-free -e ORACLE_PASSWORD='Password123!' -p 1522:1521 \
  -v /tmp/oracle-init-free:/container-entrypoint-initdb.d:ro gvenzl/oracle-free:slim
until docker logs libredb-oracle-free 2>&1 | grep -q 'DATABASE IS READY TO USE!'; do sleep 10; done
LIBREDB_LIVE_ORACLE_URL='oracle://app:Password123!@127.0.0.1:1522/FREEPDB1' \
  bun tests/live/oracle-column-type.ts

The guard also holds the MODIFY rule (#1240, §7) on both servers.

---

13. Usage examples

import { createDatabaseProvider } from '@/lib/db/factory';

const provider = await createDatabaseProvider({
  id: 'or1', name: 'XE', type: 'oracle',
  host: 'localhost', port: 1521, serviceName: 'XEPDB1',
  user: 'app', password: 'secret', createdAt: new Date(),
});

await provider.connect();
const res = await provider.query('SELECT id, email FROM users WHERE active = :1', [1]);
const tables = await provider.listObjects(['APP'], 'table');
const { details } = await provider.describeObjects(['APP'], 'table');
await provider.disconnect();

Over the API: POST /api/db/query, POST /api/db/transaction, POST /api/db/cancel, POST /api/db/maintenance (admin), POST /api/db/objects/inventory, and the object tree's own routes under POST /api/db/objects/* (§7).

---

14. Known limitations & future work

• Agent mode reaches what READ ONLY does not stop only where the operator grants it. A sequence Oracle maintains, a row-level policy on a table the user reads only through a view, or a fine-grained auditing handler can run code or advance a counter for the agent user; section 5.7 names each one and what the operator does about it.

• A LOB is fetched whole. CLOB/NCLOB/BLOB are read into a string or a Buffer in one piece rather than streamed, so a single very large cell is held in memory and then in the JSON response. Measured: 16 MB of CLOB costs 66 ms and 16.4 MB of JSON; V8 refuses a string past 536,870,888 characters with RangeError: Invalid string length. Bounding it was rejected on purpose — a truncated value looks complete in the grid and would be written into the target by the SQL export (§5.3). Future: if a real workload hits the ceiling, stream the cell to the download rather than truncating it in the row.

• Large NUMBER loses digits, silently. Returned as a JS double: a NUMBER(38,0) measured as 1.2345678901234568e+37 and a NUMBER(20,4) as 1234567890123456.8. Fetching NUMBER as a string would keep them exact, at the cost of changing every numeric cell Oracle produces — which is why it was left out of the LOB change rather than bundled with it (§5.3).

• TIMESTAMP WITH TIME ZONE arrives as a Date, so the stated offset is folded into UTC and sub-millisecond precision is dropped (+03:00 10:11:12.345678 measured as "2026-08-24T07:11:12.345Z"). Not fixable here: the driver produces a Date and offers no string form that keeps the offset — measured, asking for one returns the reader process's own time zone for every row. TO_CHAR(col, '… TZR') is the way to see the stored zone (§5.5).

• A zoned timestamp replays as UTC, not in its original zone. The SQL export writes a date cell through Oracle's own conversion functions, so the file does replay with the instant intact (§5.5) — but the stored offset is gone before the export sees the value, so a TIMESTAMP WITH TIME ZONE written 10:11:12.345 -07:00 comes back rendered 17:11:12.345 UTC, and the sub-millisecond digits a Date cannot hold are not in the file either. Both are the driver's truncation, above, not the export's.

• A DATE or TIMESTAMP is only as exact as the driver's Date. The provider reads both as their wall clock in every zone (#1131), but the driver has already built the Date in the Node process's zone, so three values arrive wrong:

  • A wall clock inside that zone's spring-forward gap, an hour late.
  • A year below 100, read as 19xx.
  • Any digit past the millisecond.

  UTC has no gap, so the published image is exact for the first; TO_CHAR in the query is exact for all three (§5.3).

• oracledb ships no TypeScript declarations, so the driver surface is hand-declared. Verified on 6.10.0: no types/typings field in its package.json and no .d.ts anywhere in the package, and there is no @types/oracledb in this project's dependencies. src/types/db-drivers.d.ts declares the members this provider actually uses instead of the blanket any it used to; that declaration is checked against the driver only by the live probes and the integration mock, so a driver upgrade that changes a shape will not be caught by tsc alone.

• Every Thin-mode refusal is a non-retryable configuration error, not a transient one. mapDatabaseError() maps NJS-138, NJS-116, NJS-533, NJS-529 and NJS-089 (plus any message saying not supported by node-oracledb in Thin mode) to DatabaseConfigError instead of the generic retryable ConnectionError every other connect() failure produces — see §4.4 and §11. Each message points the operator at ORACLE_CLIENT_LIB_DIR. Only NJS-138 was recognised before #538; the others reached the user as "try again later" for a condition that never clears.

• Thick mode needs the operator to configure the loader, and the stock image ships no client. On Linux ORACLE_CLIENT_LIB_DIR alone produces DPI-1047: the directory must also be on the system library search path, and libaio.so.1 must resolve on Debian 13. That is a documented two-step recipe (§4.4), not something the provider can do for the operator — the loader reads LD_LIBRARY_PATH before Node starts, and ldconfig needs root. Future: a separately-published image variant with Instant Client already layered in.

• EXPLAIN is intentionally disabled for Oracle until a dialect wrapper exists. getCapabilities().supportsExplain is false, so the UI hides the Explain action. The UI's EXPLAIN builder only handles Postgres/MySQL; before the flag was flipped, the Explain action silently ran the unmodified query instead of producing a plan. Future: build EXPLAIN PLAN FOR … followed by SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY()), then re-enable the capability.

• No server-side query timeout. queryTimeout is not wired into the pool; runaway queries must be cancelled explicitly via cancelQuery() (connection.break()). Future: set connection.callTimeout (node-oracledb's per-round-trip timeout) from queryTimeout.

• kill and full monitoring require elevated privileges. ALTER SYSTEM KILL SESSION needs the ALTER SYSTEM privilege; the V$ monitoring views need SELECT on the V_$ views. A least-privilege application user can neither kill sessions nor read most monitoring (the queries degrade to absent/N/A/[]).

• Module-global driver settings. The constructor sets oracledb.outFormat/autoCommit on the shared oracledb module singleton (not per-pool/connection) — fine for a single embedding, but a process-wide side effect to be aware of if Oracle is ever used alongside another oracledb consumer.

• TLS cannot be encryption-only, and cannot be forced onto a pasted connect string. Thin mode always verifies the chain, so ssl.mode: require needs the server's CA in caCert when the certificate is self-signed, and ssl.rejectUnauthorized: false has no Oracle equivalent (§4.3). A connectionString is passed through verbatim, so the protocol it names is the one used. Future: surface the mismatch in the dialog rather than leaving the connect string to decide silently.

• No transaction auto-rollback timeout without an owner (unlike Postgres/MySQL): outside governance an abandoned transaction holds its connection/locks until committed, rolled back, or pool-reclaimed (§3.4).

• getIndexStats().scans is always 0 and isPrimary always false — Oracle index usage counters aren't read here.

• Row counts (NUM_ROWS) are optimizer estimates populated by DBMS_STATS; they can be stale or NULL until stats are gathered.

• Monitoring depends on V$ privileges. A low-privilege app user silently gets N/A/[] for the views it can't read, and no activeConnections at all in either the health or the overview reading (§7.2). getPerformanceMetrics() reports only the cache-hit ratio (no QPS, deadlocks, or buffer-pool usage), and omits even that when V$SYSSTAT is unreadable rather than substituting a figure — §7.1.

• An Oracle-maintained owner other than the session user is not browsable, SYSTEM, MDSYS and XDB included, because listContainers() filters on ALL_USERS.ORACLE_MAINTAINED. Measured: the fixture's APP user can see 4 tables and 1 view in SYSTEM, and none of them is reachable in the tree. Same trade as PostgreSQL's system-schema exclusion, and the session's own owner is exempted so connecting as SYSTEM still browses SYSTEM (§7).

• A package's members are not browsable. The package kind declares childKinds: ['procedure', 'function'], which is true of the engine, but Phase 1's provider surface is container-scoped end to end and nothing lists an object's children. Phase 2 owns it.

---

Migration DDL

ORACLE_MIGRATION_DDL is what the schema-diff migration generator writes for oracle, read through migrationDdlFor in src/lib/db/migration-ddl/. Oracle DDL commits implicitly and BEGIN opens a PL/SQL block, not a transaction, so transactionWrapper is null. Oracle has no IF EXISTS on DROP CONSTRAINT or DROP INDEX, so a dropped foreign key, index or table is written bare. A column definition puts DEFAULT before NOT NULL, falls back to VARCHAR2(255), and is added with ADD (...). A modified column writes its nullability only when it changes, because restating NULL on a nullable column is ORA-01451 and the column keeps its old type (#1240). The live regression probe is tests/live/schema-diff-dialects.ts. The fields it takes unchanged from src/lib/db/migration-ddl/shared.ts are standardAddForeignKey, standardCreateIndex, standardCreateTableForeignKey, standardDropColumn and standardPrimaryKey.

It prints no refusal reason.

---

15. References

• Driver: node-oracledb (Thin mode)
• Source: src/lib/db/providers/sql/oracle/index.ts
• SQL base: src/lib/db/providers/sql/sql-base.ts
• Query limiter: src/lib/db/utils/query-limiter.ts
• Interface & DTOs: src/lib/db/types.ts
• Errors: src/lib/db/errors.ts, and the ORA-* mapping in src/lib/db/providers/sql/oracle-driver-errors.ts
• Tests: tests/integration/db/oracle-provider.test.ts
• API contract: docs/API_DOCS.md
• Sibling provider docs: PostgreSQL · MySQL · Trino · Redis