Re: SELECT @@IDENTITY... is there in Oracle a better way to go?

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Tue, 07 May 2002 07:53:36 +0200
Message-ID: <3CD76BE0.9C0466EB@d2mail.de>

Alas, the most interesting thing does not work (8.1.7.2.1 W2K):

SQL> declare
2 some_variable number;
3 begin

  4     insert into clients ( idclient)
  5     select idclient_seq.nextval from dual
  6     RETURNING IDCLIENT into some_variable;

7 end;
8 /
declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended ORA-06512: at line 4

It would be *very* nice to have it with bulk insert so I could get all the inserted numbers in one PL/SQL table without retrieving them *again*.

Martin

Thomas Kyte wrote:
>
> In article <ab5p5q$ff8q6$1_at_ID-114658.news.dfncis.de>, "Giovanni says...
> >
> >Hi all,
> >
> >I was trying to simulate the SELECT @@IDENTITY effect
> >of SQL Server in an Oracle Database and I found a way to
> >achieve the same effect by using the combination: SEQUENCE,
> >TRIGGER ON BEFORE INSERT and a Global Package
> >variable which have a different copy/instance for each
> >different connection/session.
> >
> >Is there another clearer/straighter way to achieve this effect
> >in Oracle9i?
> >
> >create table CLIENTS (
> > IDClient number(9) not null,
> > CONSTRAINT IDClient_pk primary key (IDClient)
> >);
> >
> >create sequence IDClient_seq
> > increment by 1 start with 1;
> >
> >create or replace trigger CLIENTS_BIR
> > before insert on CLIENTS for each row
> >begin
> > select IDClient_seq.NextVal
> > into :new.IDClient
> > from DUAL;
> > IdentityPkg.LastIdentity := :new.IDClient;
> >end;
> >/
> >.
> >
> >create or replace package IdentityPkg as
> > LastIdentity number := -1000;
> >end IdentityPkg;
> >/
> >.
> >
> >create or replace procedure LastIdentity (
> > ident out number ) is
> >begin
> > ident := IdentityPkg.LastIdentity;
> >end;
> >/
> >.
> >
> >Best Regards,
> >Giovanni
> >
> >
>
> Just
>
> select IDClient_seq.CURRVal from dual
>
> to get the "last identity". CURRVAL is specific to a session, it returns the
> value returned to YOUR session by the last call to NEXTVAL. It works as you
> would want it to work in a multi-user environment -- it always returns your
> sessions last "nextval".
>
> Also, you can just code:
>
> decalre
> some_variable number;
> begin
> insert into t ( a, b, c ) values ( d, e, f )
> RETURNING IDCLIENT into some_variable;
> end;
>
> to generate and get the generated key in one call.
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Tue May 07 2002 - 00:53:36 CDT

Re: SELECT @@IDENTITY... is there in Oracle a better way to go?