How do I generate unique numbers without using sequence in Oracle for the multi-user environment application?

    Requires Free Membership to View

    Login

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.

For Example:

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top