How to execute anonymous PL/SQL from Java using jdbctemplate

Question

I want to call this query (which works when run on SQL developer) from Java

DECLARE
    TYPE my_id_tab IS
        TABLE OF my_table.my_id%TYPE;
    my_ids  my_id_tab;
BEGIN
    UPDATE my_table
        SET
            another_id = NULL
    WHERE
        another_id IS NULL
        AND   create_datetime BETWEEN '03-JUN-19' AND '05-JUN-19'
    RETURNING my_id BULK COLLECT INTO my_ids;

    COMMIT;
END;

But I believe Java is having a tough time trying to figure out that I want the collection of my_ids returned to me.

Here's what I tried so far with exception messages like java.sql.SQLException: Invalid column index or java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!

final Connection connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource());

      try (final CallableStatement callableStatement = connection.prepareCall(TEST_SQL))
      {
         callableStatement.registerOutParameter("my_ids", Types.ARRAY);
         callableStatement.executeUpdate();
         int[] arr = (int[]) callableStatement.getArray("my_ids").getArray();
         return Arrays.stream(arr).boxed().collect(Collectors.toSet());
      }
      catch (final SQLException e)
      {
         LOG.info("threw exception, {}", e);
      }
      finally
      {
         DataSourceUtils.releaseConnection(connection, jdbcTemplate.getDataSource());
      }

What is "TEST_SQL"? your pl/sql code is a anonymous block. you should make it a procedure and call that. mabye this helps - oracle-base.com/articles/misc/dml-returning-into-clause — OldProgrammer
– OldProgrammer, Commented Jun 10, 2019 at 16:00
@OldProgrammer TEST_SQL is the PL/SQL that's posted in the first code block. — surprised_ferret
– surprised_ferret, Commented Jun 10, 2019 at 16:02
If TEST_SQL is indeed the exact PL/SQL in the first code block then it won't work--there are no bind variables in that block and it's not a function. — Tad Harrison
– Tad Harrison, Commented Jun 10, 2019 at 18:05
@TadHarrison so if i transform that TEST_SQL into a function AND make my_ids a bind variable, it will work? — surprised_ferret
– surprised_ferret, Commented Jun 10, 2019 at 18:35
What version of Oracle are you using? You are going to run into difficulties in getting the array out of the PL/SQL into Java, so knowing the DB version will help. — Tad Harrison
– Tad Harrison, Commented Jun 10, 2019 at 20:23

Tad Harrison · Accepted Answer · 2019-06-11 12:47:20Z

2

It's not the simplest thing, but it's pretty easy to do. You will need to create a TYPE in Oracle to define the results.

For this demo, create and populate EMP and DEPT: EMP and DEPT script

Create the TYPE, needed to define the array that will be returned:

create type t_integer_array as table of integer;

We will be running the following UPDATE, which will update only a few rows:

UPDATE emp
   SET job = job        -- a nonsense update
 WHERE comm IS NOT NULL -- only affect some of the rows

Here is the Java:

package test;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import java.util.Arrays;

public class OracleTest {

  public static void main(String[] args) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Connection conn = DriverManager.getConnection(
              "<your JDBC url>", "<your user>", "<your password>");

      // Prepare the call, without defining the the output variable
      // in a DECLARE section of the PL/SQL itself, you just need to
      // include a "?" and then associate the correct type in the
      // next step.
      CallableStatement cs = conn.prepareCall(
                "BEGIN\n"
              + "  UPDATE emp\n"
              + "     SET job = job\n"
              + "   WHERE comm is not null\n"
              + "  RETURNING empno BULK COLLECT INTO ?;\n"
              + "END;");

      // Register the single OUT parameter as an array, using the 
      // type that was defined in the database, T_INTEGER_ARRAY.
      cs.registerOutParameter(1, Types.ARRAY, "T_INTEGER_ARRAY");
      cs.execute();

      // Now get the array back, as array of BigDecimal.
      // BigDecimal is used because it doesn't have precision 
      // problems like floating point, it will contain all digits
      // that the database provided.
      BigDecimal[] nums = (BigDecimal[]) (cs.getArray(1).getArray());
      System.out.println(Arrays.toString(nums));
      cs.close();
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
}

And here's my output:

[7499, 7521, 7654, 7844]

These are the technical keys (empno) for only the rows that were affected by the update.

answered Jun 11, 2019 at 12:47

Tad Harrison

1,2937 silver badges9 bronze badges

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

2 Comments

surprised_ferret Over a year ago

thank you so much! I tried going another route by using a cursor and doing open my_refcursor for select * from table (my_ids) but it still required the create type outside of the actual query. i will work with this and see what i can do! thank you!

Tad Harrison Over a year ago

Watch out for the cursor reference approach. I believe the DBMS_SQL functionality for returning cursors is only available in Oracle 12c, which is why I asked.

Collectives™ on Stack Overflow

How to execute anonymous PL/SQL from Java using jdbctemplate

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related