2.1.3.3 Database Packages

Database packages contain a specification of available procedures and functions. Oracle recommends you use them to organize custom business logic.

A package specification lists the procedures and functions available to call from application pages, triggers, or other packages. It also defines the names and data types of parameters these named program units accept. Parameters let a caller pass data into the function or procedure (IN), receive data back out of it (OUT), or pass data in both directions (IN OUT). Any IN parameter can be mandatory or optional. When not supplied explicitly, optional parameters take on the defined default value. The key distinction between functions and procedures is that a function returns a result value, while a procedure does not.

Public program units are a great way to provide application functionality for teammates to use in pages, workflows, or custom code. They just call a function or procedure, supplying any required parameter values. You can create and maintain your packages in App Builder using SQL Workshop's Object Browser.

For example, the hr_utils package specification below defines one procedure and one function. The p_additional_withholding parameter of the handle_employer_contribution procedure is optional, with a default value of 0 (zero). The p_total_contribution parameter returns information back OUT to the caller, while the other parameters pass IN values as input.

CREATE OR REPLACE PACKAGE hr_utils AS
    ---------------------------------------
    PROCEDURE handle_employer_contribution(
        p_empno                  IN  NUMBER,
        p_additional_withholding IN  NUMBER DEFAULT 0,
        p_total_contribution     OUT NUMBER);
    ---------------------------------------
    FUNCTION years_employed(
        p_hiredate IN DATE)
        RETURN        NUMBER;
END hr_utils;

You implement a package's publicly declared program units in a corresponding package body by writing custom business logic that remains private. The public/private distinction is important. It means you can change how a package's procedure or function is written without affecting the pages or other program units that use it. If you are distributing your application, you can optionally wrap a package body's code to prevent it from being readable.

The hr_utils package body below defines the behavior of the procedure and function in the package's public specification. Notice that the body can contain other program units, like the years_ago helper function, that are not accessible from outside the package. The years_employed function calls this package-private years_ago function.

CREATE OR REPLACE PACKAGE BODY hr_utils AS
    ---------------------------------------
    FUNCTION years_ago(
        p_date IN DATE)
    RETURN NUMBER
    IS
    BEGIN
        RETURN FLOOR(MONTHS_BETWEEN(SYSDATE, p_date) / 12);
    END years_ago;
    ---------------------------------------
    PROCEDURE handle_employer_contribution(
        p_empno                  IN NUMBER,
        p_additional_withholding IN NUMBER DEFAULT 0,
        p_total_contribution     OUT NUMBER)
    IS
    BEGIN
        apex_debug.enter('handle_employer_contribution');
        -- Interesting contribution code here, eventually
        -- assigning a value to p_total_contribution
    END handle_employer_contribution;
    ---------------------------------------
    FUNCTION years_employed(
        p_hiredate IN DATE)
        RETURN        number
    IS
    BEGIN
        RETURN years_ago(p_hiredate);
    END years_employed;
END hr_utils;

When a page, trigger, or another package calls a procedure like handle_employer_contribution, it provides values for the IN parameters and a location to store the OUT parameters. Using PL/SQL named parameter syntax, you can pass parameters in any order. Each value's purpose is clear.

-- Handle the employee's pension contribution
hr_utils.handle_employer_contribution(
    p_additional_withholding => 1500,
    p_empno                  => :P71_EMPLOYEE_ID,
    p_total_contribution     => :P71_TOTAL);

Using the APEX Builder's native "Invoke API" page process or workflow activity, it's also possible to declaratively call package procedures or functions. Simply configure the values for every parameter right in the Page Designer or Workflow Designer. This makes it even easier for less-technical teammates to take advantage of the functionality available to them in packages.