CODEWITHSUNDEEP
javasqloracle-databasejdbcplsql
user3409057
user3409057

Reputation:

How to execute an Oracle PLSQL block in Java

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:

Any solutions would be helpful.

Upvotes: 3

Views: 6878

Answers (3)

Nirmala
Nirmala

Reputation: 1338

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

Upvotes: 1

user3409057
user3409057

Reputation:

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?

Upvotes: 0

user330315
user330315

Reputation:

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

Upvotes: 7

Related Questions