Package Designer

Creating Packages in Oracle Developer Tools

Use the Package Designer to create a new PL/SQL package specification and optionally, an empty package body. If you want to edit an existing package, in Server Explorer, right-click the Package node that represents the package and click Edit Package Specification. Then edit the package code in the PL/SQL Code Editor.

Double-clicking the package node opens the PL/SQL Code Editor. If package has a package body, the PL/SQL Code Editor displays the package body code. Otherwise, the package specification code is available in the PL/SQL Code Editor for editing.

In order to create functions and stored procedures as part of the package, you need to create the package with the Generate empty package body check box enabled. To edit the package body, right-click the Package node and select Edit Package Body from its menu. The code displays in the PL/SQL Code Editor for you to modify as needed.

Starting the Package Designer

In Server Explorer, right-click the Packages node and from the menu, select New Package.

The Package Designer appears similar to the following:

Description of package_designer1.gif follows

Description of the illustration package_designer1.gif

Using the Package Designer

The controls in the Package Designer are as follows:

Control	Description
Schema name	Select from the list of available schemas in which to create the package.
Package name	Enter a name for the package.
Authentication identifier	Select from the following choices: CURRENT_USER: Specifies that the package execute with the privileges of the current user. DEFINER: Specifies that the package execute with the privileges of the owner of the schema in which the package resides, and that external names resolve in the schema where the package resides. This is the default setting.
Generate empty package body	If you want the package specification to include a package body, enable this check box to create an empty package body. You must enable this check box if you want to create the package with stored procedures or functions. The Package Designer lets you generate an empty package body. This package body includes the methods that you defined for the package specification. To customize the package body code, modify it in the PL/SQL Code Editor after you have created the package. You can do so by right-clicking the package node in Server Explorer, and from the menu, selecting Edit Package Body.
Methods	Lists the existing methods in this package. To create a new method, click the Add button to display the Add Method dialog box. The method specification is added to the package specification. If you have checked Generate empty package body, Oracle Developer Tools adds the method specification to the package body also. To change the order of the list of methods, select the method to move and click the Up or Down arrows. To remove a method, select it and click Remove.
Preview SQL	Displays the `CREATE` `PACKAGE` and `CREATE` `PACKAGE` `BODY` SQL statements in a read-only pop-up dialog box.
OK	Saves your work, creates the package (including the package body if you added one) in the database and displays the package code in the PL/SQL Code Editor so that you can customize it. To commit the new package, click the Save button in the Visual Studio .NET toolbar or select Save from the File menu. If you have created the package with errors, Oracle Developer Tools displays an error dialog box, and then displays the error messages in the Output window. Click OK and correct the error in the designer. Afterwards, when you click the designer's OK button to save your changes, click Yes to replace the incorrect package with the corrected version. After you successfully create the package, Oracle Developer Tools displays its node in Server Explorer.

Add Method Dialog Box

The Add Method dialog box appears as follows:

Description of package_designer_add_method.gif follows

Description of the illustration package_designer_add_method.gif

The controls in the Add Method dialog box are as follows:

Control	Description
Method name	Specify the name of a method to add to this package.
Method type	Select from the following choices: Function Procedure
Return type	Select from the choices of data types listed.
Use pipelining to return rows	Returns the results of a table function iteratively.
Parameters	Lists parameters for this method. To create a new parameter, click Add, and then use the Parameter Details pane to modify the parameter as needed. When you create parameters, their PL/SQL code appears in the SQL Preview box. To modify the order of parameters, select the parameter to move and click the Up or Down arrow. To remove a parameter, select it and click Remove.
Parameter details	Displays the following information: Name: Enter a name for the parameter. Direction: Select from the following: IN: Indicates that you must supply a value for the argument when calling the method. `IN` parameters can have a default value. OUT: Indicates that the method passes a value for this argument back to its calling environment after execution. IN-OUT: Indicates that you must supply a value for the argument when calling the method and that the method passes a value back to its calling environment after execution. Data type: Select from the list. If the data type is not listed, you can type it here. Default: Specify a default value to use if the parameter is not passed. No copy: Instructs the database to pass this argument as fast as possible. This clause can significantly enhance performance when passing a large value like a record, an index-by table, or a varray to an `OUT` or `IN` `OUT` parameter. `IN` parameter values are always passed using the No copy option. REF: Makes parameter data type objects into reference types.
OK	Creates the method and returns to the New Package dialog box.