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 package in the Browser, and select New > Database Package.

Database Package Properties

You can modify an object's properties from its property sheet. To open a database package property sheet, double-click its diagram symbol or its Browser entry in the Database Packages folder.

The following properties are available on the General tab:

Property	Description
Name	The name of the item which should be clear and meaningful, and should convey the item's purpose to non-technical users.
Code	The technical name of the item used for generating code or scripts, which may be abbreviated, and should not generally include spaces.
Comment	Additional information about the database package.
Stereotype	Sub-classification used to extend the semantics of an object without changing its structure; it can be predefined or user-defined.
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.

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