Create Your First PL/SQL Package in Oracle Apex in very Easy Steps

In this blog post, we will learn how to create a PL/SQL package in Oracle and integrate procedures and functions into it. We will also discuss the benefits of this approach.

What are PL/SQL Packages?

PL/SQL packages are groups of procedures, functions, variables, and other PL/SQL elements that are stored together as a unit. They serve to logically group related components and help improve the maintainability, performance, and reusability of code.

Why are Packages Important?

Modularity: Packages allow you to break down the code into smaller, manageable parts.
Data Encapsulation: Packages help keep data and procedures together, which enhances data integrity.
Performance: Packages store compiled code in memory, which speeds up the execution of applications.
Maintainability: Changes to packages can be made without affecting other parts of the system.
Security: Access controls can be set at the package level to increase security.

Creating a PL/SQL Package

A PL/SQL package consists of two parts: the specification (PACKAGE SPEC) and the body (PACKAGE BODY).

PACKAGE SPEC

The specification is the publicly visible part of the package and declares the types, variables, constants, exceptions, procedures, and functions that are accessible outside of the package.

SQL

create or replace PACKAGE BankingOperations AS
 
  -- Function that calculates the interest amount for given capital, interest rate, and years
  FUNCTION fn_CalculateInterest(
      pi_capital       NUMBER
    , pi_interest_rate NUMBER
    , pi_years         NUMBER
   ) RETURN NUMBER;

 -- Procedure that generates a personalized report
  PROCEDURE pr_PrintReport(
      pi_customer_name     VARCHAR2
    , pi_report_date       DATE
  );

END BankingOperations;
/

PACKAGE BODY

The body contains the implementation of the procedures and functions declared in the spec, as well as additional private declarations that are visible only within the package.

SQL

CREATE OR REPLACE PACKAGE BODY BankingOperations AS

  -- Implementation of the function "fn_CalculateInterest"
  FUNCTION fn_CalculateInterest(
      pi_capital       NUMBER
    , pi_interest_rate NUMBER
    , pi_years         NUMBER
   ) RETURN NUMBER

is
    v_calculation       number;
BEGIN
    
    -- calculate
    v_calculation       := pi_capital * POWER((1 + pi_interest_rate/100), pi_years) - pi_capital;

    RETURN v_calculation;
  
 END fn_CalculateInterest;

  -- Implementation of the procedure "pr_PrintReport"
  PROCEDURE pr_PrintReport(
      pi_customer_name     VARCHAR2
    , pi_report_date       DATE
  )
  
IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Report for: ' || pi_customer_name);
    DBMS_OUTPUT.PUT_LINE('Report date: ' || TO_CHAR(pi_report_date, 'DD-MON-YYYY'));
    DBMS_OUTPUT.PUT_LINE('Thank you for using our services!');


  END pr_PrintReport;

END BankingOperations;

Testing the Package

After the package has been created, you can test it as follows:

SQL

-- Calling the function "CalculateInterest"
DECLARE
    interest_amount NUMBER;
BEGIN
    
    interest_amount := BankingOperations.fn_CalculateInterest(
                          pi_capital            => 1000
                        , pi_interest_rate      => 5
                        , pi_years              => 3
                       );
                                                              
  DBMS_OUTPUT.PUT_LINE('Interest amount: ' || TO_CHAR(interest_amount));
END;
/



-- Calling the procedure "pr_PrintReport"
BEGIN
  BankingOperations.pr_PrintReport(
      pi_customer_name   => 'Max Mustermann'
    , pi_report_date     => sysdate
  );
END;
/
 /

Conclusion

By using PL/SQL packages, developers can write more efficient and secure code that is easier to maintain and manage. Packages promote code reusability and offer a powerful method for structuring your SQL applications.

{fullWidth}

APEX Note