Managing Procedures

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

Topics:

Creating a Procedure

To create a procedure:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
Click Create.
From the list of object types, select Procedure.
Enter a procedure name and click Next.
Define the arguments by specifying the following information (optional):
- Argument Name
- In/Out (the parameter mode)
- Argument Type (datatype)
- Default (value)
To add additional arguments, click Add Argument.
Click Next.
Enter PL/SQL block you want to use as the procedure body and click Next.

A confirmation page appears, which displays the SQL used to create the procedure.
Click Create Procedure.

Browsing a Procedure

To browse a procedure:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
From the Object list, select Procedures.
From the Object Selection pane, select a procedure.

The Code view appears, displaying the source code for the procedure. You can edit the code in this view.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the procedure. Table 1-8 describes all available views.

Table 1-8 Available Views for Procedures

View	Description
Code	(Default) Displays the source code for the procedure. You can edit the code in this view. Actions you can perform in this view include: Edit the code manually Save & Compile Find & Replace Undo Redo Download Source Drop See Also: "Editing a Procedure", "Compiling a Procedure", "Downloading a Procedure", and "Dropping a Procedure"
Dependencies	Displays objects that use (or depend) on the current procedure and objects the procedure depends on.
Errors	Lists errors related to the current procedure.
Grants	Lists details of grants for the current procedure, including privilege, grantee, grantable, grantor, and object name.

Editing a Procedure

When you edit a procedure you can edit the code manually or perform a search and replace.

Editing a Procedure Manually

To edit a procedure manually:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
From the Object list, select Procedures.
From the Object Selection pane, select a procedure.

The Code view appears. You can edit the code in this view.
Click Find & Replace to perform a basic search and replace.

Compiling a Procedure

If you edit and make changes to a procedure, you must compile to save your changes. There is no save function because this is just a view of the object within the database.

To compile a procedure:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
From the Object list, select Procedures.
From the Object Selection pane, select a procedure.
Click Save & 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

To download a procedure:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
From the Object list, select Procedures.
From the Object Selection pane, select a procedure.
Click Download Source to save the current procedure as a file.

Dropping a Procedure

To drop a procedure:

On the Workspace home page, click SQL Workshop and then Object Browser.

Object Browser appears.
From the Object list, select Procedures.
From the Object Selection pane, select a procedure.
Click Drop.
To confirm, click Finish.