Community technical support mailing list was retired 2010 and replaced with a professional technical support team. For assistance please contact: Pre-sales Technical support via email to sales@march-hare.com.
Tony Hoyle <tony.hoyle at march-hare.com> writes:
> Paulo Soares wrote:
>> If I assume that there's only one writer the sequence can even be generated
>> internally by the cvsnt app and do away with all this identity need.
>
> You cannot make that assumption - that's why you need the atomicity. You
> need to be able to get an ID for an inserted row in a manner that always
> returns the correct one no matter how many writers there are on the
> database.
INSERT INTO SomeTable (ID, someOtherColumn) VALUES (ID_SEQUENCE.NEXTVAL,
'someValue') RETURNING (ID) INTO :someBindVariable
will do it for Oracle. ID_SEQUENCE must be a SEQUENCE (created with CREATE
SEQUENCE).
You could also create a trigger that used ID_SEQUENCE to populate ID, in which
case the insert becomes:
INSERT INTO SomeTable (someOtherColumn) VALUES ('someValue')
RETURNING (ID) INTO :someBindVariable
Alternatively, you can get the sequence value first:
SELECT ID_SEQUENCE.NEXTVAL FROM DUAL
INSERT INTO SomeTable (ID, otherColumn) VALUES (valueFromSequence,otherValue);
or afterwards:
INSERT INTO SomeTable (ID, someOtherColumn) VALUES (ID_SEQUENCE.NEXTVAL,
'someValue')
SELECT ID_SEQUENCE.CURRVAL FROM DUAL
HTH
Anthony
--
Anthony Williams
Software Developer
Just Software Solutions Ltd
http://www.justsoftwaresolutions.co.uk