Q

Generating unique numbers without using sequence

How do I generate unique numbers without using sequence in Oracle for the multi-user environment application?
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close