File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Calling Oracle sproc using OpenJPA: param has 2 parms, one is a ref-cursor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Calling Oracle sproc using OpenJPA: param has 2 parms, one is a ref-cursor" Watch "Calling Oracle sproc using OpenJPA: param has 2 parms, one is a ref-cursor" New topic
Author

Calling Oracle sproc using OpenJPA: param has 2 parms, one is a ref-cursor

Derek West
Greenhorn

Joined: Jun 18, 2012
Posts: 9
Hey there.

I am trying to hit an oracle stored Proc with openJPA. The stored proc takes two parameters. One of them is an out parameter that is a ref cursor. How do you get a result from that? Right now I have this...



And I get this exception...

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement


What am I doing wrong?
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1649
    
    7

Wel "GetProviderLstByOrgIds" is not valid SQL. Did you perhaps mean to use createNamedQuery?

I think that is probably what you wanted if you have a @NamedNativeQuery defined on an entity somewhere.


[How To Ask Questions][Read before you PM me]
Derek West
Greenhorn

Joined: Jun 18, 2012
Posts: 9
I may have some bad syntax there. But you are saying it is possible to call a stored proc with an out paramter (that is a ref cursor)... I just need to know if it is possible and how to do it. Also note that was the name of my stored proc. I also have a mapping i can use for the result set.
Bill Gorder
Bartender

Joined: Mar 07, 2010
Posts: 1649
    
    7

Ahh sorry Derek I did not read the topic very clearly according to OpenJpa's documentation your original syntax may be OK. Unfortunately I am not up on OpenJPA so I am not going to be that much help on this one. I did find this link but I am not sure that it will be much use to you.

http://www.coderanch.com/t/305568/ORM/databases/Calling-Stored-Procedure-thru-JPA

I was surprised to see how little is said about this in their reference manual.
Derek West
Greenhorn

Joined: Jun 18, 2012
Posts: 9
Yeah. Apparently you can call a simple sproc with no params but anything else is impossible-which is completely useless. Thanks apache. Whoever made that call needs to be banished to Siberia.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Calling Oracle sproc using OpenJPA: param has 2 parms, one is a ref-cursor