Oracle vs .NET int

One of the problems of using .NET with Oracle is the type conversion of integers through the ODAC component.  Basically, the problem comes about because the ODAC component has no idea which .NET type to use when returning a value in a NUMBER field.... so it panics and just tends to return everything as a double (remember that the NUMBER field can hold decimal values!).

Oracle have also provided the INTEGER data type, which is really just a short-cut to get a NUMBER(32) field.  The best solution I can find to this is to define any columns that are being mapped to a .NET int as NUMBER(9, 0).   This will always ensure that the field will hold data that is compatible with being held in a .NET int.  

Oracle XmlType and ODAC .NET provider

I have been having some issues today with a method that inserted Xml into an XmlType field in an Oracle database. It had been working fine, but recently the Xml had grew in size considerably. I was now getting the following Oracle error returned from the ODAC component:

ORA-01461: can bind a LONG value only for insert into a LONG column

The Insert Query I was running looked a bit like this....

INSERT INTO myTable

(anId, myXmlField)

VALUES

(:anId, :myXmlField);

When I ran the query I had two parameters, the second (:myXmlField) was an Xml string. It was this that caused the issue. Thanks to this post by Stuart Campbell I found out that in order to pass a long string value to an XmlType field you must:

1) Set the parameter type of the xml parameter (:myXmlField) to Clob;

2) Use the XmlType() call in the query:

INSERT INTO myTable

(anId, myXmlField)

VALUES

(:anId, XmlType(:myXmlField));

This did resolve the issue, but in the end I ended up changing the XmlType field into a Clob! This is because 1)I didn't need to xPath the Xml in the database and 2) I couldn't use XmlType implementations in our SqlServer data access layer as we have to support earlier versions of this database.

Stored procedure maintenance

One of the nice features of Oracle stored procedures, functions and packages is that you can create or recreate the database side code in the same statement. The advantage of this is that during upgrades/patches you can run ALL database code scripts again, which ensures nothing is missed - and also that the changes you have made haven't caused breaking changes in another procedure!

For example, the following will create or replace the following security package header..

CREATE OR REPLACE PACKAGE PKG_SECURITY
AUTHID DEFINER
AS
/*  ------------------------------------------------------
    Adds authentication for specified user to system    
    ------------------------------------------------------ */
PROCEDURE proc_AddUserAuthentication(
  userId                IN USERSECURITY.USER_FK%TYPE,
  encryptedPassword     IN USERSECURITY.PASSWORD%TYPE,
  saltValue             IN USERSALT.SALT%TYPE,
  auditUserId           IN USERSECURITY.AUDITUSERID%TYPE);
END;
/

My recent digging around has revealed that you can also mimic this CREATE or REPLACE functionality in MySQL and Sql Server.

In MySQL you can call the DROP PROCEDURE IF EXISTS method....

DROP PROCEDURE IF EXISTS premierenvoy.proc_AddUserAuthentication;
CREATE
DEFINER = CURRENT_USER
PROCEDURE proc_AddUserAuthentication(
............

...and in Sql Server you can use the OBJECT_ID function and check whether the returning value is not null to determine whether to drop the procedure.

IF OBJECT_ID('proc_AddUserAuthentication') IS NOT NULL
DROP PROCEDURE proc_AddUserAuthentication;
GO
CREATE PROCEDURE proc_AddUserAuthentication(
............

Creating copies of tables in Oracle, SqlServer and MySQL

As the sub-title of my blog suggests, I am from an Oracle background. But I also have dealings with SQL Server and MySQL databases. In fact the latest project I'm working on is going to have to work with all three databases!

One issue I have encountered recently is how to create copies of tables in SQL Server and MySQL. In Oracle you would use the AS keyword in the CREATE table statement and select from the table that is being copied. For example:

CREATE TABLE myCopyTable AS
SELECT * FROM myOriginalTable;

You can add a WHERE clause to restrict the records that are included in the copy. Making this WHERE clause into a condition that is not achievable (such as WHERE 1 =2) will result in a copy of the table without the data.
Note: This copy does not include any indexes/constraints on the original table.

In MySQL it is similar but you do not need the AS keyword. For example:

CREATE TABLE myCopyTable
SELECT * FROM myOriginalTable;

Alternatively you can actually create an exact copy including all constraints and indexes. This is achieved by using the LIKE keyword.

CREATE TABLE myCopyTable
LIKE myOriginalTable;

Note: This won't take the data across, you would have to insert that afterwards
(e.g. INSERT INTO myCopyTable SELECT * FROM myOriginalTable;)

Finally, SQL Server... which wins the prize for the strangest implementation. You actually use a SELECT command to create your new table :o

SELECT *
INTO myCopyTable
FROM myOriginalTable;

I really don't like the way this is implemented as there is no mention of a table being created in the SQL statement. Even though this is what the user is trying to achieve.
Anyhow, no point complaining at least they provided a way of copying a table :)

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;

.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 ;)