Skip to content

Oracle

This page describes how ts-sql-query integrates with Oracle, including dialect-specific behavior, configuration options, and available features. It covers the proper setup of a Oracle connection, guidelines for connection management, and advanced behaviors such as UUID handling.

Info

To configure the database dialect, extend the appropriate database connection class when defining your connection. You must choose the correct database type to ensure that the generated SQL queries follow the dialect expected by that database.

Do not share connections between requests

A ts-sql-query connection object — along with the query runner instances passed to its constructor — represents a dedicated connection to the database.

Therefore, you must not share the same connection object between concurrent HTTP requests. Instead, create a new connection object for each request, along with its own query runners.

Even if the query runner internally uses a connection pool, the ts-sql-query connection still represents a single active connection, acquired from the pool. It must be treated as such and never reused across requests.

Usage Example

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> { }

Tip

Oracle doesn't have boolean data type; ts-sql-query assumes that the boolean is represented by a number where 0 is false, and 1 is true. All conversions are made automatically by ts-sql-query. In case you need a different way to represent a boolean, see Custom booleans values for more information.

Compatibility version

The compatibilityVersion property declares the minimum Oracle Database version the generated SQL must support, encoded as the integer major * 1_000_000 + minor * 1_000 + patch — e.g. 23_009_000 for Oracle Database 23.9. The default is Number.POSITIVE_INFINITY (latest).

Recognized breakpoints:

  • compatibilityVersion >= 23_004_000 (Oracle Database 23ai): the Values feature emits the SQL-standard WITH name(cols) AS (VALUES (…), …) table constructor introduced in 23ai. On earlier Oracle versions ts-sql-query emulates it as WITH name(cols) AS (SELECT … FROM dual UNION ALL SELECT … FROM dual) so the feature still works.

On older Oracle versions, set compatibilityVersion to your actual version so the right emulation is chosen automatically. It is recommended to keep this value in sync with your real database version so future ts-sql-query releases that gate additional features on it pick the right behavior automatically.

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    protected override compatibilityVersion = 23_009_000
}

Minimum Oracle version for stringConcatDistinct

Independent of compatibilityVersion, stringConcatDistinct emits LISTAGG(DISTINCT …), which requires Oracle Database 19c or later (the DISTINCT keyword inside LISTAGG was added in 19c). Targeting an older Oracle release means avoiding this aggregate.

Concatenation and NULL

Oracle reads NULL as the empty string when concatenating, so 'x' || null is 'x' and null || null is NULL. JavaScript's null does not survive the trip:

'[' || NULL || 'x' || ']'   -- [x]         and  (NULL || 'x') IS NULL  ->  false

Every other supported database returns NULL when a concatenation operand is NULL, and that is also what ts-sql-query's type declares: tIssue.body.concat('!') on an optional body is typed string | undefined. Left as Oracle's bare || this diverges in two places, and the second is the reason it matters:

  • concat returns a present string where the declared type says the result is optional — tIssue.body.concat('!') on a NULL body would give '!' instead of NULL.
  • The affix predicates — startsWith, endsWith, contains and their Insensitive variants — build their LIKE pattern by concatenation, so a NULL search term would make the pattern '%' and the filter would match every row instead of none:
-- rows 'Alpha', 'Beta';  .startsWith(<null term>)
s like (NULL || '%')   ->   s like '%'   ->   both rows

To match the declared type and the other databases, ts-sql-query wraps concatenation in a CASE that returns NULL when an operand is NULL (concat and the affix predicates alike — they always move together, since a startsWith that propagated NULL while concat did not would only relocate the inconsistency):

-- tIssue.body.concat('!')       (body optional)
case when "body" is null then null else "body" || :0 end
-- title.startsWith(body)        (body optional term) — the pattern is NULL when body is NULL
title like (case when "body" is null then null else "body" || '%' end) escape '\'

Only the leanest check the build-time optionality needs is emitted: a concatenation whose operands are all required stays the bare || (title || :0), and a chain null-checks only the operands that can be NULL, once, sharing a single CASE (case when body is null then null else body || :0 || :1 end).

Only for concat — concatIgnoringNull is untouched

Everything on this section is about concat, whose contract is to propagate NULL. When you want a NULL operand read as the empty string, use concatIgnoringNull instead of reconfiguring the connection: it is a separate operation with its own contract, it emits Oracle's bare || (no CASE, nothing to opt out of), and the same call means the same thing on every database. The settings below only change how concat — and the affix predicates — handle NULL.

Keeping Oracle's native ignore-NULL behavior

If you prefer Oracle's own semantics — a NULL operand read as the empty string rather than propagated — set ignoreNullInConcat = true:

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    protected override ignoreNullInConcat = true
}

concat and the affix predicates then emit the bare native || again ("body" || :0, title like ("body" || '%')).

Propagating NULL through a function instead of a CASE

The default CASE repeats each optional operand (once in the null check, once in ||) — free for a column, but not for an expensive value-source receiver. Set concatFunction to the name of a null-propagating function you created and every concatenation the builder emits — concat and the affix patterns alike — goes through func(a, b) instead, each operand appearing once:

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    protected override concatFunction = 'string_util.concat_strict'
}

The name is yours; the one above is only what this page's example calls it.

It must be a package, not a standalone function

Oracle cannot overload standalone subprograms — a second CREATE OR REPLACE FUNCTION of the same name replaces the first. Overloads live in a package, and you want the overloads: without them a CLOB argument is implicitly converted to VARCHAR2 and silently truncated at 32767 characters.

Four overloads, not two. With only (VARCHAR2, VARCHAR2) and (CLOB, CLOB), Oracle cannot resolve the commonest call of all — a CLOB column against a VARCHAR2 literal — and raises ORA-06553: PLS-307: too many declarations of ... match this call. The two mixed overloads are what make it resolvable. NVARCHAR2 and NCLOB arguments reach these through implicit conversion and need no overloads of their own.

CREATE OR REPLACE PACKAGE string_util AS
    FUNCTION concat_strict(a IN VARCHAR2, b IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION concat_strict(a IN CLOB, b IN CLOB) RETURN CLOB;
    FUNCTION concat_strict(a IN CLOB, b IN VARCHAR2) RETURN CLOB;
    FUNCTION concat_strict(a IN VARCHAR2, b IN CLOB) RETURN CLOB;
END string_util;

CREATE OR REPLACE PACKAGE BODY string_util AS
    FUNCTION concat_strict(a IN VARCHAR2, b IN VARCHAR2) RETURN VARCHAR2 IS
    BEGIN
        IF a IS NULL OR b IS NULL THEN RETURN NULL; END IF;
        RETURN a || b;
    END;
    FUNCTION concat_strict(a IN CLOB, b IN CLOB) RETURN CLOB IS
    BEGIN
        IF a IS NULL OR b IS NULL THEN RETURN NULL; END IF;
        RETURN a || b;
    END;
    FUNCTION concat_strict(a IN CLOB, b IN VARCHAR2) RETURN CLOB IS
    BEGIN
        IF a IS NULL OR b IS NULL THEN RETURN NULL; END IF;
        RETURN a || b;
    END;
    FUNCTION concat_strict(a IN VARCHAR2, b IN CLOB) RETURN CLOB IS
    BEGIN
        IF a IS NULL OR b IS NULL THEN RETURN NULL; END IF;
        RETURN a || b;
    END;
END string_util;

With the option set, the emitted SQL calls it on both halves, each operand appearing once:

select string_util.concat_strict("body", :0) as "tagged" from issue where id = :1
select id from issue where title like string_util.concat_strict(:0, '%') escape '\'

Which of the three to pick

Leave both options unset and the default CASE matches the declared type and the other databases — the right choice for a portable application, where a NULL reaching a concat or an affix filter would otherwise go unnoticed until the whole-table match above surfaces as a production bug. Reach for concatFunction when you want that same NULL propagation but the repeated operand is expensive enough to matter. Reach for ignoreNullInConcat only when you specifically want Oracle's native ignore-NULL semantics back.

Collations & case sensitivity

Oracle's default collation is BINARY — case- and accent-sensitive, the JavaScript-sensible default, so no configuration is needed unless you want the insensitive direction. The plain string operations follow the session collation; the *Insensitive operations force case-insensitivity over it. Oracle has a full session collation lever (ALTER SESSION SET NLS_COMP = LINGUISTIC; NLS_SORT = <coll>), which flips equals / like / distinct / order session-wide; you can also force a collation per value with .collate('<name>') or connection-wide with insensitiveCollation. See the dedicated Collations & case sensitivity page and the Oracle tab for the collation names (e.g. binary_ci, binary_ai).

replaceAll and collation

.replaceAll(search, replacement) mirrors JavaScript's String.replaceAll, which is case-sensitive. Oracle's REPLACE() resolves its search argument under the session collation, so on a database configured case-insensitive (NLS_COMP = LINGUISTIC with a _CI / _AI sort) a bare replace('ABCabc', 'abc', 'X') returns 'XX', corrupting the value:

replace('ABCabc', 'abc', 'X')                                                      -- XX (under a CI session)
replace('ABCabc' collate BINARY, 'abc' collate BINARY, 'X') collate USING_NLS_COMP -- ABCX

To prevent that, ts-sql-query forces a binary / code-point collation on the match operands by default (BINARY) and resets the result to USING_NLS_COMP so the forced collation does not leak into a chained comparison. replaceAll is therefore code-point exact whatever the session collation. The collation is controlled by the replaceCollation property:

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    override replaceCollation = '' // opt out — follow the session collation
}

Set it to another collation name to force a different one, or to the empty string ('') to opt out and emit the bare native replace(...).

For a deliberately case-insensitive replace, use .replaceAllInsensitive(...) instead. Because Oracle's default is case-sensitive, it forces a case-insensitive collation on the operands: insensitiveCollation when it names one, otherwise the replaceInsensitiveCollation property, which defaults to Oracle's neutral BINARY_CI:

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    protected override replaceInsensitiveCollation = 'BINARY_AI' // also fold accents; '' opts out
}

Set replaceInsensitiveCollation (or insensitiveCollation) to '' to opt out to the bare native replace(...). Full detail on the Collations page.

UUID strategies

ts-sql-query provides different strategies to handle UUID values in Oracle. These strategies control how UUID values are represented in JavaScript and stored in the database. In every case, UUIDs are exchanged as string at the JavaScript layer.

  • 'built-in' (default strategy): UUIDs are stored as RAW(16) and converted using Oracle's built-in UUID_TO_RAW / RAW_TO_UUID functions, available since Oracle Database 23ai (23.9). The bytes are stored in canonical order. Note that these built-ins validate the value through Oracle's IS_UUID check, which accepts only version-4 UUIDs: a non-v4 value (v1, v7, …) is rejected at runtime with ORA-62432: … is not a valid UUID value. Use this strategy when your application uses UUID v4; for any other version (including v7) use 'custom-functions'.
  • 'custom-functions': same RAW(16) storage, but ts-sql-query calls user-provided functions named uuid_to_raw and raw_to_uuid instead. Use this option on Oracle versions older than 23.9 (where the built-ins don't exist), when you need a UUID version the built-ins reject (such as v7 or v1), or when you want to inject your own conversion logic — for example, the v1-style byte reordering shown below. Because these functions do the conversion themselves, they accept any UUID version. Oracle resolves identifiers case-insensitively, so the names match the built-ins on 23.9+ and your functions take precedence over them if both exist.
  • 'string': UUIDs are stored as text in character-based columns such as CHAR(36), VARCHAR(36), or TEXT. No conversion functions are involved.

You can configure the strategy by overriding the uuidStrategy field in your connection class:

import { OracleConnection } from "ts-sql-query/connections/OracleConnection";

class DBConnection extends OracleConnection<'DBConnection'> {
    protected override uuidStrategy = 'string' as const
}

Generating UUIDs

For chronologically sortable primary keys, generate UUID v7 and use the 'custom-functions' strategy with the canonical implementation shown in UUID utility functions for Oracle: it stores the 16 bytes as-is, so v7's leading timestamp keeps inserts clustered at the end of the primary-key index. The default 'built-in' strategy cannot store v7 (or v1) values — Oracle's UUID_TO_RAW only accepts v4 and rejects other versions with ORA-62432. If you use UUID v4 (random, no clustering benefit), the 'built-in' strategy works out of the box; Oracle 23ai (23.9)+ also provides a server-side UUID() function that returns v4 — useful if you want the database to generate the value as a column DEFAULT. There is no server-side v7 generator, so v7 must be generated in the application. See the column types page for more context.

UUID utility functions for Oracle

The 'custom-functions' strategy requires the uuid_to_raw and raw_to_uuid functions to exist in the database. Two implementations are documented below — pick the one that matches the UUID version your application generates, since each preserves the time-ordering of a different version. The 'built-in' strategy doesn't need either implementation, because Oracle 23ai (23.9)+ already ships built-ins that behave like the canonical (non-reordering) variant — but those built-ins accept only version-4 UUIDs, so reach for 'custom-functions' (the canonical implementation below) whenever you store v7 or v1 values.

For UUID v7 (or UUID v4) — preserve canonical byte order

UUID v7 already places the 48-bit timestamp at the start of the value, so storing the bytes as-is yields a chronologically sortable RAW(16). UUID v4 is random, so byte ordering is irrelevant. This is the recommended implementation for new applications:

CREATE FUNCTION uuid_to_raw(uuid IN char) RETURN raw AS
BEGIN 
    RETURN HEXTORAW(REPLACE(uuid, '-'));
END uuid_to_raw;

CREATE FUNCTION raw_to_uuid(raw_uuid IN raw) RETURN char IS
    hex_text char(32);
BEGIN 
    hex_text := RAWTOHEX(raw_uuid);
    -- If you want the lower-case version wrap the expression in lower( ... )
    RETURN SUBSTR (hex_text, 1, 8) || '-' || 
           SUBSTR (hex_text, 9, 4) || '-' || 
           SUBSTR (hex_text, 13, 4) || '-' || 
           SUBSTR (hex_text, 17, 4) || '-' || 
           SUBSTR (hex_text, 21);
END raw_to_uuid;

For UUID v1 — reorder bytes so the timestamp segment sorts first

UUID v1 places the 60-bit timestamp split across the time-low, time-mid and time-hi fields, with time-low at the start of the string but the most-significant bits sitting in time-hi. To make a v1 UUID sortable inside RAW(16), the fields are rearranged on store and put back on read. Use this only if your application generates UUID v1; it will scramble v7's timestamp prefix.

CREATE FUNCTION uuid_to_raw(uuid IN char) RETURN raw IS
    hex_text nvarchar2(36);
BEGIN 
    hex_text := REPLACE(uuid, '-');
    RETURN HEXTORAW(SUBSTR (hex_text, 13, 4) || 
                    SUBSTR (hex_text, 9, 4) || 
                    SUBSTR (hex_text, 0, 8) || 
                    SUBSTR (hex_text, 17));
END uuid_to_raw;

CREATE FUNCTION raw_to_uuid(raw_uuid IN raw) RETURN char IS
    hex_text char(32);
BEGIN 
    hex_text := RAWTOHEX(raw_uuid);
    -- If you want the lower-case version wrap the expression in lower( ... )
    RETURN SUBSTR (hex_text, 9, 8) || '-' || 
           SUBSTR (hex_text, 5, 4) || '-' || 
           SUBSTR (hex_text, 0, 4) || '-' || 
           SUBSTR (hex_text, 17, 4) || '-' || 
           SUBSTR (hex_text, 21);
END raw_to_uuid;