Similar to the lack of documentation I'm finding on stored proc queries in my other reply on this thread, I'm finding a bit of a lack of documentation on insert/update/delete in hibernate using straight sql calls and stored procedures here:
Skip down to section 16.4. There it discusses how to set up the mapping file and the stored proc (actually function again), but it doesn't explain how to set up the call in java.
Hopefully I'm just not looking in the right place, or it's something really obvious, and I can't see the forest for the trees... but I've looked through the above URL, plus the Hibernate In Action book and the Hibernate Quickly book and I can't find any examples of how to do this yet, or get my own to work.
Could someone perhaps point me to the example, or post a simple example for the updatePerson stored function in the above URL?
I think because it was someone elses thread that we didn't see it. Sorry.
Well, I will give you a better answer tomorrow when I am at work. But I believe in the named queries xml file, you post the stored procedure name and the parameters and map it to a Java class file that you have. The Java Class file should be mapped to either a database table or the ref cursor info that comes out of that first OUT parameter. If you have no out parameters then there is no need to map to a Java class, just write a named query in the mapping file. I will give you an example tomorrow.
A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.
So for stored procedures in Oracle, they must be functions.
In their example in that section you pointed out it just returns the rowcount of the update, insert, or delete.
Have you rtied something like this.
Joined: Jul 28, 2003
Not yet, coz I don't understand what the java source code say, i.e. if the first parameter is an OUT and need to be a SYS_REF_CURSOR, what would the java-hibernate type be, and how would you send it as a parameter to the stored proc?
I tried ArrayList, using setParameter, but it didn't like that.
Reading "between the lines" of the documentation Chapter 16. Native SQL - I'd go with the assumption that Hibernate treats.
procname( p0 OUT, p1 IN, p2 IN, ... )
p0 = procname( p1 IN, p2 IN, ... ).
Get a sample working with a stored function - then change the function to a procedure by adding the first OUT parameter to carry the return value - and get rid of the function return value. Based on any complaints you get you might need to tweak the mapping to get it to work. But I wouldn't be surprised if that first OUT parameter is simply modelled as the function return value in the Java code.