Cant Map SYS_REFCURSOR in Entity Framework

Question

I am trying to access a Stored procedure on Oracle 11g through Entity Framework. I can access stored procedures which returns scalars and those returns correct value. But when using SYS_REFCURSOR for returning a result set, the OUT parameter is not detected on function import.

My stored procedure is as below

create or replace PROCEDURE "GetAllClientNames" (
  "ID" IN NUMBER, 
  "SAL" IN NUMBER, 
  "EMP_CURSOR" OUT SYS_REFCURSOR) IS

BEGIN 


    OPEN EMP_CURSOR FOR SELECT FIRSTNAME FROM CLIENTS;

END;

But when updating the entity and does the function import, the SYS_REFCURSOR OUT parameter is not detected in imported function to retrieve result set.

please help me out on this.Without getting OUT parameter I can't access the result set retrieved by stored procedure

Answer 1

A complex type such as a ref cursor can be returned from an Oracle stored procedure using Entity Framework; it just requires a bit of extra configuration. You must add the proper XML to the config file to identify the refcursor being returned and the data types of the columns.

Example:

<oracle.dataaccess.client>
<settings>
  <add name="schema.storedproc.RefCursor.ref_cursor_param_name" value="implicitRefCursor bindinfo='mode=Output'" />
  <add name="schema.storedproc.RefCursorMetaData.OUT_REF_CUR.Column.0" value="implicitRefCursor metadata='ColumnName=<column_name_here>;BaseColumnName=<column_name_here>;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
  <add name="schema.storedproc.RefCursorMetaData.OUT_REF_CUR.Column.1" value="implicitRefCursor metadata='ColumnName=<column_name_here>;NATIVEDATATYPE=Date;ProviderType=Date'" />
  <add name="schema.storedproc.RefCursorMetaData.OUT_REF_CUR.Column.2" value="implicitRefCursor metadata='ColumnName=<column_name_here>;NATIVEDATATYPE=Number;ProviderType=Int32'" />
</settings>
</oracle.dataaccess.client>

Just replace the schema.storedproc value with yours. Such as ACCOUNTING.GET_EMPLOYEES. And replace the column_name_here with your column. Such as EMP_ID. Remove the angle brackets too.

Here is a full article for reference: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm#t3

Documentation on XML entries: http://docs.oracle.com/cd/E11882_01/win.112/e18754/featImplRefCursor.htm#ODPNT319

Answer 2

Now oracle data provider allows this kind of operations without much hustle.Its too elaborate to answer here. A quick read is added. Please follow the below link for detailed information.

ODP.NET 11g Release 2 (11.2.0.3.0), and higher, enables applications to run stored procedures with REF CURSOR parameters without using explicit binding for these parameters in the .NET code.

For a read-only result set, such as a REF CURSOR using OracleDataReader, REF CURSOR schema information is retrieved automatically.

For some scenarios, such as when updateable REF CURSORs or Entity Framework is used, developers need to define the REF CURSOR schema information so that the application can bind the implicit REF CURSOR. Entity Framework applications use implicit REF CURSOR binding to instantiate complex types from REF CURSOR data. Applications must specify REF CURSOR bind and metadata information in the app.config, web.config, or machine.config .NET configuration file.

The attributes supplied in the .NET configuration file are also used when the application requests for schema information from the OracleDataReader object that represents a REF CURSOR. This means that for REF CURSORs that are created using a SELECT from a single table, the application can update that table through the use of OracleDataAdapter and OracleCommandBuilder.

When using the Entity Framework, function imports can return an implicitly-bound REF CURSOR. The REF CURSOR can be returned as a collection of complex types or entity types. To return a complex type collection, the .NET configuration file needs to define the REF CURSOR bind and metadata information. To return an entity type collection, only the bind information needs to be defined in the .NET configuration file.

Find complete information here

Answer 3

To use the Import Function with EF you need to do the following:

1) The first OUT SYS_REFCURSOR encountered in your parameter list will become the output of the Entity Function. So you may need to wrap your SP or Function with a new SP that contains this OUT SYS_REFCURSOR

2) You need to set up some metadata about this cursor inside your app.config or web.config. This is automated by the Run Stored Procedure dialog in Server Explorer.

You can view a step by step walkthrough of this here: https://apex.oracle.com/pls/apex/f?p=44785:24:6479673193812:::24:P24_CONTENT_ID,P24_PROD_SECTION_GRP_ID,P24_PREV_PAGE:10068,,24

For more documentation on this subject, see the Entity Framework section of the Oracle Developer Tools for Visual Studio Online Help.

Answer 4

I had a similar problem.

After a long research I found out that Oracle support for entity framework is not yet evolved for using complex data types as return type from stored procedure. It can be done; but its like touching nose around your head. So if you are using EF for ease then better avoid it and use Stored procedures directly instead.

Finally I decided to go with EF for Create, Update and Delete (since I have to implement Audit trail) and stored procedure for returning result sets.