Procedure Editor

DbVisualizer Logo
DbVisualizer 6.0
July 2007
http://www.dbvis.com
support@dbvis.com


[ Master documentation index ]

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 a function returns a value while a procedure does not. In addition, some databases offer the package concept, which means that a collection of functions and/or procedures are grouped together in one unit. A package is the interface describing the functions and procedures, while the package body contains the implementation. You can use DbVisualizer actions to create and drop procedural object of these types, and use the procedure editor to browse, edit and compile these object types.

The examples throughout this document refer to the procedure object type, but 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 is displayed in which you enter the procedure name and the parameters for the new procedure. This data forms the interface for the procedure. 


Figure: The create procedure dialog

Use the buttons to the right of the parameter list to insert, remove and move the parameters. For every parameter, you must supply its Name. Leaving out the Data Type results in the VARCHAR data type. The Direction is by default set to IN.

You can also enter the implementation source code in the Source area, but be aware that it is saved as-is for some databases; if there are any errors, you may not be notified when you save the procedure. You need to verify the status and fix errors, if any, as described in the next section. As an alternative, you may therefore like to leave just the default sample implementation in the Create Procedure dialog and enter the real the implementation later.

Click Execute 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! For other databases, you need to use the SQL Commander and commands like ALTER PROCEDURE to edit and compile the procedure. See your database documentation for details.

The editor has a toolbar with various actions to compile the procedure, save and load the source 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.

Edit the source code and compile the procedure when you are happy with the code, using the Compile toolbar button.


Figure: Compiling procedure with errors

If error(s) occur during compilation, the error list appears below the editor. It show the row number for the error in the source editor and an error message. When you click the error in the list, the corresponding row is highlighted in the editor.

In addition to the Status indicator in the editor, the object icon in the tree shows a little red cross for invalid procedures. The figure below shows the result of correcting the errors and recompiling the procedure:


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 shown in the next screenshot.


Figure: Running the procedure in SQL Commander

The figure shows the invocation of the update_status procedure with parameter values meaning that all IDs in the ORDERS table between 1200 and 2000 should be set to "OPEN". The second statement selects from the updated table.


Copyright © 2007 Onseven Software AB. All rights reserved.