PL/SQL Stored Procedures and Functions

PL/SQL stored procedures and functions are schema objects that encapsulate SQL and PL/SQL statements into reusable, named units. By moving logic from the application tier to the database tier, you reduce network traffic, enforce security boundaries, and ensure complex business rules are applied consistently across all consuming services.

These subprograms solve two production problems simultaneously: repeated logic across applications and excessive network round-trips. Oracle parses, compiles, and optimizes the code once, storing the executable form in the library cache. Every application — Java microservice, Python script, or legacy ERP — benefits from the same execution plan.

By the end, you'll have the conceptual understanding and practical code examples to use PL/SQL stored procedures and functions with confidence, including the production failure modes that catch teams off guard.

The Core Mechanics: Procedures for Action, Functions for Logic

PL/SQL Stored Procedures and Functions is a core feature of PL/SQL. It was designed to solve the problem of repeated logic and high network latency. Procedures are typically used to perform an action (DML operations like INSERT, UPDATE, or DELETE), while Functions are designed to compute and return a single value.

They exist to provide 'compiled' performance — Oracle parses, compiles, and optimizes the code once, storing the executable form in the database's library cache. This ensures that every application — whether it's a Java microservice, a Python script, or a legacy ERP — benefits from the same high-performance execution plan and centralized business logic. Furthermore, by granting EXECUTE privileges on a procedure rather than direct SELECT/UPDATE access on tables, you implement a robust 'Principle of Least Privilege' security model.

-- io.thecodeforge: Function to calculate discounted price
-- Optimized with DETERMINISTIC ifecodeforge.fn_get_discounted_price(
    p_price    IN NUMBER,
    p results are consistent for inputs
CREATE OR REPLACE FUNCTION io.th_discount IN NUMBER
) RETURN NUMBER DETERMINISTIC IS
BEGIN
    -- Functions must return a value and avoid side effects
    RETURN ROUND(p_price * (1 - (p_discount / 100)), 2);
END fn_get_discounted_price;
/

-- io.thecodeforge: Procedure to update project budget
CREATE OR REPLACE PROCEDURE io.thecodeforge.sp_update_budget(
    p_project_id IN NUMBER,
    p_new_budget IN NUMBER
) IS
    v_old_budget NUMBER;
BEGIN
    -- Logical validation before DML
    SELECT budget INTO v_old_budget FROM forge_projects WHERE id = p_project_id;
    
    IF p_new_budget < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Budget cannot be negative.');
    END IF;

    UPDATE forge_projects 
    SET budget = p_new_budget, 
        last_updated = SYSDATE
    WHERE id = p_project_id;
    
    -- Commit logic usually belongs to the caller, but can be here for batch tasks
    COMMIT;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, 'Project ID not found.');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- Propagate error to the application tier
END sp_update_budget;
/

Parameter Modes and Production Gotchas

When learning PL/SQL Stored Procedures and Functions, most developers hit the same set of gotchas. A frequent mistake is performing DML (Insert/Update/Delete) inside a Function; while technically possible using 'Autonomous Transactions,' it prevents the function from being used in a standard SELECT statement because it violates purity levels (WNDS - Writes No Database State).

Another error is failing to specify parameter modes (IN, OUT, IN OUT) correctly. By default, parameters are 'IN' (read-only). If you need to return multiple values from a single subprogram, a Procedure with 'OUT' parameters is your best friend. However, be cautious with 'IN OUT' parameters, as they can lead to side effects where the calling environment's variable is mutated unexpectedly. Production-grade code should also consider the 'NOCOPY' hint for large collection parameters to avoid expensive memory copying.

-- io.thecodeforge: Using OUT parameters correctly for metadata retrieval
CREATE OR REPLACE PROCEDURE io.thecodeforge.sp_get_project_meta(
    p_id      IN  NUMBER,
    p_name    OUT VARCHAR2,
    p_version OUT VARCHAR2,
    p_status  OUT VARCHAR2
) IS
BEGIN
    -- One trip to the DB to fetch multiple values (Network Efficiency)
    SELECT name, version_info, status
    INTO p_name, p_version, p_status
    FROM forge_projects 
    WHERE id = p_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_name    := 'NOT_FOUND';
        p_version := '0.0.0';
        p_status  := 'INACTIVE';
    WHEN TOO_MANY_ROWS THEN
        RAISE_APPLICATION_ERROR(-20003, 'Data integrity error: duplicate project IDs.');
END sp_get_project_meta;
/

Exception Handling: The Production Safety Net

Exception handling in PL/SQL is not optional — it is the difference between a procedure that fails gracefully with a meaningful error and one that silently corrupts data or leaves the application blind. Oracle provides named exceptions (NO_DATA_FOUND, DUP_VAL_ON_INDEX, TOO_MANY_ROWS) for common conditions, and RAISE_APPLICATION_ERROR for custom business-rule violations.

The single most dangerous pattern in production PL/SQL is WHEN OTHERS THEN NULL. It catches every exception — including ones you never anticipated — and discards them silently. The procedure completes 'successfully,' the caller thinks the operation worked, but the data was never written. This pattern has caused silent data corruption in every Oracle shop I've worked in.

Production-grade exception handling follows three rules: (1) catch specific named exceptions first, (2) log unexpected errors before re-raising them, (3) never let an exception handler exit without either handling the error properly or propagating it to the caller.

-- io.thecodeforge: Production-grade exception handling pattern
CREATE OR REPLACE PROCEDURE io.thecodeforge.sp_process_payment(
    p_order_id   IN NUMBER,
    p_amount     IN NUMBER,
    p_status     OUT VARCHAR2
) IS
    v_customer_id NUMBER;
    v_balance     NUMBER;
BEGIN
    -- Validate order exists
    SELECT customer_id INTO v_customer_id
    FROM forge_orders WHERE order_id = p_order_id;

    -- Check balance
    SELECT balance INTO v_balance
    FROM forge_customers WHERE customer_id = v_customer_id;

    IF v_balance < p_amount THEN
        RAISE_APPLICATION_ERROR(-20100, 'Insufficient balance for order ' || p_order_id);
    END IF;

    -- Deduct and record
    UPDATE forge_customers SET balance = balance - p_amount
    WHERE customer_id = v_customer_id;

    INSERT INTO forge_payments (order_id, amount, paid_on)
    VALUES (p_order_id, p_amount, SYSDATE);

    COMMIT;
    p_status := 'SUCCESS';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        p_status := 'ORDER_NOT_FOUND';
    WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK;
        p_status := 'DUPLICATE_PAYMENT';
    WHEN OTHERS THEN
        ROLLBACK;
        -- Log the unexpected error for investigation
        io.thecodeforge.logging.log_error(
            p_proc  => 'sp_process_payment',
            p_errm  => SQLERRM,
            p_ctx   => 'order_id=' || p_order_id
        );
        RAISE; -- Always re-raise unexpected errors
END sp_process_payment;
/

Frequently Asked Questions