Using ACCESSIBLE BY Clauses to Enforce Package Level Encapsulation

Controlling Who Can Call Your Code

In large PL/SQL codebases, it is common to have utility packages that should only be called by specific application packages. Before Oracle 12c, there was no way to enforce this at the language level. The ACCESSIBLE BY clause changes this by allowing you to declaratively restrict which program units can invoke a given package, function, procedure, or type.

Basic Syntax

CREATE OR REPLACE PACKAGE internal_utils
ACCESSIBLE BY (
    PACKAGE order_processing_pkg,
    PACKAGE inventory_pkg,
    PROCEDURE nightly_batch_job
)
AS
    FUNCTION calculate_discount(
        p_customer_id IN NUMBER,
        p_total       IN NUMBER
    ) RETURN NUMBER;

    PROCEDURE update_audit_trail(
        p_table_name IN VARCHAR2,
        p_action     IN VARCHAR2,
        p_details    IN VARCHAR2
    );
END internal_utils;
/

Any other code that attempts to call these functions or procedures will get a compile-time error, not a runtime error. This is a significant advantage because invalid calls are caught during development rather than in production.

Granular Control in 12c Release 2 and Later

Starting in 12c Release 2, you can apply ACCESSIBLE BY at the individual subprogram level within a package body for finer-grained control:

CREATE OR REPLACE PACKAGE BODY internal_utils AS
    FUNCTION calculate_discount(
        p_customer_id IN NUMBER,
        p_total       IN NUMBER
    )
    ACCESSIBLE BY (PACKAGE order_processing_pkg)
    RETURN NUMBER IS
    BEGIN
        RETURN p_total * 0.1;
    END;
END internal_utils;
/

Practical Applications

Use ACCESSIBLE BY for internal utility packages that implement sensitive business logic like pricing or discount calculations. Use it for audit trail packages where you want to ensure only authorized code paths can write audit records. Use it for security sensitive code like encryption wrappers. The clause makes the dependency graph of your application explicit and enforceable.

Limitations

ACCESSIBLE BY restricts compile-time references. It does not prevent dynamic SQL from calling the restricted code at runtime using EXECUTE IMMEDIATE. It also does not apply to calls made through database links. For complete access control, combine ACCESSIBLE BY with proper GRANT management.