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.

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
Typical in-house application	Day-to-day SQL runs as written; the Oracle-specific forms listed in the rewrites below 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 cover the common ones. PL/SQL is not executed by the listener: move procedural logic to the application or to Lite’s 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 (HeliosDB Full documentation), and the same feature probes were used for Lite.

Enabling the listener

On the command line:

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:

[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

Create a role with a password through the PostgreSQL interface:
```
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.
Connect as that role. Upper-case logon names (APP) resolve to the lower-case role (app).
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)

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)

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` 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 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