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:
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>];
In this syntax:
- Follow the
CREATE PACKAGEclause is the name of the package which you want to create.
OR REPLACEoption allows you to replace the package if it exists and recompile it.
schema_nameis the schema to which the package belongs. By default, it is your schema.
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;
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
A) 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.
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.
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.
In this example, the forward slash (/) instructed Oracle to compile and create the
C) 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:
For example, a file
sample.txt located in the
c:\plsql folder that contains the source code for creating the
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;
To create the package form the
sample.sql file, you use the following command:
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.
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:
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;
The block returns:
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;
The result is:
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.