Procedure Designer

Creating Procedures in Oracle Developer Tools

Use the Procedure Designer to create the template code for a new standalone procedure. After you create the procedure template, the PL/SQL Code Editor opens with the function code displayed so that you can customize the procedure as needed.

If you want to edit an existing procedure, in Server Explorer, double-click the node that represents the procedure or right-click the node and click Edit. Then edit the procedure code in the PL/SQL Code Editor.

If you want to create a procedure and add it to a package, consider using the Package Designer to create the procedure.

Once you have completed the procedure, you can compile and run it by right-clicking its procedure node in Server Explorer and choosing from the menu that appears. To delete the procedure, right-click its node and from the menu, select Delete. For more information, see Procedure Nodes.

Note: If you want to create a SQLJ, C, or Java call specification, or a pragma clause, use the PL/SQL Code Editor.

Starting the Procedure Designer

In Server Explorer, right-click the Procedures node and from the menu, select New PL/SQL Procedure.

The Procedure Designer appears as follows:

Description of storedproc_designer1.gif follows

Description of the illustration storedproc_designer1.gif

Using the Procedure Designer

In general, use the Procedure Designer to create a template for the procedure. The template contains parameters for the base definition of the procedure, but not more elaborate code such as statements to add within the template's BEGIN and END statements. After you create this template, you can customize it in the PL/SQL Code Editor.

The controls in the Procedure Designer are as follows:

Control	Description
Schema name	Select from the list of available schemas in which to create the procedure.
Procedure name	Enter a name for the procedure.
Authentication identifier	Select from the following choices: CURRENT_USER: Specifies that the procedure execute with the privileges of the current user. DEFINER: Specifies that the procedure execute with the privileges of the owner of the schema in which the procedure resides, and that external names resolve in the schema where the procedure resides. This is the default setting.
Parameters	Lists the parameters and their data types used in this procedure. To create a new parameter, click Add, and then use the Parameter Details pane to modify the parameter as needed. As 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.
Parameters detail	Displays detailed information about the selected parameter: 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 procedure. `IN` parameters can have a default value. OUT: Indicates that the procedure 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 procedure and that the procedure 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.
Preview SQL	Displays the `CREATE` `PROCEDURE` SQL command code in a pop-up dialog box and in the output window. Read-only.
OK	Saves your work, creates the procedure with the template code in the database, and displays the procedure template code in the PL/SQL Code Editor so that you can customize it. To commit the changes to the new procedure template, click the Save button in the Visual Studio .NET toolbar or select Save from the File menu. If you have created the procedure 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 Procedure Designer. Afterwards, when you click the designer's OK button to save your changes, click Yes to replace the incorrect function with the corrected version. After you successfully create the procedure, Oracle Developer Tools displays its node in Server Explorer.