Oracle Protocol Quick Reference

Quick reference for using HeliosDB Nano’s Oracle TNS/TTC protocol support.

Connecting

SQL*Plus

sqlplus username/password@//localhost:1521/heliosdb

sqlcl

sql username/password@//localhost:1521/heliosdb

JDBC

String url = "jdbc:oracle:thin:@localhost:1521:heliosdb";
Connection conn = DriverManager.getConnection(url, "username", "password");

Python (cx_Oracle)

import cx_Oracle
conn = cx_Oracle.connect("username/password@localhost:1521/heliosdb")

Supported Oracle SQL Translations

Oracle	PostgreSQL	Example
`FROM DUAL`	(removed)	`SELECT 1 FROM DUAL` → `SELECT 1`
`SYSDATE`	`CURRENT_TIMESTAMP`	`SELECT SYSDATE` → `SELECT CURRENT_TIMESTAMP`
`SYSTIMESTAMP`	`CURRENT_TIMESTAMP`	`SELECT SYSTIMESTAMP` → `SELECT CURRENT_TIMESTAMP`
`NVL(a, b)`	`COALESCE(a, b)`	`NVL(col, 0)` → `COALESCE(col, 0)`
`NVL2(a, b, c)`	`CASE WHEN...`	`NVL2(col, 'Y', 'N')` → `CASE WHEN col IS NOT NULL THEN 'Y' ELSE 'N' END`
`DECODE(...)`	`CASE WHEN...`	See examples below
`seq.NEXTVAL`	`nextval('seq')`	`emp_seq.NEXTVAL` → `nextval('emp_seq')`
`ROWNUM <= N`	`LIMIT N`	`WHERE ROWNUM <= 10` → `LIMIT 10`
`TO_DATE(str, fmt)`	`TO_TIMESTAMP(str, fmt)::DATE`	`TO_DATE('2024-01-01', 'YYYY-MM-DD')`
`TO_NUMBER(str)`	`CAST(str AS NUMERIC)`	`TO_NUMBER('123')` → `CAST('123' AS NUMERIC)`

DECODE Examples

Simple DECODE

-- Oracle
SELECT DECODE(status, 1, 'Active', 2, 'Inactive', 'Unknown') FROM users;

-- Translates to
SELECT CASE WHEN status = 1 THEN 'Active'
            WHEN status = 2 THEN 'Inactive'
            ELSE 'Unknown' END FROM users;

DECODE with Default

-- Oracle
SELECT DECODE(grade, 'A', 90, 'B', 80, 'C', 70, 0) FROM students;

-- Translates to
SELECT CASE WHEN grade = 'A' THEN 90
            WHEN grade = 'B' THEN 80
            WHEN grade = 'C' THEN 70
            ELSE 0 END FROM students;

Working Examples

Create Table

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    salary NUMBER,
    hire_date DATE
);

Insert with DUAL

INSERT INTO employees
SELECT 1, 'John Doe', 50000, SYSDATE FROM DUAL;

Query with Oracle Functions

SELECT emp_id,
       name,
       NVL(salary, 0) AS salary,
       DECODE(salary,
              NULL, 'No salary',
              0, 'Zero',
              'Has salary') AS salary_status
FROM employees
WHERE ROWNUM <= 10;

Using Sequences

-- Create sequence
CREATE SEQUENCE emp_seq START WITH 1000;

-- Use in INSERT
INSERT INTO employees (emp_id, name)
VALUES (emp_seq.NEXTVAL, 'Jane Smith');

Data Type Mapping

Oracle	HeliosDB Nano
VARCHAR2(n)	TEXT
NUMBER	NUMERIC
NUMBER(p,s)	NUMERIC
DATE	TIMESTAMP
TIMESTAMP	TIMESTAMP
CHAR(n)	CHAR(n)
CLOB	TEXT
BLOB	BYTEA
RAW	BYTEA

Unsupported Features

These will return errors:

PL/SQL Blocks

-- NOT SUPPORTED
BEGIN
    SELECT 1 INTO v_var FROM DUAL;
END;

Error: “PL/SQL blocks are not supported”

Oracle (+) Outer Join

-- NOT SUPPORTED
SELECT * FROM a, b
WHERE a.id = b.id(+);

Error: “Oracle (+) outer join syntax not supported. Use ANSI JOIN syntax instead.”

Use instead:

SELECT * FROM a
LEFT JOIN b ON a.id = b.id;

Stored Procedures

-- NOT SUPPORTED
EXEC my_procedure(param1);

Error: “Stored procedure execution not yet supported”

Configuration

Enable/Disable Oracle Protocol

[server]
listen_addr = "127.0.0.1"
port = 5432              # PostgreSQL port
oracle_port = 1521       # Oracle port (set to null to disable)
max_connections = 100

Programmatic Configuration

use heliosdb_nano::{Config, ServerConfig};

let config = Config {
    server: ServerConfig {
        oracle_port: Some(1521),  // Enable Oracle protocol
        // oracle_port: None,     // Disable Oracle protocol
        ..Default::default()
    },
    ..Default::default()
};

Troubleshooting

Connection Refused

Symptom: ORA-12541: TNS:no listener

Solutions:

Check server is running
Verify port 1521 is not blocked by firewall
Check oracle_port is enabled in config

Authentication Failed

Symptom: ORA-01017: invalid username/password

Note: Current implementation accepts any credentials. This error typically indicates a connection problem.

Invalid SQL

Symptom: ORA-00900: invalid SQL statement

Solutions:

Check SQL syntax
Verify feature is supported (see unsupported features)
Review translation in logs (if enabled)

Table Not Found

Symptom: ORA-00942: table or view does not exist

Solutions:

Verify table exists: SELECT * FROM user_tables; (if catalog view is implemented)
Check case sensitivity
Ensure table was created successfully

Logging

Enable debug logging to see SQL translation:

RUST_LOG=heliosdb_nano=debug cargo run

Debug output shows:

[DEBUG] Parsing Oracle SQL: SELECT SYSDATE FROM DUAL
[DEBUG] Translated SQL: SELECT CURRENT_TIMESTAMP
[DEBUG] Executing SQL: SELECT CURRENT_TIMESTAMP

Best Practices

Use ANSI SQL When Possible

-- Preferred
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

-- Avoid
SELECT * FROM a, b WHERE a.id = b.id(+);

Explicit Type Conversions

-- Good
SELECT CAST(value AS NUMBER) FROM table;

-- May work but less clear
SELECT TO_NUMBER(value) FROM table;

Avoid Complex DECODE

-- Prefer simple CASE
SELECT CASE
           WHEN status = 1 THEN 'Active'
           ELSE 'Inactive'
       END
FROM users;

-- Over complex DECODE
SELECT DECODE(status, 1, 'Active', 'Inactive') FROM users;

Test Queries
- Test Oracle-specific syntax in development first
- Verify translations produce expected results
- Use EXPLAIN to check query plans

Performance Tips

Limit Result Sets

SELECT * FROM large_table WHERE ROWNUM <= 100;

Use Indexes

CREATE INDEX idx_emp_name ON employees(name);

Batch Inserts

INSERT INTO table VALUES (1, 'a');
INSERT INTO table VALUES (2, 'b');
-- Consider using batch operations when available

Getting Help

Check this reference guide
Review /home/claude/HeliosDB Nano/ORACLE_PROTOCOL_IMPLEMENTATION.md for detailed documentation
Enable debug logging to see SQL translations
Verify feature support in supported features list

Example Session

-- Connect
$ sqlplus helios/password@//localhost:1521/heliosdb

-- Create table
SQL> CREATE TABLE departments (
  2    dept_id NUMBER PRIMARY KEY,
  3    dept_name VARCHAR2(50),
  4    created_date DATE
  5  );
Table created.

-- Insert with Oracle functions
SQL> INSERT INTO departments
  2  VALUES (1, 'Engineering', SYSDATE);
1 row created.

-- Query with Oracle syntax
SQL> SELECT dept_id,
  2         dept_name,
  3         NVL(dept_name, 'Unknown') AS name_safe
  4  FROM departments
  5  WHERE ROWNUM <= 10;

 DEPT_ID DEPT_NAME                NAME_SAFE
-------- ------------------------ ------------------------
       1 Engineering              Engineering

1 row selected.

-- Standard SQL also works
SQL> SELECT * FROM departments WHERE dept_id = 1;

 DEPT_ID DEPT_NAME                CREATED_DATE
-------- ------------------------ -------------------
       1 Engineering              2025-11-20 08:00:00

-- Exit
SQL> exit

Summary

HeliosDB Nano provides Oracle protocol compatibility for common SQL operations. While not supporting all Oracle features (especially PL/SQL), it enables standard SQL queries using Oracle client tools with automatic syntax translation.

Key Points:

Port 1521 (configurable)
Common Oracle functions translated automatically
No PL/SQL support
Use ANSI SQL syntax when possible
Enable debug logging to see translations