Creating a dvd trigger using the jdbc thin driver

Question

I am currently setting up a test environment for an application. I use jUnit and Spring in my test environment. Before running the test, I want to configure the state of the test database environment. I have already written SQL scripts (schemas and data), and they work great in OracleDeveloper. When I tried to execute them using the jdbc driver with oracle, the execution will fail. It appears that the thin driver does not like to create trigger statements.

I read that instead of a thin driver I need to use the oci driver. The problem with the oci driver is that it is not platform independent and it takes time to configure it.

An 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 trigger statement, the execution fails, but I look like the first part of the request (CREATE TRIGGER "USER_ID_SEQ_INC" ... "USER" ... BEGIN ... FROM DUAL;) succeeds, but the trigger seems to be damaged if i try to use it. A runtime error is associated with the second part of the END instruction; "ORA-00900: invalid SQL statement."

Does anyone know a solution to this problem? I just want to create a trigger with a platform independent thin jdbc driver.

Hooray!

Kevin

Answer 1

Thank you guys for your answers. Now it works great. The reason was a syntax error or interpretation of my SQL code using Spring Framefork. When I execute the instructions directly using the execute jdbc method, it works, when I use Spring to execute the execution script, the execution fails. With oracle sql code this seems complicated because if i use hsqldb sql code it works fine.

test 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:

 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; 

---

It's fine! It is important to remove it ; at the end of statements, except for the trigger instruction !!!

 @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); } } 

Answer 2

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

Since you are using BEGIN ... END; make sure you really have one ; after END in SQL, which you send to the database.

If this is not the case, check out this article .

Answer 3

I know this is an old post, but here is my answer.

By default, the Spring "initialize-database" command splits the specified script with a semicolon: ";".

A trigger often has a semicolon inside a trigger, so queries are poorly split and executed.

The solution is to use another separator character (for example, "|") as follows:

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