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

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