SQL Workbench/J will split statements based on the SQL terminator
; and send each statement unaltered to the DBMS.
When executing statements such as
CREATE PROCEDURE which in turn contain valid SQL statements, delimited with a
the SQL Workbench/J will send everything up to the first semicolon to the backend (because the
; terminates the SQL statement)
In case of a
CREATE PROCEDURE statement this will obviously result in an error as the statement is not complete.
To be able to run DDL statements with embedded
; characters, SQL Workbench/J needs to
know where a statements ends. To specify the end of a statement with embedded
; the so called
"alternate delimiter" is used. This chapter describes how the alternate delimiter is used by SQL Workbench/J
The body of a function in Postgres is a character literal. Because a delimiter inside a character literal does not define the end of the statement, no special treatment is needed for Postgres.
This is an example of a
CREATE PROCEDURE which will
NOT work due to the embedded semicolon in
the procedure source itself.
CREATE OR REPLACE FUNCTION proc_sample RETURN INTEGER IS l_result INTEGER; BEGIN SELECT max(col1) INTO l_result FROM sometable; RETURN l_result; END;
When executing this script, Oracle would return an error because SQL Workbench/J will
send everything up to the keyword
INTEGER to the database. Obviously that
fragment would not be correct.
The solution is to terminate the script with a character sequence that is called the "alternate delimiter"
which can be defined in the connection profile. To be compatible with SQL Developer and SQL*Plus it is recommended to set the
alternate delimiter to a forward slash (
The script needs to be written like this:
CREATE OR REPLACE FUNCTION proc_sample RETURN INTEGER IS l_result INTEGER; BEGIN SELECT max(col1) INTO l_result FROM sometable; RETURN l_result; END; /
Note the trailing forward slash (
/) at the end in order to "turn on" the
use of the alternate delimiter. If you run scripts with embedded semicolons and you get
an error, please verify the setting for your alternate delimiter.
The standard delimiter (the semicolon) and the alternate delimiter can be mixed in a single script. Whenever a PL/SQL block (either a stored procedure or an anonymous block) is encountered, SQL Workbench/J expects the alternated delimiter to terminate that block. This follows the same rules as used in SQL*Plus.
The following script will therefore work when connected to an Oracle database:
drop table sometable cascade constraints; create table sometable ( col1 integer not null ); create or replace function proc_sample return integer is l_result integer; begin select max(col1) into l_result from sometable; return l_result; end; /
When is the alternate delimiter used?
For all other DBMS, the use of the alternate delimiter is defined by the last delimiter used in the script.
As soon as the statement (or script) that you execute ends with the alternate delimiter, the alternate delimiter is used to separate all SQL statements. When you execute selected text from the editor, be sure to select the alternate delimiter as well, otherwise it will not be recognized (if the alternate delimiter is not selected, the statement to be executed does not end with the alternate delimiter).
This means a script must use the alternate delimiter for all statements in the
script. The following script will not work, because the last statement is terminated with the alternate
delimiter and thus SQL Workbench/J assumes all statements are delimited with that.
CREATE TABLES statements are delimited with the standard delimiter, they are
not recognized as a separate statement and thus the script is sent as a single statement to the server.
create table orders ( order_id integer not null primary key, customer_id integer not null, product_id integer not null, pieces integer not null, order_date date not null ); create table orders_audit_log ( order_id integer not null, delete_date timestamp not null ); create trigger orders_audit_log for orders before delete as begin insert into audit_log (id, delete_date) values (old.order_id, current_timestamp); end; /
The solution is to terminate every statement with the alternate delimiter:
create table orders ( order_id integer not null primary key, customer_id integer not null, product_id integer not null, pieces integer not null, order_date date not null ) / create table orders_audit_log ( order_id integer not null, delete_date timestamp not null ) / create trigger orders_audit_log for orders before delete as begin insert into audit_log (id, delete_date) values (old.order_id, current_timestamp); end; /