[eclipselink-users] generating IDs with a stored procedure

[David Parker <dap@xxxxxxxxxxxxxxxxxxxx>]

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