Dancing with Oracle Packages: A Complete Code Walkthrough

Oracle Packages in PL/SQL are powerful tools for modularizing and organizing code. They allow you to group logically related procedures, functions, and variables together. This article demonstrates the structure and usage of Oracle packages with a comprehensive example.

Step 1: Package Specification

The package specification declares public variables, procedures, and functions that can be accessed outside the package.

CREATE OR REPLACE PACKAGE PL_PKG AS 

    -- Public Variables Declaration
    v_tax NUMBER;

    -- Public Procedures Declaration
    PROCEDURE CALCULATOR;

    -- Public Functions Declaration
    FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER;

END PL_PKG;
/

Key Points:

1. v_tax: Public variable accessible globally.
2. CALCULATOR: Public procedure to perform calculations.
3. TAX: Public function to compute tax.

Step 2: Package Body

The package body contains the implementation of the procedures, functions, and variables declared in the specification.

CREATE OR REPLACE PACKAGE BODY PL_PKG AS

    -- Private Variables Declaration
    V_COUNTER NUMBER DEFAULT 0;

    -- Forward Declaration
    PROCEDURE P_SP_CALC(NUM IN NUMBER);

    -- Procedure Implementation
    PROCEDURE CALCULATOR AS
    BEGIN
        -- Call the private procedure
        P_SP_CALC(100);
        V_COUNTER := V_COUNTER + 1;
        DBMS_OUTPUT.PUT_LINE('V_COUNTER := ' || V_COUNTER);
    END CALCULATOR;

    -- Private Procedure Implementation
    PROCEDURE P_SP_CALC(NUM IN NUMBER) AS
    BEGIN
        NULL;
    END P_SP_CALC;

    -- Public Function Implementation
    FUNCTION TAX (P_VALUE IN NUMBER) RETURN NUMBER IS
        V_RATE NUMBER := 0.1;
    BEGIN
        RETURN (P_VALUE * V_RATE);
    END TAX;

    -- Initialization Portion (Executes once when the package is loaded)
    BEGIN
        V_COUNTER := 100;
    END PL_PKG;
/

Key Points:

1. Private Variables and Procedures:
   • V_COUNTER: Private variable initialized to 100.
   • P_SP_CALC: Private procedure for internal use.
2. Public Procedure:
   • CALCULATOR: Uses private procedure and updates the counter.
3. Public Function:
   • TAX: Computes tax at a rate of 10%.

Step 3: Testing the Package

Execute the following anonymous block to test the package:

DECLARE
    P_VALUE NUMBER;
    v_Return NUMBER;
BEGIN
    -- Call the CALCULATOR procedure
    PL_PKG.CALCULATOR();
    PL_PKG.CALCULATOR();
    PL_PKG.CALCULATOR();

    -- Test the TAX function
    P_VALUE := NULL;
    v_Return := PL_PKG.TAX(P_VALUE => P_VALUE);
    DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;
/

Expected Output:

1. V_COUNTER Updates:

   V_COUNTER := 101
   V_COUNTER := 102
   V_COUNTER := 103
   

2. Tax Calculation:

   v_Return = 0
   

Step 4: Understanding the Execution Flow

1. Initialization:
   • When the package is loaded, the BEGIN block in the package body sets V_COUNTER to 100.
2. Procedure Execution:
   • Each call to CALCULATOR invokes P_SP_CALC and increments V_COUNTER.
3. Function Execution:
   • The TAX function computes tax. If P_VALUE is NULL, the return value is 0 (default Oracle behavior).

Conclusion

This example demonstrates the power of Oracle packages for encapsulating functionality and maintaining modularity. By defining public and private components, packages provide a clean way to manage application logic and ensure reusability.

Would you like additional examples or advanced package techniques? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio