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

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 Received on Mon May 06 2002 - 05:19:56 CDT