---
title: "Protocol Compatibility Migration Guide"
---

# Protocol Compatibility Migration Guide

## Overview

This guide helps migrate applications from Oracle 23ai or SQL Server 2022 to HeliosDB. For Oracle, HeliosDB Full's Oracle compatibility module covers a large part of Oracle's feature surface, and core data access runs unchanged through the Oracle listener today (see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/) for results by usage area); it is not a drop-in replacement, so plan to test and adapt less common features.

## Oracle to HeliosDB Migration

### Connection String Changes

**Before (Oracle)**
```
jdbc:oracle:thin:@hostname:1521:ORCL
```

**After (HeliosDB with Oracle protocol)**
```
jdbc:oracle:thin:@hostname:1521:heliosdb
```

No application code changes required - HeliosDB speaks Oracle TNS protocol.

### Automatic SQL Translation

HeliosDB automatically translates Oracle-specific SQL to standard SQL:

#### DUAL Table
```sql
-- Oracle syntax (automatically handled)
SELECT SYSDATE FROM DUAL;

-- HeliosDB internal translation
SELECT CURRENT_TIMESTAMP;
```

#### NVL to COALESCE
```sql
-- Oracle syntax (automatically handled)
SELECT NVL(salary, 0) FROM employees;

-- HeliosDB internal translation
SELECT COALESCE(salary, 0) FROM employees;
```

#### DECODE to CASE
```sql
-- Oracle syntax
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
FROM employees;

-- Automatically translated to standard CASE
```

### PL/SQL Migration

#### Cursor Handling

**Oracle PL/SQL**
```sql
DECLARE
  CURSOR emp_cursor IS SELECT * FROM employees WHERE dept_id = 10;
  emp_rec employees%ROWTYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_rec;
    EXIT WHEN emp_cursor%NOTFOUND;
    -- Process record
  END LOOP;
  CLOSE emp_cursor;
END;
```

**HeliosDB Support**: Beta in the Oracle compatibility module. PL/SQL blocks are not executed through the Oracle listener yet. 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. See the [Oracle Quick Start](/docs/full/quick-starts/protocols/oracle_quick_start/#what-runs-over-the-oracle-listener).
- All cursor attributes supported (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN)
- BULK COLLECT supported
- REF CURSOR (weak and strong) supported

#### Exception Handling

**Oracle PL/SQL**
```sql
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE emp_id = 999;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_salary := 0;
  WHEN TOO_MANY_ROWS THEN
    RAISE_APPLICATION_ERROR(-20001, 'Multiple records found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
```

**HeliosDB Support**: Beta in the Oracle compatibility module; like all PL/SQL, not executed through the Oracle listener yet.
- All standard exceptions supported
- Custom exception support
- Exception propagation across blocks

### Hierarchical Queries

**Oracle CONNECT BY**
```sql
SELECT employee_id, manager_id, LEVEL, SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;
```

**HeliosDB Support**: Production in the Oracle compatibility module. Through the Oracle listener, `CONNECT BY` is not accepted yet; use application logic or the PostgreSQL listener (see the [Oracle Quick Start](/docs/full/quick-starts/protocols/oracle_quick_start/#what-runs-over-the-oracle-listener)).
- CONNECT BY PRIOR fully supported
- START WITH clause supported
- NOCYCLE keyword for cycle detection
- LEVEL pseudo-column
- SYS_CONNECT_BY_PATH function
- CONNECT_BY_ROOT operator
- CONNECT_BY_ISLEAF pseudo-column
- ORDER SIBLINGS BY clause

### Advanced Functions

#### String Functions
```sql
-- All fully supported
SELECT
  INSTR('hello world', 'world'),           -- Position 7
  SUBSTR('hello', -3, 3),                  -- 'llo' (negative position)
  LPAD('Page 1', 10, '*.'),               -- '*.*.*Page 1'
  TRANSLATE('2KRW229', '0123456789', '----'), -- Result
FROM DUAL;
```

#### Analytic Functions
```sql
-- Fully supported
SELECT
  employee_id,
  salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
  LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary,
  LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary
FROM employees;
```

### XMLType and JSON

#### XMLType
```sql
-- Create XML
DECLARE
  xml_doc XMLType;
BEGIN
  xml_doc := XMLType('<employees><employee id="1">John</employee></employees>');

  -- Extract value
  SELECT xml_doc.extract('/employees/employee/text()') FROM DUAL;

  -- Check existence
  IF xml_doc.existsNode('/employees/employee[@id="1"]') = 1 THEN
    -- Node exists
  END IF;
END;
```

**HeliosDB Support**: Alpha in the Oracle compatibility module (XML functions); the example is a PL/SQL block, which the Oracle listener does not execute yet.
- XMLType creation and storage
- extract() with XPath
- existsNode() for validation
- Basic XSLT transformation

#### JSON Functions (Oracle 21c+)
```sql
-- JSON_VALUE
SELECT JSON_VALUE('{"name":"John","age":30}', '$.name') AS name;

-- JSON_QUERY
SELECT JSON_QUERY('{"items":[1,2,3]}', '$.items') AS items;

-- JSON_TABLE
SELECT jt.*
FROM JSON_TABLE(
  '[{"id":1,"name":"John"},{"id":2,"name":"Jane"}]',
  '$[*]'
  COLUMNS(
    id NUMBER PATH '$.id',
    name VARCHAR2(50) PATH '$.name'
  )
) jt;
```

**HeliosDB Support**: Production in the Oracle compatibility module (JSON functions); test JSON statements through the Oracle listener before relying on them.

### Migration Checklist

- [ ] Test connection string changes
- [ ] Identify PL/SQL packages usage (check compatibility)
- [ ] Review hierarchical queries with cycles
- [ ] Test exception handling edge cases
- [ ] Validate XMLType usage
- [ ] Check JSON path expressions
- [ ] Test cursor-intensive code
- [ ] Verify ROWNUM behavior in complex queries

---

## SQL Server to HeliosDB Migration

### Connection String Changes

**Before (SQL Server)**
```
Server=hostname;Database=mydb;User Id=sa;Password=***;
```

**After (HeliosDB with TDS protocol)**
```
Server=hostname;Database=mydb;User Id=user;Password=***;
```

No application code changes required - HeliosDB speaks TDS protocol.

### Automatic T-SQL Translation

#### TOP to LIMIT
```sql
-- SQL Server syntax (automatically handled)
SELECT TOP 10 * FROM employees ORDER BY salary DESC;

-- HeliosDB internal translation
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
```

#### Identity Functions

```sql
-- All fully supported
INSERT INTO orders (customer) VALUES ('John');

SELECT @@IDENTITY;        -- Last identity inserted (any table, includes triggers)
SELECT SCOPE_IDENTITY();  -- Last identity in current scope (excludes triggers)
SELECT IDENT_CURRENT('orders'); -- Last identity for specific table

SELECT @@ROWCOUNT;        -- Rows affected by last statement
```

**HeliosDB Support**: 97% compatible
- Correct scope handling for identity functions
- Transaction awareness
- Trigger distinction

### T-SQL Features

#### TRY...CATCH

**SQL Server T-SQL** (fully supported)
```sql
BEGIN TRY
  -- Risky operation
  INSERT INTO orders (customer) VALUES ('John');
  UPDATE inventory SET qty = qty - 1 WHERE product_id = 123;
END TRY
BEGIN CATCH
  -- Error handling
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_MESSAGE() AS ErrorMessage,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine;

  ROLLBACK TRANSACTION;
END CATCH;
```

**HeliosDB Support**: 95% compatible
- Full ERROR_* function support
- Nested TRY...CATCH blocks
- Severity level handling (errors >= 20 terminate connection)
- THROW for re-raising exceptions

#### Temp Tables and Table Variables

```sql
-- Local temp table
CREATE TABLE #temp_orders (
  order_id INT,
  customer VARCHAR(100)
);

-- Global temp table
CREATE TABLE ##global_temp (
  id INT
);

-- Table variable
DECLARE @results TABLE (
  id INT,
  name VARCHAR(100)
);

INSERT INTO @results VALUES (1, 'John');
SELECT * FROM @results;
```

**HeliosDB Support**: 94% compatible
- Local temp tables (#temp) - session scope
- Global temp tables (##temp) - cross-session scope
- Table variables (@table) - batch scope
- Proper scope enforcement

#### OUTPUT Clause

```sql
-- INSERT with OUTPUT
INSERT INTO products (name, price)
OUTPUT INSERTED.product_id, INSERTED.name
VALUES ('Widget', 9.99);

-- UPDATE with OUTPUT
UPDATE inventory
SET qty = qty - 1
OUTPUT DELETED.qty AS old_qty, INSERTED.qty AS new_qty
WHERE product_id = 123;

-- DELETE with OUTPUT INTO
DECLARE @deleted TABLE (product_id INT, name VARCHAR(100));

DELETE FROM products
OUTPUT DELETED.product_id, DELETED.name INTO @deleted
WHERE discontinued = 1;
```

**HeliosDB Support**: 93% compatible
- INSERTED and DELETED pseudo-tables
- OUTPUT INTO table/variable
- Works with triggers (proper isolation)

#### MERGE Statement

```sql
MERGE target t
USING source s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET t.value = s.value
WHEN NOT MATCHED BY TARGET THEN
  INSERT (id, value) VALUES (s.id, s.value)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT $action, INSERTED.*, DELETED.*;
```

**HeliosDB Support**: 92% compatible
- Multiple WHEN clauses
- WHEN NOT MATCHED BY SOURCE
- OUTPUT with $action
- Proper transaction handling

### Common Table Expressions (CTEs)

#### Non-Recursive CTEs
```sql
WITH sales_summary AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, s.total
FROM customers c
JOIN sales_summary s ON c.id = s.customer_id;
```

**HeliosDB Support**: 96% compatible

#### Recursive CTEs
```sql
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT * FROM numbers
OPTION (MAXRECURSION 100);
```

**HeliosDB Support**: 96% compatible
- MAXRECURSION limit (default 100, max 32767)
- Infinite recursion detection
- Proper validation (anchor vs recursive member)

### JSON Support (SQL Server 2016+)

#### JSON Functions
```sql
DECLARE @json NVARCHAR(MAX) = N'{"name":"John","age":30,"active":true}';

-- JSON_VALUE (scalar extraction)
SELECT JSON_VALUE(@json, '$.name') AS name;
SELECT JSON_VALUE(@json, 'lax $.missing') AS missing; -- Returns NULL (lax mode)
SELECT JSON_VALUE(@json, 'strict $.missing') AS error; -- Raises error (strict mode)

-- JSON_QUERY (object/array extraction)
SELECT JSON_QUERY(@json, '$') AS full_object;

-- JSON_MODIFY
SET @json = JSON_MODIFY(@json, '$.age', 31);
SET @json = JSON_MODIFY(@json, 'append $.hobbies', 'reading');

-- ISJSON validation
IF ISJSON(@json) = 1
  PRINT 'Valid JSON';

-- OPENJSON
SELECT *
FROM OPENJSON(@json)
WITH (
  name NVARCHAR(50) '$.name',
  age INT '$.age',
  active BIT '$.active'
);

-- FOR JSON
SELECT customer_id, name, email
FROM customers
FOR JSON PATH, ROOT('customers'), INCLUDE_NULL_VALUES;
```

**HeliosDB Support**: 95% compatible
- lax and strict path modes
- All JSON functions
- FOR JSON AUTO and PATH
- Complex path expressions

### Migration Checklist

- [ ] Test connection string changes
- [ ] Review @@IDENTITY vs SCOPE_IDENTITY usage
- [ ] Check temp table scope requirements
- [ ] Validate TRY...CATCH error handling
- [ ] Test OUTPUT clause behavior
- [ ] Verify MERGE statement logic
- [ ] Check recursive CTE depth limits
- [ ] Test JSON path modes (lax/strict)
- [ ] Review table variable performance
- [ ] Validate transaction handling

---

## Performance Optimization Tips

### Oracle Protocol
1. **Minimize ROWNUM usage**: Convert to LIMIT when possible
2. **Hierarchical queries**: Add NOCYCLE to prevent infinite loops
3. **REF CURSOR**: Use strong-typed when possible for better optimization
4. **Bulk operations**: Use BULK COLLECT for better performance

### SQL Server Protocol
1. **Identity functions**: Prefer SCOPE_IDENTITY() over @@IDENTITY
2. **CTEs**: Set appropriate MAXRECURSION to prevent runaway queries
3. **Table variables**: Use temp tables for large datasets (better statistics)
4. **JSON**: Use strict mode only when necessary (lax mode is faster)

---

## Troubleshooting

### Common Issues

#### Oracle
1. **Cursor already open**: Check for proper OPEN/CLOSE pairing
2. **ORA-01436 (CONNECT BY loop)**: Add NOCYCLE keyword
3. **XMLType parsing errors**: Validate XML well-formedness
4. **PL/SQL block reports success but has no effect**: PL/SQL execution through the Oracle listener is on the roadmap; port the logic to PL/pgSQL functions or the application

#### SQL Server
1. **Severity 20+ errors**: These terminate connection, cannot be caught
2. **MAXRECURSION exceeded**: Increase limit or optimize recursive query
3. **Table variable out of scope**: Ensure usage within same batch
4. **JSON path errors**: Check lax vs strict mode settings

### Getting Help

- Documentation: [Protocol compatibility matrix](/docs/full/reference/protocols/protocol_compatibility_matrix/)
- Community: [Discord](https://discord.gg/yTykuUrFXc)
- Email: support@heliosdb.com

---

## Version Compatibility

| Database | Tested Versions | Compatibility |
|----------|----------------|---------------|
| Oracle | 19c, 21c, 23ai | Core data access over the Oracle listener; see [in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/) |
| SQL Server | 2016, 2019, 2022 | 95%+ |