Stored Procedures, Functions, and Packages

IN/OUT parameters, exception handling, RAISE_APPLICATION_ERROR, and building packages.

Stored procedures

A stored procedure is a named PL/SQL block, compiled and stored inside the database, callable by name instead of retyping the same logic in every application or anonymous block that needs it. It's declared with CREATE OR REPLACE PROCEDURE — OR REPLACE means re-running the same CREATE statement updates the existing procedure in place rather than failing because it already exists, which is how you'll redeploy a changed procedure in practice.

SQL
CREATE OR REPLACE PROCEDURE give_raise (
    p_employee_id IN  employees.employee_id%TYPE,
    p_percent     IN  NUMBER,
    p_new_salary  OUT employees.salary%TYPE
) AS
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent / 100)
    WHERE employee_id = p_employee_id
    RETURNING salary INTO p_new_salary;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'No employee found with ID ' || p_employee_id);
    END IF;
END give_raise;
/

Calling it from an anonymous block:

SQL
DECLARE
    v_new_salary employees.salary%TYPE;
BEGIN
    give_raise(p_employee_id => 201, p_percent => 5, p_new_salary => v_new_salary);
    DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_salary);
END;
/

IN, OUT, and IN OUT parameters

IN (the default if nothing is specified) — passes a value into the procedure; the procedure can read it but any local reassignment doesn't affect the caller's variable.
OUT — the procedure sets this parameter and the caller's variable receives the new value once the procedure finishes; an OUT parameter's incoming value is undefined, so never rely on reading it before assigning to it.
IN OUT — the caller's value flows in, the procedure can read and modify it, and the final value flows back out. Genuinely uncommon compared to IN/OUT alone, but useful for something like an accumulator variable threaded through several procedure calls.

SQL%ROWCOUNT above is an implicit cursor attribute reporting how many rows the most recent UPDATE/INSERT/DELETE affected — checking it right after the UPDATE is the standard way to notice "that update matched nothing" without a separate SELECT just to check existence first. RETURNING ... INTO is an Oracle-specific clause that captures a column's post-update value directly from the DML statement, avoiding a second round-trip SELECT to re-read what was just written.

Functions

A function is declared almost identically to a procedure, but must specify a RETURN type and return exactly one value with a RETURN statement — and, unlike a procedure, a function can be called directly from inside a SQL statement, not just from PL/SQL:

SQL
CREATE OR REPLACE FUNCTION get_department_headcount (
    p_department_id IN departments.department_id%TYPE
) RETURN NUMBER
IS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM employees
    WHERE department_id = p_department_id;

    RETURN v_count;
END get_department_headcount;
/

SQL
SELECT department_name, get_department_headcount(department_id) AS headcount
FROM departments;

That last query calling a PL/SQL function directly inside SELECT is routine in Oracle and is one of the more distinctive things about how tightly PL/SQL integrates with the SQL engine — there's no separate round trip to "the application layer" to compute a value like this.

Exception handling

Oracle raises a specific, named exception for common error conditions, and the EXCEPTION section catches them by name:

SQL
DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 9999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No such employee.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query matched more than one row unexpectedly.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

NO_DATA_FOUND and TOO_MANY_ROWS are two of Oracle's predefined exceptions (others include DUP_VAL_ON_INDEX for a unique-constraint violation, ZERO_DIVIDE, and VALUE_ERROR). WHEN OTHERS is a catch-all for anything not explicitly handled — useful for logging, but resist the temptation to swallow every error into a generic WHEN OTHERS that hides the real problem; catch specific, expected exceptions by name wherever you reasonably can.

Custom exceptions and RAISE_APPLICATION_ERROR

Real business rules don't map to Oracle's built-in exception list — "you can't give a negative raise" isn't a database error at all unless you make it one. Two ways to do that:

RAISE_APPLICATION_ERROR raises an error with a custom message and a custom error number (any integer from -20000 to -20999 is reserved for application use) directly, with no need to declare anything up front — this is exactly what give_raise used above to reject an unmatched employee ID:

SQL

IF p_percent < 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'Raise percentage cannot be negative.');
END IF;

A declared custom exception is worth the extra step when the same error condition needs to be raised and caught in more than one place, or referred to by a meaningful name rather than a bare error number:

SQL
DECLARE
    e_negative_raise EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_negative_raise, -20002);
BEGIN
    IF :percent < 0 THEN
        RAISE e_negative_raise;
    END IF;
EXCEPTION
    WHEN e_negative_raise THEN
        DBMS_OUTPUT.PUT_LINE('Rejected: raise percentage cannot be negative.');
END;
/

PRAGMA EXCEPTION_INIT links a named exception to a specific Oracle error number, so WHEN e_negative_raise THEN reads far more clearly at the call site than WHEN OTHERS THEN IF SQLCODE = -20002 THEN ....

Packages

A package groups related procedures, functions, variables, and cursors into one named unit — the PL/SQL equivalent of a module or a namespace. It has two parts: a specification (the public interface — what callers outside the package can see) and a body (the implementation, which can also contain private helpers that never appear in the specification and therefore aren't callable from outside the package at all).

SQL
CREATE OR REPLACE PACKAGE emp_pkg AS
    PROCEDURE give_raise(p_employee_id IN NUMBER, p_percent IN NUMBER);
    FUNCTION  get_headcount(p_department_id IN NUMBER) RETURN NUMBER;
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

    -- Private helper: not declared in the package spec, so it's invisible outside this body
    FUNCTION is_valid_percent(p_percent IN NUMBER) RETURN BOOLEAN IS
    BEGIN
        RETURN p_percent BETWEEN -50 AND 50;
    END is_valid_percent;

    PROCEDURE give_raise(p_employee_id IN NUMBER, p_percent IN NUMBER) IS
    BEGIN
        IF NOT is_valid_percent(p_percent) THEN
            RAISE_APPLICATION_ERROR(-20002, 'Raise percentage out of allowed range.');
        END IF;

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

        IF SQL%ROWCOUNT = 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'No employee found with ID ' || p_employee_id);
        END IF;
    END give_raise;

    FUNCTION get_headcount(p_department_id IN NUMBER) RETURN NUMBER IS
        v_count NUMBER;
    BEGIN
        SELECT COUNT(*) INTO v_count FROM employees WHERE department_id = p_department_id;
        RETURN v_count;
    END get_headcount;

END emp_pkg;
/

Calling into it uses ordinary dot notation:

SQL
BEGIN
    emp_pkg.give_raise(p_employee_id => 201, p_percent => 5);
END;
/

SELECT emp_pkg.get_headcount(20) FROM DUAL;

Packages carry real practical advantages over a pile of standalone procedures: related logic is grouped and versioned together, private helper logic (like is_valid_percent above) stays genuinely hidden from callers, package-level variables and cursors can hold state for the duration of a session, and Oracle loads and caches a whole package as one unit — which in practice tends to reduce parsing overhead compared to many separately-managed standalone procedures. Real Oracle applications lean on packages heavily; a standalone procedure is more the exception than the rule once a schema has more than a handful of related operations.

Common mistakes

Using WHEN OTHERS as a blanket catch-all with no re-raise and no logging, silently swallowing errors that should have surfaced — always log at minimum (SQLERRM, SQLCODE), and consider re-raising with RAISE if the caller genuinely needs to know something failed.
Forgetting that an OUT parameter's value is undefined until the procedure explicitly assigns it — reading it beforehand (inside the procedure) is a mistake, not a shortcut.
Committing (or rolling back) inside a reusable procedure that might be called as part of a larger caller transaction — as a general design rule, let the caller decide when to COMMIT/ROLLBACK unless the procedure is explicitly documented as managing its own transaction.
Declaring everything as loose standalone procedures once a schema has many related operations, instead of grouping them into a package — losing the organizational, encapsulation, and loading benefits packages provide for free.

Stored Procedures, Functions, and Packages