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

Question

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?

Missing commit; maybe. In SQL Develeoper is AutoCommit on maybe. — Fabian Barney
– Fabian Barney, Commented Jun 8, 2011 at 21:05
I run the commit via the connection object after the update runs. I have also tried this with autocommit on and adding commit at the end of the block. — Sam
– Sam, Commented Jun 8, 2011 at 21:16
Never mind. I completely missread your SQL, deleting my answer. — Shannon Severance
– Shannon Severance, Commented Jun 8, 2011 at 21:45

Gary Myers · Accepted Answer · 2011-06-09 01:56:15Z

1

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.

answered Jun 9, 2011 at 1:56

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Samuel · Accepted Answer · 2011-06-09 05:12:40Z

0

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();

answered Jun 9, 2011 at 5:12

Samuel

2,5102 gold badges19 silver badges21 bronze badges

1 Comment

JavaRocky Over a year ago

cb.execute(); should be cb.executeQuery();, otherwise you'll get java.sql.SQLException: Incorrectly set or registered parameters.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related