Packages In Oracle

In this blog, you will learn about packages in Oracle.

A package is a group of procedures, functions, variables and SQL statements, created as a single unit. It is used to store together the related objects. A package has two parts, Package Specification or Spec or Package Header and Package Body.

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code. Thus, a package specification lists the functions and procedures in the package, with their call specifications, the arguments and their datatypes. It can also define the variables and constants, accessible to all the procedures and functions in the package.

Package body is used to provide an implementation for the subprograms, queries for the cursors, declared in the package specification or spec.

Example 1

 package specification
SQL> create or replace package circle_area_peri is
 function area(r number) return number; ---> function area is declared with datatype.
 function perimeter(r number) return number; ---> function perimeter is declared with datatype,
 end;
 /
Package created.
SQL>

Package body

SQL> create or replace package body circle_area_peri is
 function area(r number) return number is --> function area is implemented here.
 ar number(7,2);
 begin
 ar := 3.14159*r*r;
 return ar;
 end;
 function perimeter(r number) return number is --> function perimeter is implemented here.
 pr number(7,2);
 begin
 pr := 2*3.14159*r;
 return pr;
 end;
 end;
 /

Package body created.

SQL>

For using the package , create SQL file as follows:

ed packagedemo
declare
r number(5,2);
area number(7,2);
perimeter number(7,2);
ar number(7);
pr number(7);
begin
dbms_output.put_line('CIRCLE');
dbms_output.put_line('Enter the radius:');
r := &r;
area := circle_area_peri.area(r);
perimeter := circle_area_peri.perimeter(r);
dbms_output.put_line('Area of the circle is :'||area);
dbms_output.put_line('Perimeter of the circle is :'||perimeter);
end;

Execute SQL, mentioned above, to see how the package works.

SQL> @packagedemo
Enter value for r: 10
old 12: r := &r;
new 12: r := 10;

CIRCLE

Enter the radius:
Area of the circle is :314.16
Perimeter of the circle is :62.83

PL/SQL procedure successfully completed.