[cvsnt] Re: Repository auditing with Oracle

[Anthony Williams]

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