Oracle 23ai Protocol Documentation
This directory contains comprehensive documentation for HeliosDB's Oracle 23ai protocol support, SQL compatibility, and PL/SQL implementation.
Overview
HeliosDB implements the Oracle TNS (Transparent Network Substrate) wire protocol, enabling Oracle clients, drivers, and tools to connect directly to HeliosDB. This compatibility layer translates Oracle SQL dialect and PL/SQL constructs to HeliosDB's native execution engine while maintaining semantic compatibility with Oracle 23ai.
Quick Start
Connect to HeliosDB using standard Oracle client tools:
-- SQL*Plus connection
sqlplus admin/password@//localhost:1521/heliosdb
-- Execute Oracle SQL
SELECT SYSDATE FROM DUAL;
-- Use hierarchical queries
SELECT LEVEL, employee_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
-- PL/SQL anonymous block
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello from HeliosDB!');
END;
/
Contents
| File |
Description |
| README.md |
Overview, compatibility matrix, and roadmap (this file) |
| 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 constructs, conversion patterns, and workarounds |
Oracle 23ai Compatibility Matrix
Overall Compatibility: 40-45%
HeliosDB provides foundational Oracle 23ai compatibility, focusing on the most commonly used features in enterprise applications. The compatibility level represents the percentage of Oracle 23ai features that work without modification.
| Feature Category |
Coverage |
Status |
Notes |
| Core SQL |
65% |
Production |
SELECT, INSERT, UPDATE, DELETE, MERGE |
| DDL Statements |
55% |
Production |
CREATE, ALTER, DROP for common objects |
| PL/SQL Core |
40% |
Beta |
Anonymous blocks, procedures, functions |
| PL/SQL Packages |
25% |
Beta |
Package specifications and bodies |
| DBMS Packages |
30% |
Partial |
Core DBMS_* packages only |
| Hierarchical Queries |
70% |
Production |
CONNECT BY, START WITH |
| Cursors |
45% |
Beta |
Explicit cursors, REF CURSOR |
| BULK Operations |
35% |
Beta |
BULK COLLECT, FORALL |
| XMLType |
20% |
Alpha |
Basic operations only |
| JSON Functions |
50% |
Production |
Oracle 21c+ JSON functions |
| Pseudo-columns |
60% |
Production |
ROWNUM, ROWID, LEVEL |
| Analytics |
55% |
Production |
Core window functions |
| Object Types |
15% |
Alpha |
Basic user-defined types |
| Collections |
30% |
Beta |
Nested tables, VARRAYs |
| Triggers |
40% |
Beta |
Row-level and statement-level |
| Sequences |
75% |
Production |
NEXTVAL, CURRVAL |
| Synonyms |
60% |
Production |
Public and private |
| Views |
65% |
Production |
Standard and updatable views |
| Materialized Views |
35% |
Beta |
Basic refresh only |
Compatibility Breakdown by Use Case
| Use Case |
Compatibility |
Recommendation |
| Simple CRUD Applications |
70-80% |
Ready for production |
| Reporting Applications |
55-65% |
Ready with minor modifications |
| ETL Pipelines |
45-55% |
Requires testing and adaptation |
| Complex PL/SQL Applications |
25-35% |
Significant refactoring needed |
| Oracle Forms/APEX |
10-20% |
Not recommended |
| Oracle-specific Tools |
15-25% |
Limited compatibility |
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 Engine
HeliosDB includes a PL/SQL interpreter for executing procedural code:
Supported 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)
Limited Support:
- 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
DBMS Package Support Status
Fully Implemented Packages
| Package |
Coverage |
Key Functions |
| DBMS_OUTPUT |
90% |
PUT_LINE, PUT, NEW_LINE, GET_LINE, ENABLE, DISABLE |
| DBMS_UTILITY |
60% |
FORMAT_ERROR_BACKTRACE, GET_TIME, COMMA_TO_TABLE |
| UTL_FILE |
55% |
FOPEN, FCLOSE, GET_LINE, PUT_LINE, FFLUSH |
Partially Implemented Packages
| Package |
Coverage |
Supported Functions |
Not Supported |
| DBMS_SQL |
40% |
OPEN_CURSOR, PARSE, EXECUTE, CLOSE_CURSOR |
DESCRIBE_COLUMNS, array binds |
| DBMS_LOB |
45% |
GETLENGTH, SUBSTR, INSTR, APPEND |
LOADFROMFILE, BFILE operations |
| DBMS_METADATA |
35% |
GET_DDL (tables, indexes) |
Complex object types |
| DBMS_SCHEDULER |
30% |
CREATE_JOB, DROP_JOB, RUN_JOB |
Complex schedules, chains |
| DBMS_STATS |
25% |
GATHER_TABLE_STATS |
Histogram operations |
| DBMS_RANDOM |
50% |
VALUE, STRING, INITIALIZE |
SEED with complex types |
| DBMS_SESSION |
20% |
SET_IDENTIFIER |
Most functions |
| DBMS_APPLICATION_INFO |
40% |
SET_MODULE, SET_ACTION |
READ functions |
| DBMS_CRYPTO |
15% |
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 |
-- 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 |
Status |
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 |
Status |
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 |
Status |
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 |
Status |
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 |
Status |
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 |
Status |
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, limited PL/SQL |
| DBeaver |
Latest |
Partial |
Via JDBC |
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
- Limit PL/SQL complexity: Simple procedures perform better
Roadmap for Increasing Compatibility
Phase 1: Core Compatibility (Current - 40-45%)
- Basic SQL dialect support
- Core PL/SQL constructs
- Essential DBMS packages
- CONNECT BY hierarchical queries
Phase 2: Enhanced PL/SQL (Target - 55-60%)
- Improved REF CURSOR support
- Full BULK COLLECT implementation
- FORALL with SAVE EXCEPTIONS
- Package state management
- Additional DBMS packages (DBMS_SQL complete, DBMS_LOB complete)
Phase 3: Advanced Features (Target - 65-70%)
- Object type improvements
- Compound triggers
- Pipelined functions
- Edition-based redefinition stubs
- MODEL clause (basic)
Phase 4: Enterprise Features (Target - 75-80%)
- Advanced queuing emulation
- Parallel execution hints
- Result caching
- Extended statistics
- Virtual columns
Not Planned
The following Oracle features are not planned due to architectural differences:
- Oracle RAC (HeliosDB uses native clustering)
- Data Guard (HeliosDB uses native replication)
- Automatic Storage Management (ASM)
- Oracle Label Security
- Oracle Spatial (SDO_* types)
- Java stored procedures
- Oracle Text (full-text search via HeliosDB native)
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 |
| ORA-06550: PL/SQL error |
Unsupported PL/SQL construct |
Simplify or rewrite code |
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;
Support and Resources
Getting Help
- Check this documentation for compatibility information
- Review EXAMPLES.md for working code patterns
- Consult PLSQL_SUPPORT.md for PL/SQL conversion guidance
- Contact HeliosDB support for complex migration assistance
- HeliosDB Version: 7.0+
- Oracle Protocol Version: TNS 12.2
- Target Oracle Compatibility: 23ai (23c)
- Compatibility Level: 40-45%
Last Updated: January 2026
Documentation Status: Comprehensive