How to execute an Oracle PLSQL block in Java

Question

I have an PL/SQL block like this:

BEGIN
  FOR i IN 1..100
  LOOP
    UPDATE rptbody 
       SET comments = 'abcs';
     WHERE (rptno> 100 and rptno < 200) and ROWNUM<2;
    COMMIT;
  END LOOP;
END;

This block needs to be executed using Oracle JDBC. I have tried the following methods:

• Tried to execute this using Statement object. Since this is a block, an exception was raised saying that this is not an sql statement

• This can be split up into sql statements, but I have 100s of such blocks which would be cumbersome for the code and thought of leaving this to the sqlplus.

• Tried with CallableStatement which did not work as well.

Any solutions would be helpful.

Answer 1

This has nothing to do with how you run it. The PL/SQL syntax is invalid. You have a ; after the update clause right before the WHERE clause:

BEGIN
  FOR i IN 1..100
  LOOP
    UPDATE rptbody 
       SET comments = 'abcs' --<<< no semicolon here!!
     WHERE (rptno> 100 and rptno < 200) and ROWNUM<2;
    COMMIT;
  END LOOP;
END;

The above code can be run like this:

String sql = "... the PL/SQL block ...";
Statement stmt = connection.createStatement();
stmt.execute(sql);