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

[Giovanni Azua]

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

[Thomas Kyte]

In article <ab5p5q$ff8q6$1...@ID-114658.news.dfncis.de>, "Giovanni says...

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 (tk...@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

[Martin Haltmayer]

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

[Chris Weiss]

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:ch...@hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts

"Martin Haltmayer" <Martin.H...@d2mail.de> wrote in message
news:3CD76BE0...@d2mail.de...

[Martin Haltmayer]

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]

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:ch...@hpdbe.com
www.hpdbe.com
High Performance Database Engineering
Available for long and short term contracts

"Martin Haltmayer" <Martin.H...@d2mail.de> wrote in message

news:3CE0A146...@d2mail.de...