| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SELECT @@IDENTITY... is there in Oracle a better way to go?
This works in 8i. The following example was completed on 8.1.6.0 on Linux.
The select from dual syntax should be done with a select into. Thomas' method is described in the following example:
create table t (id number);
sequence t_seq;
DECLARE
T_VAL PLS_INTEGER;
BEGIN
INSERT INTO T VALUES(T_SEQ.NEXTVAL)
RETURNING ID INTO T_VAL;
DBMS_OUTPUT.PUT_LINE('id '||t_val);
END;
/
id 2
You can do this in bulk too.
4 new_table C_type; 5 id_table t_type; 6 T_VAL PLS_INTEGER;
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Chris Weiss mailto:chris_at_hpdbe.com www.hpdbe.com High Performance Database Engineering Available for long and short term contracts "Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> wrote in message news:3CD76BE0.9C0466EB_at_d2mail.de...Received on Tue May 07 2002 - 17:04:24 CDT
> 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
 |
 |