Starting with Oracle 8.1.0 and higher there is a function named SYS_GUID() that works in a distributed environment. This generates a unique key across a distributed environment but the value is alphanumeric.
select sys_guid() from dual;
You can also use the following really old method of generating keys if you want a numeric value for your keys. However, I recommend against using it.
However, you may have to contact your DBA to ensure a grant to this function is given to you or a role that you are granted.
CREATE TABLE MY_KEY_GENERATOR ( KEY_VAL NUMBER ) LOGGING NOCACHE NOPARALLEL; INSERT INTO MY_KEY_GENERATOR VALUES (0); CREATE TABLE TABLE_WITH_KEYS ( TABLE_ID NUMBER NOT NULL, TABLE_DESC VARCHAR2(20) ) LOGGING NOCACHE NOPARALLEL; ALTER TABLE TABLE_WITH_KEYS ADD ( CONSTRAINT TABLE_WITH_KEYS_PK PRIMARY KEY (TABLE_ID));
You want to create a trigger on TABLE_WITH_KEYS that take care of the unique key generation and rudimentary error handling.
CREATE OR REPLACE TRIGGER PROVIDE_KEY_FOR_TABLE BEFORE INSERT ON TABLE_WITH_KEYS FOR EACH ROW DECLARE v_dummy NUMBER; CURSOR MY_KEY_CUR IS SELECT KEY_VAL FROM MY_KEY_GENERATOR FOR UPDATE OF KEY_VAL NOWAIT; BEGIN OPEN MY_KEY_CUR; FETCH MY_KEY_CUR INTO v_dummy; CLOSE MY_KEY_CUR; UPDATE MY_KEY_GENERATOR SET KEY_VAL = v_dummy+1; :NEW.TABLE_ID := v_dummy+1; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,' Insert failed.'); END;
This was first published in February 2004