Managing Procedures

A procedure is a subprogram that performs a specific action. Use Object Browser to view, create, edit, download, and drop procedures.

See Also: Overview of PL/SQL in Oracle AI Database PL/SQL Language Reference

Creating a Procedure

Create procedures using Object Browser.

To create a procedure:

1. In Object Browser, click the Create Database Objects menu and select Procedure.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create Procedure Wizard appears.

2. On Create Procedure:

   1. Procedure Name - Enter the name of the procedure to be created. This name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

   2. Include Sample Code - Specify whether to include sample code while creating the procedure.

   Tip:

   The SQL Script region displays the SQL generated by this wizard. To copy the SQL, click the Copy icon.

3. Click Create Procedure.

Viewing a Procedure

Select a procedure from the Object Tree and access tabs in the Object Detail View.

To view a procedure:

1. In Object Browser, Object Tree, expand Procedures and select a procedure.

2. The Object Detail View appears and displays four tabs:

   • Code - Displays the source code for the procedure. You can edit the source code in this view using a fully functional Code Editor. Available actions include:

     • Download

     • Save and Compile

     • Drop.

     • Refresh

     Tip:

     To learn more about the Code Editor, seeCode Editor in Object Browser.

   • Dependencies - Displays objects that use (or depend) on the current procedure and objects the procedure depends on. To link to an object, click the object name.

   • Errors - Lists errors related to the current procedure.

   • Grants - Details of grants for the current procedure, including privilege, grantee, grantable, grantor, and object name. Available actions include:

     • Grant

     • Revoke

     • Refresh

Editing a Procedure

Select a procedure from the Object Tree and edit it in the Code tab.

To edit a procedure:

1. In Object Browser, Object Tree, expand Procedures and select a procedure.

   The Object Detail View appears. The Code tab displays the source code for the procedure and features a fully functional Code Editor.

   Tip:

   To learn more about the Code Editor, seeCode Editor in Object Browser.

2. Edit the source code directly using the Code Editor.

   If you edit and make changes to a view, you must compile to save your changes.

3. Click Save and Compile to compile the current procedure.

   Compiling re-creates the object in the database. If the compilation fails, an error message displays above the code.

Compiling a Procedure

Select a procedure from the Object Tree and click Save and Compile.

If you edit and make changes to a procedure, you must compile to save your changes.

To compile a procedure:

1. In Object Browser, Object Tree, expand Procedures and select a procedure.

   The Object Detail View appears.

2. On the Code tab, click Save and Compile to compile the current procedure.

   Compiling re-creates the object in the database. If the compilation fails, an error message displays above the code.

Downloading a Procedure

Select a procedure from the Object Tree and click Download to save it as a file.

To download a procedure:

1. In Object Browser, Object Tree, expand Procedures and select a procedure.

   The Object Detail View appears.

2. On the Code tab, click Download to save the current procedure as a file.

Dropping a Procedure

Select a procedure from the Object Tree and click Drop.

To drop a procedure:

1. In Object Browser, Object Tree, expand Procedures and select a procedure.

   The Object Detail View appears.

2. On the Code tab, click and then Drop.

   1. Drop - Review the details.

   2. SQL - Displays the SQL generated to drop the procedure. To copy the displayed SQL, click the Copy icon.

   3. To confirm your selection, click Drop again.