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.