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…

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,…

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…