Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
Executing in the Code Editor
In the Code Editor, click the Execute button. DbVisualizer then generates a script for executing the code object, using variables for all parameters, and executes it.
Since the script contains variables, the Variable Prompt dialog pops up. Enter values for all parameters and click Continue to execute the procedure. The result is shown in the results area below the editor.
In the example shown in the figure, all parameters are input parameters but DbVisualizer also supports the execution of procedures with output parameters and functions returning a value. In this case, the generated script includes
@echo statements to write the result in the Log tab. Please see below for more details.
Executing in the SQL Commander
The scripts generated and executed by the Code Editor can also be included in a script and executed in an SQL Commander. Here's an example of a script calling a function and writing the result in the SQL Commander Log tab:
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
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:
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.
More formally, the
@call command has this syntax when calling a function:
where the <FunctionName> may need to be fully qualified with a schema (and/or catalog/database) and the <ParamList> is a comma separated list of literal values or variables. Here's an example:
For a procedure, use this syntax:
where the <ProcedureName> may need to be fully qualified with a schema (and/or catalog/database) and the <ParamList> is a comma separated list of literal values or variables. Here's an example:
As shown in these examples, you must use the
dir option to specify how the variable is to be used (in, out or inout) and you may use the
noshow option to prevent being prompted for a value for an output variable.
You can use the @echo command to write the value assigned to an output variable to the log.
Using the Script Object Dialog
Instead of writing a
@call script by hand in an SQL Commander, you can use the Script Object (e.g. Script Procedure or Script Function) right-click menu choices for the object node in the tree.
This opens the Script Object dialog where you select that you want to generate a CALL script and can adjust settings for using delimiters and qualifiers, as well as the destination for the generated script. This is how the Script Dialog will look like when opened for a Procedure.