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
Copyright © 2010 DbVis Software AB. All rights reserved.