---
title: "Oracle 23ai Protocol Documentation"
---

# 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.

:::note[Module and listener]
- **The Oracle compatibility module** implements a large part of Oracle 23ai's SQL
  and PL/SQL feature surface, at the maturity levels in the matrix below.
- **Through the Oracle listener today**, table definitions, `INSERT`/`UPDATE`/`DELETE`,
  joins and basic aggregates run unchanged and return correct results; Oracle-specific
  syntax, most built-in functions and PL/SQL need rewriting. See
  [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)
  for the results by usage area, how they were assessed and how to assess your own
  application.
:::

## Quick Start

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

```python
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](/docs/full/quick-starts/protocols/oracle_quick_start/) lists
what runs over the listener and how to write the rest.

## Contents

| File | Description |
|------|-------------|
| **README.md** | Overview, compatibility matrix, and roadmap (this file) |
| **[In-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)** | 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](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/). 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](/docs/full/migration/oracle-to-heliosdb/).


---

## 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](/docs/full/migration/oracle-to-heliosdb/#step-2-convert-plsql) 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

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

```
# tnsnames.ora
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

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

---

## Migration Guide

### Assessment Checklist

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

### Common Migration Patterns

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

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

---

## Related Documentation

- **[CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/configuration/)** - Detailed server and client configuration
- **[COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/compatibility/)** - Complete feature compatibility matrix
- **[EXAMPLES.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/examples/)** - SQL, PL/SQL, and driver examples
- **[PLSQL_SUPPORT.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/plsql_support/)** - PL/SQL roadmap, conversion patterns and workarounds
- **[Parent Directory](https://www.heliosdb.com/docs/full/reference/protocols/readme/)** - Multi-protocol overview

---

## Support and Resources

### Getting Help

1. Check this documentation for compatibility information
2. Review [EXAMPLES.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/examples/) for code patterns
3. Consult [PLSQL_SUPPORT.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/plsql_support/) for PL/SQL conversion guidance
4. 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](/docs/full/reference/protocols/oracle/compatibility/) and [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)

---