span8
span4
TheSQLCreator/SQLExecutortransformer will not run a stored procedure or multiple SQL statements against a database.The statements will run from both SQL+ and SQL Developer.如果语句失败,日志文件中将显示文本,提示用户输入分隔符。
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
默认情况下,FME不会拆分SQL语句,并且期望单个SQL语句不会以“;”分隔符结尾。如果希望在一个查询中包含多个SQL语句,则这可能是一个问题。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 ";"
在中输入SQL查询时,SQLExecutor/SQLCreatoror in the Reader/Writer BEGIN/END SQL statement parameters obey the following rules:
1)删除用于单行SQL语句的“;”或其他特殊字符。
2) At the top of the SQL Editor dialog box use the following statement:
FME_SQL_DELIMITER
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 they can have exact control on splitting the statement.
e.g:
FME_SQL_DELIMITER /DECLARE howmany number;BEGIN SELECT COUNT(*) INTO howmany FROM states;END;/
谢谢你,史蒂夫,但你为什么要用两次开始?
FME_SQL_DELIMITER $DECLARE howmany number := 0;BEGIN SELECT COUNT(*) into howmany FROM tablename;dbms-output.put-line(多少个);异常——异常处理程序在其他处理程序开始时开始——处理所有其他错误dbms_output.put_line(“发生了其他类型的错误”);结束;-- exception handlers and block end here$
I declare howmany in "Atrributes to expose" but nothing is out of the SQL executor...
Hi@julien,Thanks for the comment!
I think when this article was introduced - quite some time ago - FME may have dealt with the SQL code a little differently,but I can't be certain.第二个街区没有意义。In fact there's another issue with this code too...
未正确设置故障模式与后果分析交付器…(it should be "//www.baooytra.com/knowledge/") and the "//www.baooytra.com/knowledge/" is missing at the end.
不正确的:
FME_SQL_DELIMITER ;BEGIN DECLARE howmany number;BEGIN SELECT COUNT(*) INTO howmany FROM states;END;END;
CORRECT:
FME_SQL_DELIMITER / DECLARE howmany number;BEGIN SELECT COUNT(*) INTO howmany FROM states;END;/
我将对这篇文章做一些调整以反映这一点。Thanks for pointing this out!
嗨,史提夫,Does this mean we can run a plsql block from the sql creator and get values returned from database.If so can we see the results using "Atrributes to expose ".I am trying to executing a plsql block from sql creator and I want some variables back from database.It successfully executes a plsql block but can't find any results.
The answer is yes,but it is a little tricky,what you have to do is
Note the variables are visible from the current session,meaning you can set the value in one SQLExecuter and access it from another.Also on FMEServer we can run multiple workspaces at the same time,that will have their own version of the variable
I don't have a code example right now,but I will see if I can create something
IF you need to run a stored procedure in Oracle...
fme_sql_delimiter/begin owner.truncate_table('myFavorite_table');end;/
In the example above,DBA不允许通过FME直接截断表语句…but would allow the user to call a stored procedure to do the same.Perhaps the DBA wants to track all table truncates,and may record certain information about the transaction,可以在执行截断时在存储过程中执行。
Performing native spatial queries on database tables using the SQLExecutor
Using the SQLExecutor or SQLCreator to issue commands to a database
Using the SQLExecutor to do a SQL Join
Executing a Stored Procedure on Microsoft SQL Server with FME
The SQLCreator and SQLExecutor Transformers
如何使用本机空间数据库SQL命令在sqlexecutor/sqlcreator中执行空间查询
© 2019 亚搏在线Safe Software Inc |Legal