Function Designer

Creating Functions in Oracle Developer Tools

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

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

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

Once you have completed the function, you can compile and run it by right-clicking its function node in Server Explorer and choosing from the menu that appears. To delete the function, right-click its node and from the menu, select Delete.

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 Function Designer

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

The Function Designer appears similar to the following:

Description of function_designer.gif follows

Description of the illustration function_designer.gif

Using the Function Designer

In general, use the Function Designer to create a template for the function. The template contains parameters for the base definition of the function, 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 Function Designer are as follows:

Control	Description
Schema name	Select from the list of available schemas in which to create the function.
Function name	Enter a name for the function.
Return type	Select from the choices of data types listed. If the data type is not listed, you can type it in the Return type box.
Authentication identifier	Select from the following choices: CURRENT_USER: Specifies that the function executes with the privileges of the current user. DEFINER: Specifies that the function executes with the privileges of the owner of the schema in which the function resides, and that external names resolve in the schema where the function resides. This is the default setting.
Use pipelining to return rows	Returns the results of a table function iteratively.
Parameters	Lists the parameters and their data types used in this function. 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 function. IN parameters can have a default value. OUT: Indicates that the function 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 function and that the function 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` `FUNCTION` SQL command code in a pop-up dialog box and in the output window. Read-only.
OK	Saves your work, creates the function with the template code in the database, and displays the function template code in the PL/SQL Code Editor so that you can customize it. To commit the changes to the new function template, click the Save button in the Visual Studio .NET toolbar or select Save from the File menu. If you have created the function 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 Function 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 function, Oracle Developer Tools displays its node in Server Explorer.