Re: Sequence/Identity

"Ernest Morariu" <ernest_at_gesora.com> schrieb im Newsbeitrag news:cd5r23$nfj$1_at_carabinieri.cs.interbusiness.it...
> Matthias,
>
> Thank you very much for you replay.
>
> I wish you helped me in finding the answer for another problem:
>
> Is there any system variable / function that tells me what is the last ID
> generated by a certain user for my table.
>
> I am looking for something similar to @@Identity or better
@@Scope_Identity
> in SqlServer .

Any sequence (call it MySeq) has a property called CURRVAL, so you can call MySeq.CURRVAL once you have called .NEXTVAL (see Matthias' example) in your connection before. It will deliver the value that was produced by the previous .NEXTVAL call. Example:

SELECT MySeq.CURRVAL FROM DUAL;

>
> Ernest
>
>
>
> "Matthias Lippmann" <news_at_lippmannsoft.de> wrote in message
> news:cd5oj3$nb2$1_at_beech.fernuni-hagen.de...
> > "Ernest Morariu" asked ..
> >
> > > It is possible in Oracle 8i to define a column(called ID) in a table
as
> > been
> > > value-autogenerated or Identity(as in SqlServer)?
> > > Is there any alternative to the sequences for generating the new IDs
for
> > my
> > > tables ?
> >
> > Use a sequence in combination with before-insert trigger for
> > your table to fill your ID column.
> >
> > Example:
> >
> > CREATE OR REPLACE TRIGGER mytable_autovalue
> > BEFORE INSERT ON mytable
> > REFERENCING NEW AS NEW OLD AS OLD
> > FOR EACH ROW
> > BEGIN
> > SELECT mysequence.NEXTVAL INTO :NEW.id FROM DUAL;
> > EXCEPTION
> > WHEN OTHERS THEN
> > NULL;
> > END;
> >
> > Best Regards,
> >
> > Matthias Lippmann
> > --
> > www.lippmannsoft.de
> >
> >
>
>
Received on Thu Jul 15 2004 - 07:13:48 CDT