--- title: "Oracle Protocol Quick Reference" --- # Oracle Protocol Quick Reference Quick reference for using HeliosDB Nano's Oracle TNS/TTC protocol support. ## Connecting ### SQL*Plus ```bash sqlplus username/password@//localhost:1521/heliosdb ``` ### sqlcl ```bash sql username/password@//localhost:1521/heliosdb ``` ### JDBC ```java String url = "jdbc:oracle:thin:@localhost:1521:heliosdb"; Connection conn = DriverManager.getConnection(url, "username", "password"); ``` ### Python (cx_Oracle) ```python 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 ```sql -- 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 ```sql -- 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 ```sql CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, name VARCHAR2(100), salary NUMBER, hire_date DATE ); ``` ### Insert with DUAL ```sql INSERT INTO employees SELECT 1, 'John Doe', 50000, SYSDATE FROM DUAL; ``` ### Query with Oracle Functions ```sql 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 ```sql -- 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 ```sql -- NOT SUPPORTED BEGIN SELECT 1 INTO v_var FROM DUAL; END; ``` **Error:** "PL/SQL blocks are not supported" ### Oracle (+) Outer Join ```sql -- 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:** ```sql SELECT * FROM a LEFT JOIN b ON a.id = b.id; ``` ### Stored Procedures ```sql -- NOT SUPPORTED EXEC my_procedure(param1); ``` **Error:** "Stored procedure execution not yet supported" ## Configuration ### Enable/Disable Oracle Protocol ```toml # config.toml [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 ```rust 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:** 1. Check server is running 2. Verify port 1521 is not blocked by firewall 3. 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:** 1. Check SQL syntax 2. Verify feature is supported (see unsupported features) 3. Review translation in logs (if enabled) ### Table Not Found **Symptom:** `ORA-00942: table or view does not exist` **Solutions:** 1. Verify table exists: `SELECT * FROM user_tables;` (if catalog view is implemented) 2. Check case sensitivity 3. Ensure table was created successfully ## Logging Enable debug logging to see SQL translation: ```bash 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 1. **Use ANSI SQL When Possible** ```sql -- Preferred SELECT * FROM a LEFT JOIN b ON a.id = b.id; -- Avoid SELECT * FROM a, b WHERE a.id = b.id(+); ``` 2. **Explicit Type Conversions** ```sql -- Good SELECT CAST(value AS NUMBER) FROM table; -- May work but less clear SELECT TO_NUMBER(value) FROM table; ``` 3. **Avoid Complex DECODE** ```sql -- 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; ``` 4. **Test Queries** - Test Oracle-specific syntax in development first - Verify translations produce expected results - Use `EXPLAIN` to check query plans ## Performance Tips 1. **Limit Result Sets** ```sql SELECT * FROM large_table WHERE ROWNUM <= 100; ``` 2. **Use Indexes** ```sql CREATE INDEX idx_emp_name ON employees(name); ``` 3. **Batch Inserts** ```sql INSERT INTO table VALUES (1, 'a'); INSERT INTO table VALUES (2, 'b'); -- Consider using batch operations when available ``` ## Getting Help 1. Check this reference guide 2. Review `ORACLE_PROTOCOL_IMPLEMENTATION.md` for detailed documentation 3. Enable debug logging to see SQL translations 4. Verify feature support in supported features list ## Example Session ```sql -- 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