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