DbVisualizer - Procedure Editor

Procedure Editor
DbVisualizer 5.1 October 2006	http://www.dbvis.com support@dbvis.com

[ Master documentation index ]

Introduction
Create Procedure
Edit and Compile
Running in SQL Commander

Introduction

Many databases offers the capability to store programs in the database. These programs may be of two types, functions and procedures. The difference is that functions return a value while procedures don't. Some databases do in addition offer the package concept which means that a collection of functions and/or procedure are grouped together in one unit. A package is the interface describing the functions and procedures while the package body contains the implementation. The related functionality to create and drop these procedural object types in DbVisualizer are activated via the object actions menu. The procedure editor is used to browse, edit and compile these object types.

The examples throughout this document refer to the procedure object type while all described features can also be applied to function, package and package body objects.

Create Procedure

To create a new procedure simply select the Procedures node in the objects tree and choose Create Procedure from its action menu.

Figure: The actions menu for the Procedures node

Next a dialog will be displayed in which parameters for the new procedure are entered. This data forms the interface for the procedure. You can leave the parameters and edit them later. The source for the procedure is edited in a later step.

Figure: The create procedure dialog

Use the buttons to the right of the parameter list to insert, remove and move the entries. For every parameter you must supply its Name, leaving Data Type results in the VARCHAR datatype and the Direction is by default set to IN.

Now press Ok in the dialog to create the new procedure.

Figure: The newly created procedure

Selecting the newly created procedure in the tree will show the source for it in the procedure editor.

Edit and Compile

Note: This editor is currently supported only for Oracle databases!
The editor have a small toolbar with various actions to start compilation, save and load to/from file, show parameter information and common editing operations. The Status indicator shows whether the procedure is valid or invalid based on last compilation.

To start editing then just edit the content. Compile the procedure using the compile toolbar button.

Figure: Compiling procedure with errors

If error(s) occur during compilation then the error list will appear below the editor. It lists the row number in the source editor where the error is and an error message. Click the error in the list and it will highlight the corresponding row in the editor. The Status indicator is switched to INVALID if errors are in the procedure. The same applies for the object icon in the tree which shows a little red cross for invalid procedures.

Correcting the error yields correct results as in the next figure.

Figure: Compiling procedure with successful result

The status indicator now shows that the procedure is VALID.

Running in SQL Commander

You can now test the procedure in the SQL Commander as in the next screen shot

Figure: Running the procedure in SQL Commander

The figure shows the invocation of the moveorder procedure with parameters meaning that all IDs in the ORDERS table between 1 and 3 should be set to Pre-Closed. The second statement selects from the updated table.