Oracle 23ai Protocol Documentation

This directory contains comprehensive documentation for HeliosDB’s Oracle 23ai protocol support, SQL compatibility, and the PL/SQL roadmap.

Overview

HeliosDB Full implements the Oracle TNS wire protocol, so Oracle thin-mode clients (python-oracledb thin mode, the Oracle JDBC thin driver) can connect to the same database that PostgreSQL and MySQL clients use. Statements received on the Oracle listener are executed by HeliosDB’s SQL engine; the Oracle compatibility module (dialect translation, PL/SQL, DBMS_ packages) is only partly on that path today.

Quick Start

export HELIOSDB_ORACLE_NATIVE_PASSWORD='a-long-random-password'   # from your secrets store
heliosdb-full --data-dir /var/lib/heliosdb --enable-oracle

import os, oracledb   # thin mode

conn = oracledb.connect(user="app", password=os.environ["HELIOSDB_ORACLE_NATIVE_PASSWORD"],
                        dsn="localhost:1521/heliosdb")
conn.autocommit = True
cur = conn.cursor()
cur.execute("SELECT name FROM employees WHERE dept_id = :d ORDER BY emp_id", d=20)
print(cur.fetchall())

The Oracle Quick Start lists what runs over the listener and how to write the rest.

File	Description
README.md	Overview, compatibility matrix, and roadmap (this file)
In-house applications	What to expect for a typical in-house application, how it was assessed, and how to assess your own
CONFIGURATION.md	TNS listener, NLS settings, security, and connection pooling
COMPATIBILITY.md	Detailed SQL, PL/SQL, and DBMS package compatibility
EXAMPLES.md	Practical SQL, PL/SQL, JDBC, and OCI examples
PLSQL_SUPPORT.md	PL/SQL roadmap, constructs in scope, conversion patterns, and workarounds

Oracle 23ai Compatibility Matrix

Overall Compatibility

HeliosDB Full’s Oracle compatibility module covers a large part of Oracle’s feature surface. For typical in-house applications, core data access runs unchanged through the Oracle listener today, and the rest needs the rewrites described in Oracle compatibility for in-house applications. It is not a drop-in replacement. The matrix below describes the Oracle compatibility module; PL/SQL blocks, CONNECT BY, MERGE and several Oracle functions are not yet executed correctly when sent through the listener (see the in-house page for the results by usage area).

Feature Category	Result	Notes
Core SQL	Supported	SELECT, INSERT, UPDATE, DELETE, MERGE
DDL Statements	Supported	CREATE, ALTER, DROP for common objects
PL/SQL Core	Beta	Anonymous blocks, procedures, functions
PL/SQL Packages	Beta	Package specifications and bodies
DBMS Packages	Partial	Core DBMS_* packages only
Hierarchical Queries	Supported	CONNECT BY, START WITH
Cursors	Beta	Explicit cursors, REF CURSOR
BULK Operations	Beta	BULK COLLECT, FORALL
XMLType	Alpha	Basic operations only
JSON Functions	Supported	Oracle 21c+ JSON functions
Pseudo-columns	Supported	ROWNUM, ROWID, LEVEL
Analytics	Supported	Core window functions
Object Types	Alpha	Basic user-defined types
Collections	Beta	Nested tables, VARRAYs
Triggers	Beta	Row-level and statement-level
Sequences	Supported	NEXTVAL, CURRVAL
Synonyms	Supported	Public and private
Views	Supported	Standard and updatable views
Materialized Views	Beta	Basic refresh only

What to expect by application type

Assessed through the Oracle listener (HeliosDB Full main a3cf7ce, python-oracledb thin mode), against how typical in-house applications use the database:

Application profile	What to expect
SQL/ORM-centric (little or no PL/SQL)	The best fit: core DDL, DML and queries run as written; Oracle-specific syntax and functions need rewriting
Typical in-house application	Core data access runs unchanged; plan to rewrite Oracle-specific syntax, most built-in functions, sequences and PL/SQL
PL/SQL-heavy (business logic in packages)	PL/SQL is not executed through the listener yet; move the logic to the application or migrate it

Oracle Forms/APEX and tools that rely on the data dictionary, PL/SQL or OCI are not expected to work. For a migration, move the application to a PostgreSQL driver and convert the schema and code with a migration tool; see the Oracle migration guide.

Core Feature Overview

Wire Protocol: Oracle TNS

HeliosDB implements the TNS protocol for network communication:

Protocol Version: TNS 12.2 compatibility
Connection Types: Easy Connect, TNS Names, JDBC Thin
Authentication: Password, RADIUS (LDAP planned)
Encryption: TLS 1.2/1.3 for wire encryption
Packet Size: Configurable up to 32KB

SQL Dialect Support

Category	Supported	Partial	Not Supported
DML	SELECT, INSERT, UPDATE, DELETE, MERGE	INSERT ALL	UPSERT (use MERGE)
DDL	CREATE/ALTER/DROP TABLE, INDEX, VIEW	SEQUENCE, SYNONYM	CLUSTER, CONTEXT
Query	JOINs, Subqueries, CTEs	CONNECT BY, PIVOT	MODEL clause
Set Ops	UNION, UNION ALL, INTERSECT, MINUS	-	-
Locking	SELECT FOR UPDATE	LOCK TABLE	-

PL/SQL

PL/SQL execution through the Oracle listener — procedures, functions, anonymous blocks and common DBMS_ packages — is on the roadmap. Today, port PL/SQL to PL/pgSQL functions and run them through the PostgreSQL interface, or move the logic into the application tier.

Planned scope:

Core constructs:

Anonymous blocks (DECLARE/BEGIN/END)
Stored procedures and functions
Basic package specifications and bodies
Explicit cursors (OPEN, FETCH, CLOSE)
Control flow (IF, CASE, LOOP, WHILE, FOR)
Exception handling (EXCEPTION, RAISE, WHEN)
Basic collections (nested tables)

Basic forms:

REF CURSOR (basic operations)
BULK COLLECT (simple cases)
FORALL (without SAVE EXCEPTIONS)
Triggers (row-level only)
Object types (simple structures)

Not Supported:

Autonomous transactions
DBMS_PIPE and DBMS_ALERT
Native compilation
Java stored procedures
Pipelined functions
Compound triggers

Implemented Packages

Package	Key Functions
DBMS_OUTPUT	PUT_LINE, PUT, NEW_LINE, GET_LINE, ENABLE, DISABLE
DBMS_UTILITY	FORMAT_ERROR_BACKTRACE, GET_TIME, COMMA_TO_TABLE
UTL_FILE	FOPEN, FCLOSE, GET_LINE, PUT_LINE, FFLUSH

Partially Implemented Packages

Package	Supported Functions	Not Supported
DBMS_SQL	OPEN_CURSOR, PARSE, EXECUTE, CLOSE_CURSOR	DESCRIBE_COLUMNS, array binds
DBMS_LOB	GETLENGTH, SUBSTR, INSTR, APPEND	LOADFROMFILE, BFILE operations
DBMS_METADATA	GET_DDL (tables, indexes)	Complex object types
DBMS_SCHEDULER	CREATE_JOB, DROP_JOB, RUN_JOB	Complex schedules, chains
DBMS_STATS	GATHER_TABLE_STATS	Histogram operations
DBMS_RANDOM	VALUE, STRING, INITIALIZE	SEED with complex types
DBMS_SESSION	SET_IDENTIFIER	Most functions
DBMS_APPLICATION_INFO	SET_MODULE, SET_ACTION	READ functions
DBMS_CRYPTO	Basic ENCRYPT/DECRYPT	Advanced algorithms

Not Implemented Packages

Package	Reason	Alternative
DBMS_AQ	Message queuing architecture differs	HeliosDB native queuing
DBMS_PIPE	Inter-session communication	Use tables or external queues
DBMS_ALERT	Event notification	HeliosDB LISTEN/NOTIFY
DBMS_LOCK	Lock management	Standard transaction isolation
DBMS_PARALLEL_EXECUTE	Parallel processing	HeliosDB native parallelism
DBMS_XA	Distributed transactions	HeliosDB 2PC support
DBMS_FLASHBACK	Flashback architecture	HeliosDB MVCC time-travel
UTL_HTTP	HTTP operations	External services
UTL_SMTP	Email operations	External services
UTL_TCP	Network operations	Not applicable

Connection Parameters

Server Configuration

Parameter	Default	Description
`host`	localhost	Server hostname or IP address
`port`	1521	Oracle TNS listener port
`service_name`	heliosdb	Oracle service name
`sid`	HELIOS	Oracle SID (legacy)
`username`	-	Authentication username
`password`	-	Authentication password

Connection String Formats

-- Easy Connect format
sqlplus user/pass@//host:port/service_name

-- Easy Connect examples
sqlplus admin/password@//localhost:1521/heliosdb
sqlplus admin/password@//db.example.com/PROD

-- TNS Names format (requires tnsnames.ora)
sqlplus admin/password@HELIOSDB

TNS Names Configuration

HELIOSDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
    )
  )

HELIOSDB_SSL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2484))
    (CONNECT_DATA =
      (SERVICE_NAME = heliosdb)
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN = "cn=heliosdb,o=example,c=US")
    )
  )

Oracle SQL Functions

String Functions

Function	Result	Notes
INSTR	Supported	Negative position, nth occurrence
SUBSTR	Supported	Negative position, overflow handling
LENGTH	Supported	NULL returns NULL
LPAD/RPAD	Supported	Multi-character padding
LTRIM/RTRIM	Supported	Multi-character trim set
TRIM	Supported	LEADING, TRAILING, BOTH
TRANSLATE	Supported	Character-by-character replacement
REPLACE	Supported	String replacement
UPPER/LOWER	Supported	Case conversion
INITCAP	Supported	Title case
CONCAT	Supported	String concatenation
NVL	Supported	NULL replacement
NVL2	Supported	Conditional NULL replacement
DECODE	Supported	Arbitrary pairs with default
COALESCE	Supported	First non-NULL value
REGEXP_LIKE	Supported	Regular expression matching
REGEXP_REPLACE	Supported	Pattern replacement
REGEXP_SUBSTR	Supported	Pattern extraction
REGEXP_INSTR	Partial	Basic patterns only
REGEXP_COUNT	Partial	Basic patterns only

Numeric Functions

Function	Result	Notes
ABS	Supported	Absolute value
CEIL/FLOOR	Supported	Rounding
ROUND	Supported	Decimal places
TRUNC	Supported	Decimal truncation
MOD	Supported	Modulo operation
POWER	Supported	Exponentiation
SQRT	Supported	Square root
SIGN	Supported	Sign determination
GREATEST	Supported	NULL propagation
LEAST	Supported	NULL propagation
NULLIF	Supported	NULL on equality

Date Functions

Function	Result	Notes
SYSDATE	Supported	Current date
SYSTIMESTAMP	Supported	Current timestamp with timezone
CURRENT_DATE	Supported	Session timezone aware
CURRENT_TIMESTAMP	Supported	Session timezone aware
ADD_MONTHS	Supported	Leap year and month-end handling
MONTHS_BETWEEN	Supported	Fractional results
LAST_DAY	Supported	Last day of month
NEXT_DAY	Supported	Next weekday
TRUNC (date)	Supported	Date truncation
ROUND (date)	Supported	Date rounding
EXTRACT	Supported	Date part extraction
TO_DATE	Supported	Format models
TO_CHAR (date)	Supported	Format models
TO_TIMESTAMP	Supported	Timestamp conversion

Aggregate Functions

Function	Result	Notes
COUNT	Supported	Including COUNT(*) and COUNT(DISTINCT)
SUM	Supported	Numeric summation
AVG	Supported	Numeric average
MIN/MAX	Supported	All data types
LISTAGG	Supported	String aggregation
MEDIAN	Partial	Approximate in some cases
STDDEV	Supported	Standard deviation
VARIANCE	Supported	Statistical variance

Analytic (Window) Functions

Function	Result	Notes
ROW_NUMBER	Supported	Row numbering
RANK	Supported	Ranking with gaps
DENSE_RANK	Supported	Ranking without gaps
NTILE	Supported	Distribution
LAG	Supported	Previous row value
LEAD	Supported	Next row value
FIRST_VALUE	Supported	First in window
LAST_VALUE	Supported	Last in window
NTH_VALUE	Partial	Basic usage only
KEEP	Partial	FIRST/LAST only
Windowing clauses	Supported	ROWS, RANGE

Driver Compatibility

Driver	Version	Result	Notes
Oracle JDBC Thin	19c, 21c, 23ai	Tested	Primary development driver
Oracle JDBC OCI	19c, 21c	Partial	Requires Oracle Client
cx_Oracle (Python)	8.x	Tested	Deprecated, use oracledb
oracledb (Python)	1.x, 2.x	Tested	Recommended for Python
node-oracledb (Node.js)	6.x	Tested	Requires Oracle Client or Thin
godror (Go)	0.x	Partial	Basic operations
ODP.NET Managed	21c+	Partial	.NET Core/5+
ODP.NET Unmanaged	19c+	Limited	Requires Oracle Client
SQL Developer	21c+	Partial	Query only; PL/SQL execution on the roadmap
DBeaver	Latest	Partial	Via JDBC

Performance Characteristics

Protocol Overhead

Operation	Overhead	Notes
Connection Handshake	+20-30ms	TNS negotiation
Authentication	1-2 RTT	Password verification
Query Parsing	+5-10%	SQL dialect translation
Result Formatting	+2-5%	Oracle type mapping

Optimization Tips

Use connection pooling: Reduce handshake overhead
Batch operations: Minimize round trips
Avoid ROWNUM in WHERE: Use FETCH FIRST instead
Prefer ANSI JOINs: Better query optimization
Keep procedural logic in PL/pgSQL or the application: PL/SQL execution through the Oracle listener is on the roadmap

Migration Guide

Assessment Checklist

Catalog Oracle objects: Tables, views, procedures, packages
Identify DBMS package usage: Map to HeliosDB equivalents
Review PL/SQL complexity: Anonymous blocks vs packages
Test hierarchical queries: CONNECT BY translation
Check data types: Especially XMLType, SDO_GEOMETRY
Evaluate triggers: Row-level compatibility

Common Migration Patterns

-- Oracle: ROWNUM pagination
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM table t WHERE ROWNUM <= 20
) WHERE rn > 10;

-- HeliosDB: ANSI pagination (preferred)
SELECT * FROM table OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle: DECODE
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM t;

-- HeliosDB: CASE (also works in Oracle)
SELECT CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Unknown' END FROM t;

-- Oracle: (+) outer join
SELECT * FROM a, b WHERE a.id = b.id(+);

-- HeliosDB: ANSI outer join (preferred)
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

Troubleshooting

Common Issues

Issue	Cause	Solution
ORA-12154: TNS could not resolve	Service name not found	Check tnsnames.ora or use Easy Connect
ORA-01017: Invalid username/password	Authentication failure	Verify credentials
PLS-00201: Identifier not declared	Unsupported DBMS package	Check package compatibility
ORA-00904: Invalid identifier	Unsupported syntax	Review SQL translation
PL/SQL block reports success but has no effect	PL/SQL is not executed through the Oracle listener yet (on the roadmap)	Port it to PL/pgSQL and run it through the PostgreSQL interface, or move the logic to the application

Diagnostic Queries

-- Check Oracle protocol version
SELECT * FROM v$version;

-- View session parameters
SELECT * FROM nls_session_parameters;

-- Check supported functions
SELECT * FROM heliosdb_oracle_functions;

-- View compatibility level
SELECT heliosdb_oracle_compatibility() FROM DUAL;

CONFIGURATION.md - Detailed server and client configuration
COMPATIBILITY.md - Complete feature compatibility matrix
EXAMPLES.md - SQL, PL/SQL, and driver examples
PLSQL_SUPPORT.md - PL/SQL roadmap, conversion patterns and workarounds
Parent Directory - Multi-protocol overview

Support and Resources

Getting Help

Check this documentation for compatibility information
Review EXAMPLES.md for code patterns
Consult PLSQL_SUPPORT.md for PL/SQL conversion guidance
Contact HeliosDB support for complex migration assistance

Version Information

Oracle Protocol Version: TNS 12.2
Target Oracle Compatibility: 23ai (23c)
Compatibility Level: see the compatibility matrix and Oracle compatibility for in-house applications