[cvsnt] Re: Repository auditing with Oracle

[Anthony Williams]

Mike Wake <mike.wake at thalesgroup.com> writes:

> Anthony Williams wrote:
>> Tony Hoyle <tony.hoyle at march-hare.com> writes:
>>
>>> Luigi D. Sandon wrote:
>>>>> little harder that's all since it needs to modify the insert statement.
>>>> No need to modify the insert statement if the sequence is used in a before
>>>> insert trigger. You get the same behaviour of an autoinc column.
>>>>
>>> The issue then becomes retrieving that value - unless the trigger can force
>>> the insert to return it somehow.
>> I posted a selection of alternatives that work with Oracle yesterday.
>> Have my posts to the newsgroup not been getting through?
>> Anthony
> Unless this is what you are talking about I haven't seen it on the mailing list.
>
> <snip Anthony Williams wrote:
> Oracle provides INSERT .... RETURNING (someColumn) INTO :someBindVariable for precisely this purpose.
>
> Oracle also has sequences rather than auto-increment columns.
> </snip>

I also posted:

> 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

and now, it occurs to me that you might also be able to use this last SELECT
in conjuction with a trigger that did the actual update.

Anthony
-- 
Anthony Williams
Software Developer
Just Software Solutions Ltd
http://www.justsoftwaresolutions.co.uk