Can Entity Framework 5 map an Oracle function returning a nested table?

Question

I have an Oracle function that returns a nested table of an Oracle user defined type.

If I call the function from TOAD or SQL developer with a syntax like this:

SELECT * FROM TABLE(MYFUNCTION('SOME_STRING_PARAM'))

I get the expected result.

I am even able to create .NET classes from Visual Studio Server Explorer wizard for the user defined types.

However when I try to create import function from the Entity Framework model wizard I don't get a proper option to define the returned type.

Am I trying to do something not supported?

Answer 1

Import function doesn't behave the way you might expect with ODP.NET. It assumes that the first SYS_REFCURSOR it finds as an OUT parameter is the "return value" of the Entity Function.

Therefore, you will need to wrap your function with a stored procedure that places the nested table data in a REF CURSOR.

Then you need to learn about the required metadata in the config file for this REF CURSOR. In the online help for Oracle Developer Tools for Visual Studio, read the Entity Framework section for more information.

Here is a walkthrough that shows how to set up an Import Function: https://apex.oracle.com/pls/apex/f?p=44785:24:106387346683725:::24:P24_CONTENT_ID,P24_PROD_SECTION_GRP_ID,P24_PREV_PAGE:10068,,24

Due to the complexity of all this, I don't advise using Imported Functions with non scalar Oracle Stored Functions or Procedures return values unless you absolutely have to.