Skip to content
HeliosDB Full

Oracle 23ai Protocol Documentation

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

FileDescription
README.mdOverview, compatibility matrix, and roadmap (this file)
CONFIGURATION.mdTNS listener, NLS settings, security, and connection pooling
COMPATIBILITY.mdDetailed SQL, PL/SQL, and DBMS package compatibility
EXAMPLES.mdPractical SQL, PL/SQL, JDBC, and OCI examples
PLSQL_SUPPORT.mdPL/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 CategoryCoverageStatusNotes
Core SQL65%ProductionSELECT, INSERT, UPDATE, DELETE, MERGE
DDL Statements55%ProductionCREATE, ALTER, DROP for common objects
PL/SQL Core40%BetaAnonymous blocks, procedures, functions
PL/SQL Packages25%BetaPackage specifications and bodies
DBMS Packages30%PartialCore DBMS_* packages only
Hierarchical Queries70%ProductionCONNECT BY, START WITH
Cursors45%BetaExplicit cursors, REF CURSOR
BULK Operations35%BetaBULK COLLECT, FORALL
XMLType20%AlphaBasic operations only
JSON Functions50%ProductionOracle 21c+ JSON functions
Pseudo-columns60%ProductionROWNUM, ROWID, LEVEL
Analytics55%ProductionCore window functions
Object Types15%AlphaBasic user-defined types
Collections30%BetaNested tables, VARRAYs
Triggers40%BetaRow-level and statement-level
Sequences75%ProductionNEXTVAL, CURRVAL
Synonyms60%ProductionPublic and private
Views65%ProductionStandard and updatable views
Materialized Views35%BetaBasic refresh only

Compatibility Breakdown by Use Case

Use CaseCompatibilityRecommendation
Simple CRUD Applications70-80%Ready for production
Reporting Applications55-65%Ready with minor modifications
ETL Pipelines45-55%Requires testing and adaptation
Complex PL/SQL Applications25-35%Significant refactoring needed
Oracle Forms/APEX10-20%Not recommended
Oracle-specific Tools15-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

CategorySupportedPartialNot Supported
DMLSELECT, INSERT, UPDATE, DELETE, MERGEINSERT ALLUPSERT (use MERGE)
DDLCREATE/ALTER/DROP TABLE, INDEX, VIEWSEQUENCE, SYNONYMCLUSTER, CONTEXT
QueryJOINs, Subqueries, CTEsCONNECT BY, PIVOTMODEL clause
Set OpsUNION, UNION ALL, INTERSECT, MINUS--
LockingSELECT FOR UPDATELOCK 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

PackageCoverageKey Functions
DBMS_OUTPUT90%PUT_LINE, PUT, NEW_LINE, GET_LINE, ENABLE, DISABLE
DBMS_UTILITY60%FORMAT_ERROR_BACKTRACE, GET_TIME, COMMA_TO_TABLE
UTL_FILE55%FOPEN, FCLOSE, GET_LINE, PUT_LINE, FFLUSH

Partially Implemented Packages

PackageCoverageSupported FunctionsNot Supported
DBMS_SQL40%OPEN_CURSOR, PARSE, EXECUTE, CLOSE_CURSORDESCRIBE_COLUMNS, array binds
DBMS_LOB45%GETLENGTH, SUBSTR, INSTR, APPENDLOADFROMFILE, BFILE operations
DBMS_METADATA35%GET_DDL (tables, indexes)Complex object types
DBMS_SCHEDULER30%CREATE_JOB, DROP_JOB, RUN_JOBComplex schedules, chains
DBMS_STATS25%GATHER_TABLE_STATSHistogram operations
DBMS_RANDOM50%VALUE, STRING, INITIALIZESEED with complex types
DBMS_SESSION20%SET_IDENTIFIERMost functions
DBMS_APPLICATION_INFO40%SET_MODULE, SET_ACTIONREAD functions
DBMS_CRYPTO15%Basic ENCRYPT/DECRYPTAdvanced algorithms

Not Implemented Packages

PackageReasonAlternative
DBMS_AQMessage queuing architecture differsHeliosDB native queuing
DBMS_PIPEInter-session communicationUse tables or external queues
DBMS_ALERTEvent notificationHeliosDB LISTEN/NOTIFY
DBMS_LOCKLock managementStandard transaction isolation
DBMS_PARALLEL_EXECUTEParallel processingHeliosDB native parallelism
DBMS_XADistributed transactionsHeliosDB 2PC support
DBMS_FLASHBACKFlashback architectureHeliosDB MVCC time-travel
UTL_HTTPHTTP operationsExternal services
UTL_SMTPEmail operationsExternal services
UTL_TCPNetwork operationsNot applicable

Connection Parameters

Server Configuration

ParameterDefaultDescription
hostlocalhostServer hostname or IP address
port1521Oracle TNS listener port
service_nameheliosdbOracle service name
sidHELIOSOracle 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

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

FunctionStatusNotes
INSTRSupportedNegative position, nth occurrence
SUBSTRSupportedNegative position, overflow handling
LENGTHSupportedNULL returns NULL
LPAD/RPADSupportedMulti-character padding
LTRIM/RTRIMSupportedMulti-character trim set
TRIMSupportedLEADING, TRAILING, BOTH
TRANSLATESupportedCharacter-by-character replacement
REPLACESupportedString replacement
UPPER/LOWERSupportedCase conversion
INITCAPSupportedTitle case
CONCATSupportedString concatenation
NVLSupportedNULL replacement
NVL2SupportedConditional NULL replacement
DECODESupportedArbitrary pairs with default
COALESCESupportedFirst non-NULL value
REGEXP_LIKESupportedRegular expression matching
REGEXP_REPLACESupportedPattern replacement
REGEXP_SUBSTRSupportedPattern extraction
REGEXP_INSTRPartialBasic patterns only
REGEXP_COUNTPartialBasic patterns only

Numeric Functions

FunctionStatusNotes
ABSSupportedAbsolute value
CEIL/FLOORSupportedRounding
ROUNDSupportedDecimal places
TRUNCSupportedDecimal truncation
MODSupportedModulo operation
POWERSupportedExponentiation
SQRTSupportedSquare root
SIGNSupportedSign determination
GREATESTSupportedNULL propagation
LEASTSupportedNULL propagation
NULLIFSupportedNULL on equality

Date Functions

FunctionStatusNotes
SYSDATESupportedCurrent date
SYSTIMESTAMPSupportedCurrent timestamp with timezone
CURRENT_DATESupportedSession timezone aware
CURRENT_TIMESTAMPSupportedSession timezone aware
ADD_MONTHSSupportedLeap year and month-end handling
MONTHS_BETWEENSupportedFractional results
LAST_DAYSupportedLast day of month
NEXT_DAYSupportedNext weekday
TRUNC (date)SupportedDate truncation
ROUND (date)SupportedDate rounding
EXTRACTSupportedDate part extraction
TO_DATESupportedFormat models
TO_CHAR (date)SupportedFormat models
TO_TIMESTAMPSupportedTimestamp conversion

Aggregate Functions

FunctionStatusNotes
COUNTSupportedIncluding COUNT(*) and COUNT(DISTINCT)
SUMSupportedNumeric summation
AVGSupportedNumeric average
MIN/MAXSupportedAll data types
LISTAGGSupportedString aggregation
MEDIANPartialApproximate in some cases
STDDEVSupportedStandard deviation
VARIANCESupportedStatistical variance

Analytic (Window) Functions

FunctionStatusNotes
ROW_NUMBERSupportedRow numbering
RANKSupportedRanking with gaps
DENSE_RANKSupportedRanking without gaps
NTILESupportedDistribution
LAGSupportedPrevious row value
LEADSupportedNext row value
FIRST_VALUESupportedFirst in window
LAST_VALUESupportedLast in window
NTH_VALUEPartialBasic usage only
KEEPPartialFIRST/LAST only
Windowing clausesSupportedROWS, RANGE

Driver Compatibility

DriverVersionStatusNotes
Oracle JDBC Thin19c, 21c, 23aiTestedPrimary development driver
Oracle JDBC OCI19c, 21cPartialRequires Oracle Client
cx_Oracle (Python)8.xTestedDeprecated, use oracledb
oracledb (Python)1.x, 2.xTestedRecommended for Python
node-oracledb (Node.js)6.xTestedRequires Oracle Client or Thin
godror (Go)0.xPartialBasic operations
ODP.NET Managed21c+Partial.NET Core/5+
ODP.NET Unmanaged19c+LimitedRequires Oracle Client
SQL Developer21c+PartialQuery only, limited PL/SQL
DBeaverLatestPartialVia JDBC

Performance Characteristics

Protocol Overhead

OperationOverheadNotes
Connection Handshake+20-30msTNS negotiation
Authentication1-2 RTTPassword 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. 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

  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

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

IssueCauseSolution
ORA-12154: TNS could not resolveService name not foundCheck tnsnames.ora or use Easy Connect
ORA-01017: Invalid username/passwordAuthentication failureVerify credentials
PLS-00201: Identifier not declaredUnsupported DBMS packageCheck package compatibility
ORA-00904: Invalid identifierUnsupported syntaxReview SQL translation
ORA-06550: PL/SQL errorUnsupported PL/SQL constructSimplify 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

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

Version Information

  • 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