从 java 运行 Oracle sql 脚本给出 SQLSyntaxErrorException: ORA-00900: invalid SQL statement [英] Running Oracle sql script from java gives SQLSyntaxErrorException: ORA-00900: invalid SQL statement

问题描述

我正在使用 Oracle 11g，我正在通过 java 代码执行 Oracle sql 脚本.我的 SQL 脚本可能包含 SQL 语句(DDL 或 DML)或 PL/SQL 块，所以我不想在我的 java 代码中解析脚本，而是使用了 此解决方案 一次执行完整的脚本.以下是示例代码，其中 SQLExec 类在 ant jar 中.

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 solution to execute complete script at once. Following is the sample code, where SQLExec class is in ant jar.

此解决方案适用于大多数情况，但如果 sql 脚本包含 create or replace trigger，则它会因 java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement 而失败.我还指定了失败的 sql 脚本片段.

This solution worked for most cases except that if sql script contains create or replace trigger it fails with java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement. I have also specified snippet of sql script which fails.

请注意，如果我通过 SQL Developer 运行相同的脚本，它运行良好.

Please note that if I run same script through SQL Developer, it runs fine.

以下是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 脚本片段:

......
......
CREATE OR REPLACE TRIGGER MY_TRG
   BEFORE INSERT ON MY_TABLE
   FOR EACH ROW
   BEGIN
    :NEW.MYNUMBER := MY_SEQUENCENUM.NEXTVAL;
   END;

以下是异常跟踪:

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.access$000(SQLExec.java:627)
    at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:370)

推荐答案

正如@Reza Goodarzi 提到的，无效 SQL 语句 的原因是使用分号作为语句分隔符.所以为了解决我的问题，我用 slash(/) 作为分隔符分隔每个语句，并遵循我自己创建的这些规则:

As @Reza Goodarzi mentioned the cause of invalid SQL statement is 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:

1. 每个 SQL 语句(不是 PL/SQL 块的一部分)和 PL/SQL 块必须在新行中以斜线 (/) 结尾.

1. Each SQL statement (not part of PL/SQL block) and PL/SQL block must end with a forwarded slash (/) in a new line.

SQL 语句(不是 PL/SQL 块的一部分)不应以分号 (;) 结尾.我刚刚删除了语句末尾的分号.

SQL statement (not part of PL/SQL blocks) should not end with semicolon (;). I just removed semicolon from the end of statements.

对于 PL/SQL 块，不要从块末尾以及块中包含的任何语句中删除分号 (;).

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 脚本中进行这些更改，我执行(使用 jdbc)每个 PL/SQL 块和每个 SQL 语句(不是 PL/SQL 块的一部分)一次通过自己解析文件而不是使用 SQLExec 或任何其他外部 api/library.

And by making these changes in my SQL Scripts I executed (using jdbc) each PL/SQL block and each SQL statement (not part of PL/SQL block) at a time by parsing the file myself instead of using SQLExec or any other external api/library.

这篇关于从 java 运行 Oracle sql 脚本给出 SQLSyntaxErrorException: ORA-00900: invalid SQL statement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！