Oracle jdbc creating a table, sequence and trigger

Question

I'm trying to create a table with a sequence and with a trigger for that sequence. When I do this within Oracle Express edition I have the following SQL statements. Which DO run correctly inside Oracle Express using the same user I log in with JDBC.

CREATE table "TEST" (
    "ID"         NUMBER(10) NOT NULL,
    "NAME"       VARCHAR2(20),
    constraint  "TEST_PK" primary key ("ID")
)


CREATE sequence "TEST_SEQ" 


CREATE trigger "BI_TEST"  
  before insert on "TEST"              
  for each row 
begin  
  if :NEW."ID" is null then
    select "TEST_SEQ".nextval into :NEW."ID" from dual;
  end if;
end;

So now what I do is put each of these into a List of Strings and execute 1 by one like this:

List<String> commands = new ArrayList<String>(4);
commands.add("Create table ...");
commands.add("Create sequence ...");
commands.add("Create trigger...");

st = con.createStatement();
for (String command : commands) {
    st.execute(command);
}

etc...

But I get an error with the trigger statement. "Error with Oracle command: ORA-00942: table or view does not exist"

It seems to me that Oracle has not yet seen the new table at this point. How can I correct this? Should I create multiple statement objects or try committing in-between each command?

TIA

Answer 1

It seems that an error in invoking method execute. Another method Statement#executeUpdate should be used for DDL queries. See official javadoc.

Update: Have you tried to use executeBatch method? I'm trying to execute batch using spring jdbc:

getJdbcTemplate().batchUpdate(new String[] { createTable, createTrigger, insert });

it works fine for me.