使用瘦 jdbc 驱动程序创建一个 oracle db 触发器

Create a oracle db trigger using thin jdbc driver

提问by eglobetrotter

currently I setting up a test environment for an application. I'm using jUnit and Spring in my test environment. Before a test execution I want to set up a database test environment state. I already has written the SQL scripts (schema and data) and they runs fine in Oracles SQLDeveloper. As I tried to execute them by using the oracle thin jdbc driver, the execution fails. It looks like that the thin driver doesn't like create trigger statements.

目前我为应用程序设置了一个测试环境。我在我的测试环境中使用 jUnit 和 Spring。在测试执行之前，我想设置一个数据库测试环境状态。我已经编写了 SQL 脚本（架构和数据）并且它们在 Oracles SQLDeveloper 中运行良好。当我尝试使用 oracle 瘦 jdbc 驱动程序执行它们时，执行失败。看起来瘦驱动程序不喜欢创建触发器语句。

I read that I have to use an oci driver instead of thin driver. The problem with the oci driver is that it is not platform independent and it takes time to set it up.

我读到我必须使用 oci 驱动程序而不是瘦驱动程序。oci 驱动程序的问题在于它不是独立于平台的，并且设置它需要时间。

Example of my code:

我的代码示例：

CREATE TABLE "USER"
  (
    USER_ID          NUMBER(10) NOT NULL,
    CREATOR_USER_FK  NUMBER(10) NOT NULL,
    ...
    PRIMARY KEY (USER_ID)
  );
CREATE SEQUENCE SEQ_USER START WITH 1 INCREMENT BY 1;
CREATE TRIGGER "USER_ID_SEQ_INC" BEFORE
  INSERT ON "USER" FOR EACH ROW BEGIN
  SELECT SEQ_USER.nextval
  INTO :new.USER_ID
  FROM DUAL;
END;

If I execute the the trigger statement the execution fails, but I looks like that the first part of the query (CREATE TRIGGER "USER_ID_SEQ_INC" ... "USER" ... BEGIN ... FROM DUAL;) is executed successfully, but the trigger seems to be corrupt if I try to use it. The execution fail error comes with the second part of the statement END; "ORA-00900: invalid SQL statement".

如果我执行触发器语句，则执行失败，但我看起来查询的第一部分 (CREATE TRIGGER "USER_ID_SEQ_INC" ... "USER" ... BEGIN ... FROM DUAL;) 已成功执行，但是如果我尝试使用它，触发器似乎已损坏。执行失败错误伴随语句 END 的第二部分；“ORA-00900：无效的 SQL 语句”。

Do anyone know a solution for that problem? I just want to create a trigger with platform independent thin jdbc driver.

有谁知道该问题的解决方案？我只想使用独立于平台的瘦 jdbc 驱动程序创建触发器。

Cheers!

干杯!

Kevin

凯文

采纳答案by eglobetrotter

Thank you guys for your answers, It works fine now. The reason was a syntax mistake or the interpretation of my SQL code file with Spring Framefork. When I execute the statements directly by using the execute method of jdbc it works, when I use the Spring functionality for script execution the execution fails. With oracle sql code it seems to be tricky, because if I use hsqldb sql code it works fine.

谢谢大家的回答，现在可以正常使用了。原因是语法错误或我使用 Spring Framefork 对 SQL 代码文件的解释。当我使用 jdbc 的 execute 方法直接执行语句时，它可以工作，当我使用 Spring 功能执行脚本时，执行失败。使用 oracle sql 代码似乎很棘手，因为如果我使用 hsqldb sql 代码它工作正常。

test-condext.xml:

测试condext.xml：

...
<jdbc:initialize-database data-source="dataSource"
    ignore-failures="DROPS" enabled="${jdbc.enableSqlScripts}">
    <jdbc:script location="${jdbc.initLocation}" />
    <jdbc:script location="${jdbc.dataLocation}" />
</jdbc:initialize-database>
...

---

---

schema.sql:

模式.sql：

DROP SEQUENCE SEQ_USER;
DROP TABLE "USER" CASCADE CONSTRAINTS;
PURGE TABLE "USER";
CREATE TABLE "USER"
  (
    USER_ID          NUMBER(10) NOT NULL,
    CREATOR_USER_FK  NUMBER(10) NOT NULL,
    PRIMARY KEY (USER_ID)
  );
ALTER TABLE "USER" ADD CONSTRAINT FK_USER_CUSER FOREIGN KEY (CREATOR_USER_FK) REFERENCES "USER" (USER_ID);
CREATE SEQUENCE SEQ_USER START WITH 1 INCREMENT BY 1;
CREATE TRIGGER "USER_ID_SEQ_INC" BEFORE
  INSERT ON "USER" FOR EACH ROW
  WHEN (new.USER_ID IS NULL)
BEGIN
  SELECT SEQ_USER.nextval
  INTO :new.USER_ID
  FROM DUAL;
END;
/
ALTER TRIGGER "USER_ID_SEQ_INC" ENABLE;

---

---

This works fine! Its important to remove ;at the end of statements excepts the trigger statement!!!

这很好用！;除触发器语句外，删除语句末尾很重要！！！

@Before
public void executeSomeSql() {
    Connection c;
    try {
        c = dataSource.getConnection();
        c.createStatement()
                .execute("CREATE TABLE \"USER\" (USER_ID NUMBER(10) NOT NULL, CREATOR_USER_FK NUMBER(10) NOT NULL, PRIMARY KEY (USER_ID))");
        c.createStatement()
                .execute("CREATE SEQUENCE SEQ_USER START WITH 1 INCREMENT BY 1");
        c.createStatement()
                .execute("CREATE OR REPLACE TRIGGER \"USER_ID_SEQ_INC\" BEFORE INSERT ON \"USER\" FOR EACH ROW WHEN (new.USER_ID IS NULL) BEGIN SELECT SEQ_USER.nextval INTO :new.USER_ID FROM DUAL; END;");
    } catch (SQLException e) {
        logger.debug(e);
    }
}

回答by xfr

I know this is a old post but here's my answer.

我知道这是一个旧帖子，但这是我的答案。

By default, Spring "initialize-database" instruction split the specified script by using the semicolon character : ";".

默认情况下，Spring 的“初始化数据库”指令使用分号字符：“;”分割指定的脚本。

In a trigger, there often is a semicolon inside the trigger, thus the queries are badly splitted and executed.

在触发器中，触发器内部通常有一个分号，因此查询被严重拆分和执行。

The solution is to use another split character ("|" for example) like this :

解决方案是使用另一个拆分字符（例如“|”），如下所示：

<jdbc:initialize-database>
    <jdbc:script location="classpath:myscript.sql" separator="|"/>
</jdbc:initialize-database>

回答by Aaron Digulla

Creating triggers works with any type of JDBC driver; there must be something wrong with the SQL syntax -- which is odd because Oracle should report that when you run the CREATE TRIGGER(not when you use it the first time).

创建触发器适用于任何类型的 JDBC 驱动程序；SQL 语法一定有问题——这很奇怪，因为 Oracle 应该在您运行时报告CREATE TRIGGER（而不是在您第一次使用它时）。

Since you use BEGIN ... END;make sure that you really have a ;after ENDin the SQL which you send to the DB.

由于您使用BEGIN ... END;确保您在发送到数据库的 SQL 中确实有一个;after END。

If that isn't the cause, check this article.

如果这不是原因，请查看这篇文章。