| 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?
You can manipulate the counter example with the select by doing a bulk
collect of the sequence ids and the values from the base table
SELECT t_seq.NEXTVAL, src.s
BULK COLLECT INTO id_table, s_table
FROM src;
Then you have the ids and you can use these collections in a FORALL. It is not as "pure", but it prevents the requery against the base table after you have inserted the data. Also, if the src table values may repeat in the table, T, then bulk collecting the row values in advance of the insert may be the only way to guarantee that the id values are correct.
There is almost always a workaround :-)
Good Luck!
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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:3CE0A146.84544530_at_d2mail.de...Received on Tue May 14 2002 - 02:50:00 CDT
> 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
 |
 |