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