Packages in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Packages in Oracle with Examples

Let’s now take an example to understand what is meant by package and how the package is created and used in Oracle. Before understanding packages let’s try to create a function to calculate square area.

create or replace function square_area
( p_side number )
return number
is
begin
   return p_side*p_side;
end;

This function will create a function with square_area which takes one parameter p_side and returns a value that is also a number. The PL/SQL block is the function that just multiplies p_side with itself. Let’s now compile the function.

Let’s now check the package by using the select statement and calling the function using the select statement.

select square_area(4) from dual;

So, we can see the function is working fine. Let’s now create another function named rectangle_area.

create or replace function rectangle_area
( p_l number,p_w number )
return number
is
begin
   return p_l*p_w;
end;

From the function we can see the function takes two parameters as the input which returns a number. The logic behind this function is to multiply the length and width and return the value. Let’s go ahead and compile the function.

So, the function is now compiled. Let’s try to call this function and see if the function works or not.

select rectangle_area(4,5) from dual;

So, the function is working fine. These two functions are related to each other as calculating areas because these two functions are logically grouped. It is better to use a package as the code will be more organized and group logically subprograms.

Let’s now define a package name area in order to create a package we need two parts one is the package and the other is the package body. Let’s see the package specification.

create or replace package area
is

function square_area( p_side number )
return number;

function rectangle_area( p_l number,p_w number )
return number;

--we don’t have begin in package specification
end;

We don’t have the PL/SQL block. The package specification is only for the header part. We have two functions which take square_area and rectangle_area. Let’s now compile this package.

So, the package is compiled. Let’s see the package body specification.

create or replace package body area
is
      function square_area( p_side number )
      return number
      is
      begin
      return p_side*p_side;
      end;

      function rectangle_area( p_l number,p_w number )
      return number
      is
      begin
      return p_l*p_w;
      end;
--the begin is optional--we use it for initilization
begin
DBMS_OUTPUT.PUT_LINE('welcome ');
    
end;

In the package body, we mention the code for the functions. We have given the package body name as area and we have given the code of the two functions. We have mentioned the function square_area and rectangle_area codes for two functions. We have mentioned the Begin section but the begin section is optional. Let’s go ahead and compile the package body.

So, packages are divided into package and package body. In the package, we provide the header of the sub-programs and in the package body, we provide the actual code for the sub-programs. Let’s try to check this package by calling the function that is present inside the package.

Select area.square_area(4) from dual;

So, the function is working fine. Let’s try another function.

select area.rectangle_area(4,10) from dual;

So, the rectangle_area is also working fine. But for these two functions we did not get the DBMS output because when we run the package inside a select statement, we don’t get the DBMS output. But if we use the function between begin and end and by using dbms_output.put_line then the DBMS output will work. Let’s try to test that.

begin
DBMS_OUTPUT.PUT_LINE (area.square_area(4));
end;

So, let’s try to execute this.

So, we can see the output in the DBMS output. The purpose of the package is to organize the code and work with the SQL statement the way we need it.

What are PL/SQL Packages in Oracle?

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. We have taken two functions square area and rectangle area. We used these two functions and added them to the package and made it a group logically. Packages are usually two parts:

• A Specification(spec)
• A Body

The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the sub-programs. Enable the Oracle server to read multiple objects into memory at once.

Advantages of using Packages in Oracle:

1. Modularity: Encapsulating related constructs.
2. Easier Maintenance: Keeping logically related functionality together.
3. Easier Application Design: Coding and compiling the specification and body separately.
4. Hiding Information: Only the declarations in the package specification are visible and accessible to applications. Private constructs in the package body are hidden and inaccessible. All coding is hidden in the package body.

We will learn all these advantages in the upcoming articles.

Guidelines for writing Packages in Oracle

Now, we will learn about guidelines for creating packages. In order to create the package specification using the CREATE PACKAGE statement we need usually two parts package and package body. While creating the package we have the syntax as below.

Package Specification Syntax in Oracle:

CREATE OR REPLACE PACKAGE package_name IS| AS 
    Public type and variable declarations
    Subprogram specifications
END [package_name];

We have used public type and variable declarations. These are global variables and we use the sub-program specifications to take sub-programs without the code.

• The OR REPLACE option drops and re-creates the package specification.
• Variables declared in the package specification are initialized to NULL by default. We can define the initial value but if the initial value is not declared then it defaults to take a null value.
• All the constructs declared in a package specification are visible to users who are granted privileges on the package. Under a package if we define 5 sub-programs under a schema or user. That user can be able to access all 5 subprograms inside the package.

Package Body Syntax in Oracle:

CREATE OR REPLACE PACKAGE BODY package_name IS| AS 
    Private type and variable declarations
    Subprogram bodies
[BEGIN initialization statements]
END [package_name];

• The OR REPLACE options drop and re-creates the package body.
• Identifiers defined in the package body are private and not visible outside the package body. We cannot reference these variables outside the package body.
• All private constructs must be declared before they are referenced.
• Public constructs are visible to the package body.

Guidelines:

• Develop packages for general use.
• Define the package specification before the body.
• The package specification should contain only these constructs that you want to be public.
• Place items in the declaration part of the package body when you must maintain them throughout a session or across transactions.
• The fine-grain dependency management reduces the need to re-compile referencing subprograms when a package specification changes.
• The package specification should contain as few constructs as possible.

Package Specification:

Procedure insert emp
Procedure update emp_Sal
Procedure delete emp;

Package Body:

Procedure check_before_insert
Procedure check_before_delete
Procedure insert emp
Procedure update emp_sal
Procedure delete emp

Here we create a package for handling user data. We have a procedure for inserting, updating, and deleting tables. Anything apart from this can be created in the package body. We used the same insert, update, and delete but we have added additional procedures like check_before_insert and check_before_delete. We can use the check_before_insert and check_before_delete procedures as private procedures which are not accessible outside the package.

Note: Try to keep package specification as simple as possible and try to add additional codes that need the package body as private subprograms.

In the next article, I am going to discuss How to Create a Package in Oracle with Examples. Here, in this article, I try to explain Packages in Oracle with Examples. I hope you enjoy this Packages in Oracle with Examples article.