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

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

---

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

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

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, limited PL/SQL
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. Limit PL/SQL complexity: Simple procedures perform better

---

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

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;

---

Related Documentation

• 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 support details and workarounds
• Parent Directory - Multi-protocol overview

---

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%

---