Lesson 5	Making the package body
Objective	Build 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 — 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];

This is a typical structure of an Oracle PL/SQL package body where procedures and functions are defined.
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.

Oracle DMBS Packages

Demonstrating the Power of the Oracle Package

A package consists of up to two components of code:

the specification (required) and
the body (optional, but almost always present).

The specification defines how a developer can use the package:

which programs can be called,
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:

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.
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.
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 Example 6.5 below.

**Example 6.5:** Package body example

CREATE OR REPLACE PACKAGE BODY GET_MONTHEND_DATA IS PROCEDURE CALC_PROFIT (I_YEAR IN NUMBER, I_MONTH IN NUMBER, O_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; FUNCTION CALC_DIVIDENDS (I_CUTOFF_DATE IN DATE) RETURN NUMBER IS V_DIVIDEND NUMBER; BEGIN SELECT AVG(TOTAL_SALE_AMOUNT) * 0.04 INTO V_DIVIDEND FROM CUSTOMER_SALE WHERE SALES_DATE <= I_CUTOFF_DATE; RETURN V_DIVIDEND; END CALC_DIVIDENDS; END GET_MONTHEND_DATA; /

The provided Oracle package shown above should run successfully in an Oracle 11g environment.
The package body uses basic PL/SQL constructs and functions that are supported in Oracle 11g, including:

Procedures and Functions: Oracle 11g supports creating and using procedures and functions within packages.
Data Types: NUMBER and DATE are valid and commonly used data types in Oracle 11g.
SQL Queries: The SELECT INTO, SUM(), AVG(), and TO_CHAR() functions are all supported in Oracle 11g.
String Manipulation: The LPAD() function is available in Oracle 11g and can be used for formatting strings.

Points to Consider:

Ensure that the CUSTOMER_SALE table and columns (TOTAL_SALE_AMOUNT and SALES_DATE) exist and are correctly defined in your Oracle 11g database.
The syntax and logic of the package are appropriate for PL/SQL, so there should be no compatibility issues with Oracle 11g.

Overall, the package body is straightforward and should execute as expected in an Oracle 11g environment.
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.

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.