Parameter Definitions

When you invoke a procedure, you must pass it a value for each of the procedure's parameters. If you pass values to the parameter, they are positional and must appear in the same order as they appear in the procedure declaration. If you pass argument names, then they can appear in any order. You can have a combination of values and names in the argument values. If this is the case, the values identified in order must precede the argument names.

Listing Stored Procedure Information

Oracle provides several data dictionary views that provide information about procedures that are currently stored in your schema:

• all_errors—A list of current errors on all objects accessible to the user
• all_source—Text source of all stored objects accessible to the user
• user_objects—A list of all the objects the current user has access to
• dba_errors—Current errors on all stored objects in the database
• dba_object_size—All PL/SQL objects in the database
• dba_source—Text source of all stored objects in the database
• user_errors—Current errors on all a user's stored objects
• user_source—Text source of all stored objects belonging to the user
• user_object_size—User's PL/SQL objects

The code in Listing 8.4 queries the user_errors view to obtain information about the current errors on a procedure owned by user_01.

INPUT/
OUTPUT
Listing 8.4. Viewing errors in the database.

SELECT LINE, TYPE, NAME, TEXT from user_errors

LINE  TYPE   NAME     TEXT
----  ----   ------   -----------------------------------
  4   PROC   PST_QTY  PL/SQL-00387: into variable cannot be a database object
                      PL/SQL: SQL statement ignored

Additionally, you can select from the user_objects table and interrogate which objects are invalid and need to be recompiled. The following piece of code will produce the object names and their types for those database objects that need to be evaluated for recompilation:

SELECT object_name, object_type
from user_objects
WHERE status = `INVALID';

Dropping a Stored Procedure

Issue the SQL statement DROP procedure to drop a procedure object. The following statement drops the procedure parts_qty:

DROP PROCEDURE parts_qty;

Overloading

Oracle permits you to call the same procedure name in a package but with different arguments. This is known as overloading. This technique is very useful, especially when you want to execute the same procedure several times but with arguments that have different datatypes. One example of using procedure overload is with the package DBMS_OUTPUT. In this package, the PUT_LINE procedure is called numerous times to produce output lines of different datatypes.

The following example illustrates the definition of two overloaded local procedures:

DECLARE
PROCEDURE compute_sales (begin_date in date) RETURN boolean
is
BEGIN
RETURN begin_date > :start_date;
END;

PROCEDURE compute_sales (sales_in in date) RETURN boolean
is
BEGIN
RETURN sales_in > :sales_target;
END;

When the PL/SQL engine encounters a call to compute_sales, the compiler executes the module in the body that has the correct and matching module header.

Recursion

A recursive procedure is a procedure that calls itself. Each recursive call creates a new instance of any object declared in the procedure, including parameters, variables, cursors, and exceptions. Also, new instances of SQL statements are created at each level in the recursive procedure.

With recursive logic, the procedure must be able to terminate itself at some predefined point or else the recursion would last forever. This point of termination is defined in a terminating condition. The following example uses a conditional statement to terminate the recursive cycle:

FUNCTION inv_calc
BEGIN
IF qty = > :max_qty THEN -- terminating condition;
RETURN 1;
ELSE

RETURN qty * inv_calc (qty * :part_qty); -- recursive call
...
END IF
END inv_calc;

Be careful with recursion and where you place the recursive call. If you place the recursive call inside a cursor FOR loop or between OPEN and CLOSE statements, a cursor is opened at each call. This can open enough cursors to violate the maximum allowable open cursors permitted by the OPEN_CURSOR initialization parameter.

Packages

A package is an encapsulated collection of related schema objects. These objects can include procedures, functions, variables, constants, cursors, and exceptions. A package is compiled and then stored in the database's data dictionary as a schema object.

The packages contain stored subprograms, or standalone programs, which are called the package's subprograms. These subprograms can be called from another stored program, triggers, precompiler programs, or any of the interactive Oracle programs like SQL*Plus. Unlike the stored subprograms, the package itself cannot be called, passed parameters to, or nested.

A package usually has two components to it, a specification and a body. The specification declares the types, variables, constants, exceptions, cursors, and subprograms that are available for use. The body fully defines cursors, functions, and procedures and so implements the specification.

Why Use Packages?

Packages offer the following advantages:

• Packages enable you to organize your application development more efficiently into modules. Each package is easily understood, and the interfaces between packages are simple, clear, and well-defined.
• Packages allow you to grant privileges more efficiently.
• The package's public variables and cursors persist for the duration of the session. Therefore, all cursors and procedures that execute in this environment can share them.
• Packages enable you to perform overloading on procedures and functions.
• Packages improve performance by loading multiple objects into memory at once. Therefore, subsequent calls to related subprograms in the package require no I/O.
• Packages promote code reuse through the use of libraries that contain stored procedures and functions, thereby eliminating redundant coding.

Package Helpful Hints

The following helpful hints can make your use of Oracle packages more successful.

Package Specification

The package specification contains public declarations of the name of the package and the names and datatypes of any arguments. This declaration is local to your database and global to the package. This means that the declared objects in your package are accessible from anywhere in the package. Therefore, all the information your application needs to execute a stored subprogram is contained in the package specification.

The following is an example of a package declaration. In this example, the specification declares a function and a procedure:

CREATE PACKAGE inv_pck_spec as

FUNCTION inv_count(qty number, part_nbr varchar2(15))
RETURN number;

PROCEDURE inv_adjust(qty number);

END inv_pck_spec;

Sometimes a specification only declares variables, constants, and exceptions, and therefore, a package body is not necessary. The following example is a package specification for a package that does not have a package body:

CREATE PACKAGE inv_costings is

type inv_rec is record
(part_name varchar2(30),
part_price number,
part_cost number);