generating IDs with a stored procedure

[DavidParker]

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@...




_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[James Sutherland]

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
[e-mail]