Procedure Editor

• Introduction
• Create Procedure
• Edit and Compile
• Execute in SQL Commander
• Script CALL to Editor

Introduction

Many databases offer the capability to store custom code in the database, primarily as functions and procedures, where a function has a return value but a procedure does not (a procedure may instead have output parameters). In addition, some databases offer a 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. Many databases also support triggers: code that is executed when triggered by an event such as deleting a row in a table. 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. Procedures and functions can also be executed in the SQL Commander, with return values and parameters bound to DbVisualizer variables.

The examples throughout this document refer to the procedure object type, but all described features can also be applied to the other types of custom code objects. The screenshots show the interface for the Oracle profile, but it is very similar for other profiles.

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, the Data Type and the Direction (typically one of IN, OUT or INOUT).

The action uses this information together with a simple sample body to compose a CREATE statement. You can not enter the real code in the action dialog. The real code is often complex and large, so DbVisualizer provides a more powerful editing environment than what would fit in an action dialog via the Procedure Editor, described below. What you create with the action should be seen as a template that you then complete and work with in the editor.

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

The editor has a toolbar with various actions to save/compile the procedure, save and load the source to/from file and perform common editing operations. The Status indicator shows whether the procedure is valid or invalid based on last compilation (not available for all databases).

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

Figure: Compiling procedure with errors

If errors occur during compilation, the error list appears below the editor. It shows the row/column 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. Note, however, that some databases do not provide row/column information, only an error message. You then have to locate the incorrect statement yourself based on the description of the error.

In addition to the Status indicator in the editor, the object icon in the tree shows a little red cross for invalid procedures, for databases that provide this information. You can see this for the UPDATE_STATUS procedure node in the figure in the previous section.

The figure below shows the result after correcting the errors and recompiling the procedure:

Figure: Compiling procedure with successful result

The  status indicator now shows that the procedure is VALID.

Execute in SQL Commander

You can also test the procedure. First, click the Execute button in the Procedure Editor. DbVisualizer then generates a script for executing the procedure, using variables for all parameters, and executes it in the SQL Commander as shown in the next screenshot.

Figure: Running the procedure in SQL Commander

Because the script contains variables, the Variable Prompt dialog pops up. Enter values for all parameters and click Continue to execute the procedure.

In the example shown in the figure, all parameters are input parameters but DbVisualizer also support execution of procedures with output parameters and functions returning a value:

@call ${STATUS||(null)||String||noshow dir=out}$ = "HR"."GET_STATUS"(1002);
@echo STATUS: ${STATUS}$;

In this example, the result value from the GET_STATUS function is assigned to a variable named STATUS. Note that is has an option dir=out. This is a requirement for a variable that is assigned a value at runtime, whether it is used for a return value from a function call or for an output parameter in a procedure call. It also has the noshow option, to avoid getting prompted for a value for the variable. The value of the STATUS variable is then written to the log using the @echo command.

You can also use the output from one function or procedure as input to another, or even as a value in a SELECT or other SQL statement:

@call ${STATUS||(null)||String||noshow dir=out}$ = "HR"."GET_STATUS"(1002);
@call "HR"."UPDATE_STATUS"(1000, 2000, ${STATUS||||String||noshow dir=in}$);

Note that dir=in is specified for the STATUS variable when it is used in the UPDATE_STATUS procedure call. When you use a variable first for output and then as input with another @call command, you must change the direction option like this.

The @call command is described more formally in the Client Side Commands section of the SQL Commander chapter, and the full variable syntax is described in the Variables section of the same chapter.

Script CALL to Editor

As an alternative to using the Execute button in the Procedure Editor to generate a @call script, you can use the Script Object to SQL Editor right-click menu choices for a procedure or function object in the Objects Tree.

Figure: Creating a @call script for a procedure

---