disabling triggers from java

Question

I am not entirely sure of why this is happening. When I run the command:

 ALTER TRIGGER ORIGDV87.UPDATE_TEAM_GROUP_TRIGGER DISABLE/ENABLE

I can see how it changes the trigger. I am trying to do this programatically from java:

private void trigger_alter(Connection con, String flag) throws Exception
{
    Query query = new Query(con);
    String selectSql = "select table_owner, trigger_name from user_triggers";
    Query queryTrg = new Query(con);
    query.executeQuery(selectSql);
     while(query.next()) {
        int i = 0;
        String table_owner = query.getColValue("table_owner");
        String trigger_name = query.getColValue("trigger_name");
        String triggerSql = "ALTER TRIGGER " + table_owner + "." + trigger_name + " "+ flag  ;
        System.out.println( triggerSql);
        queryTrg.executeQuery(triggerSql);
    }
    log_obj.FmtAndLogMsg("All triggers have been " + flag+ "D");
}

I get this exception: 2013/02/26 10:38:56.795 TableConverter: ERROR main() occurred: java.lang.Exception: Error executing query: ALTER TRIGGER XXXX.UPDATE_TEAM_GROUP_TRIGGER DISABLE error=java.sql.SQLException: ORA-01003: no statement parsed

It is an oracle database, and I am trying this on a Solaris box.

Answer 1

First, you are doing an executeQuery with a statement that is not a query. Try with execute instead.

Anyway, the ALTER sentence is part of the DDL (Data Definition Language) (SQL is for queries, DML -Data Manipulation Language- is for insert/update/delete). I am not sure how well DDL is supported in JDBC.

Answer 2

Replace

queryTrg.executeQuery(triggerSql);

With

con.createStatement().execute(triggerSql);

However, batching up the ALTER TRIGGER statements would be better:

private void trigger_alter(Connection con, String flag) throws Exception
{
    Query query = new Query(con);
    String selectSql = "select table_owner, trigger_name from user_triggers";

    Statement trgStmt = con.createStatement();

    query.executeQuery(selectSql);
     while(query.next()) {
        int i = 0;
        String table_owner = query.getColValue("table_owner");
        String trigger_name = query.getColValue("trigger_name");
        String triggerSql = "ALTER TRIGGER " + table_owner + "." + trigger_name + " "+ flag  ;
        System.out.println( triggerSql);
        trgStmt.addBatch(triggerSql);
    }

    trgStmt.executeBatch();
    log_obj.FmtAndLogMsg("All triggers have been " + flag+ "D");
}