Skip to content
HeliosDB Full

PL/SQL Support in HeliosDB

PL/SQL Support in HeliosDB

Comprehensive documentation for PL/SQL support in HeliosDB, including supported constructs, conversion patterns for unsupported features, and working code examples.

Table of Contents

Overview

PL/SQL Compatibility Level: 40%

HeliosDB provides foundational PL/SQL support for executing procedural code within the database. The interpreter handles common PL/SQL constructs used in typical business applications while some advanced Oracle-specific features require conversion.

Key Capabilities

CapabilityStatusNotes
Anonymous blocksSupportedDECLARE/BEGIN/END
Stored proceduresSupportedCREATE PROCEDURE
FunctionsSupportedCREATE FUNCTION
PackagesPartialBasic spec/body
TriggersPartialRow-level DML
CursorsSupportedExplicit and implicit
Exception handlingSupportedWHEN/RAISE
Control flowSupportedIF, CASE, LOOP
CollectionsPartialBasic nested tables
BULK operationsPartialSimple BULK COLLECT

Supported PL/SQL Constructs

Variables and Constants

DECLARE
    -- Scalar variables
    v_number    NUMBER := 100;
    v_string    VARCHAR2(100) := 'Hello';
    v_date      DATE := SYSDATE;
    v_boolean   BOOLEAN := TRUE;
    v_timestamp TIMESTAMP := SYSTIMESTAMP;


    -- Constants
    c_tax_rate  CONSTANT NUMBER := 0.0825;
    c_max_value CONSTANT NUMBER := 1000000;


    -- Anchored types
    v_emp_id    employees.employee_id%TYPE;
    v_emp_row   employees%ROWTYPE;


    -- NULL initialization
    v_nullable  VARCHAR2(50) := NULL;
BEGIN
    -- Variable assignment
    v_number := v_number + 50;
    v_string := v_string || ' World';


    -- Using constants
    v_number := v_number * (1 + c_tax_rate);


    DBMS_OUTPUT.PUT_LINE('Result: ' || v_number);
END;
/

Control Flow Statements

DECLARE
    v_grade VARCHAR2(1) := 'B';
    v_score NUMBER := 85;
    v_message VARCHAR2(100);
BEGIN
    -- IF-THEN-ELSE
    IF v_score >= 90 THEN
        v_message := 'Excellent';
    ELSIF v_score >= 80 THEN
        v_message := 'Good';
    ELSIF v_score >= 70 THEN
        v_message := 'Average';
    ELSE
        v_message := 'Needs Improvement';
    END IF;


    DBMS_OUTPUT.PUT_LINE('Score message: ' || v_message);


    -- Simple CASE
    v_message := CASE v_grade
        WHEN 'A' THEN 'Outstanding'
        WHEN 'B' THEN 'Above Average'
        WHEN 'C' THEN 'Satisfactory'
        WHEN 'D' THEN 'Below Average'
        ELSE 'Failing'
    END;


    DBMS_OUTPUT.PUT_LINE('Grade message: ' || v_message);


    -- Searched CASE
    v_message := CASE
        WHEN v_score >= 90 THEN 'A Grade'
        WHEN v_score >= 80 THEN 'B Grade'
        WHEN v_score >= 70 THEN 'C Grade'
        ELSE 'Below C'
    END;


    DBMS_OUTPUT.PUT_LINE('Final: ' || v_message);
END;
/

Loops

DECLARE
    v_counter NUMBER := 1;
    v_sum NUMBER := 0;
BEGIN
    -- Simple LOOP
    LOOP
        v_sum := v_sum + v_counter;
        v_counter := v_counter + 1;
        EXIT WHEN v_counter > 10;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Simple loop sum: ' || v_sum);


    -- WHILE loop
    v_counter := 1;
    v_sum := 0;
    WHILE v_counter <= 10 LOOP
        v_sum := v_sum + v_counter;
        v_counter := v_counter + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('While loop sum: ' || v_sum);


    -- FOR loop (numeric)
    v_sum := 0;
    FOR i IN 1..10 LOOP
        v_sum := v_sum + i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('For loop sum: ' || v_sum);


    -- FOR loop REVERSE
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
    END LOOP;


    -- Cursor FOR loop
    FOR emp IN (SELECT employee_id, first_name FROM employees WHERE ROWNUM <= 5) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp.employee_id || ' - ' || emp.first_name);
    END LOOP;


    -- CONTINUE statement
    FOR i IN 1..10 LOOP
        IF MOD(i, 2) = 0 THEN
            CONTINUE;  -- Skip even numbers
        END IF;
        DBMS_OUTPUT.PUT_LINE('Odd: ' || i);
    END LOOP;
END;
/

Cursors

DECLARE
    -- Explicit cursor declaration
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = 10
        ORDER BY salary DESC;


    -- Cursor with parameters
    CURSOR dept_cursor(p_dept_id NUMBER) IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = p_dept_id;


    -- Variables
    v_emp emp_cursor%ROWTYPE;
    v_total_salary NUMBER := 0;
    v_count NUMBER := 0;
BEGIN
    -- Explicit cursor operations
    OPEN emp_cursor;
    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(v_emp.first_name || ' ' || v_emp.last_name ||
                           ': $' || v_emp.salary);
    END LOOP;


    DBMS_OUTPUT.PUT_LINE('Total: $' || v_total_salary);
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
    DBMS_OUTPUT.PUT_LINE('Found: ' || emp_cursor%ROWCOUNT);


    CLOSE emp_cursor;


    -- Cursor with parameter
    OPEN dept_cursor(20);
    LOOP
        FETCH dept_cursor INTO v_emp.employee_id, v_emp.first_name, v_emp.salary;
        EXIT WHEN dept_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Dept 20: ' || v_emp.first_name);
    END LOOP;
    CLOSE dept_cursor;


    -- Implicit cursor attributes
    UPDATE employees SET salary = salary * 1.01 WHERE department_id = 10;
    DBMS_OUTPUT.PUT_LINE('Updated rows: ' || SQL%ROWCOUNT);


    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Update was successful');
    END IF;
END;
/

Exception Handling

DECLARE
    v_salary NUMBER;
    v_name VARCHAR2(100);


    -- User-defined exceptions
    e_salary_too_high EXCEPTION;
    e_employee_not_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_employee_not_found, -20001);
BEGIN
    -- Try to get employee data
    BEGIN
        SELECT first_name || ' ' || last_name, salary
        INTO v_name, v_salary
        FROM employees
        WHERE employee_id = 9999;  -- May not exist


    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee not found in inner block');
            v_name := 'Unknown';
            v_salary := 0;
    END;


    -- Check salary limit
    IF v_salary > 500000 THEN
        RAISE e_salary_too_high;
    END IF;


    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary: $' || v_salary);


EXCEPTION
    WHEN e_salary_too_high THEN
        DBMS_OUTPUT.PUT_LINE('Salary exceeds maximum allowed');


    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate value error');


    WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Value conversion error');


    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Division by zero');


    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;  -- Re-raise the exception
END;
/

Procedures and Functions

-- Procedure with IN, OUT, and IN OUT parameters
CREATE OR REPLACE PROCEDURE calculate_employee_stats(
    p_dept_id     IN  NUMBER,
    p_total_sal   OUT NUMBER,
    p_avg_sal     OUT NUMBER,
    p_emp_count   IN OUT NUMBER
) AS
BEGIN
    SELECT SUM(salary), AVG(salary), COUNT(*)
    INTO p_total_sal, p_avg_sal, p_emp_count
    FROM employees
    WHERE department_id = p_dept_id;


    IF p_emp_count = 0 THEN
        p_total_sal := 0;
        p_avg_sal := 0;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        p_total_sal := NULL;
        p_avg_sal := NULL;
        p_emp_count := 0;
        RAISE;
END calculate_employee_stats;
/


-- Function with default parameter
CREATE OR REPLACE FUNCTION format_salary(
    p_salary IN NUMBER,
    p_currency IN VARCHAR2 DEFAULT '$',
    p_decimals IN NUMBER DEFAULT 2
) RETURN VARCHAR2 AS
    v_format VARCHAR2(50);
BEGIN
    IF p_salary IS NULL THEN
        RETURN p_currency || '0.00';
    END IF;


    -- Build format string
    v_format := p_currency || TO_CHAR(ROUND(p_salary, p_decimals),
                              'FM999,999,999,990.' || RPAD('0', p_decimals, '0'));


    RETURN v_format;
END format_salary;
/


-- Using the procedure and function
DECLARE
    v_total NUMBER;
    v_avg NUMBER;
    v_count NUMBER := 0;
BEGIN
    calculate_employee_stats(10, v_total, v_avg, v_count);


    DBMS_OUTPUT.PUT_LINE('Department 10 Statistics:');
    DBMS_OUTPUT.PUT_LINE('  Total Salary: ' || format_salary(v_total));
    DBMS_OUTPUT.PUT_LINE('  Average Salary: ' || format_salary(v_avg));
    DBMS_OUTPUT.PUT_LINE('  Employee Count: ' || v_count);
END;
/

Basic Packages

-- Package Specification
CREATE OR REPLACE PACKAGE hr_utils AS
    -- Constants
    c_min_salary CONSTANT NUMBER := 30000;
    c_max_salary CONSTANT NUMBER := 500000;


    -- Type declarations
    TYPE salary_record IS RECORD (
        employee_id NUMBER,
        salary NUMBER,
        department_id NUMBER
    );


    -- Function declarations
    FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN;
    FUNCTION get_tax_rate(p_salary NUMBER) RETURN NUMBER;


    -- Procedure declarations
    PROCEDURE give_raise(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER,
        p_new_salary OUT NUMBER
    );


END hr_utils;
/


-- Package Body
CREATE OR REPLACE PACKAGE BODY hr_utils AS


    -- Private variable
    g_last_updated DATE := SYSDATE;


    -- Implement functions
    FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_salary >= c_min_salary AND p_salary <= c_max_salary;
    END validate_salary;


    FUNCTION get_tax_rate(p_salary NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN CASE
            WHEN p_salary <= 50000 THEN 0.15
            WHEN p_salary <= 100000 THEN 0.25
            WHEN p_salary <= 200000 THEN 0.30
            ELSE 0.35
        END;
    END get_tax_rate;


    -- Implement procedure
    PROCEDURE give_raise(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER,
        p_new_salary OUT NUMBER
    ) AS
        v_current_salary NUMBER;
    BEGIN
        SELECT salary INTO v_current_salary
        FROM employees
        WHERE employee_id = p_emp_id;


        p_new_salary := v_current_salary * (1 + p_percent / 100);


        IF NOT validate_salary(p_new_salary) THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary out of range');
        END IF;


        UPDATE employees
        SET salary = p_new_salary
        WHERE employee_id = p_emp_id;


        g_last_updated := SYSDATE;
        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20002, 'Employee not found');
    END give_raise;


END hr_utils;
/

Row-Level Triggers

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


    -- Normalize email
    :NEW.email := LOWER(:NEW.email);


    -- Set audit fields
    :NEW.created_at := SYSDATE;
    :NEW.created_by := USER;
END;
/


-- After update trigger for auditing
CREATE OR REPLACE TRIGGER employees_audit
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.salary != NEW.salary)
BEGIN
    INSERT INTO salary_audit (
        employee_id,
        old_salary,
        new_salary,
        change_date,
        changed_by
    ) VALUES (
        :NEW.employee_id,
        :OLD.salary,
        :NEW.salary,
        SYSDATE,
        USER
    );
END;
/


-- Using INSERTING, UPDATING, DELETING
CREATE OR REPLACE TRIGGER employees_dml
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_log (action, table_name, record_id, action_date)
        VALUES ('INSERT', 'EMPLOYEES', :NEW.employee_id, SYSDATE);
    ELSIF UPDATING THEN
        INSERT INTO audit_log (action, table_name, record_id, action_date)
        VALUES ('UPDATE', 'EMPLOYEES', :NEW.employee_id, SYSDATE);
    ELSIF DELETING THEN
        INSERT INTO audit_log (action, table_name, record_id, action_date)
        VALUES ('DELETE', 'EMPLOYEES', :OLD.employee_id, SYSDATE);
    END IF;
END;
/

Partially Supported Features

BULK COLLECT (Simple Cases)

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_salaries salary_tab;
BEGIN
    -- Simple BULK COLLECT
    SELECT employee_id, salary
    BULK COLLECT INTO v_emp_ids, v_salaries
    FROM employees
    WHERE department_id = 10;


    FOR i IN 1..v_emp_ids.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Emp ' || v_emp_ids(i) || ': $' || v_salaries(i));
    END LOOP;
END;
/

FORALL (Basic Operations)

DECLARE
    TYPE emp_id_tab IS TABLE OF NUMBER;
    v_emp_ids emp_id_tab := emp_id_tab(101, 102, 103, 104, 105);
BEGIN
    -- Basic FORALL update
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employees
        SET salary = salary * 1.05
        WHERE employee_id = v_emp_ids(i);


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

REF CURSOR (Weak Cursors)

CREATE OR REPLACE PROCEDURE get_employees(
    p_dept_id IN NUMBER,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        WHERE department_id = p_dept_id
        ORDER BY salary DESC;
END;
/


-- Using the REF CURSOR
DECLARE
    v_cursor SYS_REFCURSOR;
    v_emp_id NUMBER;
    v_name VARCHAR2(100);
    v_salary NUMBER;
BEGIN
    get_employees(10, v_cursor);


    LOOP
        FETCH v_cursor INTO v_emp_id, v_name, v_salary;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ': ' || v_name || ' - $' || v_salary);
    END LOOP;


    CLOSE v_cursor;
END;
/

Collections (Nested Tables)

DECLARE
    -- Nested table type
    TYPE number_table IS TABLE OF NUMBER;
    TYPE string_table IS TABLE OF VARCHAR2(100);


    v_numbers number_table := number_table(10, 20, 30, 40, 50);
    v_names string_table := string_table();
BEGIN
    -- Collection methods
    DBMS_OUTPUT.PUT_LINE('Count: ' || v_numbers.COUNT);
    DBMS_OUTPUT.PUT_LINE('First: ' || v_numbers.FIRST);
    DBMS_OUTPUT.PUT_LINE('Last: ' || v_numbers.LAST);


    -- Check existence
    IF v_numbers.EXISTS(3) THEN
        DBMS_OUTPUT.PUT_LINE('Element 3: ' || v_numbers(3));
    END IF;


    -- EXTEND and add elements
    v_names.EXTEND(3);
    v_names(1) := 'John';
    v_names(2) := 'Jane';
    v_names(3) := 'Bob';


    -- Iterate
    FOR i IN v_names.FIRST..v_names.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Name ' || i || ': ' || v_names(i));
    END LOOP;


    -- DELETE element
    v_numbers.DELETE(3);  -- Delete element at index 3


    -- Iterate remaining
    DBMS_OUTPUT.PUT_LINE('After delete:');
    FOR i IN v_numbers.FIRST..v_numbers.LAST LOOP
        IF v_numbers.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('  ' || i || ': ' || v_numbers(i));
        END IF;
    END LOOP;
END;
/

Unsupported Features

Autonomous Transactions

Not Supported: PRAGMA AUTONOMOUS_TRANSACTION

Workaround: Use separate connections or application-level transaction management.

-- NOT SUPPORTED:
-- CREATE PROCEDURE log_error(p_msg VARCHAR2) AS
--     PRAGMA AUTONOMOUS_TRANSACTION;
-- BEGIN
--     INSERT INTO error_log VALUES (SYSDATE, p_msg);
--     COMMIT;  -- Independent commit
-- END;


-- WORKAROUND: Log to a separate table after main transaction
CREATE OR REPLACE PROCEDURE do_work_with_logging AS
    v_error_msg VARCHAR2(4000);
BEGIN
    -- Main work
    BEGIN
        UPDATE employees SET salary = salary * 1.1;
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            v_error_msg := SQLERRM;
            ROLLBACK;
            -- Log after rollback completes
            INSERT INTO error_log (log_date, message) VALUES (SYSDATE, v_error_msg);
            COMMIT;
            RAISE;
    END;
END;
/

Pipelined Functions

Not Supported: PIPELINED keyword

Workaround: Return collection or use regular cursor.

-- NOT SUPPORTED:
-- CREATE FUNCTION get_numbers RETURN number_tab PIPELINED AS
-- BEGIN
--     FOR i IN 1..10 LOOP
--         PIPE ROW(i);
--     END LOOP;
-- END;


-- WORKAROUND: Return collection
CREATE OR REPLACE FUNCTION get_numbers RETURN number_tab AS
    v_result number_tab := number_tab();
BEGIN
    FOR i IN 1..10 LOOP
        v_result.EXTEND;
        v_result(v_result.COUNT) := i;
    END LOOP;
    RETURN v_result;
END;
/

Compound Triggers

Not Supported: COMPOUND TRIGGER syntax

Workaround: Use separate triggers for each timing point.

-- NOT SUPPORTED:
-- CREATE TRIGGER employees_compound
-- FOR INSERT OR UPDATE ON employees
-- COMPOUND TRIGGER
--     BEFORE STATEMENT IS ...
--     BEFORE EACH ROW IS ...
--     AFTER EACH ROW IS ...
--     AFTER STATEMENT IS ...
-- END;


-- WORKAROUND: Multiple separate triggers
CREATE OR REPLACE TRIGGER employees_before_stmt
BEFORE INSERT OR UPDATE ON employees
BEGIN
    -- Statement-level before logic
    NULL;
END;
/


CREATE OR REPLACE TRIGGER employees_before_row
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Row-level before logic
    NULL;
END;
/


CREATE OR REPLACE TRIGGER employees_after_row
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    -- Row-level after logic
    NULL;
END;
/

FORALL SAVE EXCEPTIONS

Partially Supported: Basic FORALL works, but SAVE EXCEPTIONS may not fully capture all errors.

-- WORKAROUND: Use loop with individual error handling
DECLARE
    TYPE emp_id_tab IS TABLE OF NUMBER;
    v_emp_ids emp_id_tab := emp_id_tab(101, 999, 102, 998, 103);
    v_error_count NUMBER := 0;
BEGIN
    FOR i IN 1..v_emp_ids.COUNT LOOP
        BEGIN
            UPDATE employees
            SET salary = salary * 1.05
            WHERE employee_id = v_emp_ids(i);


            IF SQL%ROWCOUNT = 0 THEN
                DBMS_OUTPUT.PUT_LINE('No employee found: ' || v_emp_ids(i));
                v_error_count := v_error_count + 1;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error for ' || v_emp_ids(i) || ': ' || SQLERRM);
                v_error_count := v_error_count + 1;
        END;
    END LOOP;


    DBMS_OUTPUT.PUT_LINE('Total errors: ' || v_error_count);
    COMMIT;
END;
/

Conversion Patterns

Pattern 1: DECODE to CASE

Oracle’s DECODE is supported, but CASE is more portable:

-- Oracle DECODE
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM table;


-- ANSI CASE (recommended)
SELECT CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END FROM table;

Pattern 2: ROWNUM Pagination to FETCH FIRST

-- Oracle ROWNUM (supported but limited)
SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM table t WHERE ROWNUM <= 20
) WHERE rn > 10;


-- ANSI Standard (recommended)
SELECT * FROM table
ORDER BY column
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Pattern 3: (+) Joins to ANSI Joins

-- Oracle proprietary join (partially supported)
SELECT a.*, b.*
FROM table_a a, table_b b
WHERE a.id = b.id(+);


-- ANSI Standard (recommended)
SELECT a.*, b.*
FROM table_a a
LEFT OUTER JOIN table_b b ON a.id = b.id;

Pattern 4: Complex Package State

-- Oracle package with session state (limited support)
-- Package variables persist across calls in Oracle


-- WORKAROUND: Use context or session tables
CREATE TABLE session_state (
    session_id VARCHAR2(100),
    key VARCHAR2(100),
    value VARCHAR2(4000),
    PRIMARY KEY (session_id, key)
);


CREATE OR REPLACE PACKAGE session_utils AS
    PROCEDURE set_value(p_key VARCHAR2, p_value VARCHAR2);
    FUNCTION get_value(p_key VARCHAR2) RETURN VARCHAR2;
END;
/


CREATE OR REPLACE PACKAGE BODY session_utils AS
    FUNCTION get_session_id RETURN VARCHAR2 IS
    BEGIN
        RETURN SYS_CONTEXT('USERENV', 'SESSIONID');
    END;


    PROCEDURE set_value(p_key VARCHAR2, p_value VARCHAR2) IS
    BEGIN
        MERGE INTO session_state s
        USING (SELECT get_session_id() AS sid, p_key AS k, p_value AS v FROM DUAL) src
        ON (s.session_id = src.sid AND s.key = src.k)
        WHEN MATCHED THEN UPDATE SET s.value = src.v
        WHEN NOT MATCHED THEN INSERT VALUES (src.sid, src.k, src.v);
        COMMIT;
    END;


    FUNCTION get_value(p_key VARCHAR2) RETURN VARCHAR2 IS
        v_value VARCHAR2(4000);
    BEGIN
        SELECT value INTO v_value
        FROM session_state
        WHERE session_id = get_session_id() AND key = p_key;
        RETURN v_value;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN NULL;
    END;
END;
/

Pattern 5: DBMS_PIPE Alternative

-- Oracle DBMS_PIPE (not supported)
-- Used for inter-session communication


-- WORKAROUND: Use queue table
CREATE TABLE message_queue (
    msg_id NUMBER PRIMARY KEY,
    channel VARCHAR2(100),
    message CLOB,
    created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
    processed NUMBER DEFAULT 0
);


CREATE SEQUENCE msg_seq;


-- Send message
CREATE OR REPLACE PROCEDURE send_message(
    p_channel VARCHAR2,
    p_message CLOB
) AS
BEGIN
    INSERT INTO message_queue (msg_id, channel, message)
    VALUES (msg_seq.NEXTVAL, p_channel, p_message);
    COMMIT;
END;
/


-- Receive message
CREATE OR REPLACE FUNCTION receive_message(
    p_channel VARCHAR2,
    p_timeout NUMBER DEFAULT 10
) RETURN CLOB AS
    v_message CLOB;
    v_msg_id NUMBER;
BEGIN
    SELECT msg_id, message INTO v_msg_id, v_message
    FROM message_queue
    WHERE channel = p_channel AND processed = 0
    ORDER BY created_at
    FETCH FIRST 1 ROW ONLY
    FOR UPDATE SKIP LOCKED;


    UPDATE message_queue SET processed = 1 WHERE msg_id = v_msg_id;
    COMMIT;
    RETURN v_message;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
/

Working Code Examples

Complete Employee Management Package

-- Package for comprehensive employee management
CREATE OR REPLACE PACKAGE emp_management AS
    -- Types
    TYPE emp_summary_rec IS RECORD (
        department_id NUMBER,
        dept_name VARCHAR2(100),
        emp_count NUMBER,
        total_salary NUMBER,
        avg_salary NUMBER
    );


    TYPE emp_summary_tab IS TABLE OF emp_summary_rec;


    -- Procedures
    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 transfer_employee(
        p_emp_id IN NUMBER,
        p_new_dept_id IN NUMBER
    );


    PROCEDURE give_raise(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER
    );


    -- Functions
    FUNCTION get_employee_count(p_dept_id NUMBER DEFAULT NULL) RETURN NUMBER;
    FUNCTION calculate_annual_compensation(p_emp_id NUMBER) RETURN NUMBER;
    FUNCTION get_department_summary RETURN emp_summary_tab;


END emp_management;
/


CREATE OR REPLACE PACKAGE BODY emp_management AS


    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
    BEGIN
        -- Validate salary
        IF p_salary < 30000 OR p_salary > 500000 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary must be between 30000 and 500000');
        END IF;


        -- Generate new ID
        SELECT emp_seq.NEXTVAL INTO p_emp_id FROM DUAL;


        -- Insert 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, LOWER(p_email),
            SYSDATE, p_salary, p_dept_id
        );


        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Hired employee ' || p_emp_id || ': ' || p_first_name);
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            RAISE_APPLICATION_ERROR(-20002, 'Email already exists: ' || p_email);
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
    END hire_employee;


    PROCEDURE transfer_employee(
        p_emp_id IN NUMBER,
        p_new_dept_id IN NUMBER
    ) AS
        v_old_dept NUMBER;
    BEGIN
        SELECT department_id INTO v_old_dept
        FROM employees
        WHERE employee_id = p_emp_id;


        UPDATE employees
        SET department_id = p_new_dept_id
        WHERE employee_id = p_emp_id;


        -- Log transfer
        INSERT INTO emp_transfers (employee_id, from_dept, to_dept, transfer_date)
        VALUES (p_emp_id, v_old_dept, p_new_dept_id, SYSDATE);


        COMMIT;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20003, 'Employee not found: ' || p_emp_id);
    END transfer_employee;


    PROCEDURE give_raise(
        p_emp_id IN NUMBER,
        p_percent IN NUMBER
    ) AS
        v_current_salary NUMBER;
        v_new_salary NUMBER;
    BEGIN
        SELECT salary INTO v_current_salary
        FROM employees
        WHERE employee_id = p_emp_id
        FOR UPDATE;


        v_new_salary := v_current_salary * (1 + p_percent / 100);


        IF v_new_salary > 500000 THEN
            RAISE_APPLICATION_ERROR(-20004, 'New salary exceeds maximum');
        END IF;


        UPDATE employees
        SET salary = v_new_salary
        WHERE employee_id = p_emp_id;


        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Updated salary from ' || v_current_salary ||
                           ' to ' || v_new_salary);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE_APPLICATION_ERROR(-20003, 'Employee not found: ' || p_emp_id);
    END give_raise;


    FUNCTION get_employee_count(p_dept_id NUMBER DEFAULT NULL) RETURN NUMBER AS
        v_count NUMBER;
    BEGIN
        IF p_dept_id IS NULL THEN
            SELECT COUNT(*) INTO v_count FROM employees;
        ELSE
            SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_dept_id;
        END IF;
        RETURN v_count;
    END get_employee_count;


    FUNCTION calculate_annual_compensation(p_emp_id NUMBER) RETURN NUMBER AS
        v_salary NUMBER;
        v_bonus NUMBER;
    BEGIN
        SELECT salary, NVL(bonus, 0)
        INTO v_salary, v_bonus
        FROM employees
        WHERE employee_id = p_emp_id;


        RETURN (v_salary * 12) + v_bonus;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN RETURN NULL;
    END calculate_annual_compensation;


    FUNCTION get_department_summary RETURN emp_summary_tab AS
        v_result emp_summary_tab := emp_summary_tab();
    BEGIN
        FOR rec IN (
            SELECT d.department_id,
                   d.department_name,
                   COUNT(e.employee_id) AS emp_count,
                   NVL(SUM(e.salary), 0) AS total_salary,
                   NVL(AVG(e.salary), 0) AS avg_salary
            FROM departments d
            LEFT JOIN employees e ON d.department_id = e.department_id
            GROUP BY d.department_id, d.department_name
            ORDER BY d.department_id
        ) LOOP
            v_result.EXTEND;
            v_result(v_result.COUNT).department_id := rec.department_id;
            v_result(v_result.COUNT).dept_name := rec.department_name;
            v_result(v_result.COUNT).emp_count := rec.emp_count;
            v_result(v_result.COUNT).total_salary := rec.total_salary;
            v_result(v_result.COUNT).avg_salary := rec.avg_salary;
        END LOOP;


        RETURN v_result;
    END get_department_summary;


END emp_management;
/

Best Practices

1. Keep PL/SQL Simple

HeliosDB handles simple, straightforward PL/SQL best. Avoid deeply nested constructs.

-- Good: Simple, clear logic
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_new_sal NUMBER) AS
BEGIN
    UPDATE employees SET salary = p_new_sal WHERE employee_id = p_emp_id;
    COMMIT;
END;
/


-- Avoid: Overly complex nested logic
-- CREATE OR REPLACE PROCEDURE complex_procedure AS
--     -- Multiple levels of nesting, complex cursor operations, etc.
-- BEGIN ...

2. Use Explicit Cursors Over Implicit

-- Preferred: Explicit cursor
DECLARE
    CURSOR emp_cur IS SELECT * FROM employees;
    v_emp emp_cur%ROWTYPE;
BEGIN
    OPEN emp_cur;
    LOOP
        FETCH emp_cur INTO v_emp;
        EXIT WHEN emp_cur%NOTFOUND;
        -- Process v_emp
    END LOOP;
    CLOSE emp_cur;
END;
/

3. Handle Exceptions Explicitly

BEGIN
    -- Your code
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle specifically
    WHEN TOO_MANY_ROWS THEN
        -- Handle specifically
    WHEN OTHERS THEN
        -- Log and re-raise
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

4. Avoid Package State

Minimize reliance on package-level variables that persist across calls.

5. Test Incrementally

Test PL/SQL code in small blocks before combining into larger procedures.

Troubleshooting

Common Error Messages

ErrorCauseSolution
PLS-00201: identifier not declaredUnknown package/functionCheck DBMS package support
PLS-00103: Encountered symbolSyntax errorVerify construct is supported
PLS-00306: wrong number of argumentsParameter mismatchCheck procedure signature
ORA-06550: line N, column NPL/SQL compilation errorReview error details
PRA-00000: autonomous transactionAutonomous transaction usedUse workaround pattern

Debugging Tips

  1. Use DBMS_OUTPUT.PUT_LINE liberally for debugging
  2. Test anonymous blocks before creating stored procedures
  3. Break complex logic into smaller procedures
  4. Check compatibility matrix for unsupported features

Last Updated: January 2026 PL/SQL Engine Version: HeliosDB 7.0+