---
title: "Oracle Protocol Quick Reference"
---

# Oracle Protocol Quick Reference

HeliosDB-Lite's Oracle listener serves Oracle thin clients over TNS next to the
PostgreSQL wire: python-oracledb in thin mode, the Oracle JDBC thin driver and other
clients that speak TNS/TTC 12.1 or later. Logons are checked against Lite roles,
statements run as the logged-on role with Lite's normal permissions, and common Oracle
SQL is translated automatically. The listener is off until you enable it.

:::note[Availability]
The Oracle listener is planned for an upcoming HeliosDB-Lite release. Current builds do
not accept `--oracle-port` or the `oracle_*` settings, and applications connect through
the PostgreSQL wire protocol. This page describes the listener as it will ship.
:::

## What to expect for in-house applications

The Lite Oracle listener covers the SQL most in-house applications rely on.
Connections, binds, Oracle transaction semantics, DDL, DML, joins, subqueries,
`GROUP BY`, CTEs, `DUAL`, `SYSDATE`, `ROWNUM`, `NVL2`, `DECODE`, sequences, common
string functions and `ROW_NUMBER`/`RANK`/`LAG`/`LEAD` work as written, checked feature
by feature with python-oracledb (thin mode).

| Application profile | What to expect |
|---------------------|----------------|
| SQL/ORM-centric (little or no PL/SQL) | The strongest fit: joins, subqueries, DML and DDL run as written; check pagination (`FETCH FIRST`, `OFFSET`) against the [rewrites below](#statements-to-rewrite) |
| Typical in-house application | Day-to-day SQL runs as written; the Oracle-specific forms listed in the [rewrites below](#statements-to-rewrite) need a change |
| PL/SQL-heavy (business logic in packages) | The SQL runs as written; procedural logic moves to the application or to Lite's procedural language |

Most statements outside the supported set are refused with a clear Oracle error, which
makes them quick to find in testing; the [rewrites below](#statements-to-rewrite) cover
the common ones. PL/SQL is not executed by the listener: move procedural logic to the
application or to Lite's [procedural language](/docs/lite/features/procedural-language/)
through the PostgreSQL interface. To assess your own application, capture the SQL it
sends and run it against a test server; the method and sources are described in
[Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)
(HeliosDB Full documentation), and the same feature probes were used for Lite.

## Enabling the listener

On the command line:

```bash
export HELIOSDB_JWT_SECRET="..."   # signing key for the built-in HTTP API; start requires it
heliosdb-lite start --data-dir /var/lib/heliosdb \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --oracle-port 1521
```

Or in `config.toml`:

```toml
[server]
listen_addr = "127.0.0.1"
port = 5432            # PostgreSQL
oracle_port = 1521     # Oracle listener; off unless set

# Optional tuning (defaults shown)
oracle_idle_timeout_ms = 300000      # idle time between requests
oracle_auth_timeout_ms = 60000       # accept-to-logon deadline
oracle_max_open_cursors = 300        # per session, then ORA-01000
oracle_max_request_bytes = 268435456 # largest request (SQL + binds)
oracle_session_kdf_iterations = 3

[authentication]
oracle_verifier_iterations = 4096    # PBKDF2 rounds of the stored Oracle verifier
```

`--oracle-port` overrides `[server] oracle_port`. The listener binds
`listen_addr:oracle_port`; if the port cannot be bound, `start` fails. The startup
banner shows `Oracle TNS listener: <address>:<port>`.

## Who can log on

1. Create a role with a password through the PostgreSQL interface:

   ```sql
   CREATE ROLE app LOGIN PASSWORD 'a-long-random-password';
   GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app;
   ```

   Setting the password also stores the role's Oracle 12c verifier. A role whose
   password was set before the listener was enabled needs
   `ALTER ROLE ... PASSWORD '...'` once before it can log on over TNS.
2. Connect as that role. Upper-case logon names (`APP`) resolve to the lower-case role
   (`app`).
3. The `start --password` bootstrap accounts (`postgres`, `helios`) can also log on
   when password authentication is configured (`--auth` other than `trust`).

`NOLOGIN` roles, roles that require SSL, and SYSDBA-style logon modes are refused.
A wrong password or an unknown user gets `ORA-01017`, and the connection is closed.
There is no unauthenticated mode and no shared credential in the environment.

## Connecting

The service name is not checked; `heliosdb` is a good convention.

### Python (python-oracledb, thin mode)

```python
import oracledb

conn = oracledb.connect(user="app", password=PASSWORD, dsn="localhost:1521/heliosdb")
with conn.cursor() as cur:
    cur.execute("SELECT SYSDATE FROM DUAL")
    print(cur.fetchone())
    cur.execute("INSERT INTO employees (emp_id, name) VALUES (:1, :2)", [1, "Ada"])
conn.commit()
```

### Java (JDBC thin)

```java
String url = "jdbc:oracle:thin:@//localhost:1521/heliosdb";
try (Connection conn = DriverManager.getConnection(url, "app", password)) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT name FROM employees WHERE emp_id = ?")) {
        ps.setInt(1, 1);
        try (ResultSet rs = ps.executeQuery()) { while (rs.next()) System.out.println(rs.getString(1)); }
    }
}
```

The connection reports protocol level 19c (`conn.version` is `19.0.0.0.0`), and
`SELECT * FROM v$version` returns the HeliosDB banner.

## Transactions

Transactions follow Oracle's model:

- **Autocommit off** (the client default): the first DML statement opens a
  transaction that lasts until `commit()`/`rollback()` or SQL `COMMIT`/`ROLLBACK`.
- **Autocommit on:** each call commits when it succeeds.
- **DDL** commits an open transaction first.
- **Disconnect** rolls back an open transaction.
- A statement that fails inside a transaction aborts it (HeliosDB's all-or-nothing
  rule): later statements return `ORA-25402` and `COMMIT` reports `ORA-02091`. Roll back
  and retry the transaction.

## SQL translation

String literals, quoted identifiers and comments are never rewritten. The listener
handles:

| Oracle | Runs as |
|--------|---------|
| `SELECT ... FROM DUAL` | `SELECT ...` |
| `SYSDATE`, `SYSTIMESTAMP` | current timestamp |
| `USER` | the logged-on role |
| `NVL(a, b)`, `NVL2(a, b, c)` | `COALESCE` / `CASE` |
| `DECODE(...)` (arguments that are not nested) | `CASE` |
| `seq.NEXTVAL`, `seq.CURRVAL` | sequence functions |
| `WHERE ROWNUM <= n`, `< n`, `= 1` at the top level | `LIMIT` |
| `TO_NUMBER`, `TO_CHAR(date, format)` | equivalent conversions |
| `q'[...]'` literals | standard literals |
| `v$version`, `v$session` | HeliosDB equivalents |
| `NUMBER`, `VARCHAR2`, `CLOB`, `BLOB`, `RAW`, `DATE` in `CREATE`/`ALTER` | native types (`DATE` becomes `TIMESTAMP`) |

Bind variables (`:1`, `:name`) are bound as values, never spliced into SQL text.

### Result types

| HeliosDB value | Sent to the client as |
|---|---|
| Integers, `NUMERIC`, booleans | `NUMBER` (booleans as 1/0) |
| Floats | `BINARY_DOUBLE` |
| Text | `VARCHAR2` |
| `BYTEA` | `RAW` |
| `DATE`, `TIMESTAMP` | `DATE`, `TIMESTAMP` |
| `TIMESTAMPTZ` | `TIMESTAMP WITH TIME ZONE` (UTC) |

As in Oracle, an empty string arrives at the client as `NULL`.

### Statements to rewrite

The listener returns a clear error for most of these, so they are easy to find in
testing. Each replacement below was checked over the Oracle listener.

| Oracle form | Write instead |
|-------------|---------------|
| `a.id = b.id(+)` | `LEFT JOIN b ON a.id = b.id` |
| `CONNECT BY` | `WITH RECURSIVE r (...) AS (... UNION ALL ...)` (include the `RECURSIVE` keyword) |
| `MINUS` | `EXCEPT` |
| `FETCH FIRST n ROWS ONLY`, `OFFSET ... ROWS` | `WHERE ROWNUM <= n` or `LIMIT n` |
| `TO_DATE('2024-01-31', 'YYYY-MM-DD')`, date literals in `WHERE` | see [Dates in `WHERE`](#dates-in-where) below |
| `MERGE` | `UPDATE`, then `INSERT ... VALUES` the rows that did not match (`INSERT ... SELECT` into `NUMBER` columns: see below) |
| `INSERT ... RETURNING ... INTO` | fetch the sequence value first (`SELECT seq.NEXTVAL FROM DUAL`), then insert it |
| `INSTR(s, sub)` | `strpos(s, sub)` |
| `LISTAGG(x, ',') WITHIN GROUP (...)` | `string_agg(x, ',')` |
| `REGEXP_LIKE(s, pattern)` | `LIKE` patterns; `regexp_replace(s, pattern, replacement)` is available |
| `ADD_MONTHS`, `MONTHS_BETWEEN`, `LAST_DAY`, `d + n`, `d1 - d2` | compute the date in the application and bind it; `EXTRACT` is available for date parts |
| PL/SQL blocks (`BEGIN ... END;`, `DECLARE ...`) | application code, or Lite's [procedural language](/docs/lite/features/procedural-language/) through the PostgreSQL interface |

Test each statement your application sends; capturing them from the existing
application (driver logs, AWR/`V$SQL`) is the quickest way to find what needs a change.

### Current behaviour

A few statements are accepted but do not yet behave as in Oracle. Check them in your
tests:

- **Uncommitted rows and `NUMBER` filters.** With autocommit off (the client default),
  rows written in the open transaction are returned by an unfiltered `SELECT`, but a
  `WHERE` filter on a `NUMBER` column (`WHERE id = 1`) does not find them until the
  transaction commits. Committed rows, and filters on integer columns (`INT`,
  `BIGINT`), behave as expected. If a transaction reads back its own rows by key,
  declare the key columns as integer types or commit before the lookup.
- **`INSERT ... SELECT` into `NUMBER` columns** inserts no rows and reports a row count
  of 0, while the same `SELECT` on its own returns the rows. Fetch the rows and insert
  them with `executemany`, or use integer column types.
- **`'a' || NULL`** returns `NULL` (Oracle returns `'a'`). Wrap nullable operands in
  `NVL(x, '')`.
- **`SUM`/`AVG ... OVER (PARTITION BY ...)` on `NUMBER(p,s)` columns** returns 0.
  `GROUP BY` aggregates are correct, and window functions on integer columns work.
- **`FETCH FIRST` and `OFFSET ... ROWS`** are not applied; use `ROWNUM` or `LIMIT`.
- **`MERGE`** changes no rows (it reports a row count of 0).
- **Result types:** `NVL`, `COALESCE`, `EXTRACT` and some numeric functions return
  their numbers as text; `TIMESTAMP` values lose fractional seconds.

`executemany` itself works: it inserts every row, subject to the visibility rule above.

### Dates in `WHERE`

How a date comparison behaves depends on how the column was created:

| Column | `d > DATE '2021-01-01'` | `d > :1` bound to a `datetime` |
|--------|-------------------------|--------------------------------|
| Created over the Oracle listener (`DATE` becomes `TIMESTAMP`) | no rows | correct |
| Created through the PostgreSQL interface as `DATE` | correct | no rows |

Bind the value for `TIMESTAMP` columns (including Oracle-created `DATE` columns), and
use a `DATE '...'` literal for `DATE` columns created through the PostgreSQL interface.
Declaring date columns as `TIMESTAMP` on both interfaces keeps one style (binds)
everywhere.

## Security

- The listener has **no TLS**. The password is never sent in clear text (O5LOGON
  encrypts it under a per-session key), but query text and results are plaintext. Keep
  the listener on loopback or a private network and firewall the port.
- Roles marked `SSL_REQUIRED` cannot log on over this listener.
- Native network encryption and fast authentication are not offered.

## Troubleshooting

| Symptom | Fix |
|---------|-----|
| `ORA-12541` / connection refused | Start with `--oracle-port` (or set `oracle_port`); check the firewall and `listen_addr` |
| `ORA-01017` | Wrong user or password; or the role's password was set before the listener existed (`ALTER ROLE ... PASSWORD` again); or the role is `NOLOGIN` |
| `DPY-3010` or version errors | Use a thin client that supports TNS 12.1 or later (python-oracledb 2.x+, a current JDBC thin driver) |
| `ORA-01000` | Close cursors, or raise `oracle_max_open_cursors` |
| `ORA-25402` / `ORA-02091` | A statement in the transaction failed; roll back and retry |
| `ORA-03001` / `ORA-00900` | The statement uses a form from "Statements to rewrite" above |

Run the server with `RUST_LOG=debug` to log each translated statement.

## Summary

- Planned for an upcoming release; off by default; enable with `--oracle-port` or `[server] oracle_port`
- Logons are Lite roles with passwords (O5LOGON, 12c verifier); permissions apply
- Common Oracle SQL translated automatically; most unsupported forms return clear errors
- Oracle transaction semantics; plaintext transport, so keep it on a private network