5

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?

Hash
  • 4,647
  • 5
  • 21
  • 39
Prashanth
  • 1,388
  • 2
  • 11
  • 26
  • Never worked in Hibernate. Not sure: is this line right? `query.setParameter(1, '1234');` – Guru Apr 16 '11 at 17:12
  • Yes, that is a valid statement in JPA. – Prashanth Apr 16 '11 at 17:15
  • 2
    can you try `query.setParameter("accountNumber", '1234');` and let me know the result for me? – Guru Apr 16 '11 at 17:36
  • 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');` – Guru Apr 16 '11 at 17:40
  • 1
    possible duplicate of [Calling stored procedure from Java / JPA](http://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa) – Bozho Apr 16 '11 at 18:01
  • have you tried with setting parameter value directly in sql as - "call CFB.FC_AMOUNT_CHECK("+accountNumber+")" – Nayan Wadekar Apr 16 '11 at 18:40
  • 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. – Prashanth Apr 16 '11 at 21:49
  • I was able to call it, but without any in/out parameters, but no clue about to passing parameters. – Nayan Wadekar Apr 17 '11 at 11:13
  • As far as I know JPA doesn't support named parameters binding in native queries... you should stick to ? and indexed binding. – JanM Jun 16 '14 at 14:44
  • Possible duplicate of [How to call a custom Oracle function returning a value from JPA](http://stackoverflow.com/questions/6989263/how-to-call-a-custom-oracle-function-returning-a-value-from-jpa) – Vadzim Oct 13 '16 at 22:20

2 Answers2

1

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

suraj bahl
  • 2,864
  • 6
  • 31
  • 42
-1

If you want to set query parameters by index: 

query.setParameter(1, '1234');

You need to change the named parameters inside the query, and replace it with a ?, like this:

StringBuilder sql = new StringBuilder("call CFB.FC_AMOUNT_CHECK(?)");
Draken
  • 3,134
  • 13
  • 34
  • 54
Victor Gimeno
  • 11
  • 2