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

See Also:

"Oracle Database PL/SQL Language Reference"

2.13.1 Creating a Procedure

Create procedure using Object Browser.

To create a procedure:

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

    Object Browser appears.

  2. Click the Create icon.
  3. From the list of object types, select Procedure.
  4. For Name:

    1. Procedure Name - Enter the name of the procedure.

      Note:

      Object names must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

    2. Specify whether to Include Arguments or Preserve Case.

    3. Click Next.

  5. For Arguments, define the arguments by specifying the following information (optional):

    • Argument Name

    • In/Out (the parameter mode)

    • Argument Type (datatype)

    • Default (value)

    • Move

    To add additional arguments, click Add Argument.

  6. Click Next.
  7. For Define, enter PL/SQL block you want to use as the procedure body and click Next.

    A confirmation page appears.

  8. Click Create Procedure.

2.13.2 Browsing a Procedure

Select a procedure from the Object Selection pane and view different reports about the procedure.

To browse a procedure:

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

    Object Browser appears.

  2. From the Object list, select Procedures.
  3. 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.

  4. Click the tabs at the top of the page to view different reports about the procedure.

2.13.3 Reports for Procedures

Alternative views available when viewing a procedure in Object Browser.

Table 2-8 describes all available reports for procedures.

Table 2-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

  • Download Source

  • Drop

  • Undo

  • Redo

  • Find

  • Replace

  • Auto Complete

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.

See Also:

2.13.4 Editing a Procedure

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

2.13.4.1 Editing a Procedure Manually

Select a procedure from the Object Selection pane and click Find or Replace.

To edit a procedure manually:

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

    Object Browser appears.

  2. From the Object list, select Procedures.
  3. From the Object Selection pane, select a procedure.

    The Code view appears. You can edit the code in this view.

  4. To perform a basic search, click the Find icon (which resembles a magnifying glass).
  5. To replace existing code, click the Replace icon (which resembles a two-sided arrow).

2.13.5 Compiling a Procedure

If you edit and make changes to a view, 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:

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

    Object Browser appears.

  2. From the Object list, select Procedures.
  3. From the Object Selection pane, select a procedure.
  4. 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.

2.13.6 Downloading a Procedure

Select a procedure from the Object Selection pane and click Download Source to save the current procedure as a file.

To download a procedure:

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

    Object Browser appears.

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

2.13.7 Dropping a Procedure

Select a procedure from the Object Selection pane and click Drop.

To drop a procedure:

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

    Object Browser appears.

  2. From the Object list, select Procedures.
  3. From the Object Selection pane, select a procedure.
  4. Click Drop.
  5. To confirm, click Finish.