---
title: "Oracle 23ai Compatibility Matrix"
---

# Oracle 23ai Compatibility Matrix

Comprehensive compatibility reference for HeliosDB's Oracle 23ai implementation. This document provides detailed feature-by-feature compatibility information.

## Table of Contents

- [Overall Compatibility Summary](#overall-compatibility-summary)
- [SQL Language Support](#sql-language-support)
- [PL/SQL Support](#plsql-support)
- [DBMS Packages](#dbms-packages)
- [Data Types](#data-types)
- [Built-in Functions](#built-in-functions)
- [Advanced Features](#advanced-features)
- [Known Limitations](#known-limitations)
- [Behavioral Differences](#behavioral-differences)

---

## Overall Compatibility Summary

### Compatibility Level

HeliosDB Full's Oracle compatibility module covers a large part of Oracle's SQL and PL/SQL feature surface, at the maturity levels shown below. Through the Oracle listener today, table definitions, `INSERT`/`UPDATE`/`DELETE` and common queries run unchanged, while 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 results by usage area and how to assess your own application. It is not a drop-in replacement.

:::caution[Module coverage, not listener behaviour]
The tables on this page describe the Oracle compatibility module in the HeliosDB Full
code base. What a client gets through the Oracle listener today was measured feature
by feature: PL/SQL blocks report success without running, `MERGE` reports success
without changing rows, `CONNECT BY`, `PIVOT`, `INSERT ALL`, `RETURNING INTO`,
identity columns and synonyms are refused, and several Oracle functions (`NVL`,
`DECODE`, `INITCAP`, `LPAD`, `TRIM`, `REPLACE`, `SUBSTR`, `ROUND`) return wrong
results. Use [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/)
and the [Oracle Quick Start](/docs/full/quick-starts/protocols/oracle_quick_start/)
to plan, and test every statement your application sends.
:::

| Category | Maturity | Hardened |
|----------|--------|------------------|
| **Core SQL** | Supported | Yes |
| **DDL Statements** | Supported | Yes |
| **DML Statements** | Supported | Yes |
| **PL/SQL Core** | Beta | Limited |
| **PL/SQL Packages** | Beta | No |
| **DBMS Packages** | Partial | Limited |
| **Hierarchical Queries** | Supported | Yes |
| **Analytic Functions** | Supported | Yes |
| **JSON Functions** | Supported | Yes |
| **XML Functions** | Alpha | No |
| **Object Types** | Alpha | No |
| **Collections** | Beta | Limited |
| **Triggers** | Beta | Limited |
| **Large Objects** | Beta | Limited |

---

## SQL Language Support

### Data Definition Language (DDL)

| Statement | Result | Notes |
|-----------|--------|-------|
| CREATE TABLE | Supported | Most options supported |
| CREATE TABLE AS SELECT | Supported | Basic CTAS |
| ALTER TABLE | Supported | Common alterations |
| DROP TABLE | Supported | CASCADE, PURGE |
| TRUNCATE TABLE | Supported | REUSE STORAGE partial |
| CREATE INDEX | Supported | B-tree, basic bitmap |
| CREATE UNIQUE INDEX | Supported | |
| DROP INDEX | Supported | |
| CREATE VIEW | Supported | WITH CHECK OPTION |
| CREATE OR REPLACE VIEW | Supported | |
| DROP VIEW | Supported | |
| CREATE MATERIALIZED VIEW | Partial | Basic refresh only |
| CREATE SEQUENCE | Supported | CACHE, NOCACHE |
| ALTER SEQUENCE | Supported | Basic modifications |
| DROP SEQUENCE | Supported | |
| CREATE SYNONYM | Supported | PUBLIC, PRIVATE |
| DROP SYNONYM | Supported | |
| CREATE TYPE | Partial | Simple object types |
| CREATE PROCEDURE | Supported | See PL/SQL section |
| CREATE FUNCTION | Supported | See PL/SQL section |
| CREATE PACKAGE | Partial | See PL/SQL section |
| CREATE TRIGGER | Partial | Row-level triggers |
| CREATE CLUSTER | Not Supported | |
| CREATE CONTEXT | Not Supported | |
| CREATE DATABASE LINK | Not Supported | |
| CREATE DIRECTORY | Partial | Limited I/O |
| COMMENT ON | Supported | |
| GRANT | Supported | Basic privileges |
| REVOKE | Supported | Basic privileges |

### Data Manipulation Language (DML)

| Statement | Result | Notes |
|-----------|--------|-------|
| SELECT | Supported | See query features |
| INSERT | Supported | Single and multi-row |
| INSERT ALL | Partial | Conditional limited |
| INSERT FIRST | Partial | |
| UPDATE | Supported | |
| DELETE | Supported | |
| MERGE | Supported | Basic merge operations |
| SELECT FOR UPDATE | Supported | NOWAIT, WAIT n |
| LOCK TABLE | Partial | Basic modes |

### Query Features

| Feature | Result | Notes |
|---------|--------|-------|
| Basic SELECT | Supported | |
| WHERE clause | Supported | |
| ORDER BY | Supported | NULLS FIRST/LAST |
| GROUP BY | Supported | |
| GROUP BY ROLLUP | Supported | |
| GROUP BY CUBE | Partial | |
| GROUP BY GROUPING SETS | Partial | |
| HAVING | Supported | |
| DISTINCT | Supported | |
| Table aliases | Supported | |
| Column aliases | Supported | |
| Subqueries | Supported | Correlated supported |
| Scalar subqueries | Supported | |
| WITH clause (CTEs) | Supported | |
| Recursive CTEs | Partial | Basic recursion |
| UNION | Supported | |
| UNION ALL | Supported | |
| INTERSECT | Supported | |
| MINUS | Supported | |
| INNER JOIN | Supported | |
| LEFT/RIGHT OUTER JOIN | Supported | |
| FULL OUTER JOIN | Supported | |
| CROSS JOIN | Supported | |
| NATURAL JOIN | Partial | |
| USING clause | Supported | |
| Oracle (+) syntax | Partial | Prefer ANSI joins |
| FETCH FIRST n ROWS | Supported | Oracle 12c+ |
| OFFSET n ROWS | Supported | |
| ROWNUM | Supported | See limitations |

### Hierarchical Query Features

| Feature | Result | Notes |
|---------|--------|-------|
| CONNECT BY | Supported | |
| START WITH | Supported | |
| PRIOR | Supported | |
| LEVEL | Supported | |
| CONNECT_BY_ROOT | Supported | |
| SYS_CONNECT_BY_PATH | Supported | |
| CONNECT_BY_ISLEAF | Supported | |
| CONNECT_BY_ISCYCLE | Partial | |
| NOCYCLE | Supported | |
| ORDER SIBLINGS BY | Partial | |

### Advanced Query Features

| Feature | Result | Notes |
|---------|--------|-------|
| PIVOT | Partial | Static pivot |
| UNPIVOT | Partial | |
| MODEL clause | Not Supported | Planned |
| MATCH_RECOGNIZE | Not Supported | |
| Flashback queries | Partial | AS OF timestamp |
| SAMPLE clause | Partial | |
| XMLTABLE | Partial | |
| JSON_TABLE | Supported | |
| Lateral inline views | Partial | |
| CROSS APPLY | Partial | |
| OUTER APPLY | Partial | |

---

## PL/SQL Support

:::note[PL/SQL roadmap]
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.
The tables in this section describe the PL/SQL coverage of the Oracle compatibility module, which is the planned scope for the Oracle listener.
:::

### Language Constructs

| Construct | Result | Notes |
|-----------|--------|-------|
| Anonymous blocks | Supported | DECLARE/BEGIN/END |
| Variables | Supported | Most scalar types |
| Constants | Supported | |
| %TYPE | Supported | |
| %ROWTYPE | Supported | |
| IF-THEN-ELSE | Supported | |
| CASE statement | Supported | Simple and searched |
| Simple LOOP | Supported | |
| WHILE LOOP | Supported | |
| FOR LOOP | Supported | Numeric ranges |
| Cursor FOR LOOP | Supported | |
| EXIT/EXIT WHEN | Supported | |
| CONTINUE | Supported | |
| GOTO | Partial | Limited support |
| NULL statement | Supported | |
| RETURN | Supported | |
| Nested blocks | Supported | |
| Labels | Partial | |

### Procedures and Functions

| Feature | Result | Notes |
|---------|--------|-------|
| CREATE PROCEDURE | Supported | |
| CREATE FUNCTION | Supported | |
| CREATE OR REPLACE | Supported | |
| IN parameters | Supported | |
| OUT parameters | Supported | |
| IN OUT parameters | Partial | |
| DEFAULT values | Supported | |
| NOCOPY hint | Ignored | Syntax accepted |
| DETERMINISTIC | Partial | Hint only |
| RESULT_CACHE | Not Supported | |
| AUTHID CURRENT_USER | Partial | |
| AUTHID DEFINER | Partial | |
| Named notation | Supported | |
| Positional notation | Supported | |
| Mixed notation | Partial | |

### Packages

| Feature | Result | Notes |
|---------|--------|-------|
| Package specification | Partial | |
| Package body | Partial | |
| Package variables | Partial | Session state limited |
| Package constants | Partial | |
| Package types | Partial | |
| Package cursors | Partial | |
| Package initialization | Partial | |
| Overloading | Partial | |
| PRAGMA SERIALLY_REUSABLE | Not Supported | |
| Package state | Limited | Per-session state |

### Cursors

| Feature | Result | Notes |
|---------|--------|-------|
| Explicit cursors | Supported | |
| OPEN | Supported | |
| FETCH | Supported | |
| CLOSE | Supported | |
| %FOUND | Supported | |
| %NOTFOUND | Supported | |
| %ROWCOUNT | Supported | |
| %ISOPEN | Supported | |
| Cursor parameters | Partial | |
| REF CURSOR | Partial | Weak cursors |
| SYS_REFCURSOR | Partial | |
| Cursor variables | Partial | |
| Cursor expressions | Partial | |
| Implicit cursors | Supported | SQL%* attributes |

### Exception Handling

| Feature | Result | Notes |
|---------|--------|-------|
| EXCEPTION block | Supported | |
| WHEN...THEN | Supported | |
| WHEN OTHERS | Supported | |
| NO_DATA_FOUND | Supported | |
| TOO_MANY_ROWS | Supported | |
| DUP_VAL_ON_INDEX | Supported | |
| INVALID_CURSOR | Supported | |
| CURSOR_ALREADY_OPEN | Supported | |
| VALUE_ERROR | Supported | |
| ZERO_DIVIDE | Supported | |
| INVALID_NUMBER | Supported | |
| LOGIN_DENIED | Partial | |
| PROGRAM_ERROR | Partial | |
| SQLCODE | Supported | |
| SQLERRM | Supported | |
| RAISE | Supported | |
| RAISE_APPLICATION_ERROR | Supported | |
| User-defined exceptions | Supported | |
| EXCEPTION_INIT | Partial | |

### Bulk Operations

| Feature | Result | Notes |
|---------|--------|-------|
| BULK COLLECT | Partial | Simple cases |
| BULK COLLECT LIMIT | Partial | |
| FORALL | Partial | |
| FORALL INDICES OF | Partial | |
| FORALL VALUES OF | Partial | |
| SAVE EXCEPTIONS | Partial | |
| SQL%BULK_EXCEPTIONS | Partial | |
| SQL%BULK_ROWCOUNT | Partial | |

### Collections

| Feature | Result | Notes |
|---------|--------|-------|
| Nested tables | Partial | |
| VARRAYs | Partial | |
| Associative arrays | Partial | |
| INDEX BY PLS_INTEGER | Supported | |
| INDEX BY VARCHAR2 | Partial | |
| Collection methods | Partial | COUNT, FIRST, LAST |
| EXISTS | Partial | |
| COUNT | Partial | |
| FIRST/LAST | Partial | |
| NEXT/PRIOR | Partial | |
| EXTEND | Partial | |
| TRIM | Partial | |
| DELETE | Partial | |
| Nested table operators | Not Supported | |

### Triggers

| Feature | Result | Notes |
|---------|--------|-------|
| BEFORE INSERT | Supported | |
| AFTER INSERT | Supported | |
| BEFORE UPDATE | Supported | |
| AFTER UPDATE | Supported | |
| BEFORE DELETE | Supported | |
| AFTER DELETE | Supported | |
| FOR EACH ROW | Supported | |
| Statement-level | Partial | |
|:new/:old | Supported | |
| WHEN clause | Partial | |
| INSTEAD OF | Partial | Views only |
| COMPOUND triggers | Not Supported | |
| System triggers | Not Supported | |
| DDL triggers | Not Supported | |

### Advanced PL/SQL

| Feature | Result | Notes |
|---------|--------|-------|
| Autonomous transactions | Not Supported | Planned |
| Pipelined functions | Not Supported | |
| Parallel enable | Not Supported | |
| PRAGMA RESTRICT_REFERENCES | Ignored | |
| PRAGMA INLINE | Ignored | |
| Native compilation | Not Supported | |
| Conditional compilation | Partial | |
| Object types in PL/SQL | Partial | |
| Dynamic SQL (EXECUTE IMMEDIATE) | Partial | |
| DBMS_SQL | Partial | See package section |

---

## DBMS Packages

### DBMS_OUTPUT

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| PUT_LINE | Supported | Full |
| PUT | Supported | Full |
| NEW_LINE | Supported | Full |
| GET_LINE | Supported | |
| GET_LINES | Partial | |
| ENABLE | Supported | |
| DISABLE | Supported | |

### DBMS_LOB

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| GETLENGTH | Supported | |
| SUBSTR | Supported | |
| INSTR | Supported | |
| APPEND | Supported | |
| COPY | Partial | |
| ERASE | Partial | |
| TRIM | Partial | |
| WRITE | Partial | |
| READ | Partial | |
| COMPARE | Partial | |
| CREATETEMPORARY | Partial | |
| FREETEMPORARY | Partial | |
| ISTEMPORARY | Partial | |
| LOADFROMFILE | Not Supported | |
| LOADBLOBFROMFILE | Not Supported | |
| LOADCLOBFROMFILE | Not Supported | |
| CONVERTTOBLOB | Partial | |
| CONVERTTOCLOB | Partial | |

### DBMS_SQL

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| OPEN_CURSOR | Supported | |
| PARSE | Supported | |
| BIND_VARIABLE | Partial | Scalar types |
| BIND_ARRAY | Not Supported | |
| DEFINE_COLUMN | Partial | |
| EXECUTE | Supported | |
| EXECUTE_AND_FETCH | Partial | |
| FETCH_ROWS | Partial | |
| COLUMN_VALUE | Partial | |
| VARIABLE_VALUE | Partial | |
| CLOSE_CURSOR | Supported | |
| IS_OPEN | Supported | |
| LAST_ROW_COUNT | Partial | |
| LAST_ERROR_POSITION | Partial | |
| DESCRIBE_COLUMNS | Not Supported | |
| DESCRIBE_COLUMNS2 | Not Supported | |
| TO_REFCURSOR | Not Supported | |
| TO_CURSOR_NUMBER | Not Supported | |

### DBMS_UTILITY

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| FORMAT_ERROR_BACKTRACE | Supported | |
| FORMAT_ERROR_STACK | Supported | |
| FORMAT_CALL_STACK | Partial | |
| GET_TIME | Supported | Centiseconds |
| GET_CPU_TIME | Partial | |
| COMMA_TO_TABLE | Supported | |
| TABLE_TO_COMMA | Supported | |
| NAME_RESOLVE | Partial | |
| NAME_TOKENIZE | Partial | |
| DB_VERSION | Partial | |
| CURRENT_INSTANCE | Partial | |
| EXEC_DDL_STATEMENT | Partial | |
| COMPILE_SCHEMA | Not Supported | |
| ANALYZE_SCHEMA | Not Supported | |

### DBMS_METADATA

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| GET_DDL | Partial | Tables, indexes |
| GET_DEPENDENT_DDL | Partial | |
| SET_TRANSFORM_PARAM | Partial | |
| OPEN | Partial | |
| SET_FILTER | Partial | |
| SET_COUNT | Partial | |
| FETCH_DDL | Partial | |
| CLOSE | Partial | |

### DBMS_SCHEDULER

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| CREATE_JOB | Partial | Basic jobs |
| DROP_JOB | Supported | |
| ENABLE | Supported | |
| DISABLE | Supported | |
| RUN_JOB | Supported | |
| STOP_JOB | Supported | |
| CREATE_SCHEDULE | Partial | |
| DROP_SCHEDULE | Partial | |
| CREATE_PROGRAM | Partial | |
| DROP_PROGRAM | Partial | |
| CREATE_CHAIN | Not Supported | |
| CREATE_WINDOW | Not Supported | |
| SET_ATTRIBUTE | Partial | |

### DBMS_STATS

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| GATHER_TABLE_STATS | Partial | Basic statistics |
| GATHER_INDEX_STATS | Partial | |
| GATHER_SCHEMA_STATS | Partial | |
| GATHER_DATABASE_STATS | Not Supported | |
| DELETE_TABLE_STATS | Partial | |
| DELETE_INDEX_STATS | Partial | |
| SET_TABLE_STATS | Partial | |
| GET_TABLE_STATS | Partial | |
| CREATE_STAT_TABLE | Not Supported | |
| EXPORT_TABLE_STATS | Not Supported | |
| IMPORT_TABLE_STATS | Not Supported | |

### UTL_FILE

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| FOPEN | Partial | Limited modes |
| FCLOSE | Supported | |
| FCLOSE_ALL | Supported | |
| GET_LINE | Supported | |
| PUT | Supported | |
| PUT_LINE | Supported | |
| NEW_LINE | Supported | |
| FFLUSH | Supported | |
| FSEEK | Not Supported | |
| FGETPOS | Not Supported | |
| IS_OPEN | Partial | |
| FCOPY | Not Supported | |
| FGETATTR | Not Supported | |
| FREMOVE | Partial | |
| FRENAME | Partial | |

### DBMS_RANDOM

| Function/Procedure | Result | Notes |
|-------------------|--------|-------|
| INITIALIZE | Supported | |
| SEED | Partial | |
| RANDOM | Partial | |
| VALUE | Supported | |
| VALUE (range) | Supported | |
| STRING | Supported | |
| NORMAL | Partial | |
| TERMINATE | Supported | |

### Other Packages

| Package | Result | Notes |
|---------|--------|-------|
| DBMS_SESSION | Partial | SET_IDENTIFIER only |
| DBMS_APPLICATION_INFO | Partial | SET_MODULE, SET_ACTION |
| DBMS_CRYPTO | Partial | Basic encryption |
| DBMS_LOCK | Not Supported | |
| DBMS_PIPE | Not Supported | |
| DBMS_ALERT | Not Supported | |
| DBMS_AQ | Not Supported | |
| DBMS_AQADM | Not Supported | |
| DBMS_JOB | Deprecated | Use DBMS_SCHEDULER |
| DBMS_PARALLEL_EXECUTE | Not Supported | |
| DBMS_FLASHBACK | Not Supported | Use MVCC |
| DBMS_REDEFINITION | Not Supported | |
| DBMS_XA | Not Supported | |
| UTL_HTTP | Not Supported | |
| UTL_SMTP | Not Supported | |
| UTL_TCP | Not Supported | |
| UTL_RAW | Partial | |
| UTL_ENCODE | Partial | |

---

## Data Types

### Scalar Data Types

| Type | Result | Notes |
|------|--------|-------|
| NUMBER | Supported | Full precision |
| NUMBER(p) | Supported | |
| NUMBER(p,s) | Supported | |
| INTEGER | Supported | Maps to NUMBER(38) |
| SMALLINT | Supported | |
| DECIMAL | Supported | |
| FLOAT | Supported | |
| BINARY_FLOAT | Supported | IEEE 754 |
| BINARY_DOUBLE | Supported | IEEE 754 |
| VARCHAR2(n) | Supported | Up to 32767 |
| NVARCHAR2(n) | Supported | Unicode |
| CHAR(n) | Supported | Fixed length |
| NCHAR(n) | Supported | Unicode |
| CLOB | Supported | |
| NCLOB | Supported | Unicode |
| BLOB | Supported | |
| BFILE | Partial | Read-only |
| RAW(n) | Supported | |
| LONG | Supported | Deprecated |
| LONG RAW | Supported | Deprecated |
| DATE | Supported | |
| TIMESTAMP | Supported | |
| TIMESTAMP WITH TIME ZONE | Supported | |
| TIMESTAMP WITH LOCAL TIME ZONE | Partial | |
| INTERVAL YEAR TO MONTH | Supported | |
| INTERVAL DAY TO SECOND | Supported | |
| ROWID | Supported | Different format |
| UROWID | Partial | |
| BOOLEAN (PL/SQL) | Supported | |
| PLS_INTEGER | Supported | |
| BINARY_INTEGER | Supported | |
| SIMPLE_INTEGER | Partial | |
| SIMPLE_FLOAT | Partial | |
| SIMPLE_DOUBLE | Partial | |

### Complex Types

| Type | Result | Notes |
|------|--------|-------|
| Object types | Partial | Simple structures |
| Nested tables | Partial | |
| VARRAYs | Partial | |
| REF types | Not Supported | |
| Opaque types | Not Supported | |

### XML Types

| Type | Result | Notes |
|------|--------|-------|
| XMLType | Partial | Basic operations |
| createXML | Partial | |
| extract | Partial | XPath |
| extractValue | Partial | |
| existsNode | Partial | |
| XMLQuery | Partial | |
| XMLTable | Partial | |
| XMLELEMENT | Partial | |
| XMLFOREST | Partial | |
| XMLAGG | Partial | |

### JSON Types

| Type | Result | Notes |
|------|--------|-------|
| JSON | Supported | Native JSON type |
| JSON_VALUE | Supported | |
| JSON_QUERY | Supported | |
| JSON_TABLE | Supported | |
| JSON_EXISTS | Supported | |
| JSON_OBJECT | Supported | |
| JSON_ARRAY | Supported | |
| JSON_OBJECTAGG | Supported | |
| JSON_ARRAYAGG | Supported | |
| IS JSON | Supported | |
| JSON_SERIALIZE | Partial | |
| JSON_MERGEPATCH | Partial | |
| JSON_TRANSFORM | Not Supported | |

---

## Built-in Functions

### String Functions

| Function | Result | Oracle Behavior Preserved |
|----------|--------|--------------------------|
| CONCAT | Supported | Yes |
| SUBSTR | Supported | Yes (negative position) |
| LENGTH | Supported | Yes |
| INSTR | Supported | Yes (nth occurrence) |
| UPPER | Supported | Yes |
| LOWER | Supported | Yes |
| INITCAP | Supported | Yes |
| LPAD | Supported | Yes |
| RPAD | Supported | Yes |
| LTRIM | Supported | Yes (trim set) |
| RTRIM | Supported | Yes (trim set) |
| TRIM | Supported | Yes |
| REPLACE | Supported | Yes |
| TRANSLATE | Supported | Yes |
| REVERSE | Supported | Yes |
| ASCII | Supported | Yes |
| CHR | Supported | Yes |
| SOUNDEX | Partial | Approximate |
| REGEXP_LIKE | Supported | Most patterns |
| REGEXP_REPLACE | Supported | Most patterns |
| REGEXP_SUBSTR | Supported | Most patterns |
| REGEXP_INSTR | Partial | Basic patterns |
| REGEXP_COUNT | Partial | Basic patterns |
| NVL | Supported | Yes |
| NVL2 | Supported | Yes |
| DECODE | Supported | Yes |
| COALESCE | Supported | Yes |
| NULLIF | Supported | Yes |
| LNNVL | Partial | |
| NANVL | Partial | |

### Numeric Functions

| Function | Result | Notes |
|----------|--------|-------|
| ABS | Supported | |
| CEIL | Supported | |
| FLOOR | Supported | |
| ROUND | Supported | |
| TRUNC | Supported | |
| MOD | Supported | |
| POWER | Supported | |
| SQRT | Supported | |
| EXP | Supported | |
| LN | Supported | |
| LOG | Supported | |
| SIGN | Supported | |
| SIN, COS, TAN | Supported | |
| ASIN, ACOS, ATAN | Supported | |
| SINH, COSH, TANH | Supported | |
| GREATEST | Supported | NULL propagation |
| LEAST | Supported | NULL propagation |
| WIDTH_BUCKET | Partial | |
| REMAINDER | Supported | |
| BITAND | Supported | |

### Date/Time Functions

| Function | Result | Notes |
|----------|--------|-------|
| SYSDATE | Supported | |
| SYSTIMESTAMP | Supported | |
| CURRENT_DATE | Supported | Session TZ |
| CURRENT_TIMESTAMP | Supported | Session TZ |
| LOCALTIMESTAMP | Supported | |
| DBTIMEZONE | Partial | |
| SESSIONTIMEZONE | Supported | |
| ADD_MONTHS | Supported | Month-end handling |
| MONTHS_BETWEEN | Supported | Fractional |
| LAST_DAY | Supported | |
| NEXT_DAY | Supported | |
| ROUND (date) | Supported | Format models |
| TRUNC (date) | Supported | Format models |
| EXTRACT | Supported | |
| TO_DATE | Supported | Format models |
| TO_CHAR | Supported | Format models |
| TO_TIMESTAMP | Supported | |
| TO_TIMESTAMP_TZ | Partial | |
| TO_DSINTERVAL | Partial | |
| TO_YMINTERVAL | Partial | |
| FROM_TZ | Partial | |
| NEW_TIME | Partial | |
| TZ_OFFSET | Partial | |

### Aggregate Functions

| Function | Result | Notes |
|----------|--------|-------|
| COUNT | Supported | Including DISTINCT |
| SUM | Supported | |
| AVG | Supported | |
| MIN | Supported | |
| MAX | Supported | |
| LISTAGG | Supported | |
| MEDIAN | Partial | |
| PERCENTILE_CONT | Partial | |
| PERCENTILE_DISC | Partial | |
| STDDEV | Supported | |
| STDDEV_POP | Supported | |
| STDDEV_SAMP | Supported | |
| VARIANCE | Supported | |
| VAR_POP | Supported | |
| VAR_SAMP | Supported | |
| COVAR_POP | Partial | |
| COVAR_SAMP | Partial | |
| CORR | Partial | |
| REGR_* | Partial | Basic regression |
| STATS_* | Not Supported | |
| COLLECT | Partial | |
| XMLAGG | Partial | |

### Analytic Functions

| Function | Result | Notes |
|----------|--------|-------|
| ROW_NUMBER | Supported | |
| RANK | Supported | |
| DENSE_RANK | Supported | |
| NTILE | Supported | |
| LAG | Supported | |
| LEAD | Supported | |
| FIRST_VALUE | Supported | |
| LAST_VALUE | Supported | |
| NTH_VALUE | Partial | |
| CUME_DIST | Partial | |
| PERCENT_RANK | Partial | |
| RATIO_TO_REPORT | Partial | |
| FIRST/LAST | Partial | |
| KEEP | Partial | FIRST/LAST only |
| OVER clause | Supported | |
| PARTITION BY | Supported | |
| ORDER BY | Supported | |
| ROWS/RANGE | Supported | |
| UNBOUNDED | Supported | |
| CURRENT ROW | Supported | |

### Conversion Functions

| Function | Result | Notes |
|----------|--------|-------|
| TO_CHAR | Supported | Most formats |
| TO_NUMBER | Supported | |
| TO_DATE | Supported | Most formats |
| TO_TIMESTAMP | Supported | |
| TO_CLOB | Partial | |
| TO_BLOB | Partial | |
| TO_LOB | Partial | |
| CAST | Supported | Most conversions |
| CONVERT | Partial | Character sets |
| HEXTORAW | Supported | |
| RAWTOHEX | Supported | |
| ROWIDTOCHAR | Partial | |
| CHARTOROWID | Partial | |

---

## Advanced Features

### Flashback Support

| Feature | Result | Notes |
|---------|--------|-------|
| AS OF TIMESTAMP | Partial | Uses MVCC |
| AS OF SCN | Not Supported | |
| VERSIONS BETWEEN | Not Supported | |
| Flashback Table | Not Supported | |
| Flashback Drop | Not Supported | |
| Flashback Database | Not Supported | |

### Partitioning

| Feature | Result | Notes |
|---------|--------|-------|
| Range partitioning | Partial | Basic |
| List partitioning | Partial | Basic |
| Hash partitioning | Partial | Basic |
| Composite partitioning | Not Supported | |
| Interval partitioning | Not Supported | |
| Reference partitioning | Not Supported | |
| Partition pruning | Partial | |

### Advanced Security

| Feature | Result | Notes |
|---------|--------|-------|
| Virtual Private Database | Not Supported | |
| Label Security | Not Supported | |
| Transparent Data Encryption | Not Supported | Use HeliosDB encryption |
| Data Masking | Not Supported | |
| Database Vault | Not Supported | |
| Audit Vault | Not Supported | |

---

## Known Limitations

### Not Supported Features

| Feature | Alternative |
|---------|-------------|
| Oracle RAC | HeliosDB native clustering |
| Data Guard | HeliosDB replication |
| ASM | Standard filesystem storage |
| Streams/GoldenGate | HeliosDB CDC (connectors planned) |
| Oracle Spatial (SDO_*) | Not available |
| Oracle Text | HeliosDB full-text search |
| Java stored procedures | Use PL/pgSQL or external |
| APEX | Web UI alternatives |
| Oracle Forms | Web UI alternatives |

### SQL Limitations

1. **ROWNUM**: Works in simple cases but complex ROWNUM usage may behave differently
2. **MODEL clause**: Not supported; use application logic
3. **MATCH_RECOGNIZE**: Pattern matching not available
4. **CONNECT BY**: Limited to 1000 levels by default
5. **Recursive CTEs**: Basic recursion only

### PL/SQL Limitations

These apply to the planned PL/SQL support (see [PL/SQL Support](#plsql-support)):

1. **Autonomous transactions**: Not supported
2. **Pipelined functions**: Not supported
3. **Package state**: Limited session state management
4. **Native compilation**: Interpreted only
5. **Complex object types**: Basic structures only

---

## Behavioral Differences

### NULL Handling

- Oracle empty strings are treated as NULL; HeliosDB distinguishes empty strings from NULL
- Use NVL() or COALESCE() for consistent behavior

### ROWID Format

- HeliosDB uses a different internal ROWID format
- ROWID values are not portable between systems

### Sequence Behavior

- Caching behavior may differ slightly
- CURRVAL requires NEXTVAL to be called first in the same session

### Transaction Isolation

- Uses HeliosDB's MVCC implementation
- Snapshot isolation is the default

### Date Arithmetic

- Oracle allows direct date + number operations
- Some edge cases with month-end dates may differ

---

## Related Documentation

- **[README.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/readme/)** - Protocol overview
- **[CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/configuration/)** - Server configuration
- **[EXAMPLES.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/examples/)** - Working examples
- **[PLSQL_SUPPORT.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/plsql_support/)** - PL/SQL details

---

**Compatibility target**: Oracle 23ai