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.

Note: To rebuild database package procedure dependencies (along with other procedure dependencies), select Tools > Rebuild Objects > Rebuild Procedures Dependencies (see Rebuilding Trigger and Procedure Dependencies).

To view or edit a database package procedure'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.

DB Package

Name of the database package to which the procedure belongs.

Type

Allows you to choose between procedure and function.

Return data type

Allows you to define the return data type of a function.

Pragma

Allows you to type a compiler directive, that is, a string for specifying compilation parameters for the procedure.

Public

Allows you to declare the procedure in the package specification and to permit use from outside the database package. A private procedure (check box deselected) is only defined in the package body.

Keywords

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

The following tabs are also available:
  • Parameters – Lists the input and output parameters required by the procedure (see Database Package Parameters).

  • Definition tab - Lets you define package procedures. Package procedures are not built using the structure of templates defined in the DBMS. You have to type the entire package procedure definition. To do so, you can use operators and functions to insert script items into the cursor definition.

For example, the definition of the CREDIT package procedure is the following:

CREATE PROCEDURE credit (Account_number NUMBER, Amount IN NUMBER) AS
BEGIN
UPDATE accounts
SET balance = balance + amount
WHERE account_id = acc_no;
END;