The best way to generate unique numbers in Oracle is by creating sequence. This alleviates the headache of issues such as locking, transaction failure, etc.
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;
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.