Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] generating IDs with a stored procedure 
Not sure why you need to flush after the native update query, it should just
execute.  You may need to set your flushMode to commit instead of auto.

For a stored procedure on Oracle, you can only return values using output
parameters, so can't use a basic JPA NativeQuery, but need to use
EclipseLink's StoredProcedureCall support.  You could write a stored
function, and select the value from DUAL in Oracle without requiring output
parameters.



DavidParker wrote:
> 
> I need to generate monotonically increasing IDs (not the primary keys)  
> for a a variety of types. I have table that looks something like
> 
> type  char(2)
> object_id char(36)
> curr_val int
> 
> So for a given type, for instance a Project, I need to look for the  
> object_id matching my project, update the curr_val then return it.
> 
> The old fashioned way to do this is to do the update first to get the  
> update lock, then do the select. Problem is, when I do this with  
> NativeQueries in eclipselink, I need to to a flush after the update  
> before I can do the select, and the flush appears to be expensive.
> 
> I just need to get the value back - I'm not returning a JPA object in  
> this case.
> 
> My thought is to do it in an Oracle Stored Procedure. Is there a way  
> to get a value back from a stored proc call using just NativeQuery in  
> the JPA interface, or should I use the EclipseLink support for this.
> 
> Alternatively, is there a better way to do this? I don't want to use  
> sequences because they are not transactional, and the monotonic  
> increasing key is important, for whatever reason, to the user.
> 
> Thanks. Pointers to code examples appreciated.
> 
> aloha
> 
> =============================================
> David Parker
> dap@xxxxxxxxxxxxxxxxxxxx
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/generating-IDs-with-a-stored-procedure-tp25997454p26010386.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.

Back to the top