Oracle PL/SQL Core Knowledge

Full Reference: See advanced.md for pipelined table functions, packages, collections, BULK COLLECT/FORALL, compound triggers, and advanced cursors.

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: oracle for comprehensive documentation.

Basic Structure

DECLARE
    -- Variable declarations
    v_count NUMBER := 0;
BEGIN
    -- Executable statements
    DBMS_OUTPUT.PUT_LINE('Hello World');
EXCEPTION
    WHEN OTHERS THEN
        -- Exception handling
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Procedures

Basic Procedure

CREATE OR REPLACE PROCEDURE update_salary(
    p_employee_id IN NUMBER,
    p_percentage  IN NUMBER
)
IS
    v_current_salary NUMBER;
BEGIN
    SELECT salary INTO v_current_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    UPDATE employees
    SET salary = salary * (1 + p_percentage / 100)
    WHERE employee_id = p_employee_id;

    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found: ' || p_employee_id);
END update_salary;
/

-- Execute
EXEC update_salary(100, 10);
-- or
BEGIN
    update_salary(100, 10);
END;
/

Procedure with OUT Parameters

CREATE OR REPLACE PROCEDURE get_employee_info(
    p_employee_id IN  NUMBER,
    p_name        OUT VARCHAR2,
    p_salary      OUT NUMBER,
    p_dept_name   OUT VARCHAR2
)
IS
BEGIN
    SELECT e.first_name || ' ' || e.last_name,
           e.salary,
           d.department_name
    INTO p_name, p_salary, p_dept_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = p_employee_id;
END;
/

-- Call with OUT parameters
DECLARE
    v_name VARCHAR2(100);
    v_salary NUMBER;
    v_dept VARCHAR2(100);
BEGIN
    get_employee_info(100, v_name, v_salary, v_dept);
    DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_salary);
END;
/

Functions

Scalar Function

CREATE OR REPLACE FUNCTION calculate_bonus(
    p_salary IN NUMBER,
    p_years  IN NUMBER
)
RETURN NUMBER
DETERMINISTIC  -- Same inputs always return same output
IS
    v_bonus NUMBER;
BEGIN
    IF p_years >= 10 THEN
        v_bonus := p_salary * 0.15;
    ELSIF p_years >= 5 THEN
        v_bonus := p_salary * 0.10;
    ELSE
        v_bonus := p_salary * 0.05;
    END IF;

    RETURN v_bonus;
END;
/

-- Usage in SQL
SELECT employee_id, salary, calculate_bonus(salary, years_of_service) as bonus
FROM employees;

Cursors

Implicit Cursor

BEGIN
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || SQL%ROWCOUNT);

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows found');
    END IF;
END;
/

Explicit Cursor

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_emp emp_cursor%ROWTYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);
    END LOOP;
    CLOSE emp_cursor;
END;
/

Cursor FOR Loop (Preferred)

BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, salary
                    FROM employees WHERE department_id = 10)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/

Collections

Associative Array (INDEX BY)

DECLARE
    TYPE salary_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE name_tab IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(20);

    salaries salary_tab;
    names    name_tab;
BEGIN
    salaries(1) := 50000;
    salaries(2) := 60000;

    names('EMP001') := 'John Doe';
    names('EMP002') := 'Jane Smith';

    DBMS_OUTPUT.PUT_LINE(salaries(1));
    DBMS_OUTPUT.PUT_LINE(names('EMP001'));
END;
/

Exception Handling

Predefined Exceptions

| Exception | Description | |-----------|-------------| | NO_DATA_FOUND | SELECT INTO returned no rows | | TOO_MANY_ROWS | SELECT INTO returned multiple rows | | ZERO_DIVIDE | Division by zero | | VALUE_ERROR | Numeric or value error | | INVALID_CURSOR | Invalid cursor operation | | DUP_VAL_ON_INDEX | Duplicate value on unique index |

Exception Handling

DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple employees found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
        RAISE;
END;
/

RAISE_APPLICATION_ERROR

BEGIN
    IF some_condition THEN
        RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
    END IF;
END;
/

Triggers

Row-Level Trigger

CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;

    IF :NEW.salary > 1000000 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary exceeds maximum');
    END IF;
END;
/

Best Practices

DO

• Use packages to organize related code
• Use BULK COLLECT and FORALL for large datasets
• Use cursor FOR loops (auto open/close)
• Define exceptions at package level
• Use %TYPE and %ROWTYPE for type safety
• Use bind variables to prevent SQL injection

DON'T

• Use implicit commits in triggers
• Ignore exceptions
• Use SELECT INTO without handling NO_DATA_FOUND
• Create excessive triggers (performance impact)

When NOT to Use This Skill

• Basic Oracle SQL - Use oracle skill for queries, data types, partitioning
• PL/pgSQL (PostgreSQL) - Use plpgsql skill for PostgreSQL procedures
• T-SQL (SQL Server) - Use tsql skill for SQL Server procedures
• Basic SQL - Use sql-fundamentals for ANSI SQL basics

Anti-Patterns

| Anti-Pattern | Problem | Solution | |--------------|---------|----------| | Not using BULK COLLECT | Row-by-row processing | Use BULK COLLECT for large datasets | | SELECT INTO without exception | Runtime errors | Handle NO_DATA_FOUND | | Not using packages | Code disorganization | Organize related code in packages | | Excessive triggers | Performance issues | Minimize trigger logic | | WHEN OTHERS without RAISE | Silent failures | Re-raise or log exceptions | | Implicit cursors for large sets | Memory issues | Use explicit cursors with LIMIT |

Quick Troubleshooting

| Problem | Diagnostic | Fix | |---------|------------|-----| | NO_DATA_FOUND | SELECT INTO with no rows | Add exception handler | | TOO_MANY_ROWS | SELECT INTO with multiple rows | Add WHERE or use cursor | | ORA-06502 numeric error | Type conversion failure | Check data types, use TO_NUMBER | | Slow procedure | DBMS_PROFILER | Use BULK operations | | Package state lost | Session reset | Use PRAGMA SERIALLY_REUSABLE or re-initialize |

Reference Documentation

• Procedures
• Functions
• Triggers
• Packages
• Cursors