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

Nice, but it does not help me when I select from a table:

SQL> create table t (id number, c varchar2 (30));

Table created.

Elapsed: 00:00:00.70
SQL> create table src as
  2 select substr (owner || object_name, 1, 60) as s from all_objects;

Table created.

Elapsed: 00:00:04.47
SQL> create sequence t_seq;

Sequence created.

Elapsed: 00:00:00.30
SQL> DECLARE
  2 TYPE t_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;   3 TYPE C_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;   4 id_table t_type;
  5 T_VAL PLS_INTEGER;
  6 BEGIN
  7 INSERT INTO T (id, c) select T_SEQ.NEXTVAL, src.s from src   8 RETURNING ID BULK COLLECT
  9 INTO ID_TABLE;
 10 COMMIT;
 11 END;
 12 /
DECLARE
*
ERROR at line 1:
ORA-00933: SQL command not properly ended ORA-06512: at line 7

Chris Weiss wrote:
>
> 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.
>
> -------------------------------------------------
> ALTER TABLE T ADD T_STRING VARCHAR2(20);
>
> 1 DECLARE
> 2 TYPE t_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
> 3 TYPE C_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
> 4 new_table C_type;
> 5 id_table t_type;
> 6 T_VAL PLS_INTEGER;
> 7 BEGIN
> 8 FOR I IN 1..10 LOOP
> 9 NEW_TABLE(I) := 'VALUE '||i;
> 10 END LOOP;
> 11 FORALL I IN 1..NEW_TABLE.COUNT
> 12 INSERT INTO T VALUES(T_SEQ.NEXTVAL,NEW_TABLE(i))
> 13 RETURNING ID BULK COLLECT
> 14 INTO ID_TABLE;
> 15 COMMIT;
> 16 FOR I IN 1..ID_TABLE.COUNT LOOP
> 17 DBMS_OUTPUT.PUT_LINE('id '||ID_TABLE(I));
> 18 END LOOP;
> 19* END;
> /
> id 3
> id 4
> id 5
> id 6
> id 7
> id 8
> id 9
> id 10
> id 11
> id 12
>
> --
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 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...
>
> > 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 14 2002 - 00:31:50 CDT