3

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.

Improve this question
6
  • You are right in your point, this is an anonymous PL/SQL block not SQL Commented Sep 16, 2015 at 17:29
  • Can you post the Java code you're using (particularly for the CallableStatement version)? And post the exact error stack you're getting. Commented Sep 16, 2015 at 17:29
  • In conjunction with what Justin mentioned, you might find this helpful to get the errors fixed: stackoverflow.com/a/10465239/350136 Commented Sep 16, 2015 at 17:31
  • You can run this through a Statement object, you have to use Statement.execute() though. You only need a CallableStatement if oyu need to pass parameters to the PL/SQL block. And you need to post your Java code. "did not work" is neither an acceptable error description nor a valid Java or Oracle exception Commented Sep 16, 2015 at 17:33
  • 2
    Btw: I just hope your example is not the real code you are running, because that can be achieved much more efficiently without a PL/SQL block using a single statement - rather than the slow-by-slow approach through a cursor. Commented Sep 16, 2015 at 17:34

3 Answers 3

Reset to default
7

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Check out some code samples to use CallableStatement and PreparedStatement on Github

Improve this answer

Comments

0

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

Since you were trying to execute a plsql block, you should not use Statement object.

From https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html: The object used for executing a static SQL statement and returning the results it produces.

This is the way you need to execute a block:

CallableStatement anonBlock = null // Note that CallableStatement is an interface

String anonBlockString = '' //Generally multi line
// Set in and out parameters as needed by your anonBlockString

anonBlock.registerOutParameter( <number> , <type> )

...

// executeUpdate() is inherited from PreparedStatement and can be used for executing DML statements (update, insert, delete)
anonBlock.executeUpdate(); 


To access out parameters:
anonBlock.getString(<number which you have assigned in registerOutParameter() calls);

For complete example: (https://download.oracle.com/otn_hosted_doc/timesten/1121/quickstart/sample_code/jdbc/plsqlJDBC.java)

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.

Prefer to use stored procedures instead of anonymous blocks. Since stored procedures are stored in a compiled and optimized format, they have a performance boost compared to anonymous ones

Tried with CallableStatement which did not work as well:

What was the code, error/stack?

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.