PL/SQL Package Body

Summary: in this tutorial, you will learn how to create a PL/SQL package body using the CREATE PACKAGE BODY statement.

Introducing to the PL/SQL package body

A PL/SQL package consists of two parts: package specification and package body.

If the package specification has cursors or subprograms, then the package body is mandatory. Otherwise, it is optional. Both the package body and package specification must be in the same schema.

Every cursor or subprogram declared in the package specification must have a corresponding definition in the package body.

Besides the implementation of the cursors and subprograms in the package specification, a package body may have private items that are accessible only within itself.

A package body can have an initialization part which consists of statements that initialize public variables and do other one-time setup tasks. The initialization part only runs once at the first time the package is referenced. It can also include an exception handler.

Creating a package body

To create a package body, you use the CREATE PACKAGE BODY as shown below:

CREATE [OR REPLACE] PACKAGE BODY [schema_name.]<package_name> IS | AS declarations implementations; [BEGIN EXCEPTION] END [<package_name>];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, you specify the package name after the CREATE PACKAGE BODY keywords. The schema name is optional. By default, it is your schema.
  • Second, the OR REPLACE option replaces the existing package body definition or do nothing if the package body does not exist.
  • Third, between the AS and END keywords are the declarations of private items and the implementations of the public items declared in the package specification. Note that you can use either IS or AS keyword.

The following example illustrates how to create the body of the order_mgmt package:

CREATE OR REPLACE PACKAGE BODY order_mgmt AS -- get net value of a order FUNCTION get_net_value( p_order_id NUMBER) RETURN NUMBER IS ln_net_value NUMBER BEGIN SELECT SUM(unit_price * quantity) INTO ln_net_value FROM order_items WHERE order_id = p_order_id; RETURN p_order_id; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); END get_net_value; -- Get net value by customer FUNCTION get_net_value_by_customer( p_customer_id NUMBER, p_year NUMBER) RETURN NUMBER IS ln_net_value NUMBER BEGIN SELECT SUM(quantity * unit_price) INTO ln_net_value FROM order_items INNER JOIN orders USING (order_id) WHERE extract(YEAR FROM order_date) = p_year AND customer_id = p_customer_id AND status = gc_shipped_status; RETURN ln_net_value; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE( SQLERRM ); END get_net_value_by_customer; END order_mgmt;
Code language: SQL (Structured Query Language) (sql)

This package body includes the implementations of the two functions declared in the package specification.

Compiling a package body

The process of compiling a package body is the same as compiling a package specification.

PL/SQL Developer

From PL/SQL Developer, you click the Run Script button to compile the package body.

PLSQL Package Body Compile using SQL Developer

SQL*Plus

If you use SQL*Plus for compiling and creating a package body, you type forward slash (/) as follows:

PL/SQL Package Body Compile using SQLPlus

Compiling a package body from a file

If you manage package body using files, you can compile the package body using the following command:

@path_to_packge_body_file
Code language: SQL (Structured Query Language) (sql)

In SQL Developer, you click the Run Script button to create a package body from a file.

Similarly, you can use forward slash (/) to compile and create a package body from a file as

SQL > @path_to_package_body_file 2 /
Code language: SQL (Structured Query Language) (sql)

Calling functions from a package

The following statement calls the get_net_value_by_customer function of the order_mgmt package:

SELECT order_mgmt.get_net_value_by_customer(1,2017) sales FROM dual;
Code language: SQL (Structured Query Language) (sql)

The result is:

PL/SQL package body - calling function example

In this tutorial, you have learned how to create a package body using the CREATE PACKAGE BODY statement.

Was this tutorial helpful?