Chapter 9

Developing and Using Packages

In this chapter, you will cover the following topics on developing and using packages:

• Developing PL/SQL packages
• Managing packages on the database
• Using Oracle server-supplied packages

Modularity should be the goal of every programmer. Modular code is more flexible and reusable because the procedures and functions only perform one or two tasks. Each module then becomes a building block that can be incorporated many different times in different places within the same or different programs. Originally, modularity and the use of procedures and functions was designed to phase out "spagetti code" produced as a result of numerous goto statements in a large program block. However, large applications often have scores or hundreds of different procedures and functions. Each of them are modular, and perform only a few operations. But, hundreds of them available on one Oracle database creates a mess of its own, a needle in a haystack, if you will, in which you could really use that one program component, if you could only find it in the mass of named PL/SQL on the database. Packages are a way to change this scenario by grouping procedures and functions together logically, either by function, application, dependency, or any other category you can think of. This chapter will focus on packages, which have been mentioned in several different places so far. The development and use of packages will be considered, along with the special uses of a package you may have in an application. The usage and role of Oracle-supplied packages found inside the database server will be treated as well. Together, these topics comprise about 24% of test content on OCP exam 2.

In this section, you will cover the following points on developing PL/SQL packages:

• Overview of using PL/SQL packages
• Creating packages to bundle PL/SQL constructs

• Public and private objects in packages
• Debugging package compilation

Packages are perhaps one of the most significant achievements Oracle has made to commercial PL/SQL programming. Unlike standalone functions and procedures, packages allow you to take a full-scale approach to application development by combining the program units you have learned how to develop in the last several chapters into a cohesive and robust application. You already know of several packages in the Oracle database and in Procedure Builder, now you will find out how packages work, and why. In addition, you will cover the mechanics of developing packages, along with methods for their debugging.

Oracle makes available a special construct called packages to help developers group blocks of loose PL/SQL code into logical units. You have already been introduced to several packages, both in the database like DBMS_OUTPUT, and in Procedure Builder like the DEBUG or TEXT_IO package. But, while you have been shown that there are packages, even shown how to call program units residing in packages, you may still wonder – what exactly is a package? A package is a PL/SQL development construct that allows you to combine several program units into one logical structure.

Rather than having dozens (hundreds, perhaps) of individual program units floating around in the Oracle database, it may be difficult for you as the developer to understand what each program unit does. If you’re the lone developer, this may not be a problem for you, since you wrote and now maintain all the code. But, if you’re like most developers, you work in a team, and without some cohesive development strategy, you may be less able to reuse a team member’s code if you have little idea what those program units out there are doing.

Packages relieve some of the problems in this situation. They allow you to group several program units, types, exceptions, variables and other PL/SQL language constructs together according to your needs or logical collection. For example, you may have thirty program units for an application that fall into roughly three areas, SQL statement processing, obtaining valid values, and manipulating temporary tables for complex accounting algorithms. Rather than simply allowing those thirty program units to sit out there loose, you instead may want to group those units together into three distinct packages according to business function.

Another option for grouping program units into packages relates to building an application. One unit in the package acts as the unit that calls all others, akin to a main( ) procedure in C. The others act in a supporting capacity by performing the smaller, modular function. In this way, a package encapsulates all PL/SQL code for all program components of an application.

Still another benefit for using packages is the ability to hide program logic behind another layer of abstraction. A package allows procedure and functions within it to publish their specifications together, while the package body containing all program unit bodies relieves another programmer from having to think about whatever complexity lies "behind the curtain." You can use this feature in conjunction with the PL/SQL wrapper program as well, which allows you to encrypt the logic of each program unit to hide any trade secrets and prevent users from modifying your packages as well. Figure 9-1 illustrates the use of packages in the Oracle database, along with visual representation of some of the examples provided in this discussion.

Finally, you will see better performance on procedures and functions grouped as a package because calling one program unit in the package causes the whole package to be loaded into memory. This fact means that, when you call a different procedure in the package later, that different package will be loaded into memory as well, thus being more accessible for your use.

A package consists of two structures: package specification and package body. These constructs come to PL/SQL from Ada, and the concept of specification and body stored as two separate modules is nothing new. A package specification is a unit of PL/SQL code that names the procedures, functions, exceptions, user-defined types, variables, and other PL/SQL constructs that are available as a part of this package. Consider a specification to be a package’s "table of contents" – it lists what is available for use in the package body, without wallowing you in the mire of each unit’s intricacies.

The package body contains the code bodies for all procedures and functions named in the package specification. If a package specification contains definitions for program units that don’t appear in the package body, the package body compilation will fail. Optionally, the package body can contain procedures and functions that were not identified in the package specification. However, these procedures and functions are not part of the package’s "table of contents," and as such, you will not be able to call those procedures and functions directly from outside the package. These program units are then localized in their usage to the package body only.

Now that you understand the fundamental premise behind packages, turn your attention to creating them. Special syntactic and semantic constructs are required for creating a package, and both the creation of package specification and package body are covered in this discussion. An example to use for this discussion will now be described. You are enhancing functionality on the existing international financial application introduced in chapter 8. Recall that the application consisted of proc_today_tran( ), proc_yest_trans( ), and proc_mnth_trans( ), which are procedures that execute the actual transactions of the financial application. In addition, you have convert_money( ), called by all the other procedures to help with converting between different currencies as a part of the international financial transactions. The database includes the EXCH_RATE table, which stores your exchange rate information.

Assume there are hundreds of other procedures and functions on the database housing this application. Your enhancement goal is to convert the application from one that uses a lot of loose program units to one that encapsulates program units into packages according to functionality. Take a moment to look at these four program units. There are many different ways to categorize these according to functionality. One way is to categorize these program units according to those that process transactions and utilities that support transaction processing. Assuming you take this course of action, you may create two new packages called TRANS_PROCS and TRANS_UTILS.

First, you develop your package specifications. Let’s look at a sample specifications for these packages. The sample is given in the following code block.

CREATE OR REPLACE PACKAGE trans_utils IS
FUNCTION convert_money (
p_from_crcy VARCHAR2,
p_to_crcy IN OUT VARCHAR2, -- example only, do not try this at home
p_from_amt NUMBER,
valid_date DATE
) RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE trans_procs IS
PROCEDURE proc_today_tran (
p_today_date DATE
);
PROCEDURE proc_yest_tran (
p_yest_date DATE
);
PROCEDURE proc_mnth_tran (
p_mnth_name VARCHAR2
);
END;

Observe that the overall syntax for creating packages is create package pkg_name as … end. Within the ellipses, you can place the program unit specifications for your functions and procedures, along with any record type declarations, exceptions, cursors, or anything else, so long as it is not a PL/SQL code body. No code bodies are allowed. Each of the program unit specifications listed doesn’t include the create keyword. This is because you are not creating the function, procedure, or any other component as an individual component, but rather you are creating a package comprised of those units.

The package specification is actually an excellent place to provide documentation on how to use the programs contained in the package. Commenting a line of code is done with a double dash (--). This method works for commenting one line of code only, while if you want to comment a substantial block of text, the standard C notation for comments (/* */) works nicely. The following block demonstrates use of comments in a package specification.

CREATE OR REPLACE PACKAGE trans_utils IS
–- This package contains utilities used by the transaction application
FUNCTION convert_money (
-- This function converts money from one currency to another.
–- You pass in four parameters, the first two are the "from" currency and
–- the to currency. The function also returns the "to"currency you passed.
–- The next parameter is the amount you want to convert out of the "from"
–- currency. The last parameter tells Oracle what date to use for selection --- of the appropriate exchange rate. The function returns a number,
-- corresponding to the monetary amount in "to" currency you passed in as
-- "from"currency.
p_from_crcy VARCHAR2,
p_to_crcy IN OUT VARCHAR2, -- example only, do not try this at home
p_from_amt NUMBER,
valid_date DATE
) RETURN NUMBER;
END;

After defining the package specification, you then must define and compile the package bodies. Because only the code for convert_money( ) was given in chapter 8, the discussion is simplified by only showing a package body for the TRANS_UTILS package. The package body is shown in the following code block.

CREATE OR REPLACE PACKAGE BODY trans_utils IS
FUNCTION convert_money (
p_from_crcy VARCHAR2,
p_to_crcy IN OUT VARCHAR2, -- returning a value here, watch out!
p_from_amt NUMBER,
valid_date DATE
) RETURN NUMBER IS
my_exch_rate NUMBER(15,4);
my_ret_val NUMBER(15,4); -- return value for the function
BEGIN
-- grab the exchange rate from the appropriate table
-- and put it in MY_EXCH_RATE
SELECT exch_rate INTO my_exch_rate FROM EXCH_RATE
WHERE to_crcy = p_to_crcy AND from_crcy = p_from_crcy
AND valid_date = sysdate;
-- The big tricky calculation: convert the money to a new
-- currency
my_ret_val := p_from_amt * my_exch_rate;
-- now, give it back to the caller
RETURN my_ret_val;
END;
END;

Observe the following points about developing package bodies. First, the general syntax for this operation is create package body pkg_name as …end. Between the ellipses is where you place your PL/SQL code for the program units, as shown here for TRANS_UTILS.

The example presented is only a simple package specification and body to illustrate basic syntax and semantics of package creation. A real-world example would include different types of PL/SQL components, including PL/SQL program units, constants, variables, exceptions, types, and the rest. Note that both flat files compiled against the server-side PL/SQL engine and use of Procedure Builder is acceptable for development of PL/SQL packages. For reasons of simplicity both in the development and ease of compilation, using Procedure Builder is recommended over SQL*Plus and text files containing your source code. You can create a new package stored program unit by first connecting to the database, and then drilling down to the Stored Program Units item in the object navigator for the user you connected as, and then clicking on the create button in the left margin of the object navigator. Alternately, you can open the stored program unit editor using the program>> stored program unit editor menu option available when the PL/SQL interpreter is in the foreground of Procedure Builder, then pressing the new button in that module. Either method brings you to the interactive window, displayed in figure 9-2, where you define your package title, along with whether you will create a specification or a body. As with creation of other program units, Procedure Builder provides you with the fundamental syntax for package specification and body definition.

It is important to remember that Procedure Builder allows you to specify less base unit code for package specification and package body creation. You merely need to say package pkg_name is in Procedure Builder instead of the full-blown create or replace package pkg_name is required for SQL. The extra keywords are filled automatically by Procedure Builder.

An important point made earlier about program units and packages will now be reiterated. If you define the specification for a program unit in the package specification, the package body must contain the source code for that code block or else the package body compilation will fail. But what about the other way around? Oracle is fine with your decision to include program units in the package body that are not declared in the specification. Say for example you want to write an application much like the typical C program, with a main( ) procedure that calls several other procedures and functions. However, you don’t want any other application other than this one to use the program units, because there is perhaps some sensitive trade secrets embedded into the source code. You may have fifteen or more program units written into the package body to help the main( ) procedure, but the package specification still looks like the code in the following block.

CREATE OR REPLACE PACKAGE trans_appl AS
PROCEDURE MAIN;
END;

Now, the program units in the package body that were not defined in the package specification are considered private objects, while the main( ) procedure that is defined as part of the package specification is considered a public object. The difference is self-evident – one is published to the world of Oracle database users. The main( ) procedure can be called by users from the SQL*Plus sessions, from applications that interface with Oracle, from Procedure Builder, Pro*C programs, and other places.

The other procedures are private, which means that they can only be called and used by those procedures actually in the package body. For example, if the foo( ), foobar( ), and barfoo( ) procedures and functions were defined as part of the package body, main( ) call them within package TRANS_APPL. You, on the other hand, cannot call foo( ), foobar( ), and barfoo( ) from the Procedure Builder PL/SQL command interpreter, even if you were connected to the database.

Making program units within a package public and private, then, is a matter of including or excluding the program unit specification with or from the package specification. The same can be said for variables, constants, types, exceptions, and other PL/SQL constructs that are defined and used in the package body but not included in the specification. Remember, the package specification is like a table of contents – if you do not include information about your components in it, no one will know they are there, and even if they did, they couldn’t use it.

An important benefit is provided with the use of packages that employ variables or cursors declared to be public, and are contained in the package specification. You can use these variables across all procedures and functions in your package, eliminating the need to populate the database with temporary information from one procedure only to select it back out for use somewhere else.

Once you have developed your package specification and body code, you should compile it. Packages can be compiled either on the client side or the server, so long as there is a PL/SQL engine to use. Just as there are two ways to develop PL/SQL packages, so too are there two ways to compile them, with SQL*Plus and Procedure Builder.

The same issues with compiling procedures and functions with SQL*Plus against the Oracle PL/SQL server-side engine and the DBA_ERRORS view exist with compiling packages. In fact, given that most packages contain several PL/SQL blocks, the difficulties of locating errors in the code are often compounded. Because of this reason, you should strongly consider the advantages Procedure Builder offers for code compilation. Figure 9-3 shows you the use of the stored program unit editor module to develop and compile packages.

There is another important fact to remember about code compilation. The package specification must be compiled before the package body. Also, as mentioned, the package body must contain program unit code bodies for all program unit specifications or else the compilation of the package body will fail. One factor about package specifications and package bodies that does not impact compilation is program unit order. In other words, the program units defined in the package body needn’t be listed in the same order as the specification.

Important to remember about packages is that you do not run the package itself, you run the components in it. But, you usually have to precede the program units in the package with the package name. After the code is compiled successfully, you can run code in the package by using the PKG_NAME.proc_name( ) syntax. From SQL*Plus, you must precede references to code in packages with the execute command, while in Procedure Builder, you merely identify the procedure using the syntax mentioned previously. The following example presents a call to a procedure in a package from SQL*Plus, TRANS_APPL.proc_today_trans( ).

SQL> execute TRANS_APPL.proc_today_trans(sysdate);

Instead, in the PL/SQL interpreter module of Procedure Builder, you still simply reference the package and procedure as follows.

PL/SQL> TRANS_APPL.proc_today_trans(sysdate);

In this section, you will cover the following points on managing packages on the database:

• Managing package specification dependency
• Managing compilation dependency and packages
• Managing procedure dependency and packages
• Using pragma RESTRICT_REFERENCES
• Managing packages and security

You will recall the discussion of PL/SQL program unit management from the previous chapter. Several of the same issues of code management are present in packages as well. In this section, you will cover the issues associated with program unit dependency when the program units are part of packages. Various design methods are considered, along with their merits and shortcomings, with the ultimate offering of logically grouping program units into packages according to their dependencies. With that point established, the section will move into discussing rapid package development with SQL. Sample code will be offered to help you identify important aspects of the program units you are placing into a package, along with a script to help you actually design the package. Finally, some security issues will be presented with respect to package code management.

There is an important dependency related to packages that you may have identified already. That dependency is the one formed on the package specification by the package body. Say for example that you compile both the specification and body successfully. Both the specification and the body can be found in the DBA_OBJECTS dictionary view, complete with timestamp information and a status flag. The following code block identifies for example’s sake the status of TRANS_APPL once you compile it.

SQL> select * from dba_objects where object_name = ‘TRANS_APPL’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4060 PACKAGE 21-SEP-99 21-SEP-99 1999-09-21:17:02:54 VALID

STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:18:05:29
VALID

Now, let’s say a bug was located in the proc_mnth_tran( ) procedure. You go into Procedure Builder and make the necessary modification to the package body without changing procedure specifications, and then you recompile the package body. Regardless of whether the body recompiles successfully, so long as you do not change the program unit specification the package specification should be fine. This is because your specification to the procedure did not change, although the logic did. This important lack of procedure dependency is what makes packages so great. Rather than having to recompile all PL/SQL programs that had procedure dependencies of the one you changed, you simply get to recompile the one you changed as part of a package body, and other parts of the application remain undisturbed. In fact, even if you attempt to change the program unit specification in your package body, the package specification is still valid even though the package body compilation fails. The following code block demonstrates package specification and body status if you recompile a package body and it fails due to syntax errors.

SQL> select * from dba_objects where object_name = ‘TRANS_APPL’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4060 PACKAGE 21-SEP-99 21-SEP-99 1999-09-21:21:02:54 VALID

STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:22:05:29
INVALID

However, if your package specification fails on compilation, say because of a syntax error, the package body will fail on compilation as well. This is because a package body depends on the existence of its package specification in a valid state. If for some reason the package specification is invalidated, then the package body will invalidate as well. For example, a package specification will be invalidated if you recompile it and it contains errors. So, package bodies have a special package dependency on the package specification that must be satisfied for the body to compile successfully. The following code block demonstrates status on package specification and body if the specification is recompiled with syntax errors.

SQL> select * from dba_objects where object_name = ‘TRANS_APPL’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4060 PACKAGE 21-SEP-99 21-SEP-99 1999-09-21:22:02:54 INVALID

STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:22:05:29
INVALID

Recall from the previous chapter that you will often create dependencies between program units and between those units and objects like tables in the Oracle database. Two types of dependency were introduced, procedure dependency and object dependency. Two procedures of functions experience procedure dependency when one of the procedures calls another as part of its regular function, like proc_today_trans( ) did on convert_money( ). A procedure may also rely on the valid existence of a table in Oracle to perform its tasks as well, as convert_money( ) did on EXCH_RATE.

These procedure dependencies are still present when you recompile procedures and functions into packages. If you have programs that depend on other programs that also extend from one package to another, the referenced package body should be compiled before the referring package body. The specifications, however, can be compiled in any order. The following code block displays the package body status information from DBA_OBJECTS for TRANS_UTILS and TRANS_APPL if you compile the TRANS_APPL package body before compiling TRANS_UTILS. Note that TRANS_UTILS is valid, but TRANS_APPL is not in this situation.

SQL> select * from dba_objects where object_type = ‘PACKAGE BODY’
2> and owner = ‘STACY’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:30:02:54 INVALID

STACY TRANS_UTILS 4040 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:31:05:29
VALID

In another example, say you go into TRANS_UTILS and make a change to convert_money( ), but forgot to put a semicolon at the end of a line somewhere. When you recompile the TRANS_UTILS procedure body, the compilation fails. Note, however, that the status of TRANS_APPL, is still valid until someone actually runs proc_today_trans( ), and Oracle learns that the referenced package body is invalid. The following code block shows the status of both procedure bodies in the state after the error in compiling TRANS_UTILS but before referencing the proc_today_trans( ) procedure in TRANS_APPL.

SQL> select * from dba_objects where object_type = ‘PACKAGE BODY’
2> and owner = ‘STACY’;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:40:02:54 VALID

STACY TRANS_UTILS 4040 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:41:05:29
INVALID

Consider the same situation where you recompile the TRANS_UTILS package specification instead, and you forgot your semicolon somewhere. The TRANS_UTILS package specification compilation fails, which not only invalidates the TRANS_UTILS package body immediately, it also invalidates the TRANS_APPL package body. The TRANS_APPL specification remains valid, however.

SQL> select * from dba_objects where object_type in
2>(‘PACKAGE’,‘PACKAGE BODY’) and owner = ‘STACY’
3> order by object_name, object_type;

OWNER OBJECT_NAME O_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP
----- ----------- ---- ------------ --------- --------- -------------------
STATUS
------
STACY TRANS_APPL 4060 PACKAGE 21-SEP-99 21-SEP-99 1999-09-21:40:02:54 VALID

STACY TRANS_APPL 4061 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:40:02:54 INVALID

STACY TRANS_UTILS 4039 PACKAGE 21-SEP-99 21-SEP-99 1999-09-21:41:05:29
INVALID

STACY TRANS_UTILS 4040 PACKAGE BODY 21-SEP-99 21-SEP-99 1999-09-21:41:05:29
INVALID

Certain considerations should be given when planning packages to avoid dependencies that make packages impossible to compile. In addition, you should understand the conditions that may exist where it is advisable for you to put procedures together in one package. Code in packages should be logically grouped according to whether they are used for the same overall function in an application, or whether they are depended upon consistently by other applications.

The hallmark reason for putting procedures and functions together in a package is if they work together on the same functional area of the application. Say you have an application that has two functional uses, online transaction processing and reporting. Your functions and procedures that support OLTP work can be grouped into one package, while your reporting programs can be grouped together in another.

Procedures and functions may be grouped together according to their involvement in certain batch processes. Say for example that you have a database that has several different batch feeds happening at different times. You may package the PL/SQL programs that work on one batch feed together, while putting those that work on a different batch feed into another package.

Consider how Oracle performs in the following situation. Say you have three procedures, call them a( ), b( ), and c( ). Procedure a( ) calls procedure b( ), which in turn calls procedure c( ). You want to create two packages, X and Y. Package X will contain procedures a( ) and c( ), and Y will contain b( ). One might think that compiling X and Y is not possible, considering the cyclical nature of calls that would result from one package to another. However, there is no problem with this design. The reason for this fact lies with the package specifications. Although when Oracle attempts to compile the package body for X containing a( ), it will verify the existence of procedure b( ) in package Y’s specification and the validity of package specification Y, it does not actually check for the validity of Y’s package body. The same is true when you compile the Y’s package body. It checks X’s package specification for the referenced procedure call and for validity, but does not check the package body. Thus this design is possible.

A special case of cyclical dependency exists called forward referencing, which basically refers to situations where one program unit refers to another in a package before the other reference has been compiled. Say a package has two procedures, one called award_bonus( ) and the other called calc_rating( ). If award_bonus( ) refers to calc_rating( ) before Oracle has seen any sort of reference to it, either in the package specification or package body, Oracle will compile the package with errors. The following code block illustrates an offender.

…
Procedure award_bonus(…) is
Begin
Calc_rating(…); --illegal reference
…
End;
Procedure calc_rating(…) is
Begin
…
End;

However, if you make forward reference to the PL/SQL block before using it, you can get away with calling the program unit from another unit before actually defining the program logic. The following block shows an acceptable block of code that uses forward referencing.

…
Procedure calc_rating(…); --forward declaration
Procedure award_bonus(…) is
Begin
Calc_rating(…);
…
End;
Procedure calc_rating(…) is
Begin
…
End;

Another method for designing packages is to place programs that are commonly relied upon by other programs. These referenced programs may be utilities commonly used by other programs. As such, they may belong in a utilities package that is referenced by all other programs. In this situation, you create a dependency architecture where one or several other packages have procedural dependencies into the utilities package. However, remember that the procedure dependency applies to the package bodies only. Thus, the package specifications can be compiled in any order you want. Compilation of the bodies of packages that reference the utilities package must happen after the utilities package specification is compiled. But, because other packages are procedurally dependent only on the utilities package specification, you can recompile the utilities package body as often as you need to.

One final situation should be considered regarding procedure dependency. This situation is when a user has a session open where she is using a particular package. The user has their own private version of the package in memory for their session, which was valid as of the time she began use of the package. This version of the package in the user’s session is called an instantiation of that package. However, let’s say that as the developer, you invalidate both the package specification and the package body. This causes the instantiation of the user’s package to be lost. The first time after the instantiation is lost, when the user calls program units in the package, the user will receive the Oracle error ORA-04068: existing state of packages has been discarded. This error, though it appears threatening, is actually benign, and the next time the user calls the programs in the package, they will re-instantiate for the user without producing an error.

If you’ve seen a package body definition, you probably have noticed that it looks suspiciously similar to a procedure or function definition without an area for parameter definition. So similar, in fact, are package bodies to the procedures and functions they contain that the package body can also have some standalone PL/SQL defined within it, at the very end of the package body definition. This standalone PL/SQL is called a one-time-only procedure because it executes only once, when you submit the package body to Oracle for compilation. You may find one-time-only procedures useful in situations where you have some structures or variables you want to set up when you first compile the package. The following code block shows a greatly simplified example of a package body from Procedure Builder with a one-time-only procedure defined.

Package my_pkg is
procedure my_proc ( … ) is
begin
…
end;
procedure my_proc_2 ( … ) is
begin
end;
begin –- one-time-only procedure
…
end;

Packaging functions takes a small amount of extra effort to address the fact that the package body for the function is hidden when Oracle runs it. So, Oracle needs you to say explicitly what the function is doing with respect to writing to the database, reading from it, and other things. A pragma is a compilation directive you can set that defines items like whether the function will write information to the database, and other things. You have already seen one pragma, the EXCEPTION_INIT pragma in chapter 5, which is used to allow you to associate your own exception name with an Oracle error. RESTRICT_REFERENCES allow you to restrict how your function manipulates the Oracle database. There are several different ways to restrict how your function references the Oracle database. They are:

• WNDS Write No Database State tells Oracle to prevent the function from writing changes to the database
• WNPS Write No Package State tells Oracle to prevent the function from altering values for package global variables
• RNDS Read No Database State tells Oracle to prevent the function from issuing select statements against the database (highly restrictive)
• RNPS Read No Package State tells Oracle to prevent the function from obtaining values in package global variables (highly restrictive)

Your use of this feature will take the following form in your specification: pragma RESTRICT_REFERENCES(func_name,WNDS, [optional_restrictions]). Optional restrictions in this case are WNPS, RNDS, and RNPS. Using pragma RESTRICT_REFERENCES requires you to add some lines to your package specification. In the following code block, you will reconsider your TRANS_UTILS packge specification in light of using pragma RESTRICT_REFERENCES. Should you want to explicitly prevent your convert_money( ) function from being able to write changes to the database, you will add a line telling Oracle to enforce this restriction on convert_money( ).

CREATE OR REPLACE PACKAGE trans_utils IS
FUNCTION convert_money (
p_from_crcy VARCHAR2,
p_to_crcy IN OUT VARCHAR2,
p_from_amt NUMBER,
valid_date DATE
) RETURN NUMBER;
pragma RESTRICT_REFERENCES(convert_money,WNDS);
END;

Now, if you compile this specification, and your package body contains an update statement, the compilation of your package body will fail because it violates the RESTRICT_REFERENCES you have set for the function.

The same security considerations given for procedures and functions applies to packages and package bodies. Only those users who may create a procedure or function may create a package. This ability is bestowed with the create procedure privilege. To run a program unit that is stored in a package, you must have the execute privilege on the package, not the procedure. So, on one hand, packages allow you to make changes to source code for program unit bodies without disturbing the status of other programs with procedure dependencies on the unit. But, the execution of the procedure individually is impossible without granting execute privilege on all procedures and functions in the package. Thus, you may have to plan for this limitation in the way you allocate procedures and functions to various packages.

Another take on packages and security relates to the package body. Since your package body resides separately from the specification, you can hide information and application logic from your users while simultaneously allowing them to do their job by running the code. The PL/SQL wrapper allows you to store an encrypted version of your code body in the database for purposes of security as well.

One final area of package management is the ability you have to overload procedures and functions. You have already been exposed to overloading, so you know that an overloaded function may have three different functions out there with the same name, each handling different datatype arguments. An example is a function that returns the length of a variable in characters. Since you may want to find the length of non-character variables, you can overload this function in your package to save yourself extensive formatting on your variable from the function caller and extensive if-then statements in the function itself. The following code block shows the specification for the overloaded function.

CREATE OR REPLACE PACKAGE UTIL_FUNCS IS
FUNCTION var_length (
p_var CHAR
) RETURN NUMBER;
FUNCTION var_length (
p_var VARCHAR2
) RETURN NUMBER;

FUNCTION var_length (
p_var NUMBER
) RETURN NUMBER;

FUNCTION var_length (
p_var DATE
) RETURN NUMBER;
END;

In this section, you will cover the following points on using Oracle server-supplied packages:

• Overview of Oracle server-supplied packages
• Server packages for database and application administration
• Server packages for transaction processing
• Server packages for advanced application development
• Server packages for Oracle internals support

With all this talk about designing your own packages, you may have several ideas about designing packages to help you with several areas of basic functionality in the Oracle database. Oracle did too, at one point, and from that they decided to create some basic packages to help you do your job. You need to know what the Oracle server-supplied packages are in order to pass OCP, and this knowledge will help you as you develop and maintain database applications. Several packages have already been covered, namely those packages that are part of Procedure Builder and can be used for debugging. This section covers the packages available as part of the Oracle database that can be used in many ways to improve the functionality of your applications.

Several packages exist in the database to complement your development and administrative efforts. These packages are developed by Oracle and reside in the database. The Oracle server-supplied packages are owned by user SYS. In some cases, you must run scripts that come with the Oracle distribution software. For the most part, these scripts will be found in the rdbms/admin subdirectory of the Oracle home directory on the machine hosting the Oracle database. For more information about the scripts that must be run, consult Oracle Certified Professional DBA Certification Exam Guide. Oracle server-supplied packages fall into four different categories. These categories are transaction processing, application development, database and application administration, and internals support. The high-level category description and packages in each are described here, while the functionality highlights and program components for each package will be covered shortly. Be aware this is a survey of packages for exam preparation purposes. To find out more about use of an individual package, including parameters and other things, consult the comments in the package specification on your Oracle database.

Several different packages available to support transaction processing. Transaction processing includes all activities related to locking database objects for data changes, along with commit and rollback operations, with some additional SQL statement functionality thrown in as well. The packages falling into this category include DBMS_ALERT, DBMS_LOCK, DBMS_SQL, and DBMS_TRANSACTION.

The Oracle server makes several different packages available in the support of basic and advanced application development. This includes everything related to developing batch jobs, altering the session environment within the application, and intersession communication. The category also includes finding more information about program units available in the application, and file I/O. The packages falling into this category include DBMS_JOB, DBMS_SESSION, DBMS_PIPE, DBMS_DESCRIBE, DBMS_OUTPUT, DBMS_STANDARD, and UTL_FILE. Since the functionality of the DBMS_OUTPUT package is described in chapter 7, it will not be described again here. Instead, you may want to refer to that discussion for information about the package.

Many different packages help you with database and application administration. This includes everything related to recompiling packages or procedures, administration of the system, space management on the database, replication, and SGA. The packages falling into this category include DBMS_UTILITY, DBMS_DDL, DBMS_APPLICATION_INFO, DBMS_SYSTEM, DBMS_SPACE, and DBMS_SHARED_POOL.

A few packages in Oracle also support data replication. They are DBMS_SNAPSHOT, DBMS_REFRESH, DBMS_DEFER, DBMS_DEFER_QUERY, DBMS_REPCAT, DBMS_REPCAT_AUTH, and DBMS_REPCAT_ADMIN. Since snapshots and data replication are not topics covered in OCP, they will not be covered here.

There are some packages available in the support of Oracle database internal activities as well. Oracle often uses a package wrapper to prevent you from viewing or modifying the code in these packages, as they support several fundamentals of the database. They also support other packages used more actively by you in other categories. The packages falling into this category include STANDARD, DBMS_IJOB, DBMS_DEFER_IMPORT_INTERNAL, DBMS_DEFER_SYS, DBMS_IREFRESH, DBMS_ISNAPSHOT, DBMS_SYS_ERROR, and DBMS_SYS_SQL. Note that the STANDARD package is not the same as DBMS_STANDARD. The STANDARD package defines all SQL built-in functions covered in chapter 1, along with database datatypes and PL/SQL extensions to those types. DBMS_STANDARD, on the other hand, defines items like raise_application_error( ), and several important items that assist in PL/SQL procedure and database trigger development. See chapter 10 for more information about the DBMS_STANDARD package.

Sometimes applications want more restrictive locks than the simple row-exclusive lock granted for an update statement. Locks on database objects can be obtained with the use of the DBMS_LOCK package. The procedure allocate_unique( ) is used to generate a numeric ID for a given lock handle allocated by the application. An overloaded function called request( ) allows the application to request the lock on the database object, using either the integer or lock handle. Another function, called convert( ), is used by an application to convert one type of database object lock into another. This function is overloaded as well to handle either the lock ID or the handle. The final procedure is called sleep( ), and it is used to suspend session activity for a period of time.

The DBMS_TRANSACTION package supports transaction processing as well. There are several program units in this package for performing activities like commit and rollback, and also for defining savepoints and other items throughout the application. The procedures for setting transaction status, advising commit activity, and assigning rollback statements include read_only( ), read_write( ), advise_rollback( ), advise_commit( ), advise_nothing( ), and use_rollback_segment( ). Procedures used to begin and end transactions and define savepoints are begin_discrete_transaction( ), commit_comment( ), commit_force( ), commit( ), rollback( ), savepoint( ), rollback_savepoint( ), and rollback_force( ). You should note that some of these activities needn’t be executed as procedures or functions. Statements like commit and rollback can be executed with that syntax instead of a full-blown procedure call.

The DBMS_SQL package allows you to construct SQL statements on the fly using text strings. Several procedures and functions are available. For manipulating SQL statements in memory, called cursors, the open_cursor( ) function and close_cursor( ) procedure are available. You turn a text string into a SQL statement with the parse( ) procedure. Your SQL statement may contain a comparison operation in the where clause, so in order to incorporate the value in that statement, you use the bind_variable( ) procedure. Two procedures must be used if your SQL is a select statement to set up the return values positionally. They are define_column( ) and define_column_long( ). You then use execute( ) procedure to process the SQL statement. After processing, if you have run a select statement, you may then retrieve rows from the resultant cursor with fetch_rows( ), after which you can call variable_value( ) to obtain specific values for columns from each row. Finally, you can use column_value_long( ) to retrieve data from LONG columns a piece at a time.

You may want to set up an activity that occurs based on a change in data on the database as the result of a transaction as well. The DBMS_ALERT package can help you accomplish this task. There are several procedures and functions available to you from DBMS_ALERT. Running the register( ) procedure allows you to register your session that needs to pay attention to the alert named as a parameter. Running the remove( ) procedure specifies that you are no longer interested in being notified by the alert. When your application does something that others need to be alerted of, your application runs the signal( ) procedure to signal the alert. Running the waitany( ) procedure from your session causes your session to wait until any alert your session is registered to receive occurs. Instead, you may want to wait only for an individual alert you subscribe to, in which case you use the waitone( ) procedure. Alerts are event-driven, meaning they occur as a result of an event such as a database change rather than as the result of a continuous polling loop (the waitany( ) procedure is an exception). If you wish the use polling (such as for waitany( )), you may want to run set_defaults( ) to set the frequency Oracle will poll.

The DBMS_JOB package helps you to develop batch processes that run within the Oracle database. The job run must be a PL/SQL stored procedure. To set up the job in the job queue, you run the submit( ) procedure, while to get it out of the job queue, you can run remove( ). Jobs have a description, an interval or frequency of execution, and the next date defined for which they will run. These items can be changed individually with what( ), interval( ), and next_date( ), respectively. The more generalized change( ) procedure can be used to change any of these things as well. Jobs that fail upon execution are marked "broken" by Oracle, and will not run again until they are fixed. Alternately, you can stop a job from being run by breaking it manually with broken( ). Once the job is fixed, you force job execution with run( ). If the job runs successfully, Oracle marks the job as not broken anymore.

The DBMS_SESSION package can be used to set the current role within anonymous PL/SQL blocks. It is not usable from stored procedures in order to avoid the security risks posed by allowing those blocks to modify their own security restrictions at runtime. The procedures include set_role( ) to set a role for the session. Another is close_database_link( ), use of which is self-explanatory. You can re-instantiate a package that has been compiled after you began using it in your session with reset_package( ). To set a NLS variable, use set_nls( ).

Communication between database sessions happens with the DBMS_PIPE package. Pipes in Oracle run through the SGA, and are lost after shutdown on the Oracle instance. Messages to be sent across pipes and received from pipes are stored in local buffers for the session. Trace the route a message will take from one session to another through a pipe. Optionally, a private pipe is created beforehand with create_pipe( ) to handle message transfer between two sessions owned by the same user. The message is created and placed in the local buffer with pack_message( ), then sent on the pipe with send_message( ). If no pipe exists for send_message( ) to use, it will create a public pipe for its own use. On the receiving end, the process runs receive_message( ) to get it off the pipe and store it in the local buffer. From there, unpack_message( ) is used to take the next message off the buffer. To create a variable to hold the message, you call next_item_type( ) to obtain the datatype of the message. To eliminate the private pipe, call remove_pipe( ). To flush data out of a pipe, use purge( ). To flush data out of the local buffer, use reset_buffer( ).

The DBMS_DESCRIBE package can be used to find information about the stored procedures in an application that you may create. Items of importance in this package include two type declarations that define tables for storage of output from the one procedure DBMS_DESCRIBE houses, called describe_procedure( ). This procedure accepts as input the name of the object to be described and returns whether the procedure is overloaded, the parameters, their directions, datatypes, positions in the procedure call, and other items that help you determine the proper call to a procedure. Since there are many out parameters to describe_procedure( ), you must declare variables to hold the output using the VARCHAR2_TABLE and NUMBER_TABLE types provided in the DBMS_DESCRIBE package and pass them along with the call to the describe_procedure( ) procedure.

The DBMS_STANDARD package houses many common PL/SQL constructs. The procedure for advanced error handling, raise_application_error( ), which has already been covered, is defined in the DBMS_STANDARD package. Once nice thing about these items is that you don’t need to refer to the DBMS_STANDARD package by name when using these items.

The UTL_FILE package handles file I/O from PL/SQL packages. This functionality allows you to develop code to handle almost any form of batch processing or batch feed to or from flat file, eliminating the need to use operating system scripts that call SQL*Plus. The procedures used to open and close procedures include fopen( ), fclose( ), and fclose_all( ), while the is_open( ) function returns whether or not a file is already open. To obtain a line of data from the file, use get_line( ). To place a line of data into the file with an end-of-line marker, use put_line( ), otherwise put( ) is used to place data to the file without the end-of-line marker. The end-of-line marker can be added later with new_line( ). To place data into the file with specific formatting, use putf( ). Sometimes Oracle may write data to a file asynchronous to the actual calls listed above. To place all pending data into the file immediately, use fflush( ).

Several packages were presented that assist in administrating the Oracle database. Since only some of these packages are useful for application administration, only those packages will be covered in detail here. The packages that assist with database administration include packages such as DBMS_SYSTEM, DBMS_SPACE, and DBMS_SHARED_POOL for managing memory, disk, and other items. One important procedure in DBMS_SHARED_POOL is the keep( ) procedure, which pins a package or program unit into memory for the life of the database instance. If you recompile a package that has already been instantiated, the new version will not be readily available because an outdated version of it is stuck permanently in memory. The unkeep( ) procedure allows you to age this obsolete version of the package out of memory, but may not be supported in future releases of Oracle.

DBMS_UTILITY contains several procedures and functions used for database administrative tasks. One of interest to application developers is compile_schema( ), which allows you to recompile all procedures, functions, and packages owned by you. DBMS_DDL offers similar functionality on specific PL/SQL program units with the alter_compile( ) procedure.

Finally, DBMS_APPLICATION_INFO allows you to register your application with the Oracle database for tuning purposes. The procedure set_module( ) allows you to establish a name for the application running, while set_action( ) identifies the current action by name within the module. These functions identify your application for audit or timing purposes. The set_client_info( ) procedure also sets information about the client running in that session Procedures for reading information set about the client or application include read_module( ) and read_client_info( ).

This chapter covers development and use of packages to manage PL/SQL program units more effectively. The subject areas covered are the overview of using PL/SQL packages, development of packages, managing packages on the database, and using Oracle server-supplied packages available on the database. This chapter covers 24% of OCP test 2 content.

Packages are constructs that consolidate stored procedures and functions on a database into two distinct objects – specifications and bodies. The specification contains procedure and function definitions along with parameters passed, and the body contains program logic. Packages offer several benefits over individuated stored procedures and functions. The package groups related procedures and functions, making it easier to find those program units. Packages help reduce the burden of procedure dependency by divorcing the specification from the body. While other program units depend on the presence of the specification, you can recompile the body when needed. Package bodies can also hide program logic when used in conjunction with PL/SQL wrappers. Packages can offer improved performance when you use related procedures and functions because your use of one procedure in a package loads the entire package into memory. When you need another procedure from the package, that other procedure is in memory as well. You can also define multiple versions of the same procedure or function with a package, to handle operation of the same task on information of differing datatypes. This feature is called overloading.

Packages have two parts, a specification containing procedure and function specifications such as name, parameters, and return values, and a body containing program unit code logic. Packages can contain public and private program units. A public program unit is one whose program unit specification appears in the package specification. A private program unit is one defined exclusively in the package body. Public units can be called by other procedures outside the package, while private units are called only from within the package. But a package body must always contain program unit logic if a package specification contains its specifications, or else the package body compilation fails.

Basic syntax for package specifications is create or replace package pkgname is…end. Within the ellipses of this basic syntax are the listings for all user-defined types, procedures, functions, constants, cursors, and exceptions. For procedures, functions and types, the specification takes the form {procedure|function}whatever (parm in datatype…) [return datatype]; instead of the syntax for creating that individual unit, (ie, create or replace …).

Basic syntax for package bodies is create or replace package body pkgname is…end. Within the ellipses of this syntax are the listings and program logic for all procedures, cursors, and functions. Constants, types, and exceptions have already been defined in the specification and can be used in the body without further definition, however you can declare more of them in the body that are private. Syntax for procedures and functions defined in a package is the same used for creating procedures and functions, minus the create or replace syntax. See chapter 6 for more information about developing procedures and functions.

Several important points about package compilation were made in the text. A specification for a package must always be compiled before its body. If the body doesn’t contain program logic for a unit defined in the specification, package body compilation fails. Package body compilation failure never invalidates the specification, but package specification compilation failure always invalidates its own body, along with the body of every package that has procedure dependency on program units in the failing specification.

Package specifications should contain comments that explain genaral program usage, while package bodies should contain comments explaining tricky program logic or hardcoded values, or other minutae of the code. A package specification should always contain comments on using the program units defined, along with an explanation of every parameter passed in or out, and also return values, if appropriate. Package bodies may be easier to maintain if their program units have comments describing their design quirks. Single line comments are preceded by double dashes (--), while block comments are made with usual C notation, (/* */).

Coding and compiling your packages with Procedure Builder offers the same advantages it offered for program units. If you have a package already and you want to change the program unit logic, you need only recompile the specification when parameters into or out of the program unit change.

Several examples were provided to demonstrate packages and program dependency. To summarize, a package specification will almost never invalidate as the result of any dependency on the database. Package bodies that call procedures in other packages will not invalidate even when the package body containing the referenced procedure is recompiled or invalidated, so long as the specification remains valid and the program unit that references invalid code is not executed. As soon as you execute the procedure that calls invalid code, the referenced body will invalidate as well. If your recompilation of the package specification fails, the associated package body and all package bodies that reference code in the failed specification will invalidate.

Grouping program units together into packages according to dependency is usually a good idea. Grouping program units together according to application or functional area usually works as well. It is helpful to group utility program units that are called by several other program units into one utility package as well.

Package instantiation is another important area of package development. A package is instantiated by a session once a call is made to program units in that package. Once instantiated, a version of that package exists in session memory for that user. If the package should be recompiled after instantiation, the next reference to a program unit for that user will result in the ORA-04068 – "existing state of packages has been discarded." This error is usually a red herring – simply calling the procedure again makes it go away.

The same privileges to create and run program units is used to allow creation of packages. One limitation on security enforcement on packages is that granting execute privileges on a package allows the user to execute all program units in the package. Ensure this fact is addressed when grouping program units together in a package. To find information about a package such as validity, timestamp, compilation errors, and source code, use the same dictionary views as those for procedures and functions, namely DBA_ERRORS, DBA_SOURCE, and DBA_OBJECTS.

Several packages are available on the Oracle database, used for operation in many different areas. The general categories covered for server-supplied packages are transaction processing, application development, application/database administration, and Oracle internals support. Since this guide focuses on exam preparation and given the fact that the use of each package is intricate, the text focused only on the basic usage of each program unit in the package, along with some facts about package usage likely to be tested on the OCP exam.

The first category is transaction processing. Packages in this category were DBMS_ALERT for asynchronous notification of changes made to the database and DBMS_LOCK for acquiring locks on the database that are higher than those given with update or select for update statements. Also included here were DBMS_SQL for dynamic generation of SQL DML and DDL statements and DBMS_TRANSACTION for execution of statements to begin and end transactions or set savepoints throughout a transaction.

The next category is application development. Packages in this category include DBMS_JOB for setting up batch operations of program units, DBMS_SESSION for changing aspects about the session running environment, and DBMS_PIPE for intersession communication between program units. Also included here are DBMS_DESCRIBE to identify key points about any procedure or function in the database, DBMS_OUTPUT and UTL_FILE to send text output to both the computer screen or a text file from your program unit, and the DBMS_STANDARD package, which contains definitions for raise_application_error( ).

Application and database administration is the next category. The Oracle-supplied packages in this category are DBMS_UTILITY, DBMS_DDL, DBMS_APPLICATION_INFO, DBMS_SYSTEM, DBMS_SPACE, and DBMS_SHARED_POOL. The two packages of importance to application development are DBMS_UTILITY and DBMS_DDL. These two packages contain procedures that recompile PL/SQL code, and as such may be useful in creating more robust applications. The rest of the packages in this category are more for database administration tasks, and so the application may not have much occasion to use them. One exception to this statement is DBMS_SHARED_POOL, which contains the keep( ) procedure that allows the DBA to pin an object into Oracle’s shared pool. If you recompile a package that is pinned in Oracle memory, no one will be able to use the new version because the old instantiation is never removed from memory.

Two other package areas are Oracle internals support and data replication, but since these are areas not covered for OCP, they will not be covered here.

• WNDS Write No Database State tells Oracle to prevent the function from writing changes to the database
• WNPS Write No Package State tells Oracle to prevent the function from altering values for package global variables
• RNDS Read No Database State tells Oracle to prevent the function from issuing select statements against the database (highly restrictive)

• RNPS Read No Package State tells Oracle to prevent the function from obtaining values in package global variables (highly restrictive)

• Since function bodies are hidden within package bodies, Oracle must know how functions manipulate database and package data explicitly via your use of pragma RESTRICT_REFERENCES.

• Package specifications will be invalid only if their compilation fails. In this case, the body for that package will also be marked invalid, along with any package bodies that reference program units in the invalid package specification.
• If a package body containing program units referenced by a program in another package is rendered invalid, the referencing package will remain valid until the referencing program unit is called. Once the referencing package calls invalid code, it will also be rendered invalid.
• When a user calls a program unit in a package, a version of that package is copied into memory for that user. This act is called instantiation of the package.
• If someone recompiles the package, the next time a program unit from that package is called the user will receive error ORA-04068: Existing state of package has been discarded. Calling the procedure once more after this error is received will usually eliminate the problem.
• Regarding security, the same permissions for program unit creation and execution apply to packages, create procedure and execute.
• The same views are used to find out information as for procedures and functions, DBA_OBJECTS, DBA_ERRORS, and DBA_SOURCE.
• Oracle supplies several server-side packages for program development. Procedure Builder contains packages as well, which were covered in chapter 7.
• As an exercise, you should go back and review each of the next few of the Oracle server-supplied packages in both the book and in the package specifications given on your Oracle database. Use Procedure Builder to do so if it will help you gain some experience there as well.
• Some packages that handle aspects of transaction processing that will be on OCP exam 2 include:
• DBMS_ALERT
• DBMS_LOCK
• DBMS_SQL
• DBMS_TRANSACTION
• Some packages that handle aspects of application development that will be on OCP exam 2 include:
• DBMS_JOB
• DBMS_SESSION
• DBMS_PIPE
• DBMS_DESCRIBE
• DBMS_OUTPUT
• UTL_FILE
• DBMS_STANDARD
• Some packages that handle aspects of application and database administration that will be on OCP exam 2 include:
• DBMS_UTILITY
• DBMS_DDL
• DBMS_APPLICATION_INFO
• DBMS_SHARED_POOL