[sueamus]

Hi all,

Is it possible to create a temporary table with auto-increment feature? For example in db2 i would have:

declare global temporary table abc
(id_number integer generated always as identity(start with 1,increment by1),
branch_num char(6) )
on commit preserve rows

Thanks.

[andrewst]

No, in Oracle you use a SEQUENCE to generate surrogate keys like this:

SQL> create global temporary table abc
  2  (id_number integer ,
  3  branch_num char(6) )
  4  on commit preserve rows;

Table created.

SQL> create sequence abc_seq;

Sequence created.

SQL> insert into abc (id_number, branch_num) values (abc_seq.nextval, 'x');

1 row created.

SQL> insert into abc (id_number, branch_num) values (abc_seq.nextval, 'y');

1 row created.

SQL> select * from abc;

 ID_NUMBER BRANCH
---------- ------
         1 x
         2 y

Read more about sequences here.

[sueamus]

Thanks alot, that was very helpful.