Oracle-specific issues in NHibernate

After managing to get NHibernate connect to an Oracle database, the brave developer needs to cope with two specialties of Oracle databases, namely handling Boolean values, and Identity columns generated by INSERT triggers.

Boolean values

Oracle databases do not implement a Boolean data type (while PL/SQL does), as opposed to MS SQL Server’s data type Bit. Instead, the standard way is to have a CHAR(1) with allowed values “Y” and “N”:

CREATE TABLE "FOO"
(
  ...
  "IS_ACTIVE" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE,
  ...
  CONSTRAINT "CHK_FOO_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N')) ENABLE,
  ...
)

To access such a column in NHibernate in a type-safe way (i.e. C# bool), you need to map it to the YesNoType. In Fluent, this is done using .Map().CustomType<>() in the ClassMap constructor:

  Map(x => x.IsActive, "IS_ACTIVE").CustomType<YesNoType>();

Trigger-generated Identities

Oracle databases do not implement an Auto-Increment or Identity mechanism. Instead, sequences are used to provide incremental values for primary key columns.

The sequence values are either provided in the INSERT statement reading the sequence’s NEXTVAL property, or generated by a BEFORE INSERT FOR EACH ROW trigger.

If you declare a primary key column in Fluent using the Id() method, NHibernate will generate an INSERT statement like this:

INSERT INTO cpa.FOO (OID, ID, IS_ACTIVE)
VALUES (cpa.hibernate_sequence.nextval, ?, ?)
returning OID into :nhIdOutParam

implementing the first variant. The trigger-generated method is supported by specifying GeneratedBy.Custom(“trigger-identity”) (works in Fluent build 636):

Id(x => x.Oid).GeneratedBy.Custom("trigger-identity");

The trigger-identity generator will generate an INSERT statement like this:

INSERT INTO cpa.FOO (ID, DESCRIPTION) 
VALUES (?, ?) 
returning OID into :nhIdOutParam

See this blog for other generators in NHibernate.

Not Oracle-specific, but probably interesting:

Table name aliases

Table name aliases are specified using the Table(“tablename”) method in the ClassMap constructor.

Column name aliases

Column name aliases can be specified by passing a second argument to the Id(), Map(), and References() methods:

Table("FOO");
Id(x => x.Oid, "OID").GeneratedBy.Custom("trigger-identity");
Map(x => x.Id, "ID");
References(x => x.Bar, "BAR_OID");
Advertisements

One Response to Oracle-specific issues in NHibernate

  1. Max says:

    Why nhIdOutParam is always null ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: