Get Oracle stored procedure object output in NHibernate
I’ve wasted view hours on this so I think I share in case you have a similar problem.
We are working with Oracle Database and .NET project using NHibernate as a object-relation mapper. We have a function in Oracle that returns an Oracle Object. Objects in Oracle are user defined types. They are defined like this:
CREATE OR REPLACE TYPE Foo as object(
Value1 number,
Value2 date,
Value3 varchar(10)
);
And now Oracle Function with signature like this:
CREATE OR REPLACE FUNCTION Bar (id int)
return Foo pipelined
This function returns a Foo object and gets the id key from a table VT (some table). The 3 values from Foo object should extend an NHibernate VT object. So I want to have 3 new properties in the NHibernate entity.
Sound easy? Well it wasn’t for me. At first how to get the values? You can get an instance of IDBConnection down from NHibernate context and fight your way with ordinary ADO.NET (which is not so easy for functions with user defined return values – at least I don’t know how to do this). But you can use a TABLE() function of Oracle. You can issue this SQL select statement:
select * from Table(Bar(1))
It will return a result set as a table you can select from. Cool stuff! But to use it you have to know it exists 😉
Ok, now the easy part. I though. I define a property in NHibernate with a formula attribute and I will map this on a property in my class. Something like this:
<property name="Value1" type="Int64" formula="(select Value1 from Table(Bar(id)))"> </property>
Not so easy! If you do this you will get the Oracle exception. NHibernate thinks that Value1 from your formula is a part of NHibernate object and gives it a suffix. It is transformed to something like that:
select _vt0.Value1 from Table(Bar(4711))
_vt0 being the object alias. It is obviously not the case. There is no Value1 field physically in the database. There is a custom made property with SQL formula in NHibernate. As you can se the id was translated to actual key in the table.
The general rule for the formula attribute is everything tat NHibernate does not understand is send direct to database as is. The trick was to make NHibernate think it does not understands the statement completely.
The answer is to enclose Value1 with quotation marks. To achieve this you will have to use the XML escape marks. Just like this:
<property name="Value1" type="Int64" formula="(select "Value1" from Table(Bar(id)))">