PL/SQL Package Body

A PL/SQL package body serves as an important component in developing modular and maintainable code within the Oracle database environment. It complements the package specification by providing the implementation details for procedures, functions, and other constructs declared in the package.

The package body organizes and modularizes code, hides data, and manages dependencies effectively. In this article, We will explore What is a Package Body, How to create a Package body, How to compile a Package Body along with real-life examples, and so on.

What is a Package Body?

• PL/SQL package body is the main component for the development of the modularity of the code and maintenance of the code in the environment of the Oracle serves.
• It serves the implementation of the PL/SQL package and complements its specification of the package.
• In PL/SQL, the package body can bind the procedures implementation details, functions implementation details, and another construct declared within the specification of the package.
• It provided a way to code organization and code modularization, hiding the data and managing the effective dependencies.

Syntax of PL/SQL package body:

CREATE OR REPLACE PACKAGE BODY package_name AS
    -- Declaration of variables, constants, cursors, types, etc.
    -- These are private to the package body and can be accessed by all procedures and functions within the package body.
    -- For example:
    --   my_variable NUMBER := 10;
    
    -- Procedure declarations
    PROCEDURE procedure_name(parameter1 IN datatype1, parameter2 OUT datatype2) IS
    BEGIN
        -- Procedure logic
        -- For example:
        --   parameter2 := parameter1 * my_variable;
    END procedure_name;
    
    -- Function declarations
    FUNCTION function_name(parameter IN datatype) RETURN datatype IS
        -- Variable declaration specific to the function
        -- For example:
        --   result datatype;
    BEGIN
        -- Function logic
        -- For example:
        --   result := parameter * my_variable;
        --   RETURN result;
    END function_name;

    -- Additional procedures and functions can be declared here

BEGIN
    -- Initialization code
    -- This is executed once when the package is first loaded into memory
    -- For example:
    --   my_variable := 20;
    
    -- Additional initialization code can be written here

EXCEPTION
    -- Exception handling code
    -- This section handles any exceptions that may occur within the package body
    -- For example:
    --   WHEN OTHERS THEN
    --       DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END package_name;
/

Explanation:

• The “CREATE OR REPLACE PACKAGE BODY” statement is used to the create or replace the PL/SQL package body. When the body of the PL/SQL package is already exists, the “OR REPLACE” clause is replaced with the another new definition.
• The “package_name” is nothing but, it is the name of the package which is defined in the body.
• The “PROCEDURE”s and “FUNCTION”s are the subprograms in the body of the package. Procedures are used to perform some specific tasks such as, In functions returning the single value.
• The “DECLARE” section is used to declare the variables, constants, types, cursors and etc.
• The “BEGIN” and “END” keywords are indicate the beginning and end of the execution sections in the functions, procedures, and the whole body of the package.
• The “EXCEPTION” section is used to handle the exceptions, it can be occurred the while during the execution of body of the package.
• The “DBMS_OUTPUT.PUT_LINE” is the built-in procedure in the Oracle database. It used to display the messages to the users.
• If we want to terminate the body of the package, we must use forward slash ‘ / ‘ in a new line. It indicates the end of the SQL statement.

Note: This is the basic template of PL/SQL package body. If we want more functions, procedures and logics we can defiantly add the more procedures, functions, and logics which is used to our requirements.

How to Create a Package body?

• In Oracle PL/SQL, for creating the body of the package we must and should have specification of the package.
• The specification of the package is declared to the package interface along with the public procedures, functions, types and variables.
• After that we can create the body of the package for implementing the functionalities which are declared in the specification of the package.
• For creating a body of the package in PL/SQL, we can use the “CREATE PACKAGE BODY“.

Syntax for creating the package body:

 CREATE [OR REPLACE] PACKAGE BODY package_name AS
    -- Declarations of private variables, types, and cursors
    -- Private procedures and functions

    PROCEDURE procedure_name(parameter_list) IS
        -- Implementation of the procedure
    BEGIN
        -- Body of the procedure
    END procedure_name;

    FUNCTION function_name(parameter_list) RETURN return_datatype IS
        -- Implementation of the function
    BEGIN
        -- Body of the function
    END function_name;

END package_name;
/

Explanation:

• The “CREATE OR REPLACE PACKAGE BODY” statement is used to the create or replace the PL/SQL package body.
• The “package_name” is nothing but, it is the name of the package which is defined in the body.
• The “DECLARE” section is used to declare the variables, constants, types, cursors and etc.
• The “PROCEDURE”s and “FUNCTION”s are the subprograms in the body of the package.
• The “BEGIN” and “END” keywords are indicate the beginning and end of the execution sections in the functions, procedures, and the whole body of the package.
• If we want to terminate the body of the package, we must use forward slash ‘ / ‘ in a new line. It indicates the end of the SQL statement.

How to Compile a Package Body in Oracle?

Compiling a body of the package in PL/SQL is verify the semantics and syntax in the PL/SQL and it will be generate the executable code for it. For compilation of the package body in the Oracle, we must and should use ” ALTER PACKAGE” statement with “COMPILE BODY” option.

Syntax for compiling a package body:

 ALTER PACKAGE package_name COMPILE BODY;

Explanation: Here, “package_name” is the name of the package, which we want to compile the body of the package.

Examples for PL/SQL Package Body

Example 1: Let Create a Package for Calculating Rectangle Area and Perimeter

Step 1: Package Specification for Rectangle Area and Perimeter Calculation

Query:

CREATE OR REPLACE PACKAGE RectanglePackage AS
    PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN NUMBER, area OUT NUMBER, perimeter OUT NUMBER);
END RectanglePackage;

Output:

Creation of package specification

Step 2: Package Body for Rectangle Area and Perimeter Calculation.

Query:

CREATE OR REPLACE PACKAGE BODY RectanglePackage AS
    PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN NUMBER, area OUT NUMBER, perimeter OUT NUMBER) IS
    BEGIN
        area := length * width;
        perimeter := 2 * (length + width);
    END CalculateAreaAndPerimeter;
END RectanglePackage;
/

Output:

Creation of package body

Explanation:

1. The “RectanglePackage“ is the name of the package. It declared the procedure “CalculateAreaAndPerimeter” with the parameters such as length, width, area and perimeter. Here, length and width are IN parameters which is used to gives the inputs, area and perimeter are the OUT parameters which is used to the return the calculate the results.
2. The body of the package implements “CalculateAreaAndPerimeter” procedure. It is used to calculate the area and parameter depends upon the length and width inputs.

Step 3: Now Calculate Rectangle Area and Perimeter Using the Package.

Query:

DECLARE
    l_length NUMBER := 5;
    l_width NUMBER := 3;
    l_area NUMBER;
    l_perimeter NUMBER;
BEGIN
    RectanglePackage.CalculateAreaAndPerimeter(l_length, l_width, l_area, l_perimeter);
    DBMS_OUTPUT.PUT_LINE('The area of the Rectangle is ' || l_area);
    DBMS_OUTPUT.PUT_LINE('The Perimeter of the Rectangle is ' || l_perimeter);
END;
/

Explanation:

• In “Declare” section, declare the two variables such as “l_length” and “l_width” and initialize with the values 5 and 3 respectively. And remaining two variables such as “l_area” and “l_perimeter” are declared without initialization.
• In Procedure call, The “RectanglePackage.CalculateAreaAndPerimeter” procedure is called along with the input parameters such as “l_length” and “l_width” and output parameters such as “l_area” and “l_perimeter”.
• After completion of the procedure call, the values of area and perimeter of the rectangle is printed with the help of the “DBMS_OUTPUT.PUT_LINE“.

Output:

Output

Explanation:

• The formula of area of the rectangle is “length * breadth”. Here, the value of the length is 5 and the value of the breadth is 3. Now, we can calculate the area of the rectangle i.e. 5 * 3 = 15.
• The formula of perimeter of the rectangle is “2 * ( length + breadth )“. Here, the value of the length is 5 and the value of the breadth is 3. Now, we can calculate the perimeter of the rectangle i.e. 2*( 5 + 3 ) = 16.
• The values are printed with the help of the “DBMS_OUTPUT.PUT_LINE“.

Example 2: Temperature Conversion Package in PL/SQL

Step 1: Package Specification for Temperature Conversion.

Query:

CREATE OR REPLACE PACKAGE TemperatureConversion AS
    FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN NUMBER;
    FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER) RETURN NUMBER;
END TemperatureConversion;
/

Output:

package specification

Step 2: Package Body for Temperature Conversion Functions.

Query:

CREATE OR REPLACE PACKAGE BODY TemperatureConversion AS
    FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN (celsius * 9/5) + 32;
    END CelsiusToFahrenheit;

    FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN (fahrenheit - 32) * 5/9;
    END FahrenheitToCelsius;
END TemperatureConversion;
/

Output:

Package body

Explanation:

1. The “TemperatureConversion” contained the two functions such as “CelsiusToFahrenheit” and “FahrenheitToCelsius”.
2. The body of the package implement the function to perform the conversions of the temperature.

Step 3: Use the package in the PL/SQL code for converting the temperature.

Query:

DECLARE
    celsius_temp NUMBER := 20;
    fahrenheit_temp NUMBER;
    converted_celsius_temp NUMBER;
BEGIN
    fahrenheit_temp := TemperatureConversion.CelsiusToFahrenheit(celsius_temp);
    DBMS_OUTPUT.PUT_LINE('Temperature in Fahrenheit: ' || fahrenheit_temp);

    converted_celsius_temp := TemperatureConversion.FahrenheitToCelsius(fahrenheit_temp);
    DBMS_OUTPUT.PUT_LINE('Converted back to Celsius: ' || converted_celsius_temp);
END;
/

Output:

Output

Explanation: In the above PL/SQL code is converted the 20 degrees Celsius temperature to Fahrenheit and then it will convert to the Celsius. The output convert the 20 degree Celsius temperature to 68 degree Fahrenheit temperature and back to the 20 degree Celsius. If we execute the above code, it will convert the temperature of 20 degrees Celsius to Fahrenheit and then it convert back to the Celsius and print the both values.

Conclusion

Overall, PL/SQL package bodies are essential for organizing and maintaining code in Oracle databases. They allow developers to encapsulate implementation details, providing a modular and structured approach to development. By complementing package specifications, package bodies enhance code readability, reusability and manageability.