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:

Encapsulation of related procedures and variables in a single named, stored unit in the database. This provides for better organization during the development process and makes privilege management easier.
Separation of public and private procedures, variables, constants, and cursors.
Improved performance since the entire package is loaded into memory when an object from the package is called for the first time.

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.

Database Package Procedures
You create database package procedures on the Procedures tab of a database package using the Add a Row tool. To copy a procedure from elsewhere in the model, use the Create from Procedure tool.
Database Package Variables
Variables can be declared within a package, and can be used in a SQL or PL/SQL statement to capture or provide a value when one is needed. For example, you can define the variable in_stock with a boolean data type to verify if a product is available or not. You create database package variables on the Variables tab of a database package using the Add a Row tool.
Database Package Cursors
A cursor is a multi-row query, which lets you name a work area and access its stored information. You create database package cursors on the Cursors tab of a database package using the Add a Row tool.
Database Package Exceptions
PL/SQL allows you to explicitly handle internal and user-defined error conditions, called exceptions, that arise during processing of PL/SQL code. You create database package exceptions on the Exceptions tab of a database package using the Add a Row tool.
Database Package Types
A type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. You create database package types on the Types tab of a database package using the Add a Row tool.
Database Package Parameters
Database package procedures and cursors can use input and output parameters. For example, in a CREDIT procedure, you could define the parameters Account Number and Amount. You create database package parameters on the Parameters tab of a database package procedure or cursor using the Add a Row or Insert a Row tools.
Database Package Templates
Instead of modeling each individual database package by hand, you can use a template and have PowerDesigner generate packages specific to each table. Database packages defined through a template are updated automatically when you make changes to the table definition, and you can quickly create packages for multiple tables from the Rebuild Table Database Packages dialog.

Parent topic: Oracle