Oracle Dialect

Oracle Dialect Oracle Database is an enterprise-grade relational database. prorm talks to it through the oracledb driver. This guide covers how to connect, the data-type mapping, sequences, Oracle's positional bind style, and the Oracle-specific quirks you need to know about. The implementation lives in oracle. It targets Oracle 19c / 21c primarily (several 23c-only conveniences are emulated — see Quirks). This dialect is functional but has gaps; the honest state of each feature is called out below. Overview Driver: oracledb (OracleDialect.library === 'oracledb') Identifier quoting: double quotes ("MyTable"). Unquoted identifiers fold to uppercase, so mixed-case names created through the ORM must always be referenced quoted. Bind style: positional :1, :2, … (Oracle has no ? placeholder support). Booleans: no native boolean — mapped to NUMBER(1). Auto-increment: no AUTOINCREMENT/SERIAL — use sequences. Pooling: always on. connect() builds an oracledb connection pool. Connection Under the hood the dialect assembles an Easy Connect string from your options: Honest note: there is no dedicated connectString or TNS-alias option. The connect string is always built from host, port, and database. If you need a full descriptor or a wallet-based connection, those are not currently wired through the dialect options. Working directly with the dialect You can also instantiate the dialect on its own: Thin vs thick mode By default (thinMode unset/false) the dialect calls oracledb.initOracleClient(), which requires an Instant Client installation. Set thinMode: true to stay in the driver's pure-JS mode. Connection retry Connection-level errors can be retried automatically: Data types The ORM's abstract types map to Oracle as follows (see mapDataType()): ORM type | Oracle type | Notes | STRING(n) | VARCHAR2(n) | default n = 255 | CHAR(n) | CHAR(n) | default n = 1 | TEXT / MEDIUMTEXT / LONGTEXT | CLOB | | INTEGER | NUMBER(10) | length overridable | BIGINT | NUMBER(19) | | DECIMAL(p,s) / NUMERIC / NUMBER | NUMBER(p,s) | precision/scale honored | FLOAT / DOUBLE| FLOAT | | BOOLEAN | NUMBER(1) | no native boolean — true/false escape to 1/0 | DATE | DATE | | DATETIME / TIMESTAMP | TIMESTAMP | | DATEONLY | DATE | | TIME | VARCHAR2(8) | stored as a string | BLOB | BLOB | | BINARY(n) / VARBINARY / RAW | RAW(n) | | JSON | CLOB | stored as text | JSONB | BLOB | | UUID | RAW(16) | | GEOMETRY | SDOGEOMETRY | | CLOB / NCLOB / BFILE / XMLTYPE / LONGRAW | native equivalents | Oracle-specific | Anything unrecognized falls back to VARCHAR2(255). Value escaping quirks Date values escape to TODATE('YYYY-MM-DD HH24:MI:SS', ...). Buffer values escape via HEXTORAW('deadbeef') so raw bytes land in RAW/BLOB. Booleans escape to '1' / '0' (consistent with the NUMBER(1) mapping). Sequences Oracle has no auto-increment column; you generate surrogate keys with sequences. Supported createSequence options: startWith, incrementBy, minvalue, maxvalue, cycle, cache, nocache, order, replace, ifNotExists, schema. Gap — no CURRVAL helper. Only nextSequenceValue() (NEXTVAL) is implemented. There is no currSequenceValue() / CURRVAL method on the dialect. If you need the current value, issue it yourself via query(): (Remember CURRVAL is only valid in a session that has already called NEXTVAL.) Note — default schema is SYSTEM. Sequence names are qualified with options.schema → config.schema → 'SYSTEM'. Set a schema in the dialect options or per call to avoid accidentally targeting SYSTEM. Bind parameters (:n style) Oracle uses positional binds :1, :2, … — there is no ? placeholder. The query builders emit this style, and query() accepts a positional array (or a named object for :name binds): buildInsertQuery / buildUpdateQuery generate :n placeholders: RETURNING ... INTO Because Oracle has no ? binds, RETURNING uses OUT binds that continue the :n numbering after the insert's own binds: Passing returning: true returns the ROWID instead. Caveat — mixed placeholder styles. The WHERE-clause builder emits ? markers while INSERT/UPDATE SET clauses emit :n. For raw statements prefer building your own SQL with explicit :n binds (or interpolate through replaceReplacements()), rather than relying on ? reaching the driver. Pagination buildSelectQuery paginates by wrapping the query and filtering on ROWNUM: Gap — buildLimitOffset(). The standalone buildLimitOffset() helper emits only a bare WHERE ROWNUM <= n and does not skip the first offset rows. Prefer buildSelectQuery (which wraps correctly), or use queryStream() for streaming, which routes through a correct ROWNUM-windowed helper. Upsert (MERGE) buildUpsertQuery compiles to a MERGE INTO ... USING (SELECT ... FROM DUAL) statement and supports composite conflict keys: A general-purpose buildMergeQuery() is also available for hand-written merges (composite on keys, conditional WHEN MATCHED, WHEN MATCHED ... THEN DELETE, etc.). Oracle-specific query builders These are implemented and Oracle-idiomatic: buildWindowFunction() — analytic functions, e.g. ROWNUMBER() OVER (PARTITION BY "dept" ORDER BY "salary" DESC). buildCTE() — WITH ... AS (...). Recursive simply by supplying unionQuery (Oracle has no RECURSIVE keyword). buildConnectByQuery() — hierarchical START WITH ... CONNECT BY PRIOR queries with optional NOCYCLE, ORDER SIBLINGS BY, and LEVEL/CONNECTBYISLEAF pseudo-columns. Transactions Only READ COMMITTED and SERIALIZABLE (plus READ ONLY) are accepted; the ANSI READ UNCOMMITTED / REPEATABLE READ levels throw a DatabaseError (Oracle doesn't have them). Savepoint SQL helpers (createSavepointSQL, releaseSavepointSQL, rollbackToSavepointSQL) are provided. Important gap — query() does not join an open transaction. startTransaction() holds its own pooled connection, but query() acquires a separate connection from the pool and runs with autoCommit: true. Statements executed through query() are therefore not part of a transaction started with startTransaction(). To run statements transactionally, execute them on tx's own connection directly. Quirks IF [NOT] EXISTS is emulated for 19c/21c Native IF [NOT] EXISTS on CREATE/DROP is 23c-only and raises ORA-00922 on older releases. For tables, schemas (users), and sequences, the dialect instead runs the plain DDL wrapped in a PL/SQL block that swallows the specific "already exists" / "does not exist" ORA- code (e.g. ORA-00955, ORA-00942, ORA-01918, ORA-02289). So ifExists / ifNotExists work portably. A schema is a user Oracle has no standalone schema object — a schema is a user. createSchema() therefore runs CREATE USER with a randomly generated password; if you need a specific password, create the user through the user-management API instead. dropSchema() runs DROP USER ... CASCADE. queryStream() Streaming uses a dedicated ROWNUM-windowed helper (createOracleQueryStream) rather than the buggy buildLimitOffset(), so paginated streams skip offsets correctly. Partitioning createPartitionedTable() supports RANGE, LIST, HASH, INTERVAL, and REFERENCE partitioning, plus subpartitions. Since Oracle has no PostgreSQL-style ATTACH/DETACH PARTITION, attachPartition() / detachPartition() are implemented via ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE .... listPartitions(), modifyPartition(), and addPartition() are available. Materialized views createMaterializedView() supports BUILD IMMEDIATE/DEFERRED, REFRESH FAST/COMPLETE/FORCE, and ON COMMIT/DEMAND. Oracle has no OR REPLACE/IF NOT EXISTS for MVs, so replace drops-then-recreates. refreshMaterializedView() calls DBMSMVIEW.REFRESH (there is no REFRESH MATERIALIZED VIEW statement in Oracle); PostgreSQL's CONCURRENTLY is ignored and treated as FORCE. Unsupported features (these throw) The following are intentionally not implemented and throw an explanatory error: Foreign Data Wrappers (createForeignDataWrapper, createForeignServer, createForeignTable, user mappings, …) — Postgres-specific; use CREATE DATABASE LINK. Extensions (createExtension, dropExtension, …) — Postgres-specific. changeOwner — not supported in Oracle. addConstraint / removeConstraint — throw. Use createConstraint() / dropConstraint() (which are implemented) instead. createSecurityPolicy / dropSecurityPolicy — throw ("Security policies are not supported"). (Oracle VPD is not wired through these methods.) See also Source: oracle Driver: node-oracledb Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL