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?
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.
For Oracle the following rules apply:
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.
I tried ArrayList, using setParameter, but it didn't like that.
If it's a stored proc, wouldn't the first variable (i.e. the first setParameter() need to hold the SYS_REF_CURSOR? or am I missing something?
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.
I'm looking for a simple working example for stored proc (as opposed to stored function), showing the java, the map file and the stored proc, if anyone has one, or can quicky create one.