1 Introduction to TimesTen-Supplied PL/SQL Packages and Types

Package overview

A package is an encapsulated collection of related program objects stored together in the database. Program objects are procedures, functions, variables, constants, cursors, and exceptions.

This section covers the following topics:

• Package components

• Using TimesTen-supplied packages

• Referencing package contents

• Running package examples

Package components

PL/SQL packages have two parts, the specification and the body, although sometimes the body is unnecessary. The specification is the interface to your application. It declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the specification.

Unlike subprograms, packages cannot be called, parameterized, or nested. However, the formats of a package and a subprogram are similar:

CREATE PACKAGE name AS  -- specification (visible part)
   -- public type and item declarations
   -- subprogram specifications
END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)
   -- private type and item declarations
   -- subprogram bodies
[BEGIN
   -- initialization statements]
END [name];

The specification holds public declarations that are visible to your application. The body holds implementation details and private declarations that are hidden from your application. You can debug, enhance, or replace a package body without changing the specification. You can change a package body without recompiling calling programs because the implementation details in the body are hidden from your application.

Using TimesTen-supplied packages

TimesTen-supplied packages are automatically installed when the database is created.

All users have EXECUTE privilege for packages described in this document, other than for UTL_RECOMP and UTL_FILE, as noted in those chapters.

To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view. Instructions on special requirements for packages are documented in the individual chapters.

Referencing package contents

To reference the types, items, and subprograms declared in a package specification, use "dot" notation. For example:

package_name.type_name
package_name.item_name
package_name.subprogram_name

Running package examples

In order to see the output from the package examples in this document, first execute the following command in ttIsql:

Command> set serveroutput on

Summary of TimesTen-supplied PL/SQL packages

Table 1-1 lists the PL/SQL packages supplied with TimesTen for public use. These packages run as the invoking user, rather than the package owner.