Skip to content
HeliosDB HeliosDB Full

Oracle SQL and PL/SQL Examples

Oracle SQL and PL/SQL Examples

Practical examples for HeliosDB’s Oracle 23ai compatibility, including connection examples, SQL queries, PL/SQL programs, and driver-specific code.

Table of Contents

Connection Examples

SQL*Plus Connections

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


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


-- Connect as SYSDBA role
sqlplus admin/password@//localhost:1521/heliosdb AS SYSDBA


-- Connect with SSL/TLS
sqlplus admin/password@tcps://localhost:2484/heliosdb


-- Silent mode for scripts
sqlplus -s admin/password@//localhost:1521/heliosdb @script.sql

Connection Verification

-- Verify connection
SELECT USER, SYS_CONTEXT('USERENV', 'DB_NAME') AS database,
       SYS_CONTEXT('USERENV', 'HOST') AS host,
       SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service
FROM DUAL;


-- Check Oracle compatibility version
SELECT * FROM v$version;


-- View NLS parameters
SELECT * FROM nls_session_parameters ORDER BY parameter;


-- Check current schema
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;

SQL Examples

Basic CRUD Operations

-- Create table with Oracle-specific features
CREATE TABLE employees (
    employee_id    NUMBER(10) PRIMARY KEY,
    first_name     VARCHAR2(50) NOT NULL,
    last_name      VARCHAR2(50) NOT NULL,
    email          VARCHAR2(100) UNIQUE,
    hire_date      DATE DEFAULT SYSDATE,
    salary         NUMBER(10,2) CHECK (salary > 0),
    department_id  NUMBER(5),
    manager_id     NUMBER(10),
    created_at     TIMESTAMP DEFAULT SYSTIMESTAMP
);


-- Create sequence
CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 1 CACHE 20;


-- Insert with sequence
INSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id)
VALUES (emp_seq.NEXTVAL, 'John', 'Smith', 'jsmith@example.com', 75000, 10);


-- Insert multiple rows
INSERT ALL
    INTO employees VALUES (emp_seq.NEXTVAL, 'Jane', 'Doe', 'jdoe@example.com', SYSDATE, 80000, 20, NULL, SYSTIMESTAMP)
    INTO employees VALUES (emp_seq.NEXTVAL, 'Bob', 'Johnson', 'bjohnson@example.com', SYSDATE, 65000, 10, NULL, SYSTIMESTAMP)
SELECT * FROM DUAL;


-- Update with RETURNING clause
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 10
RETURNING employee_id, salary INTO :emp_id, :new_salary;


-- MERGE statement (upsert)
MERGE INTO employees e
USING (SELECT 1001 AS emp_id, 'Updated' AS first_name FROM DUAL) src
ON (e.employee_id = src.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.first_name = src.first_name
WHEN NOT MATCHED THEN
    INSERT (employee_id, first_name, last_name, email, salary)
    VALUES (src.emp_id, src.first_name, 'New', 'new@example.com', 50000);


-- Delete with subquery
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id FROM departments WHERE location = 'Closed'
);

Oracle Functions

-- String functions with Oracle-specific behavior
SELECT
    INSTR('Hello World', 'o', 1, 2) AS second_o,        -- 8 (2nd occurrence)
    SUBSTR('Hello World', -5) AS last_five,              -- 'World'
    SUBSTR('Hello World', 1, 5) AS first_five,           -- 'Hello'
    LPAD('123', 10, '0') AS padded,                      -- '0000000123'
    TRANSLATE('Hello', 'el', 'ip') AS translated,        -- 'Hippo'
    REPLACE('Hello World', 'World', 'Oracle') AS replaced
FROM DUAL;


-- DECODE and NVL examples
SELECT
    employee_id,
    DECODE(status, 'A', 'Active', 'I', 'Inactive', 'T', 'Terminated', 'Unknown') AS status_desc,
    NVL(commission, 0) AS commission,
    NVL2(manager_id, 'Has Manager', 'Top Level') AS manager_status,
    COALESCE(bonus, commission, 0) AS total_bonus
FROM employees;


-- Date functions
SELECT
    SYSDATE AS current_date,
    SYSTIMESTAMP AS current_timestamp,
    ADD_MONTHS(SYSDATE, 3) AS three_months_later,
    MONTHS_BETWEEN(DATE '2024-06-15', DATE '2024-01-10') AS months_diff,
    LAST_DAY(SYSDATE) AS end_of_month,
    NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday,
    TRUNC(SYSDATE, 'MM') AS first_of_month,
    TO_CHAR(SYSDATE, 'Day, DD Month YYYY HH24:MI:SS') AS formatted_date
FROM DUAL;


-- CASE expression
SELECT
    employee_id,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 75000 THEN 'Senior'
        WHEN salary >= 50000 THEN 'Mid-Level'
        ELSE 'Junior'
    END AS salary_grade
FROM employees;

Analytic Functions

-- Ranking functions
SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
    RANK() OVER (ORDER BY salary DESC) AS rank_with_gaps,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;


-- Running totals and moving averages
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3,
    SUM(amount) OVER (PARTITION BY TRUNC(order_date, 'MM') ORDER BY order_date) AS monthly_running
FROM orders;


-- LAG and LEAD
SELECT
    employee_id,
    hire_date,
    salary,
    LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_salary,
    LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS next_salary,
    salary - LAG(salary, 1, salary) OVER (ORDER BY hire_date) AS salary_change
FROM employees;


-- FIRST_VALUE and LAST_VALUE
SELECT
    department_id,
    employee_id,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS highest_in_dept,
    LAST_VALUE(salary) OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_in_dept
FROM employees;


-- LISTAGG aggregation
SELECT
    department_id,
    LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS employee_list
FROM employees
GROUP BY department_id;

PIVOT and UNPIVOT

-- PIVOT example: Sales by quarter
SELECT *
FROM (
    SELECT product, quarter, amount
    FROM sales
)
PIVOT (
    SUM(amount) AS total
    FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
);


-- UNPIVOT example: Convert columns to rows
SELECT product, quarter, amount
FROM quarterly_sales
UNPIVOT (
    amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')
);

Hierarchical Queries

Basic CONNECT BY

-- Employee hierarchy with indentation
SELECT
    LEVEL,
    LPAD(' ', (LEVEL - 1) * 4) || first_name || ' ' || last_name AS employee_name,
    employee_id,
    manager_id,
    salary
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Advanced Hierarchical Features

-- With SYS_CONNECT_BY_PATH
SELECT
    employee_id,
    first_name || ' ' || last_name AS employee_name,
    LEVEL AS depth,
    SYS_CONNECT_BY_PATH(last_name, '/') AS path,
    CONNECT_BY_ROOT last_name AS root_manager,
    CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;


-- Cycle detection with NOCYCLE
SELECT
    employee_id,
    CONNECT_BY_ISCYCLE AS is_cycle,
    SYS_CONNECT_BY_PATH(employee_id, '/') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR employee_id = manager_id;


-- Bill of materials hierarchy
SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1) * 2) || component_name AS component,
    parent_id,
    component_id,
    quantity,
    SYS_CONNECT_BY_PATH(component_name, ' > ') AS assembly_path
FROM bill_of_materials
START WITH parent_id IS NULL
CONNECT BY PRIOR component_id = parent_id;


-- Category tree with aggregation
SELECT
    LEVEL,
    category_id,
    category_name,
    CONNECT_BY_ROOT category_name AS root_category,
    (SELECT COUNT(*) FROM products p WHERE p.category_id = c.category_id) AS product_count
FROM categories c
START WITH parent_category_id IS NULL
CONNECT BY PRIOR category_id = parent_category_id
ORDER SIBLINGS BY category_name;

JSON Operations

JSON_VALUE and JSON_QUERY

-- Extract scalar values
SELECT
    customer_id,
    JSON_VALUE(profile, '$.name') AS name,
    JSON_VALUE(profile, '$.email') AS email,
    JSON_VALUE(profile, '$.address.city') AS city,
    JSON_VALUE(profile, '$.age' RETURNING NUMBER) AS age
FROM customers
WHERE JSON_VALUE(profile, '$.status') = 'active';


-- Extract objects and arrays
SELECT
    order_id,
    JSON_QUERY(order_data, '$.items') AS items_array,
    JSON_QUERY(order_data, '$.shipping_address') AS shipping
FROM orders;

JSON_TABLE

-- Convert JSON array to rows
SELECT
    o.order_id,
    o.order_date,
    jt.item_id,
    jt.product_name,
    jt.quantity,
    jt.price,
    jt.quantity * jt.price AS line_total
FROM orders o,
JSON_TABLE(o.order_data, '$.items[*]'
    COLUMNS (
        item_id      NUMBER       PATH '$.id',
        product_name VARCHAR2(100) PATH '$.product',
        quantity     NUMBER       PATH '$.qty',
        price        NUMBER       PATH '$.price'
    )
) jt;


-- Nested JSON with multiple levels
SELECT
    c.customer_id,
    addr.street,
    addr.city,
    addr.state,
    addr.zip
FROM customers c,
JSON_TABLE(c.profile, '$.addresses[*]'
    COLUMNS (
        address_type VARCHAR2(20) PATH '$.type',
        street       VARCHAR2(100) PATH '$.street',
        city         VARCHAR2(50) PATH '$.city',
        state        VARCHAR2(2) PATH '$.state',
        zip          VARCHAR2(10) PATH '$.zip'
    )
) addr
WHERE addr.address_type = 'shipping';

JSON Construction

-- Build JSON object
SELECT
    JSON_OBJECT(
        'employee_id' VALUE employee_id,
        'name' VALUE first_name || ' ' || last_name,
        'department' VALUE department_id,
        'salary' VALUE salary,
        'hire_date' VALUE TO_CHAR(hire_date, 'YYYY-MM-DD')
    ) AS employee_json
FROM employees;


-- Build JSON array
SELECT
    JSON_ARRAY(first_name, last_name, email) AS contact_array
FROM employees;


-- Aggregate to JSON
SELECT
    department_id,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'id' VALUE employee_id,
            'name' VALUE first_name || ' ' || last_name
        )
        ORDER BY last_name
    ) AS employees_json
FROM employees
GROUP BY department_id;


-- Complex JSON aggregation
SELECT
    JSON_OBJECT(
        'department_id' VALUE d.department_id,
        'department_name' VALUE d.department_name,
        'employee_count' VALUE COUNT(e.employee_id),
        'total_salary' VALUE SUM(e.salary),
        'employees' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'id' VALUE e.employee_id,
                'name' VALUE e.first_name || ' ' || e.last_name
            )
        )
    ) AS dept_summary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

PL/SQL Examples

Anonymous Blocks

-- Basic anonymous block
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
    v_hire_date DATE;
BEGIN
    SELECT first_name || ' ' || last_name, salary, hire_date
    INTO v_name, v_salary, v_hire_date
    FROM employees
    WHERE employee_id = 100;


    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || TO_CHAR(v_salary, '$999,999.99'));
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(v_hire_date, 'DD-MON-YYYY'));
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Stored Procedures

-- Procedure with IN/OUT parameters
CREATE OR REPLACE PROCEDURE give_raise(
    p_employee_id  IN  NUMBER,
    p_percent      IN  NUMBER,
    p_new_salary   OUT NUMBER,
    p_status       OUT VARCHAR2
) AS
    v_current_salary NUMBER;
BEGIN
    -- Get current salary
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id;


    -- Calculate new salary
    p_new_salary := v_current_salary * (1 + p_percent / 100);


    -- Update the salary
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;


    IF SQL%ROWCOUNT > 0 THEN
        p_status := 'SUCCESS';
        COMMIT;
    ELSE
        p_status := 'NO_UPDATE';
    END IF;


EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_new_salary := 0;
        p_status := 'EMPLOYEE_NOT_FOUND';
    WHEN OTHERS THEN
        p_new_salary := 0;
        p_status := 'ERROR: ' || SQLERRM;
        ROLLBACK;
END give_raise;
/


-- Call the procedure
DECLARE
    v_new_salary NUMBER;
    v_status VARCHAR2(200);
BEGIN
    give_raise(100, 10, v_new_salary, v_status);
    DBMS_OUTPUT.PUT_LINE('New Salary: ' || v_new_salary);
    DBMS_OUTPUT.PUT_LINE('Status: ' || v_status);
END;
/

Functions

-- Function returning scalar value
CREATE OR REPLACE FUNCTION calculate_bonus(
    p_employee_id IN NUMBER,
    p_bonus_percent IN NUMBER DEFAULT 10
) RETURN NUMBER AS
    v_salary NUMBER;
    v_years_of_service NUMBER;
    v_bonus NUMBER;
BEGIN
    SELECT salary,
           TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12)
    INTO v_salary, v_years_of_service
    FROM employees
    WHERE employee_id = p_employee_id;


    -- Calculate bonus with service multiplier
    v_bonus := v_salary * (p_bonus_percent / 100) *
               CASE
                   WHEN v_years_of_service >= 10 THEN 1.5
                   WHEN v_years_of_service >= 5 THEN 1.25
                   ELSE 1.0
               END;


    RETURN ROUND(v_bonus, 2);


EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END calculate_bonus;
/


-- Use function in SQL
SELECT
    employee_id,
    first_name || ' ' || last_name AS name,
    salary,
    calculate_bonus(employee_id) AS bonus,
    calculate_bonus(employee_id, 15) AS higher_bonus
FROM employees
WHERE department_id = 10;

Cursor Examples

-- Explicit cursor with parameters
DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY salary DESC;


    v_emp emp_cursor%ROWTYPE;
    v_total_salary NUMBER := 0;
    v_count NUMBER := 0;
BEGIN
    OPEN emp_cursor(10);
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;


        v_total_salary := v_total_salary + v_emp.salary;
        v_count := v_count + 1;


        DBMS_OUTPUT.PUT_LINE(
            RPAD(v_emp.first_name || ' ' || v_emp.last_name, 30) ||
            TO_CHAR(v_emp.salary, '$999,999.99')
        );
    END LOOP;


    DBMS_OUTPUT.PUT_LINE('---');
    DBMS_OUTPUT.PUT_LINE('Total: ' || v_count || ' employees');
    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || TO_CHAR(v_total_salary, '$9,999,999.99'));
    DBMS_OUTPUT.PUT_LINE('Average: ' || TO_CHAR(v_total_salary/v_count, '$999,999.99'));


    CLOSE emp_cursor;
END;
/


-- Cursor FOR loop (simpler syntax)
BEGIN
    FOR emp_rec IN (
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 20
        ORDER BY salary DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/

BULK COLLECT and FORALL

-- BULK COLLECT example
DECLARE
    TYPE emp_tab_type IS TABLE OF employees%ROWTYPE;
    v_employees emp_tab_type;
BEGIN
    -- Bulk collect all employees
    SELECT *
    BULK COLLECT INTO v_employees
    FROM employees
    WHERE department_id = 10;


    DBMS_OUTPUT.PUT_LINE('Found ' || v_employees.COUNT || ' employees');


    -- Process the collection
    FOR i IN 1..v_employees.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(
            v_employees(i).employee_id || ': ' ||
            v_employees(i).first_name || ' ' || v_employees(i).last_name
        );
    END LOOP;
END;
/


-- BULK COLLECT with LIMIT
DECLARE
    CURSOR emp_cur IS SELECT * FROM employees;
    TYPE emp_tab IS TABLE OF employees%ROWTYPE;
    v_employees emp_tab;
    v_batch_size CONSTANT PLS_INTEGER := 100;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur BULK COLLECT INTO v_employees LIMIT v_batch_size;
        EXIT WHEN v_employees.COUNT = 0;


        -- Process batch
        DBMS_OUTPUT.PUT_LINE('Processing batch of ' || v_employees.COUNT);


        -- Do something with the batch
        FOR i IN 1..v_employees.COUNT LOOP
            -- Process each record
            NULL;
        END LOOP;
    END LOOP;
    CLOSE emp_cur;
END;
/


-- FORALL for bulk DML
DECLARE
    TYPE emp_id_tab IS TABLE OF employees.employee_id%TYPE;
    TYPE salary_tab IS TABLE OF employees.salary%TYPE;
    v_emp_ids emp_id_tab;
    v_new_salaries salary_tab;
BEGIN
    -- Populate the collections
    SELECT employee_id, salary * 1.05
    BULK COLLECT INTO v_emp_ids, v_new_salaries
    FROM employees
    WHERE department_id = 10;


    -- Bulk update
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employees
        SET salary = v_new_salaries(i)
        WHERE employee_id = v_emp_ids(i);


    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' employees');
    COMMIT;
END;
/

Packages

-- Package specification
CREATE OR REPLACE PACKAGE employee_pkg AS
    -- Public types
    TYPE emp_rec IS RECORD (
        employee_id   employees.employee_id%TYPE,
        full_name     VARCHAR2(100),
        salary        employees.salary%TYPE
    );


    TYPE emp_tab IS TABLE OF emp_rec;


    -- Public procedures and functions
    PROCEDURE hire_employee(
        p_first_name  IN VARCHAR2,
        p_last_name   IN VARCHAR2,
        p_email       IN VARCHAR2,
        p_salary      IN NUMBER,
        p_dept_id     IN NUMBER,
        p_emp_id      OUT NUMBER
    );


    PROCEDURE terminate_employee(
        p_employee_id IN NUMBER
    );


    FUNCTION get_employees_by_dept(
        p_dept_id IN NUMBER
    ) RETURN emp_tab;


    FUNCTION get_department_salary_total(
        p_dept_id IN NUMBER
    ) RETURN NUMBER;


END employee_pkg;
/


-- Package body
CREATE OR REPLACE PACKAGE BODY employee_pkg AS


    -- Private variable
    g_last_hire_date DATE;


    -- Private function
    FUNCTION generate_email(
        p_first_name VARCHAR2,
        p_last_name VARCHAR2
    ) RETURN VARCHAR2 IS
    BEGIN
        RETURN LOWER(SUBSTR(p_first_name, 1, 1) || p_last_name || '@example.com');
    END generate_email;


    PROCEDURE hire_employee(
        p_first_name  IN VARCHAR2,
        p_last_name   IN VARCHAR2,
        p_email       IN VARCHAR2,
        p_salary      IN NUMBER,
        p_dept_id     IN NUMBER,
        p_emp_id      OUT NUMBER
    ) AS
        v_email VARCHAR2(100);
    BEGIN
        -- Generate email if not provided
        v_email := NVL(p_email, generate_email(p_first_name, p_last_name));


        -- Get next sequence value
        SELECT emp_seq.NEXTVAL INTO p_emp_id FROM DUAL;


        -- Insert the employee
        INSERT INTO employees (
            employee_id, first_name, last_name, email,
            hire_date, salary, department_id
        ) VALUES (
            p_emp_id, p_first_name, p_last_name, v_email,
            SYSDATE, p_salary, p_dept_id
        );


        g_last_hire_date := SYSDATE;
        COMMIT;


    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            RAISE_APPLICATION_ERROR(-20001, 'Email already exists');
    END hire_employee;


    PROCEDURE terminate_employee(
        p_employee_id IN NUMBER
    ) AS
    BEGIN
        DELETE FROM employees WHERE employee_id = p_employee_id;


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


        COMMIT;
    END terminate_employee;


    FUNCTION get_employees_by_dept(
        p_dept_id IN NUMBER
    ) RETURN emp_tab AS
        v_result emp_tab := emp_tab();
    BEGIN
        FOR rec IN (
            SELECT employee_id,
                   first_name || ' ' || last_name AS full_name,
                   salary
            FROM employees
            WHERE department_id = p_dept_id
        ) LOOP
            v_result.EXTEND;
            v_result(v_result.COUNT).employee_id := rec.employee_id;
            v_result(v_result.COUNT).full_name := rec.full_name;
            v_result(v_result.COUNT).salary := rec.salary;
        END LOOP;


        RETURN v_result;
    END get_employees_by_dept;


    FUNCTION get_department_salary_total(
        p_dept_id IN NUMBER
    ) RETURN NUMBER AS
        v_total NUMBER;
    BEGIN
        SELECT SUM(salary) INTO v_total
        FROM employees
        WHERE department_id = p_dept_id;


        RETURN NVL(v_total, 0);
    END get_department_salary_total;


END employee_pkg;
/


-- Using the package
DECLARE
    v_emp_id NUMBER;
    v_employees employee_pkg.emp_tab;
BEGIN
    -- Hire a new employee
    employee_pkg.hire_employee(
        p_first_name => 'New',
        p_last_name  => 'Employee',
        p_email      => NULL,  -- Will be generated
        p_salary     => 60000,
        p_dept_id    => 10,
        p_emp_id     => v_emp_id
    );
    DBMS_OUTPUT.PUT_LINE('Hired employee ID: ' || v_emp_id);


    -- Get department total
    DBMS_OUTPUT.PUT_LINE('Dept 10 total salary: ' ||
        employee_pkg.get_department_salary_total(10));
END;
/

Triggers

-- Audit trigger
CREATE OR REPLACE TRIGGER emp_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
    v_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN
        v_action := 'INSERT';
        INSERT INTO emp_audit (audit_id, employee_id, action, action_date, new_salary)
        VALUES (audit_seq.NEXTVAL, :NEW.employee_id, v_action, SYSDATE, :NEW.salary);
    ELSIF UPDATING THEN
        v_action := 'UPDATE';
        INSERT INTO emp_audit (audit_id, employee_id, action, action_date, old_salary, new_salary)
        VALUES (audit_seq.NEXTVAL, :NEW.employee_id, v_action, SYSDATE, :OLD.salary, :NEW.salary);
    ELSIF DELETING THEN
        v_action := 'DELETE';
        INSERT INTO emp_audit (audit_id, employee_id, action, action_date, old_salary)
        VALUES (audit_seq.NEXTVAL, :OLD.employee_id, v_action, SYSDATE, :OLD.salary);
    END IF;
END;
/


-- Before insert trigger for auto-population
CREATE OR REPLACE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Auto-generate ID if not provided
    IF :NEW.employee_id IS NULL THEN
        SELECT emp_seq.NEXTVAL INTO :NEW.employee_id FROM DUAL;
    END IF;


    -- Set created timestamp
    :NEW.created_at := SYSTIMESTAMP;


    -- Uppercase email
    :NEW.email := UPPER(:NEW.email);
END;
/

DBMS Package Examples

DBMS_OUTPUT

SET SERVEROUTPUT ON SIZE 1000000


BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    DBMS_OUTPUT.PUT_LINE('=== Employee Report ===');
    DBMS_OUTPUT.PUT('Processing...');
    DBMS_OUTPUT.NEW_LINE;


    FOR emp IN (SELECT * FROM employees WHERE ROWNUM <= 5) LOOP
        DBMS_OUTPUT.PUT_LINE('  ' || emp.first_name || ' ' || emp.last_name);
    END LOOP;


    DBMS_OUTPUT.PUT_LINE('=== End of Report ===');
END;
/

DBMS_SCHEDULER

-- Create a scheduled job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'DAILY_STATS_GATHER',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'DBMS_STATS.GATHER_SCHEMA_STATS',
        number_of_arguments => 1,
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
        enabled         => FALSE,
        comments        => 'Gather statistics daily at 2 AM'
    );


    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name        => 'DAILY_STATS_GATHER',
        argument_position => 1,
        argument_value  => 'HR'
    );


    DBMS_SCHEDULER.ENABLE('DAILY_STATS_GATHER');
END;
/


-- Run job immediately
BEGIN
    DBMS_SCHEDULER.RUN_JOB('DAILY_STATS_GATHER');
END;
/


-- Check job status
SELECT job_name, state, last_start_date, next_run_date
FROM user_scheduler_jobs;

DBMS_METADATA

-- Get table DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;


-- Get all indexes for a table
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', 'EMPLOYEES') FROM DUAL;


-- With transform parameters
BEGIN
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM,
        'STORAGE', FALSE
    );
    DBMS_METADATA.SET_TRANSFORM_PARAM(
        DBMS_METADATA.SESSION_TRANSFORM,
        'TABLESPACE', FALSE
    );
END;
/


SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES') FROM DUAL;

JDBC Examples

Basic JDBC Connection

import java.sql.*;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;


public class BasicJdbcExample {


    private static final String URL = "jdbc:oracle:thin:@//localhost:1521/heliosdb";
    private static final String USER = "admin";
    private static final String PASSWORD = "password";


    public static void main(String[] args) throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(URL);
        ods.setUser(USER);
        ods.setPassword(PASSWORD);


        try (Connection conn = ods.getConnection()) {
            System.out.println("Connected to HeliosDB");


            // Simple query
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(
                     "SELECT employee_id, first_name, last_name, salary " +
                     "FROM employees WHERE department_id = 10")) {


                while (rs.next()) {
                    System.out.printf("%d: %s %s - $%.2f%n",
                        rs.getInt("employee_id"),
                        rs.getString("first_name"),
                        rs.getString("last_name"),
                        rs.getDouble("salary"));
                }
            }
        }
    }
}

Prepared Statements and Batching

import java.sql.*;


public class PreparedStatementExample {


    public void insertEmployees(Connection conn, List<Employee> employees)
            throws SQLException {


        String sql = "INSERT INTO employees " +
            "(employee_id, first_name, last_name, email, salary, department_id) " +
            "VALUES (emp_seq.NEXTVAL, ?, ?, ?, ?, ?)";


        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            conn.setAutoCommit(false);


            for (Employee emp : employees) {
                pstmt.setString(1, emp.getFirstName());
                pstmt.setString(2, emp.getLastName());
                pstmt.setString(3, emp.getEmail());
                pstmt.setBigDecimal(4, emp.getSalary());
                pstmt.setInt(5, emp.getDepartmentId());
                pstmt.addBatch();
            }


            int[] results = pstmt.executeBatch();
            conn.commit();


            System.out.println("Inserted " + results.length + " employees");
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }


    public Employee getEmployee(Connection conn, int employeeId)
            throws SQLException {


        String sql = "SELECT * FROM employees WHERE employee_id = ?";


        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, employeeId);


            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new Employee(
                        rs.getInt("employee_id"),
                        rs.getString("first_name"),
                        rs.getString("last_name"),
                        rs.getString("email"),
                        rs.getBigDecimal("salary"),
                        rs.getInt("department_id")
                    );
                }
            }
        }
        return null;
    }
}

Calling Stored Procedures

import java.sql.*;


public class StoredProcedureExample {


    public void callGiveRaise(Connection conn, int employeeId, double percent)
            throws SQLException {


        String sql = "{call give_raise(?, ?, ?, ?)}";


        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            // Set IN parameters
            cstmt.setInt(1, employeeId);
            cstmt.setDouble(2, percent);


            // Register OUT parameters
            cstmt.registerOutParameter(3, Types.NUMERIC);  // new_salary
            cstmt.registerOutParameter(4, Types.VARCHAR);  // status


            // Execute
            cstmt.execute();


            // Get OUT values
            double newSalary = cstmt.getDouble(3);
            String status = cstmt.getString(4);


            System.out.printf("New salary: $%.2f, Status: %s%n", newSalary, status);
        }
    }


    public double callCalculateBonus(Connection conn, int employeeId)
            throws SQLException {


        String sql = "{ ? = call calculate_bonus(?) }";


        try (CallableStatement cstmt = conn.prepareCall(sql)) {
            cstmt.registerOutParameter(1, Types.NUMERIC);
            cstmt.setInt(2, employeeId);
            cstmt.execute();
            return cstmt.getDouble(1);
        }
    }
}

Connection Pooling with HikariCP

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;


public class ConnectionPoolExample {


    private static HikariDataSource dataSource;


    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:oracle:thin:@//localhost:1521/heliosdb");
        config.setUsername("admin");
        config.setPassword("password");


        // Pool settings
        config.setMinimumIdle(5);
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);


        // Oracle-specific properties
        config.addDataSourceProperty("oracle.jdbc.implicitStatementCacheSize", "20");
        config.addDataSourceProperty("oracle.jdbc.FAN.enabled", "false");


        // Validation
        config.setConnectionTestQuery("SELECT 1 FROM DUAL");


        dataSource = new HikariDataSource(config);
    }


    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }


    public static void close() {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

OCI Examples

C OCI Connection

#include <oci.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>


void check_error(sword status, OCIError *errhp) {
    text errbuf[512];
    sb4 errcode;


    if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO) {
        OCIErrorGet(errhp, 1, NULL, &errcode, errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
        printf("Error: %s\n", errbuf);
        exit(1);
    }
}


int main() {
    OCIEnv *envhp;
    OCIError *errhp;
    OCISvcCtx *svchp;
    OCIServer *srvhp;
    OCISession *authp;
    OCIStmt *stmthp;
    OCIDefine *defnp = NULL;


    sword status;


    // Connection parameters
    const char *username = "admin";
    const char *password = "password";
    const char *dbname = "//localhost:1521/heliosdb";


    // Initialize OCI environment
    status = OCIEnvCreate(&envhp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    if (status != OCI_SUCCESS) {
        printf("OCIEnvCreate failed\n");
        return 1;
    }


    // Allocate error handle
    OCIHandleAlloc(envhp, (void **)&errhp, OCI_HTYPE_ERROR, 0, NULL);


    // Allocate server handle
    OCIHandleAlloc(envhp, (void **)&srvhp, OCI_HTYPE_SERVER, 0, NULL);


    // Attach to server
    status = OCIServerAttach(srvhp, errhp, (text *)dbname, strlen(dbname), OCI_DEFAULT);
    check_error(status, errhp);


    // Allocate service context
    OCIHandleAlloc(envhp, (void **)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);
    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, srvhp, 0, OCI_ATTR_SERVER, errhp);


    // Allocate session handle
    OCIHandleAlloc(envhp, (void **)&authp, OCI_HTYPE_SESSION, 0, NULL);
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (void *)username, strlen(username), OCI_ATTR_USERNAME, errhp);
    OCIAttrSet(authp, OCI_HTYPE_SESSION, (void *)password, strlen(password), OCI_ATTR_PASSWORD, errhp);


    // Begin session
    status = OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT);
    check_error(status, errhp);


    OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, authp, 0, OCI_ATTR_SESSION, errhp);


    printf("Connected to HeliosDB successfully!\n");


    // Allocate statement handle
    OCIHandleAlloc(envhp, (void **)&stmthp, OCI_HTYPE_STMT, 0, NULL);


    // Prepare and execute query
    const char *sql = "SELECT employee_id, first_name FROM employees WHERE ROWNUM <= 5";
    status = OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    check_error(status, errhp);


    // Define output variables
    int emp_id;
    char first_name[50];


    OCIDefineByPos(stmthp, &defnp, errhp, 1, &emp_id, sizeof(emp_id), SQLT_INT, NULL, NULL, NULL, OCI_DEFAULT);
    OCIDefineByPos(stmthp, &defnp, errhp, 2, first_name, sizeof(first_name), SQLT_STR, NULL, NULL, NULL, OCI_DEFAULT);


    // Execute
    status = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, NULL, NULL, OCI_DEFAULT);
    check_error(status, errhp);


    // Fetch results
    printf("\nEmployees:\n");
    while ((status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
        printf("  %d: %s\n", emp_id, first_name);
    }


    // Cleanup
    OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT);
    OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
    OCIHandleFree(stmthp, OCI_HTYPE_STMT);
    OCIHandleFree(authp, OCI_HTYPE_SESSION);
    OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);
    OCIHandleFree(srvhp, OCI_HTYPE_SERVER);
    OCIHandleFree(errhp, OCI_HTYPE_ERROR);
    OCIHandleFree(envhp, OCI_HTYPE_ENV);


    return 0;
}

OCI Connection Pool (CPOOL)

#include <oci.h>


int main() {
    OCIEnv *envhp;
    OCICPool *poolhp;
    OCIError *errhp;
    text *poolName;
    sb4 poolNameLen;


    const char *username = "admin";
    const char *password = "password";
    const char *dbname = "//localhost:1521/heliosdb";


    // Initialize
    OCIEnvCreate(&envhp, OCI_THREADED, NULL, NULL, NULL, NULL, 0, NULL);
    OCIHandleAlloc(envhp, (void **)&errhp, OCI_HTYPE_ERROR, 0, NULL);
    OCIHandleAlloc(envhp, (void **)&poolhp, OCI_HTYPE_CPOOL, 0, NULL);


    // Create connection pool
    sword status = OCIConnectionPoolCreate(
        envhp, errhp, poolhp,
        &poolName, &poolNameLen,
        (text *)dbname, strlen(dbname),
        2,      // min connections
        10,     // max connections
        1,      // increment
        (text *)username, strlen(username),
        (text *)password, strlen(password),
        OCI_DEFAULT
    );


    if (status == OCI_SUCCESS) {
        printf("Connection pool created: %.*s\n", poolNameLen, poolName);


        // Get connection from pool
        OCISvcCtx *svchp;
        OCIHandleAlloc(envhp, (void **)&svchp, OCI_HTYPE_SVCCTX, 0, NULL);


        status = OCILogon2(
            envhp, errhp, &svchp,
            (text *)username, strlen(username),
            (text *)password, strlen(password),
            poolName, poolNameLen,
            OCI_CPOOL
        );


        if (status == OCI_SUCCESS) {
            printf("Got connection from pool\n");
            // Use connection...
            OCILogoff(svchp, errhp);
        }
    }


    // Destroy pool
    OCIConnectionPoolDestroy(poolhp, errhp, OCI_DEFAULT);


    return 0;
}

Python Examples

Basic oracledb Connection

import oracledb


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


print(f"Connected to: {connection.version}")


# Simple query
with connection.cursor() as cursor:
    cursor.execute("""
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = :dept_id
        ORDER BY salary DESC
    """, dept_id=10)


    for row in cursor:
        print(f"{row[0]}: {row[1]} {row[2]} - ${row[3]:,.2f}")


connection.close()

Connection Pool

import oracledb


# Create connection pool
pool = oracledb.create_pool(
    user="admin",
    password="password",
    dsn="localhost:1521/heliosdb",
    min=2,
    max=10,
    increment=1,
    getmode=oracledb.POOL_GETMODE_WAIT,
    timeout=60
)


print(f"Pool opened: {pool.opened} connections")


# Use pool
with pool.acquire() as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT COUNT(*) FROM employees")
        count = cursor.fetchone()[0]
        print(f"Total employees: {count}")


# Pool statistics
print(f"Pool busy: {pool.busy}")
print(f"Pool open: {pool.opened}")


pool.close()

Calling PL/SQL

import oracledb


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


with connection.cursor() as cursor:
    # Call procedure with OUT parameters
    new_salary = cursor.var(oracledb.NUMBER)
    status = cursor.var(oracledb.STRING, 200)


    cursor.callproc('give_raise', [100, 10, new_salary, status])


    print(f"New salary: ${new_salary.getvalue():,.2f}")
    print(f"Status: {status.getvalue()}")


    # Call function
    bonus = cursor.callfunc('calculate_bonus', oracledb.NUMBER, [100])
    print(f"Bonus: ${bonus:,.2f}")


    # Execute anonymous block
    cursor.execute("""
        BEGIN
            DBMS_OUTPUT.ENABLE(10000);
            DBMS_OUTPUT.PUT_LINE('Hello from PL/SQL');
            FOR emp IN (SELECT first_name FROM employees WHERE ROWNUM <= 3) LOOP
                DBMS_OUTPUT.PUT_LINE('  ' || emp.first_name);
            END LOOP;
        END;
    """)


    # Get DBMS_OUTPUT
    lines = cursor.var(oracledb.STRING, arraysize=100)
    num_lines = cursor.var(oracledb.NUMBER)
    num_lines.setvalue(0, 100)


    cursor.callproc('DBMS_OUTPUT.GET_LINES', [lines, num_lines])


    for i in range(int(num_lines.getvalue())):
        if lines.getvalue()[i]:
            print(lines.getvalue()[i])


connection.close()

Node.js Examples

Basic Connection

const oracledb = require('oracledb');


async function run() {
    let connection;


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


        console.log('Connected to HeliosDB');


        // Simple query
        const result = await connection.execute(
            `SELECT employee_id, first_name, last_name, salary
             FROM employees
             WHERE department_id = :deptId
             ORDER BY salary DESC`,
            { deptId: 10 },
            { outFormat: oracledb.OUT_FORMAT_OBJECT }
        );


        console.log('Employees:');
        for (const row of result.rows) {
            console.log(`  ${row.EMPLOYEE_ID}: ${row.FIRST_NAME} ${row.LAST_NAME} - $${row.SALARY}`);
        }


    } finally {
        if (connection) {
            await connection.close();
        }
    }
}


run().catch(console.error);

Connection Pool

const oracledb = require('oracledb');


async function init() {
    await oracledb.createPool({
        user: 'admin',
        password: 'password',
        connectString: 'localhost:1521/heliosdb',
        poolMin: 2,
        poolMax: 10,
        poolIncrement: 1,
        poolTimeout: 60,
        poolAlias: 'hrpool'
    });


    console.log('Pool created');
}


async function getEmployeeCount() {
    const connection = await oracledb.getConnection('hrpool');


    try {
        const result = await connection.execute(
            'SELECT COUNT(*) as count FROM employees'
        );
        return result.rows[0][0];
    } finally {
        await connection.close();
    }
}


async function close() {
    await oracledb.getPool('hrpool').close(0);
}


// Usage
init()
    .then(() => getEmployeeCount())
    .then(count => console.log(`Total employees: ${count}`))
    .then(() => close())
    .catch(console.error);

Error Handling Examples

PL/SQL Exception Handling

DECLARE
    v_salary NUMBER;
    e_employee_not_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_employee_not_found, -20001);
BEGIN
    BEGIN
        SELECT salary INTO v_salary
        FROM employees
        WHERE employee_id = 9999;  -- Non-existent


    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END;


EXCEPTION
    WHEN e_employee_not_found THEN
        DBMS_OUTPUT.PUT_LINE('Custom error: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Stack: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
        RAISE;
END;
/

JDBC Exception Handling

import java.sql.*;


public class ErrorHandlingExample {


    public void handleOracleError(Connection conn) {
        try {
            CallableStatement cstmt = conn.prepareCall(
                "{call procedure_that_may_fail(?)}"
            );
            cstmt.setInt(1, 100);
            cstmt.execute();


        } catch (SQLException e) {
            int errorCode = e.getErrorCode();
            String sqlState = e.getSQLState();
            String message = e.getMessage();


            System.err.printf("Oracle Error %d (SQLSTATE %s): %s%n",
                errorCode, sqlState, message);


            // Handle specific Oracle errors
            switch (errorCode) {
                case 1:     // ORA-00001: unique constraint violated
                    System.err.println("Duplicate key detected");
                    break;
                case 1017:  // ORA-01017: invalid username/password
                    System.err.println("Authentication failed");
                    break;
                case 1400:  // ORA-01400: cannot insert NULL
                    System.err.println("Required field is null");
                    break;
                case 2291:  // ORA-02291: integrity constraint violated
                    System.err.println("Foreign key violation");
                    break;
                default:
                    if (errorCode >= 20000 && errorCode <= 20999) {
                        System.err.println("Application error: " + message);
                    } else {
                        System.err.println("Unexpected database error");
                    }
            }
        }
    }
}

Last Updated: January 2026 Examples Version: HeliosDB 7.0+