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;

Deep Copies in .NET

A problem that I often have when writing WCF services is that WCF complains when you try to send an Object that has been cast to an ancestor over the wire (as a DataContract). WCF responds by getting confused as to what type of object it is returning.

The only solutions is to create a new instance of the parent class and copy all the fields/properties from the child. This is effectively making a Deep Copy of the object... so it got me thinking about how I could impliment a Deep Copying function in .NET.

My Solution has been implimented as an Exension Method as I want it to be called on a class, but I don't want to have a distant ancestor to all my classes that contains this method. The method itself uses reflection to work out the fields and property values that it needs to copy across.

The code is as follows:

public static object GetDeepCopy(this T originalObject, Type newObjectType)
{
  object newObject = Activator.CreateInstance(newObjectType);

  //copy fields
  FieldInfo[] fields = newObject.GetType().GetFields();
  int i = 0;
  foreach (FieldInfo field in fields)
  {
       fields[i].SetValue(newObject, field.GetValue(originalObject));
       i++;
   }

  //copy properties
  PropertyInfo[] properties = newObject.GetType().GetProperties();
   i = 0;
  foreach (PropertyInfo property in properties)
  {
      properties[i].SetValue(newObject, property.GetValue(originalObject, null), null);
       i++;
  }
  return newObject;
}

This class can be used against any classes if it's namespace is included in the code in which you are using the objects. Here is an example of it in use:

ChildClass one = new ChildClass()
{
Active = true,
Address = "HHWWH",
testone = Testy.two,
otherstuff = "sdsadasd"
};

ParentClass two = (ParentClass)one.GetDeepCopy(typeof(ParentClass));

As you can see, the routine requires that a type is passed in and also the returned value must be cast to the same type.

Checking if a string is empty

In C# there are a variety of ways of checking if a string is empty. The main methods I have seen people use are:

1. myString == String.Empty;
2. myString == "";
3. myString.Length == 0;

So, which is the best one to use? FxCop provides the answer in it's performance rule TestForEmptyStringsUsingStringLength.

As the name probably gives away, the most efficient method is 3. myString.Length == 0; Or alternatively we can use the String.IsNullOrEmpty() method to test that the string isn't null at the same time.

.NET Role Based Security

I am currently looking at implimenting Users and Security in a new product. Ideally I want to use a Role Based Security (RBS) system, so I've been revisiting the role Based security classes and interfaces that exist in the .NET framework. I have to say that I believe that Microsoft have got this area of the .NET framework wrong *. This is why...

RBS has three components:

1. The Users
2. The Roles
   
3. The Permissions (AKA rights)
   

Users are assigned to 0..* roles and roles contain 0..* permissions.
Permissions can be assigned to many roles.
(see the diagram on the wikipedia page here).

In the .NET framework Microsoft have developed classes such as WindowsPrincipal and GenericPrinciple to test whether a User is a member of a particular Role.
For example:

GenericIdentity identity = new GenericIdentity("IMitchell");
string[] roles = new string[] { "Administrator", "Manager" };
GenericPrincipal principle = new GenericPrincipal(identity, roles);

//Imperative Security Check
bool able = principle.IsInRole("Administrator");

//Declarative Security Check
[PrincipalPermission(SecurityAction.Demand, Role="Manager")]
private void DoSomething()
{
....
}

I don't think this is right... I don't think that roles should play a part in the programming. Really you should be checking if the user has the permission to do a certain task. Whether they have that aquired that right from being in role "Administrator" or role "Manager" is irrelevant to the program.

Also, by including security checking based on role names you are imposing these on the end users... who would be far better off defining their own roles to reflect their own organisational structure.

A good example of a good RBS system in operation is in the Oracle Database. In the database various permissions to database objects are automatically defined by the database (e.g. select from aTable, execute stored procedure). Oracle leaves it to the Database Administrator to define their own roles and users and build the association. So one can assume that underneath the hood Oracle is actually checking whether the User has the permission rather than being concerned with how they got it!

I'm not going to be overly critical though, as Microsoft have provided the IPrinciple and IIdentity interfaces to help us build custom implementations of the RBS. I will be using these to develop a permission based checking security system. If I managed to develop this in a non-product specific way I'll post it on this blog in the near future.



* This of course is only my opinion ;)