Get Oracle stored procedure object output in NHibernate

September 5, 2009 on 5:24 pm | In Oracle | No Comments

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)))">

A lot of tiny puzzles to put together. But it works!

SQL from Linq to Entity Framework

November 27, 2008 on 8:49 am | In Oracle | No Comments

Problem: how do I tell what SQL does Linq to Entity Framework generates?

If you are using SQL Server its easy. You start the SQL Profiler and you are done. If you are using Oracle the task is not so easy. You can:

1. start the trace on the Oracle server and get the huge barely readable text file physically on the server and use TKPROF

2. look up the lat SQL query from session using the Enterprise Manager-Konsole

3. buy a 3rd party tool like FlexTracer to trace the OCI communication.

Well yeah! Not fun.

But there is another option inside Entity Framework itself! It is not quite obvious, so it took me a little to figure it out:

((System.Data.Objects.ObjectQuery)Query).ToTraceString()

where Query is something like System.Linq.IQueryable<T>.

Oracle Hierarchical Queries

July 31, 2007 on 11:14 pm | In Oracle | 1 Comment

It is rather common developer task to draw a hierarchical data on the screen. You have tons of controls that draw a tree like structures. But did you know that when you are using Oracle you are able to select hierarchical data direct from database? How?

It is rather simple. As an example we will draw a tree of an organization structure. Lets create a simple table with employees:

CREATE TABLE EMP
(
ID NUMBER(2,0) NOT NULL,
REFID NUMBER(2,0) NULL,
NAME VARCHAR2(40 BYTE) NULL
)

Its time to fill the table with data:


INSERT INTO EMP(ID, REFID, NAME)
VALUES(1, 1, 'John');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(2, 1, 'Mary');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(3, 1, 'Tom');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(4, 2, 'Michael');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(5, 5, 'Richard');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(6, 6, 'Andy');
INSERT INTO EMP(ID, REFID, NAME)
VALUES(7, 6, 'Helen');

Guess what? That’s almost done! We have to write only one select statement to get the hierarchical data:

SELECT id, refid, name, LEVEL, SYS_CONNECT_BY_PATH(name,'/') path
FROM emp start WITH id = refid
connect BY nocycle prior id = refid

Voila! You should get something like that:

Peace of cake! Right?

Originally published at Sunday, February 25, 2007

Powered by WordPress with Pool theme design by Borja Fernandez.
Text © Marcin Kawalerowicz. Hosting CODEFUSION.