Create, Alter and Drop – Packages
A package may optionally include a package body but is not necessarily required to. The requirement for a package to have a body will be determined by what you declare in a package specification; you may simply declare a package specification and no body. However, most packages often include both the package specification and the package body.
You can declare a package specification without the package body and successfully store it in the database as a valid object. Furthermore, with only a package specification, it’s possible to create other PL/SQL programs that call on the constructs of your package, even procedures or functions, whose code isn’t written yet—the actual code can only be defined in the package body. However, the existence of the package specification enables other outside PL/SQL program units to reference the constructs of this package.
It’s recommended that you create the package specification first, before the package body. The package specification, as we have seen, will successfully store, compile, and support the successful compilation of outside program units. A package body, on the other hand, cannot be compiled successfully without a corresponding package specification. However, the package body can be submitted and stored in the data dictionary without a package specification. The package body will simply be flagged with a status of INVALID in the USER_OBJECTS data dictionary view. After the package specification is successfully created, you need to either issue the ALTER PACKAGE … COMPILE statement, or simply reference a packaged construct and let the database automatically compile the package at that time.
Creating a Package Specification
The following is an example of a statement that creates a stored PL/SQL package specification:
CREATE OR REPLACE PACKAGE pack_booking AS
c_tax_rate NUMBER(3,2) := 0.05;
CURSOR cur_cruises IS
SELECT CRUISE_ID, CRUISE_NAME
FROM CRUISES;
rec_cruises cur_cruises%ROWTYPE;
FUNCTION func_get_start_date
(p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
RETURN DATE;
END pack_booking;
The syntax of the statement is the reserved word CREATE, followed by the optional OR REPLACE words, the reserved word PACKAGE, the name you choose for the package, and the reserved word AS. (The word IS is also accepted here.) Next is a series of declared constructs. Eventually, the closing END is included, followed by the package name, optionally repeated for clarity, and then the semicolon.
This package specification declares a constant c_tax_rate, a cursor cur_cruises, a record variable rec_cruises, and the function func_get_start_date. Notice that the function’s actual code isn’t included here, only the function header.
The package specification is the part of a package that declares the constructs of the package. These declared constructs may include any of the following:
- Variables and/or constants
- Compound datatypes, such as PL/SQL tables and TYPEs
- Cursors
- Exceptions
- Procedure headers and function headers
- Comments
The package specification contains no other code. In other words, the actual source code of procedures and functions is never included in the package specification. The specification merely includes enough information to enable anyone who wants to use these constructs to understand their names, parameters, and their datatypes, and in the case of functions, their return datatypes, so that developers who want to write calls to these constructs may do so. In other words, if any developer wants to create new programs that invoke your package constructs, all the developer needs to see is the package specification. That’s enough information to create programs that employ the procedures and functions of your package. The developer does not need to have full access to the source code itself, provided that he or she understands the intent of the program unit.
Once the package specification has been successfully stored in the database, it will be given a status of VALID, even if the associated package body, containing the actual source code of any and all functions and/or procedures, has yet to be stored in the database.
Creating a Package Body
The following is a sample of a package body that correlates to the package specification we saw earlier:
CREATE OR REPLACE PACKAGE BODY pack_booking AS
FUNCTION func_get_start_date
(p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
RETURN DATE
IS
v_start_date CRUISES.START_DATE%TYPE;
BEGIN
SELECT START_DATE
INTO v_start_date
FROM CRUISES
WHERE CRUISE_ID = p_cruise_id;
RETURN v_start_date;
END func_get_start_date;
END pack_booking;
This package body defines the source code of the function func_get_start_date. Notice that the function header is completely represented here, even though it was already declared completely in the package specification. Also, notice that there are no provisions for the other declared constructs in the package specification. Only the functions and/or procedures that were declared in the package specification need to be defined in the package body. The package specification handles the full declaration of the other public constructs, such as variables, constants, cursors, types, and exceptions.
The package body is only required if the package specification declares any procedures and/or functions, or in some cases of declared cursors, depending on the cursor syntax that is used. The package body contains the complete source code of those declared procedures and/or functions, including the headers that were declared in the specification.
The package body can also include privately defined procedures and/or functions. These are program units that are recognized and callable only from within the package itself and are not callable from outside the package. They are not declared in the package specification, but are defined in the package body.
Altering a Package
Packages, like procedures and functions, should be recompiled with the ALTER command if their referenced constructs are changed for any reason. This includes any referenced database objects, such as tables, views, snapshots, synonyms, and other PL/SQL packages, procedures, and functions.
The syntax to recompile a package with the ALTER statement is
ALTER PACKAGE package_name COMPILE;
This statement will attempt to recompile the package specification and the package body.
The syntax to recompile just the package body is
ALTER PACKAGE package_name COMPILE BODY;
Note that the package is listed in the data dictionary with two records: one record for the PACKAGE and another for the PACKAGE BODY. Both have their individual status assignments. The PACKAGE, meaning the package specification, can be VALID, while the PACKAGE BODY is INVALID. If this is the case, then an ALTER PACKAGE package_name COMPILE statement will attempt to restore the entire package, including the body, to a status of VALID.
If a change is made to the package body and it is recompiled, then the package specification does not demand that the package be recompiled. Even if the recreated package body results in a change that is inconsistent with the package specification, the package specification will still show a status of VALID in the data dictionary (assuming it was VALID to begin with), and the package body will be flagged with a status of INVALID
Dropping a Package
You have two options when dropping a package. The following statement will remove the package body reservations from the database:
DROP PACKAGE BODY RESERVATIONS;
This statement will remove the package body, but will leave the package specification in the database. Furthermore, the package specification for reservations will still be VALID.
The following statement will remove the entire package:
DROP PACKAGE RESERVATIONS;
The result of issuing this statement to the database will be the complete removal of both the package specification and the package body from the database.
Dropping a package will cause any other program units that reference the package to be flagged with a status of INVALI
Public versus Private Constructs
Constructs that are declared in the package specification are considered public constructs. However, a package body can also include constructs that aren’t declared in the package specification. These are considered private constructs, which can be referenced from anywhere within its own package body but cannot be called from anywhere outside the particular package body. Furthermore, any developers with privileges to use the constructs of the package do not necessarily have to see the package body, which means that they do not necessarily know of the existence of the private constructs contained within the package body.
Global Constructs
Package constructs, such as variables, constants, cursors, types, and user-defined exceptions, are global to the user session that references them. Note that this dynamic is irrelevant for packaged procedures and packaged functions, but applies to all other packaged constructs. This is true for both public and private constructs in the package. In other words, the values for these constructs will be retained across multiple invocations within the user session.
For example, if an anonymous PL/SQL block references a public packaged variable and changes its value, the changed value can be identified by another PL/SQL block that executes afterwards. Neither block declares the variable because it’s declared as part of the package.
The user cannot directly access any private constructs, such as a variable, but imagine that a user invokes a packaged procedure, for example, that references its own private variable value and changes that value. If the user re-invokes that packaged procedure again within the same user session, the changed value will be recognized by the packaged procedure.
The value will be retained as long as the user session is still active. As soon as the user session terminates, the modified states of the packaged constructs are released, and the next time the user session starts up, the packaged constructs will be restored to their original state, until the user sessions modifies them again.
Leave a Reply
Want to join the discussion?Feel free to contribute!