---
title: "PL/SQL Support in HeliosDB"
---

# PL/SQL Support in HeliosDB

PL/SQL in HeliosDB Full: the roadmap for running PL/SQL through the Oracle listener, the constructs in scope, conversion patterns for features outside that scope, and code examples.

:::note[PL/SQL roadmap]
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.
:::

## Table of Contents

- [Overview](#overview)
- [PL/SQL Constructs in Scope](#plsql-constructs-in-scope)
- [Partial Coverage](#partial-coverage)
- [Unsupported Features](#unsupported-features)
- [Conversion Patterns](#conversion-patterns)
- [Code Examples](#code-examples)
- [Best Practices](#best-practices)
- [Troubleshooting](#troubleshooting)

---

## Overview

### PL/SQL Roadmap

The planned scope covers the constructs common in business applications, listed on this page; some advanced Oracle-specific features will still need conversion (see [Unsupported Features](#unsupported-features) and [Conversion Patterns](#conversion-patterns)).

### Key Capabilities

| Capability | Planned scope | Notes |
|------------|---------------|-------|
| Anonymous blocks | In scope | DECLARE/BEGIN/END |
| Stored procedures | In scope | CREATE PROCEDURE |
| Functions | In scope | CREATE FUNCTION |
| Packages | Basic forms | Basic spec/body |
| Triggers | Basic forms | Row-level DML |
| Cursors | In scope | Explicit and implicit |
| Exception handling | In scope | WHEN/RAISE |
| Control flow | In scope | IF, CASE, LOOP |
| Collections | Basic forms | Basic nested tables |
| BULK operations | Basic forms | Simple BULK COLLECT |

---

## PL/SQL Constructs in Scope

### Variables and Constants

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
-- 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

```sql
-- 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

```sql
-- 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;
/
```

---

## Partial Coverage

### BULK COLLECT (Simple Cases)

```sql
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)

```sql
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)

```sql
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)

```sql
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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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.

```sql
-- 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:

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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;
/
```

---

## Code Examples

### Complete Employee Management Package

```sql
-- 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

Simple, straightforward PL/SQL ports to PL/pgSQL most easily today and is the best fit for the planned Oracle-listener support. Avoid deeply nested constructs.

```sql
-- 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

```sql
-- 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

```sql
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

Until PL/SQL runs through the Oracle listener, a PL/SQL block sent there reports success without running; the `PLS-`/`ORA-` messages below apply once it does.

| Error | Cause | Solution |
|-------|-------|----------|
| Block reports success but has no effect | PL/SQL is not executed through the Oracle listener yet | Port it to PL/pgSQL and run it through the PostgreSQL interface, or move the logic to the application |
| PLS-00201: identifier not declared | Unknown package/function | Check DBMS package support |
| PLS-00103: Encountered symbol | Syntax error | Verify construct is supported |
| PLS-00306: wrong number of arguments | Parameter mismatch | Check procedure signature |
| ORA-06550: line N, column N | PL/SQL compilation error | Review error details |
| PRA-00000: autonomous transaction | Autonomous transaction used | Use 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

---

## Related Documentation

- **[README.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/readme/)** - Protocol overview
- **[CONFIGURATION.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/configuration/)** - PL/SQL configuration settings
- **[COMPATIBILITY.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/compatibility/)** - Full compatibility matrix
- **[EXAMPLES.md](https://www.heliosdb.com/docs/full/reference/protocols/oracle/examples/)** - More PL/SQL examples