Oracle Quick Start Guide

Oracle Quick Start Guide

Get started with HeliosDB’s Oracle 23ai protocol compatibility. Connect using familiar Oracle tools and leverage Oracle-specific SQL features including PL/SQL.

Prerequisites

• HeliosDB server running (default TNS port: 1521)
• Oracle client tools (SQL*Plus, SQLcl) or JDBC/oracledb driver
• Basic familiarity with Oracle SQL and PL/SQL

Connection Details

Parameter	Default Value
Host	localhost
Port	1521
Service Name	heliosdb
Protocol	Oracle TNS
PL/SQL	Fully supported

---

5-Minute Getting Started

Step 1: Connect with SQL*Plus

-- Easy connect syntax
sqlplus admin/password@//localhost:1521/heliosdb

-- Test connection
SELECT SYSDATE FROM DUAL;

Step 2: Create a Table

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    salary NUMBER(10,2),
    hire_date DATE,
    manager_id NUMBER
);

CREATE SEQUENCE emp_seq START WITH 1;

Step 3: Insert and Query Data

INSERT INTO employees (employee_id, employee_name, salary, hire_date)
VALUES (emp_seq.NEXTVAL, 'Alice', 75000, SYSDATE);

INSERT INTO employees (employee_id, employee_name, salary, hire_date, manager_id)
VALUES (emp_seq.NEXTVAL, 'Bob', 55000, SYSDATE, 1);

SELECT employee_id, employee_name, salary FROM employees;

---

Connecting with Oracle Clients

SQL*Plus / SQLcl

-- Easy connect
sqlplus admin/password@//localhost:1521/heliosdb

-- With TNS name (requires tnsnames.ora)
sqlplus admin/password@HELIOSDB

-- Execute script
@schema.sql

Python (oracledb)

import oracledb

connection = oracledb.connect(
    user="admin",
    password="password",
    dsn="localhost:1521/heliosdb"
)

cursor = connection.cursor()
cursor.execute("SELECT employee_name, salary FROM employees")
for row in cursor:
    print(f"{row[0]}: ${row[1]:,.2f}")

# Using bind variables
cursor.execute(
    "SELECT * FROM employees WHERE employee_id = :id",
    {"id": 1}
)

Java (JDBC)

import java.sql.*;

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

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT employee_name, salary FROM employees");
while (rs.next()) {
    System.out.println(rs.getString("employee_name") + ": " + rs.getDouble("salary"));
}

// Prepared statement
PreparedStatement pstmt = conn.prepareStatement(
    "INSERT INTO employees (employee_id, employee_name, salary, hire_date) " +
    "VALUES (emp_seq.NEXTVAL, ?, ?, SYSDATE)"
);
pstmt.setString(1, "Charlie");
pstmt.setDouble(2, 65000);
pstmt.executeUpdate();

Node.js (oracledb)

const oracledb = require('oracledb');

const connection = await oracledb.getConnection({
    user: 'admin',
    password: 'password',
    connectString: 'localhost:1521/heliosdb'
});

const result = await connection.execute('SELECT employee_name, salary FROM employees');
result.rows.forEach(row => console.log(`${row[0]}: ${row[1]}`));

---

Basic Schema Creation

Tables and Constraints

CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL,
    location VARCHAR2(100)
);

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100) NOT NULL,
    email VARCHAR2(100) UNIQUE,
    salary NUMBER(10,2) CHECK (salary > 0),
    dept_id NUMBER REFERENCES departments(dept_id),
    hire_date DATE DEFAULT SYSDATE
);

CREATE INDEX emp_dept_idx ON employees(dept_id);

Sequences and Auto-Increment

CREATE SEQUENCE dept_seq START WITH 100 INCREMENT BY 10;
CREATE SEQUENCE emp_seq START WITH 1;

-- Using sequences
INSERT INTO departments VALUES (dept_seq.NEXTVAL, 'Engineering', 'Building A');

---

PL/SQL Basics

Anonymous Block

DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    SELECT employee_name, salary
    INTO v_name, v_salary
    FROM employees
    WHERE employee_id = 1;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name || ', Salary: $' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
END;
/

Stored Procedure

CREATE OR REPLACE PROCEDURE give_raise(
    p_emp_id IN NUMBER,
    p_percent IN NUMBER
) AS
    v_new_salary NUMBER;
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent / 100)
    WHERE employee_id = p_emp_id
    RETURNING salary INTO v_new_salary;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END IF;

    DBMS_OUTPUT.PUT_LINE('New salary: $' || v_new_salary);
    COMMIT;
END;
/

-- Execute procedure
EXEC give_raise(1, 10);

Function

CREATE OR REPLACE FUNCTION get_annual_salary(
    p_emp_id IN NUMBER
) RETURN NUMBER AS
    v_salary NUMBER;
BEGIN
    SELECT salary * 12 INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;

    RETURN v_salary;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END;
/

-- Use function
SELECT employee_name, get_annual_salary(employee_id) AS annual
FROM employees;

---

Oracle-Specific SQL Features

Hierarchical Queries (CONNECT BY)

SELECT LEVEL,
       LPAD(' ', (LEVEL - 1) * 2) || employee_name AS name,
       employee_id,
       manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

DECODE and NVL Functions

SELECT employee_id,
       DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS status_desc,
       NVL(commission, 0) AS commission,
       NVL2(manager_id, 'Has Manager', 'Top Level') AS manager_status
FROM employees;

PIVOT and UNPIVOT

-- PIVOT: Rows to columns
SELECT * FROM (
    SELECT department, quarter, sales
    FROM quarterly_sales
)
PIVOT (SUM(sales) FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4));

Analytic Functions

SELECT employee_id,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

---

Migration Quick Tips

From Oracle to HeliosDB

Oracle Feature	HeliosDB Support	Notes
PL/SQL	95%	Full execution engine
CONNECT BY	93%	Full hierarchical support
DECODE/NVL	100%	All variants
Sequences	100%	NEXTVAL, CURRVAL
DBMS_* Packages	Most	OUTPUT, LOB, SQL, etc.

Connection String Migration

# Oracle
jdbc:oracle:thin:@//oracle-host:1521/ORCL

# HeliosDB (same format!)
jdbc:oracle:thin:@//heliosdb-host:1521/heliosdb

Code Migration Checklist

1. Test PL/SQL procedures in HeliosDB environment
2. Verify DBMS_* package calls are supported
3. Check data type compatibility (NUMBER, VARCHAR2, etc.)
4. Validate hierarchical queries (CONNECT BY)
5. Test application with HeliosDB connection

---

Key Features

Feature	Coverage	Notes
PL/SQL Core	95%	Complete execution engine
CONNECT BY	93%	Full hierarchical support
Oracle Functions	97%	DECODE, NVL, INSTR, etc.
DBMS_OUTPUT	100%	Debug output
DBMS_SQL	100%	Dynamic SQL
JSON Functions	94%	Oracle 21c+ compatible

---

Next Steps

• Oracle Configuration - Connection configuration
• Oracle Compatibility - Oracle 23ai feature compatibility
• Oracle Examples - Advanced SQL/PL/SQL examples
• Protocol Compatibility Matrix - Cross-protocol comparison

---

Last Updated: January 2026