• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help with how to call from JPA oracle stored procedure with array as IN parameter

 
larben gab
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I need to call a stored proc in Oracle that accepts an array as input parameter.

Please let me know how should i call it from my JPA. Is this even possible without using JDBC directly?

i keep getting the ff error:

wrong number or types of arguments in call to ....

my code is something like this:

...
String[] myArr...

Query query = em.createNativeQuery("BEGIN myStoredProc(:arr); END;");
query.setParameter("arr", myArr);
...

Thanks in advance.
 
larben gab
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
anyone please?
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does you stored procedure look like? What type is being used for the array?

I assume you are using either a VARRAY in Oracle, or a TABLE type?

What JPA provider are you using?

If you are using EclipseLink (or TopLink), you can define a StoredProcedureCall object that takes a JDBC java.sql.Array argument.
If you need a TABLE, then you can use the PLSQLStoredProcedureCall to call this.

The JPA spec has no support for this, so if your JPA provider does not have custom support for this you need to use JDBC.
 
larben gab
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your reply.

after searching hopelessly, I thought so too. JPA alone does not support it. we're using hibernate behind but i would prefer to only use generic JPA methods since there is a possiblity that we may switch later on.

sadly, we were forced to use JDBC instead when using this feature. it would have been alot better if this were not so.

but thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic