PL/SQL Package Specification

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

Introducing the package specification

A PL/SQL package has two parts: package specification and package body. The package specification is where you declare public items. By default, the scope of package items is the schema of the package. In other words, you can access items declared in a package specification from anywhere in the schema e.g., you can access items in a package specification from other packages.

A package specification does not contain any implementations of the public items. For example, in case of procedures or functions, the package specification contains only their headers, but not their bodies.

A package specification can exist independently if their items do not require implementations.

Typically, a package specification contains the following items:

Procedures
Functions
Cursors
Types, variables, and constants
Records
Collections

Creating a package specification

To create a new package specification, you use the CREATE PACKAGE statement as shown below:

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

In this syntax:

Follow the CREATE PACKAGE clause is the name of the package which you want to create.
The OR REPLACE option allows you to replace the package if it exists and recompile it.
The schema_name is the schema to which the package belongs. By default, it is your schema.

Between the AS and END keywords, you declare the public items of the package specification.

See the following package specification example:

CREATE OR REPLACE PACKAGE order_mgmt
AS
  gc_shipped_status  CONSTANT VARCHAR(10) := 'Shipped';
  gc_pending_status CONSTANT VARCHAR(10) := 'Pending';
  gc_canceled_status CONSTANT VARCHAR(10) := 'Canceled';

  -- cursor that returns the order detail
  CURSOR g_cur_order(p_order_id NUMBER)
  IS
    SELECT
      customer_id,
      status,
      salesman_id,
      order_date,
      item_id,
      product_name,
      quantity,
      unit_price
    FROM
      order_items
    INNER JOIN orders USING (order_id)
    INNER JOIN products USING (product_id)
    WHERE
      order_id = p_order_id;

  -- get net value of a order
  FUNCTION get_net_value(
      p_order_id NUMBER)
    RETURN NUMBER;

  -- Get net value by customer
  FUNCTION get_net_value_by_customer(
      p_customer_id NUMBER,
      p_year        NUMBER)
    RETURN NUMBER;

END order_mgmt;
Code language: SQL (Structured Query Language) (sql)

In this example, the package name is order_mgmt. Let’s examine it in detail:

First, we declared three constants that represent the order status: shipped, pending, and canceled.
Second, we defined a cursor that returns the order details.
Third, we declared two functions that return the net value of an order and the net value of orders which belong to a specific customer.

Compiling a package

1) SQL Developer

If you are using SQL developer, you can click the Run Script button to compile the package specification as shown in the following screenshot.

PL/SQL package specification compile using SQL Developer

Once the package is compiled successfully, you can find it under the packages node of the object list:

If you don’t see the package, you click the Refresh button to reload the object list.

2) SQL*Plus

If you are using SQL*Plus to create a package, you use type forward slash (/) as shown below:

SQL> create package test_package as
  2  gc_status constant varchar(10) := 'Active';
  3  end;
  4  /

Package created.   
Code language: SQL (Structured Query Language) (sql)

In this example, the forward-slash (/) instructed Oracle to compile and create the test_package package.

3) Compile a package from the source file

Sometimes, you may use a source control like Git to manage the versions of packages and you want to create a package from a source file.

To do this, you use the following syntax:

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

For example, a file sample.txt located in the c:\plsql folder that contains the source code for creating the sample package:

CREATE OR REPLACE PACKAGE sample
AS
  gc_shipped  CONSTANT VARCHAR(10) := 'Shipped';
  gc_pending  CONSTANT VARCHAR(10) := 'Pending';
  gc_canceled CONSTANT VARCHAR(10) := 'Canceled';
  gv_status   VARCHAR(10)          
END sample; 
Code language: SQL (Structured Query Language) (sql)

To create the package from the sample.sql file, you use the following command:

@c:\plsql\sample.sqlCode language: SQL (Structured Query Language) (sql)

In SQL Developer, you click the Run Script button to create the package.

However, if you use SQL*Plus, you must type the forward slash (/) to compile and create the package as shown below:

SQL> @c:\plsql\sample.txt
  4  /

Package created.Code language: SQL (Structured Query Language) (sql)

Package state

Once the package is created, you can reference its public items declared in the package specification. Oracle will create a separate instance of the package for each session that references to the package items.

The values of constants, variables, and cursors declared in either package specification or body consists of its package state. If the package specification has at least one variable, constant, or cursor, the package is stateful; or else it is stateless.

To refer to an item using the following syntax:

package_name.item_nameCode language: SQL (Structured Query Language) (sql)

For example, the following anonymous block assigns the variable gv_status of the package sample to the gc_shipped constant and prints the value.

BEGIN
  sample.gv_status 
  DBMS_OUTPUT.PUT_LINE(sample.gv_status);
END;Code language: SQL (Structured Query Language) (sql)

The block returns:

ShippedCode language: SQL (Structured Query Language) (sql)

If you log in using a separate session and reference to the gv_status variable of the sample package, its value will be different because each session has its own instance of the package.

BEGIN
  DBMS_OUTPUT.PUT_LINE(sample.gv_status);
END;Code language: SQL (Structured Query Language) (sql)

The result is:

PendingCode language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to create a package specification using the CREATE PACKAGE statement. Let’s learn how to create a package body.

Was this tutorial helpful?