Unique sequence for a column in oracle table

Question

I am trying to load a table where i have a PK column called E_id. I want to generate a unique ID for this column every time a row is loaded into this table at informatica level. But i want this e_id column to generate a unique value at table level. Can i achieve this by writing a query to this column at table create script itself? Currently i tried this method of defaulting the value with this

e_id NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF9')) NOT NULL

Although at times i am getting the same value for two records. Can someone help how to go about this?

TIA.

why not simply use an auto increment column? – Zohar Peled Jan 17 '17 at 10:42 — Zohar Peled, Jan 17 '17 at 10:42

score 1 · Answer 1 · answered Jan 17 '17 at 10:43

1

If you are using 12c the easiest way is to use an IDENTITY column. Oracle will automatically generate a unique value for the column whenever you insert a record.

In prior versions you can define a sequence and generate it via a trigger or reference it directly in the insert statement.

You mention Informatica. Its a long time since I used it but I seem to remember there being a simple way to generate a unique ID using Informatica too.

answered Jan 17 '17 at 10:43

BriteSponge

1,034
9
15

Yeah but the constraint here is i can not use informatica to load unique values for this columns else i would hav easily used the sequence generator transformation. Thats my constraint here. I need to find a way to populate this column at table script level. – gbppa Jan 17 '17 at 11:12
If it has to be purely at table script level then you will need to be on 12c, otherwise it sounds like the trigger is the way to go. – BriteSponge Jan 17 '17 at 12:17

score 1 · Accepted Answer · answered Jan 17 '17 at 11:18

one of the most common way to do this is using sequences

1 create sequence

2 before insert row trigger that will populate value into the pk column

create sequence GSEQUENCE
minvalue 1000
maxvalue 99999999999999999
start with 93581
increment by 1
cache 20;


CREATE OR REPLACE TRIGGER BI_DOCUMENTS
  BEFORE INSERT
  on DOCUMENTS

  for each row
declare numrows INTEGER;
begin


select gsequence.nextval
into :new.id_DOCUMENT
from dual;

end;
/

score 0 · Answer 3 · edited May 23 '17 at 12:16

You can use Globally Unique ID (GUID) which is unique for all practice purpose. Lot of systems use GUID to generate unique id.

select sys_guid() from dual

Mote into here

While GUID gives truly random unique ids, you can always use Oracle sequence if you need sequential unique values.

Oracle doesn't have "auto increment" columns, but you can achive similar results by a trigger.

EDIT: You can refer this link too How to generate a GUID in Oracle?

Unique sequence for a column in oracle table

3 Answers3