how to call oracle stored procedure in jpa2.0 which returns integer as out variable

Question

I have written one stored procedure which will return integer value. but am not able to get the count in jpa. can any one suggest me the way to call stored procedure and get the return out value into some variable in java using jpa.

int count = 0;
String id = "m123"

count = getEm().createNativeQuery("call sample_procedure(?,?)")
                    .setParameter(1, id)
                    .setParameter(2, count)
                    .executeUpdate();

stored procedure: I have some logic in procedure and inside loop I was incrementing the count.

    create or replace
PROCEDURE sample_procedure(
    id IN VARCHAR,
    count OUT NUMBER)
IS
  ........
BEGIN
.....
 LOOP
---------
 count := count + 1;

Sylvain Leroux · Answer 1 · 2014-11-29T17:09:41.493

1

I have some logic in procedure and inside loop I was incrementing the count.

First, in you given use case, count has to be declared INOUT (both input and output).

PROCEDURE sample_procedure(
    id IN VARCHAR,
    count INOUT NUMBER)

Then:

For JPA < 2.1 don't have support for getting back values from OUT or INOUT parameters. So, you are probably stuck if you need to stay with that version.

JPA >= 2.1 has explicit support for calling stored procedure using the EntityManager.html.createStoredProcedureQuery method:

count = getEm().createStoredProcedureQuery("sample_procedure")
               .registerStoredProcedureParameter(1, String.class, ParameterMode.IN)
               .registerStoredProcedureParameter(2, Integer.class, ParameterMode.INOUT)
               .setParameter(1, id)
               .setParameter(2, count);

storedProcedure.execute();

int count = (Integer)storedProcedure.getOutputParameterValue(2);

_{Untested. Beware of typos !}

edited Nov 29 '14 at 17:09

answered Nov 27 '14 at 07:12

Sylvain Leroux

50,096
7
103
125

thanks and I tried the first way(JPA<2.1) but its throwing exception "Exception data: java.lang.IllegalArgumentException: Parameter – Madhu sudhan Reddy Nov 27 '14 at 09:52
@MadhusudhanReddy I knew -- or at least I had the feeling -- there was some typo while typing, but cannot put my finger on it. On course, your need [getParameterValue](https://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#getParameterValue%28int%29), not [getParameter](https://docs.oracle.com/javaee/6/api/javax/persistence/Query.html#getParameter%28int,%20java.lang.Class%29). Sorry. I've fixed my answer accordingly. – Sylvain Leroux Nov 27 '14 at 17:41

score 1 · Accepted Answer · edited May 23 '17 at 10:26

Looks similar to this answer

Options which I tried:

Tried with curly braces before the call statement.

Query q = entitymanager.createNativeQuery("{call sample_procedure(?,?)}").setParameter(1, inparam).setParameter(2, outparam);

q.executeUpdate();

It did not work. Did not return the output to java class.

Tried with this:

Query q = entitymanager.createNativeQuery("{call sample_procedure(?,?)}", Integer.class).setParameter(1, inparam).setParameter(2, outparam);

q.executeUpdate();

It did not work either. Did not return the output to java class.

Tried with this as well:

Query q = entitymanager.createNativeQuery("{call sample_procedure(?,?)}").setParameter(1, inparam).setParameter(2, outparam);

q.getSingleResult();

This also threw an exception: Cannot perform an update or delete operation on select query: "{call sample_procedure(?,?)}".

I believe it is not possible to do it like this as mentioned by Sean Patrick Floyd in the answer link i have provided above.(Note: I do not have the book :)) If you need to process your out parameter and if that is the reason you need it, you need to handle it logically. Seems its not possible in jpa2.0

yes I agree with your statements. It is not possible in jpa 2.0 and I handled with logic in my code. — Madhu sudhan Reddy, Nov 28 '14 at 05:41

how to call oracle stored procedure in jpa2.0 which returns integer as out variable

2 Answers2