I am designing an application for a client that uses an Oracle database. The client has a policy that all database inserts use a standard trigger to generate the primary key for a record in every table. The primary key generated by the trigger is a 34 character string.

Is there an identity generator which will insert a new record into a table and then read the record the obtain the primary key value for an id? The sequence generator seems close to what I need, but the hibernate sequence generate returns a long, short, or int.

Thank you,

brad

Either try using a custom Dialect and override getIdentitySelectString() to return a SQL which fetches the last generated identifier, then use identity generator. Or implement your own custom generator.

As far as I understand, this problem is quite unsolvable because Oracle provides no way to safely fetch the identifier that was generated by the trigger. You should explain this to your data people and ask them to change their policies.

Actually you can get the value generated from the trigger using the RETURNING clause on the insert statement:



I don't know which (if any) other database engines support that syntax.

If that works with session.executeQuery("INSERT INTO ... RETURNING ID"), you should be able to include this in your dialect by overriding appendIdentitySelectToInsert() in a custom dialect. Take a look at SybaseDialect for an example.

Thanks for the tips on using the returning clause. I will look into it. I found some additional and useful information at ExpertsExchange in this thread:

http://www.experts-exchange.com/Databas ... ml#8893641

FYI. Registration required to see the full thread.

Thanks,

Brad

Based on the information in the thread cited above (experts exchange), the Returning clause in Oracle's sql has to be accessed via a stored procedure and a CallableStatement from jdbc.

Is this possible with Hibernate? Or will I need to implement custom versions of the ClassPersister interface as well as a custom dialect?

Thanks,

Brad