[cvsnt] Re: Repository auditing with Oracle

Anthony Williams anthony.ajw at gmail.com
Wed Jan 11 14:04:50 GMT 2006


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.


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



More information about the cvsnt mailing list
Download the latest CVSNT, TortosieCVS, WinCVS etc. for Windows 8 etc.
@CVSNT on Twitter   CVSNT on Facebook