Skip to content

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.