Calling an oracle function from JPA

Question

I am trying to call a simple function from a JPA class that returns a number based on some calculations and has the following definition.

'CREATE OR REPLACE FUNCTION CFB.FC_AMOUNT_CHECK(accountNumber IN VARCHAR2)
return NUMBER IS .....'

I am trying to call this function from JPA the following way.

StringBuilder sql = new StringBuilder("call CFB.FC_AMOUNT_CHECK(:accountNumber)");
Query query = em.createNativeQuery(sql.toString());
query.setParameter(1, '1234');
List<?> result = query.getResultList();

....

However, when I execute this class, I get the below exception all the time:

java.lang.IllegalArgumentException: org.hibernate.QueryParameterException: could not locate named parameter [1]

I cant seem to get how JPA cannot find parameter 1....I have been breaking my head with this for the last 4 hours. Can anyone please suggest how to get the result I want?

Solution

If you are looking to call a function in JPA select query then follow below link, it works:

http://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/j_func.htm

Comments

[Guru]

Never worked in Hibernate. Not sure: is this line right? query.setParameter(1, '1234');

[Prashanth]

Yes, that is a valid statement in JPA.

[Guru]

can you try query.setParameter("accountNumber", '1234'); and let me know the result for me?

[Guru]

Also not sure why you want to use bind variable when you are replacing it with literals. If possible change this "call CFB.FC_AMOUNT_CHECK(accountNumber)" and then try query.setParameter("accountNumber", '1234');

[Bozho]

possible duplicate of Calling stored procedure from Java / JPA

[Nayan Wadekar]

have you tried with setting parameter value directly in sql as - "call CFB.FC_AMOUNT_CHECK("+accountNumber+")"

[Prashanth]

Yes Nayan - I tried to put in the value as you have suggested but with no luck. I think this is a fundamental problem with JPA and function calls. JPA seems to be lagging in terms of implementing a standard way of invoking SPs and functions in general.

[Nayan Wadekar]

I was able to call it, but without any in/out parameters, but no clue about to passing parameters.

[JanM]

As far as I know JPA doesn't support named parameters binding in native queries... you should stick to ? and indexed binding.

[Vadzim]

Possible duplicate of How to call a custom Oracle function returning a value from JPA