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 How to execute stored procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to execute stored procedure?" Watch "How to execute stored procedure?" New topic
Author

How to execute stored procedure?

SathishCse kumar
Greenhorn

Joined: Mar 09, 2010
Posts: 1
Dear friends,
I'm having stored procedure call fetchStudDetails(?, ?, ?, ?,?); First 2 are input parameters and other 3 are output parameters. Anyone please tell me how to execute this stored procedure through hibernate (in Java)?

I have seen some of the threads here to call the stored procedure in hibernate but I couldn't get an idea to call the stored procedure with in and out parameter.

Could anyone please help me to configure this stored procedure in hibernate with in and out parameter and to execute this in Java?



Thanks in advance,
Sathish kumar
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 15665
    
  15

I could give you my standard diatribe about the perils of over-reliance on stored procedures, but that's beside the point.

There's a decent amount of coverage in Hibernate's documentation on this, and I'd recommend checking it. But if I haven't got my ORMs crossed, what it all boils down to is that you have to obtain the Connection object from the Hibernate session object and do it the hard way using the Connection.


Customer surveys are for companies who didn't pay proper attention to begin with.
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )


-Mike
Pro JPA 2: Mastering the Java Persistence API
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )


Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Mark Spritzler wrote:
Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )


Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark


Sorry, I don't understand your comment. You should be able to have as many OUT parameters as you want. If you want an output cursor then you can only have one of those, but any number of output params, and at any place in the stored proc.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Mike Keith wrote:
Mark Spritzler wrote:
Mike Keith wrote:Or... you could JPA and make use of vendor extensions that are easy to use and consistent with the programming model. (How's that for a sales pitch )


Are the caveats of;

One and only one out parameter and it must be the first parameter and it must be a RefCursor?

Thanks

Mark


Sorry, I don't understand your comment. You should be able to have as many OUT parameters as you want. If you want an output cursor then you can only have one of those, but any number of output params, and at any place in the stored proc.


Well to be vendor specific, this is a caveat in Hibernate in calling Stored Procedures. And as far as I know today before Hibernate 3.5 (haven't used it yet) the caveats still exists.

Thanks

Mark
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Well to be vendor specific, this is a caveat in Hibernate in calling Stored Procedures. And as far as I know today before Hibernate 3.5 (haven't used it yet) the caveats still exists.


Ah. You didn't click on the vendor extensions link. I was linking to the EclipseLink stored procedure extensions. They are are much more powerful and flexible, and much nicer to use.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

No, I didn't even see the link. Kind of read things a little too quickly most of the time.

I see those annotations. Any chance they could be in the next JPA spec?

Mark
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Stored procs are certainly on my wishlist for the next version. I obviously can't make any promises as to what ends up in the spec, though. The final product is the result of a maze of expert group twists and turns, inputs and requirements, preferences and abhorrences...
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Mike Keith wrote:Stored procs are certainly on my wishlist for the next version. I obviously can't make any promises as to what ends up in the spec, though. The final product is the result of a maze of expert group twists and turns, inputs and requirements, preferences and abhorrences...


Yeah, I know, read a lot of those emails.

Even got slammed, in email, by one of those "experts", not based on what I emailed them, but because they thought I had no real-world experience, because at that time I worked for JBoss. Even though I had worked at real businesses for over 15 years before JBoss.

Anyway, lets just say that that expert is not high on my list and after that I just ignored that person.

Mark
Mike Keith
author
Ranch Hand

Joined: Jul 14, 2005
Posts: 304
Since you don't seem to be ignoring me I'm assuming that it wasn't me ;-)
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1368
Anyone please tell me how to execute this stored procedure through hibernate (in Java)?

Thread question is about Hibernate, not about JPA related issues.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

kri shan wrote:
Anyone please tell me how to execute this stored procedure through hibernate (in Java)?

Thread question is about Hibernate, not about JPA related issues.


Yep, can't be done in Hibernate.

Read my post about the caveats of calling Stored Procedures in Hibernate

1) Only one out parameter allowed
2) It must be the first parameter
3) It must be a reference cursor.

You can always get the JDBC Connection and call via JDBC Api ways. Although check your vendors JDBC api to make sure it implements the JDBC spec fully. Some don't

Mark
larben gab
Greenhorn

Joined: Apr 05, 2010
Posts: 4
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to execute stored procedure?
 
Similar Threads
how to call stored procedure and also get parameters from the web page
does Hibernate supports Stored Procedures?
Executing procedure in Hibernet with more than one out parameters
How to Create and run Oracle Stored procedure
How to call stored procedure from Entity Manager if there is out parameter in stored procedure.