Splitting SQL statements using the FME_SQL_DELIMITER directive

Liz Sanderson
Liz Sanderson
  • Updated


The SQLCreator/SQLExecutor transformer will not run a stored procedure or multiple SQL statements against a database. The statements will run from both SQL+ and SQL Developer. If a statement fails text will appear in the log file prompting users to enter a delimiter.

Or executing a SQL Query with a trailing ";" in the Oracle reader or other FME transformer returns an error:

error was `ORA-00911: invalid character

*You may also receive other ora-* errors or other errors depending on the database.


By default FME expects a single SQL Statement and expects that a single SQL Statement will not end with the usual  ";" delimiter. This also implies that, by default, FME will not recognize multiple SQL statements. This can be a problem if you want to include multiple SQL statements in one query. And it also can be a problem if you have cut and pasted the SQL Statement from some other tool such that it includes the ";"


When entering a SQL query in the SQLExecutor/SQLCreator or in the Reader/Writer BEGIN/END SQL statement parameters obey the following rules:

1) Remove the ';' or other special characters for single-line SQL statements.

2) At the top of the SQL Editor dialog box use the following statement:




Any character can be used as a delimiter, usually you will want to split by ‘;’. Sometimes you may wish to enter a character that does not appear within the SQL and then integrate the character into the statement so you can have exact control on splitting the statement.


Multiple SQL statements - use a delimiter of ';'

-TRUNCATE TABLE dbo.[mytable];
INSERT INTO dbo.[CellSignalsTable2]

Separate procedures using '/', or any other character

DECLARE howmany number;    
   SELECT COUNT(*) INTO howmany FROM states;    

More documentation on using SQL in FME can be found here.

Was this article helpful?



Please sign in to leave a comment.