Splitting SQL statements using the FME_SQL_DELIMITER directive

Symptom

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.
 

Cause

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 ";"
 

Resolution

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:

	FME_SQL_DELIMITER <VALUE>

e.g.

	FME_SQL_DELIMITER ;

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.
 

Examples:

Multiple SQL statements - use a delimiter of ';'

FME_SQL_DELIMITER ;
-TRUNCATE TABLE dbo.[mytable];
INSERT INTO dbo.[CellSignalsTable2]
    ([Code],[ID],[num_measures])
VALUES
    ('ABC12345',7654,6);

Separate procedures using '/', or any other character

FME_SQL_DELIMITER /
DECLARE howmany number;    
BEGIN        
   SELECT COUNT(*) INTO howmany FROM states;    
END;
/

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