Auto numbering fields in Oracle

Auto numbering fields often come up in debates between fans of Oracle and SqlServer. The SqlServer devotee will argue that their database has the advantage as it provides easy to use auto numbering fields. The Oracle believers come-back will undoubtedly be... "ah but you can use Triggers".

So how do you? Luckily, I've just been implimenting this very thing in Oracle, so I thought I'd share with you my solution.

First off, let me define a table that the solution will be based upon....

CREATE TABLE TestTable
( TestTable_PK NUMBER(10) NOT NULL PRIMARY KEY,
AValue VARCHAR2(10)

The first column (TestTable_PK) is the one which we want to populate with an auto numbering value.

# Applying auto-numbering:

The auto-numbering solution is made up of two components:

1. The Sequence - to provide the auto numbering value.
2. The Trigger - to add the auto numbering value to the row.
   

So, let us start by defining the Sequence....

CREATE SEQUENCE TestTable_SEQ
MINVALUE 0
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1;

...and then we can define our auto numbering Trigger.

CREATE TRIGGER TestTable_TRG
BEFORE INSERT ON TestTable
FOR EACH ROW
BEGIN
SELECT TestTable_SEQ.nextval INTO :new.TestTable_PK FROM dual;
END;

The Trigger has to occur before the insert otherwise the Primary Key unique constraint will throw an error. It also has to fire for each row because if we are inserting multiple records we want a different sequence value for each. The :new variable in the trigger references the row contents that is inserted to the table after the Trigger has fired.

# Returning the auto-numbering value:

Okay, so now we have out auto numbering working, how can we insert data and return the auto-generated number? This can be achieved by using the RETURNING keyword on the INSERT statement. For example:

INSERT INTO TestTable
(aValue)
VALUES
('Some Data')
RETURNING TestTable_PK INTO :value;

The :value is a variable that can be queried in the client to get the returned field.

# Generic Solution:

In the code block below I have included the code to PL/SQL package that I have written to automatically add auto-numbering to a specified table. I do make an assumption in my code that the column that is being used as a auto numbering field is called _PK. You could write to query the data dictionary views to return the actual Primary Key, but this could cause problems if the Primary Key includes more than one field.

Hope you find it useful.

CREATE OR REPLACE PACKAGE PKG_Utilities
AUTHID DEFINER
AS
/*  ------------------------------------------------------
Add Auto numbering to the Primary Key of the table.
------------------------------------------------------ */
PROCEDURE proc_AddAutoNumbering(
tableName IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_Utilities
AS
/* ***********************************************************************
FORWARD DECLARATION OF PRIVATE METHODS
*********************************************************************** */
FUNCTION func_AddAutoNumberingSequence(
tableName IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE proc_AddAutoNumberingTrigger(
tableName IN VARCHAR2,
sequenceName IN VARCHAR2);
FUNCTION func_ObjectExists(
/*  Does the object exist in the current schema? */
objectName IN VARCHAR2,
objectType IN VARCHAR2)
RETURN BOOLEAN;
FUNCTION func_GetSafeIdentifier(
/*  Returns an identifier under 26 characters in length
that can be concatenated with a prefix such as _TRG and
still be under the 30 character limit for Oracle object names */
objectName IN VARCHAR2)
RETURN VARCHAR2;
/* ***********************************************************************
PUBLIC METHODS
*********************************************************************** */
PROCEDURE proc_AddAutoNumbering(
tableName IN VARCHAR2)
IS
sequenceName VARCHAR2(200);
BEGIN
sequenceName:= func_AddAutoNumberingSequence(tableName);
proc_AddAutoNumberingTrigger(tableName, sequenceName);
END;
/* ***********************************************************************
PRIVATE METHODS
*********************************************************************** */
FUNCTION func_AddAutoNumberingSequence(
tableName IN VARCHAR2)
RETURN VARCHAR2
IS
execute_sql  VARCHAR2(1000);
sequenceName VARCHAR2(30);
BEGIN
sequenceName:= func_GetSafeIdentifier(tableName) || '_SEQ';
IF (NOT(func_ObjectExists(sequenceName, 'SEQUENCE'))) THEN
execute_sql:= 'CREATE SEQUENCE PremierEnvoy.' || sequenceName || chr(10) ||
              'MINVALUE 0 ' || chr(10) ||
              'MAXVALUE 999999999999999999999999999' || chr(10) ||
              'START WITH 1' || chr(10) ||
              'INCREMENT BY 1';
EXECUTE IMMEDIATE execute_sql;
END IF;
RETURN sequencename;
EXCEPTION
WHEN others THEN
raise_application_error(-20001, 'Failed to Add Sequence - ' || execute_sql);
END;

PROCEDURE proc_AddAutoNumberingTrigger(
tableName IN VARCHAR2,
sequenceName IN VARCHAR2)
IS
triggerName VARCHAR2(30):= func_GetSafeIdentifier(tableName) || '_TRG';
execute_sql VARCHAR2(2000):=
'CREATE TRIGGER PremierEnvoy.' || triggerName || chr(10) ||
'BEFORE INSERT ON PremierEnvoy.' || tableName || chr(10) ||
'FOR EACH ROW ' || chr(10) ||
'BEGIN ' || chr(10) ||
'  SELECT ' || sequenceName || '.nextval' || chr(10) ||
'  INTO :new.' || tableName || '_PK FROM dual;' ||  chr(10) ||
'END;';
BEGIN
IF (NOT(func_ObjectExists(triggerName, 'TRIGGER'))) THEN
EXECUTE IMMEDIATE execute_sql;
END IF;
END;

FUNCTION func_ObjectExists(
objectName IN VARCHAR2,
objectType IN VARCHAR2)
RETURN BOOLEAN IS
objectCount NUMBER;
BEGIN
SELECT count(object_name)
INTO objectCount
FROM user_objects
WHERE UPPER(object_name) = UPPER(objectName)
 AND UPPER(object_type) = UPPER(objectType);
RETURN (objectCount > 0);
END;

FUNCTION func_GetSafeIdentifier(
   objectName IN VARCHAR2)
 RETURN VARCHAR2
 IS 
 BEGIN
   IF (LENGTH(objectName) < 25) THEN
     RETURN objectName;
   ELSE
     RETURN SUBSTR(objectName, 0, 25);
   END IF;
 END;