Anonymous block called via JDBC executes without error but does not run

Probably missing something trivial here but I have a function in my Java application running on Oracle 10g which generates a bunch of insert and delete statements into a BEGIN...; END; block.

When I execute the statement, it runs without error yet the values are not inserted/deleted to the database. If I copy the SQL that is to be run into SQL developer and execute it, it works as expected.

Example SQL...

BEGIN
  INSERT INTO tablea_archive (col1,col2,col3)
  SELECT col1,col2,col3 FROM tablea;
  DELETE FROM tablea;
  INSERT INTO tableb_archive (col1,col2,col3)
  SELECT col1,col2,col3 FROM tableb;
  DELETE FROM tableb;
END;

I have tried running the code via prepared,callabale and normal Statements with execute(开发者_运维问答) and executeUpdate() and no joy.

Can anyone point out what I'm doing wrong?

I'd try something like

BEGIN
  INSERT INTO tablea_archive (col1,col2,col3)
  SELECT col1,col2,col3 FROM tablea;
  RAISE_APPLICATION_ERROR(-20001,'Inserted '||sql%rowcount||' rows');
END;

Your error handling should give you some form of message saying how many rows the INSERT though were being inserted. If you don't have error logging, look at recording errors on the database end

I'd suspect either the wrong database or wrong schema.

This code snippet works. May be it can help you:

String  plsql = "BEGIN :myresult := dbms_random.random ; END;";
OracleDriver oracledrv = new OracleDriver();
Connection   con = oracledrv.connect(connstr, new Properties());

for (int i = 0 ; i < 1000 ; i++ ) {
    CallableStatement cb = con.prepareCall(plsql);
    cb.registerOutParameter("myresult", Types.INTEGER);
    cb.execute();
    System.out.println("random ->" +cb.getInt("myresult"));
    cb.close();
}
con.close();