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. As the 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;
/