Creating triggers over JDBC (oracle)

Question

Does anyone know how to get triggers created over JDBC. It appears that the problem is to do with the semicolon. Any feedback much appreciated.

The following SQL works when run on the database, but not when run using the following Java code:

Connection c=null;
    Statement s=null;
    try {
        c=dataSource.getConnection();
        s=c.createStatement();
        s.executeUpdate("create or replace trigger startuptrigger after insert on startuptest for each row begin insert into startuptest values(99); end");
        s.close();
        s=null;
        c.close();
        c=null;
    } catch(SQLException e) {
        if(s!=null) { try { s.close(); } catch(Exception f){} }
        if(c!=null) { try { c.close(); } catch(Exception f){} }
        throw new IOException(e.toString());
    }

I have tried s.execute(...) and s.executeUpdate(...) and it makes no difference. I am using the ojdbc5.jar driver. Oracle returns the error:

ORA-04098: trigger 'POLICYUAT.STARTUPTRIGGER' is invalid and failed re-validation

Answer 1

I tried the above code and it is working fine in creating the trigger. So the reason why the above does not work could be:

1. The account with which you are connecting does not have rights to create triggers: Try to connect directly and check if you have rights.
2. Manually execute the above trigger in sqlplus and check if your table exists and you have rights to insert in it.

Answer 2

Maybe this can help: create trigger and colon character over JDBC

Answer 3

The PL/SQL syntax requires a semi-colon at the end of the end at the end. That is the string should be as follows

"create or replace trigger startuptrigger after insert on startuptest for each row begin insert into startuptest values(99); end;"