Creating Packages   «Prev  Next»

Lesson 5Making the package body
ObjectiveBuild the package body

Making the Package Body in Oracle

The package body is created separately from the package specification[1], although the two objects are closely associated. When the package body is generated, each procedure and function listed in the package specification must be accurately represented. A procedure defined in the package specification must have the same name and the same parameter list in the package body.
The following diagram describes the Oracle package body syntax.

Package body syntax and example

The following notes in the table below are with respect to the red rectangles in the syntax diagram above.


Location 1 Specify the same package name here that was used in your package specification command.
Location 2 Each public sub-program is defined here. Parameters must match the package specifications. Separate the sub-programs using the END statement followed by a semicolon. In addition to the public sub-programs, additional private sub-programs, cursors, variables, and exceptions can be defined here.

CREATE [OR REPLACE] PACKAGE BODY packagename {IS | AS} 
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN
  ... PL/SQL commands here ...
END subprogram_name;
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN 
  ...PL/SQL commands here ...
END subprogram_name;
  ...
END [packagename];

Demonstrating the Power of the Oracle Package

A package consists of up to two components of code:
  1. the specification (required) and
  2. the body (optional, but almost always present).

The specification defines how a developer can use the package:
  1. which programs can be called,
  2. what cursors can be opened.
The body contains the implementation of the programs (and, perhaps, cursors) listed in the specification, plus other code elements as needed. Suppose that I need to write code to retrieve the full name of an employee whose name is in the form "last, first". That seems easy enough to write:
PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_fullname VARCHAR2(100);
BEGIN
SELECT last_name || ',' || first_name
INTO l_fullname
FROM employees
WHERE employee_id = employee_id_in;
...
END;

Yet there are many problems lurking in this seemingly transparent code:
  1. I have hardcoded the length of the l_fullname variable. I did this because it is a derived value, the concatenation of two column values. I did not, therefore, have a column against which I could %TYPE the declaration. This could cause difficulties over time if the size of last_name and/or first_name columns are expanded.
  2. I have also hardcoded or explicitly placed in this block the formula (an application rule, really) for creating a full name. What is wrong with that, you wonder? What if next week I get a call from the users: We want to see the names in first-space- last format. Now you must hunt through all my code for the last-comma-first constructions.
  3. Finally, this very common query will likely appear in a variety of formats in multiple places in my application. This SQL redundancy can make it very hard to maintain app logic and optimize its performance.

How should a Developer deal with this Challenge?

I would like to be able to change the way I write my code to avoid the above hardcodings. To do that, I need to write these things once (one definition of a "full name" datatype, one representation of the formula, one version of the query) and then call them whenever needed. The Oracle Package can solve this challenge. Consider the following package specification:

/* Files on web: fullname.pkg, fullname.tst */
1 PACKAGE employee_pkg
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (200);
4
5 FUNCTION fullname (
6 last_in employees.last_name%TYPE,
7 first_in employees.first_name%TYPE)
8 RETURN fullname_t;
9
10 FUNCTION fullname (
11 employee_id_in IN employees.employee_id%TYPE)
12 RETURN fullname_t;
13 END employee_pkg;

What I have done here is essentially list the different elements I want to use. The following table summarizes the important elements of the code.
An example of a package body is shown in the diagram below.

Package body example
Package body example
CREATE OR REPLACE PACKAGE BODY GET_MONTHEND_DATA IS
PROCEDURE CALC_PROFIT
  (I_YEAR IN NUMBER, I_MONTH IN NUMBER, 0_PROFIT OUT NUMBER) IS
BEGIN
  SELECT SUM(TOTAL_SALE_AMOUNT) INTO O_PROFIT FROM
	CUSTOMER_SALE
	WHERE TO_CHAR(SALES_DATE, 'MMYYYY')= LPAD(I_MONTH || I_YEAR, 6 , 0);
END CALC_PROFIT
The next lesson shows you how to execute a procedure contained within a package.

Making Package Body - Exercise

Click the exercise link below to create your own package.
Making Package Body - Exercise

Create Package - Exercise

Click the link below to do the same exercise, but run the commands at home on your own database.
Create Package - Exercise

[1]package specification: he Oracle package specification serves as the blueprint for a package, declaring all the elements accessible from outside the package itself. Think of it like a contract or a technical manual that outlines what functions and features are available in the package and how to use them.

Ad Oracle DMBS Packages