从 java 运行 Oracle sql 脚本给出 SQLSyntaxErrorException: ORA-00900: invalid SQL statement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20705807/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running Oracle sql script from java gives SQLSyntaxErrorException: ORA-00900: invalid SQL statement
提问by Kuldeep Jain
I am using Oracle 11g, I am executing Oracle sql script through java code. My SQL script may contain SQL statements(DDL or DML) or PL/SQL blocks, so I don't want to parse the script in my java code but used This solutionto execute complete script at once. Following is the sample code, where SQLExecclass is in ant jar.
我正在使用 Oracle 11g，我正在通过 java 代码执行 Oracle sql 脚本。我的 SQL 脚本可能包含 SQL 语句（DDL 或 DML）或 PL/SQL 块，所以我不想在我的 java 代码中解析脚本，而是使用此解决方案一次执行完整的脚本。以下是示例代码，其中SQLExec类位于ant jar.
This solution worked for most cases except that if sql script contains create or replace triggerit fails with java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement. I have also specified snippet of sql script which fails.
此解决方案适用于大多数情况，除非 sql 脚本包含create or replace trigger它失败并出现java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement。我还指定了失败的 sql 脚本片段。
Please note that if I run same script through SQL Developer, it runs fine.
请注意，如果我通过 SQL Developer 运行相同的脚本，它运行良好。
Following is the Java code:
以下是Java代码：
private void executeSql(String sqlFilePath) {
final class SqlExecuter extends SQLExec {
public SqlExecuter() {
Project project = new Project();
project.init();
setProject(project);
setTaskType("sql");
setTaskName("sql");
}
}
SqlExecuter executer = new SqlExecuter();
executer.setSrc(new File(sqlFilePath));
executer.setDriver(args.getDriver());
executer.setPassword(args.getPwd());
executer.setUserid(args.getUser());
executer.setUrl(args.getUrl());
executer.execute();
}
SQL Script snippet:
SQL 脚本片段：
......
......
CREATE OR REPLACE TRIGGER MY_TRG
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
BEGIN
:NEW.MYNUMBER := MY_SEQUENCENUM.NEXTVAL;
END;
Following is the Exception trace:
以下是异常跟踪：
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:398)
at com.kuldeep.OracleConnectionTest.executeSql(OracleConnectionTest.java:160)
at com.kuldeep.OracleConnectionTest.main(OracleConnectionTest.java:25)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
at org.apache.tools.ant.taskdefs.SQLExec.execSQL(SQLExec.java:499)
at org.apache.tools.ant.taskdefs.SQLExec.runStatements(SQLExec.java:470)
at org.apache.tools.ant.taskdefs.SQLExec$Transaction.runTransaction(SQLExec.java:664)
at org.apache.tools.ant.taskdefs.SQLExec$Transaction.accesscreate or replace trigger MY_TRG
BEFORE insert MY_TABLE
for each row
begin
if (:new.MYNUMBER is null) then
select MY_SEQUENCENUM.nextval
into :new.MYNUMBER
from DUAL;
end if;
end;
/
0(SQLExec.java:627)
at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:370)
采纳答案by Kuldeep Jain
As @Reza Goodarzi mentioned the cause of invalid SQL statementis semicolon being used as the statement separator. So to solve my issue I am separating each statement with slash(/)as delimiter and followed these rules which I created myself:
正如@Reza Goodarzi 提到的，无效 SQL 语句的原因是使用分号作为语句分隔符。因此，为了解决我的问题，我将每个语句与slash(/)分隔符分开，并遵循我自己创建的这些规则：
Each SQL statement (not part of PL/SQL block) and PL/SQL block must end with a forwarded slash (/) in a new line.
SQL statement (not part of PL/SQL blocks) should not end with semicolon (;). I just removed semicolon from the end of statements.
For PL/SQL block do not remove the semicolon(;) from end of the block as well as from any statement contained within the block.
每个 SQL 语句（不是 PL/SQL 块的一部分）和 PL/SQL 块必须在新行中以斜线 (/) 结尾。
SQL 语句（不是 PL/SQL 块的一部分）不应以分号 ( ;)结尾。我刚刚从语句的末尾删除了分号。
对于 PL/SQL 块，不要从块的末尾以及块中包含的任何语句中删除分号 ( ;)。
And by making these changes in my SQL Scripts I executed (using jdbc) each PL/SQL blockand each SQL statement (not part of PL/SQL block)at a time by parsing the file myself instead of using SQLExecor any other external api/library.
通过在我的 SQL 脚本中进行这些更改，我通过自己解析文件而不是使用SQLExec或任何其他外部 api一次执行（使用 jdbc）每个PL/SQL 块和每个SQL 语句（不是 PL/SQL 块的一部分）/图书馆。
回答by Andrew Paes
I think you need to change your trigger to set your new ID
我认为您需要更改触发器以设置新 ID
create or replace trigger TG_BIU_TABLE1
before insert or update on TABLE1
for each row
begin
if (:new.ID1 is null) then
select SQ_TABLE1.nextval
into :new.ID1
from DUAL
end if
end
/
or this:
或这个：
......
......
DELIMITER $$
CREATE OR REPLACE TRIGGER MY_TRG
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
BEGIN
:NEW.MYNUMBER := MY_SEQUENCENUM.NEXTVAL;
END; $$
......
回答by RGO
In the documentationit says:
在文档中它说：
Multiple statements can be provided, separated by semicolons (or the defined delimiter).
可以提供多个语句，用分号（或定义的分隔符）分隔。
Therefore, using the semicolon character (;) as the default delimiter, SQLEXECinterprets the CREATE TRIGGERstatement of your script as two statements, giving this error message as the result.
因此，使用分号字符 (;) 作为默认分隔符，将脚本语句SQLEXEC解释CREATE TRIGGER为两个语句，结果给出此错误消息。
回答by Katesclau
You can also add a delimiter in the execute statement, as so:
您还可以在执行语句中添加分隔符，如下所示：
##代码##I also left the final part of the script just for the triggers and procedures, as the delimiter is used onward. That did the trick for me. Courtesy of SQL Developer′s Migration tool.
我还为触发器和过程留下了脚本的最后一部分，因为分隔符是向前使用的。这对我有用。由 SQL Developer 的迁移工具提供。
