In SQL Server it is so easy to write:
create table #tmp (
id integer identity(1,1) primary key,
message varchar(256)
);
When I try to migrate this to Oracle, I end up with:
CREATE GLOBAL TEMPORARY TABLE tmp(
id integer primary key,
message varchar2(256)
) ON COMMIT PRESERVE ROWS;
CREATE SEQUENCE S_TMP_ID START WITH 1;
CREATE OR REPLACE TRIGGER TR_TMP
BEFORE INSERT ON tmp
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT S_TMP_ID.NEXTVAL
INTO :new.id
FROM dual;
END;
/
OK. I get unique, increasing id values. But, when two sessions are using the same global temporary table at the same time, I get gaps in the sequence of my ideas and they start with some arbitrary value.
Any ideas how to create better identity values for a global temporary table?