Executing Complex Statements
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
DbVisualizer splits a script into statements at the delimiters, such as ;, and sends them to the database one by one. A complex statement, such as CREATE PROCEDURE, contains other statements with their own delimiters, so DbVisualizer may split it in the wrong place. This page describes four ways to tell DbVisualizer where such a statement starts and ends.
To create or edit a single procedure, function or trigger, it is easier to use Create Procedure and the procedure editor.
Using Execute Buffer
SQL Commander → Execute Buffer sends the whole text in the editor, or the selected text, to the database as one statement. Nothing is split, and comments aren't removed. Use it when the complex statement is the only statement in the editor.
Using an SQL dialect
For most of the supported databases, DbVisualizer can recognize complex statements by their syntax. To turn this on, select Allow SQL Dialects in the General / SQL Commander / Statement Delimiters category in Tools → Tool Properties.
For example, this MySQL trigger is then executed as one statement:
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;
Recognizing the statements takes time. If very large scripts run slowly, turn off Allow SQL Dialects and use one of the other ways.
Using an SQL block
To mark a part of a script as one statement, put a begin identifier on the line before it and an end identifier on the line after it. Each identifier must be alone on its line. By default, the begin identifier is --/ and the end identifier is /. You can change them in the General / SQL Commander / Statement Delimiters category.
For example, an Oracle block:
--/
script to disable foreign keys
declare cursor tabs is select table_name, constraint_name
from user_constraints where constraint_type = 'R' and owner = user;
begin
for j in tabs loop
execute immediate ('alter table '||j.table_name||' disable constraint'||j.constraint_name);
end loop;
end;
/
Using the @delimiter command
The @delimiter client-side command changes the statement delimiter for the rest of the script. Use it before the complex statement, and again after it to change the delimiter back.
@delimiter ++;
CREATE OR REPLACE FUNCTION HELLO (p1 IN VARCHAR2) RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello ' || p1;
END;
++
@delimiter ;++
@call ${returnValue||(null)||String||noshow dir=out}$ = HELLO('World');
@echo returnValue = ${returnValue}$;
The first @delimiter command sets the delimiter to ++, so that ; can be used in the function body. ++ ends the CREATE statement, and the second @delimiter command sets the delimiter back to ;.
Calling a function or procedure
Databases use different commands to call a procedure, for example EXECUTE in SQL Server and Oracle SQL*Plus, and CALL in PostgreSQL and MySQL. In DbVisualizer, you can use the @call client-side command for all databases. See Executing a Procedure or Function.