3.9 Managing Procedures

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

• Creating a Procedure
  Create procedures using Object Browser.
• Viewing a Procedure
  Select a procedure from the Object Tree and access tabs in the Object Detail View.
• Editing a Procedure
  Select a procedure from the Object Tree and edit it in the Code tab.
• Compiling a Procedure
  Select a procedure from the Object Tree and click Save and Compile.
• Downloading a Procedure
  Select a procedure from the Object Tree and click Download to save it as a file.
• Dropping a Procedure
  Select a procedure from the Object Tree and click Drop.

See Also:

Overview of PL/SQL in Oracle Database PL/SQL Language Reference

3.9.1 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.

3.9.2 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, see Code 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

3.9.3 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, see Code 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.

3.9.4 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.

3.9.5 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.

3.9.6 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.