Database Packages (Oracle)

In Oracle, packages encapsulate related procedures, functions, and associated cursors and variables together as a unit in the database. Packages usually have two parts, a specification and a body. The specification is the interface with your applications; 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.

Packages provide advantages in the following areas:

You can generate and reverse engineer database packages in the same way as other database objects (see Generating and Reverse-Engineering Databases). When you reverse engineer a database package, the sub-objects (variable, procedure, cursor, exception, and type) are created from the specification and the body of the database package.

Creating a Database Package

You can create a database package in any of the following ways:
  • Select Model > Database Packages to access the List of Database Packages, and click the Add a Row tool.

  • Right-click the model (or a package) in the Browser, and select New > Database Package.

Database Package Properties

To view or edit a database package's properties, double-click its Browser or list entry. The property sheet tabs and fields listed here are those available by default, before any customization of the interface by you or an administrator.

The General tab contains the following properties:

Property

Description

Name/Code/Comment

Identify the object. The name should clearly convey the object's purpose to non-technical users, while the code, which is used for generating code or scripts, may be abbreviated, and should not normally include spaces. You can optionally add a comment to provide more detailed information about the object. By default the code is generated from the name by applying the naming conventions specified in the model options. To decouple name-code synchronization, click to release the = button to the right of the Code field.

Stereotype

Extends the semantics of the object. You can enter a stereotype directly in this field, or add stereotypes to the list by specifying them in an extension file.

Owner

Specifies the name of the database package owner, which you choose from the list of users.

Privilege

Lets you specify whether the functions and procedures in the database package execute with the privileges and in the schema of the user who owns it (definer), or with the privileges and in the schema of CURRENT_USER (invoker).

Table

Specifies the table with which the database package is associated.

Template

Specifies the template on which the database package is based (see Database Package Templates). If you use a template, then the remaining tabs of the property sheet will be completed by the template. If you make any modifications to the other tabs, then the User-Defined button to the right of the field is depressed and the package is detached from the template and will no longer be automatically updated when you modify the definition of the table with which it is associated.

Keywords

Provide a way of loosely grouping objects through tagging. To enter multiple keywords, separate them with commas.

The following tabs are also available:
  • Procedures – Lists the procedures associated with the database package (see Database Package Procedures).

  • Variables - Lists the variables associated with the database package (see Database Package Variables).

  • Cursors - Lists the cursors associated with the database package (see Database Package Cursors).

  • Exceptions – Lists the exceptions associated with the database package (see Database Package Exceptions).

  • Types - Lists the types associated with the database package (see Database Package Types).

  • Initialization - Lets you define initialization code for the database package body. Typically initialization holds statements that initialize database package variables. Initialization takes place after database package creation and compilation in the server.

  • Preview - Displays the SQL code that will be generated for the database package.