Cant Map SYS_REFCURSOR in Entity Framework

Question

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

My stored procedure 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 object and importing the function, the SYS_REFCURSOR OUT parameter is not found in the imported function to retrieve the result set.

please help me with this. Without receiving the OUT parameter, I cannot access the result set obtained by the stored procedure

Answer 1

Now the oracle data provider allows such operations without much fuss. It is too detailed to answer here. Added quick read. Please follow the link below.

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

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

For some scenarios, such as when an updated REF CURSOR or Entity Framework is used, developers must define a REF CURSOR scheme so that the application can bind the implicit REF CURSOR. Entity Framework applications use the REF CURSOR implicit binding to create complex types from REF CURSOR data. Applications must provide the REF CURSOR binding and metadata information in app.config, web.config, or machine.config.NET configuration file.

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

When using the Entity Framework, importing functions may return an implicitly linked REF CURSOR. REF CURSOR can be returned as a collection of complex or entity types. To return a complex collection type, the .NET configuration file must define the REF CURSOR binding and metadata. To return a collection of object types, only binding information needs to be defined in .NET. configuration file.

Find full information here.

Answer 2

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

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 value of schema.storedproc with yours . For example, ACCOUNTING.GET_EMPLOYEES. And replace the column column_name_here . For example, EMP_ID. Also remove the corner brackets.

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

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

Answer 3

To use the import function with EF, you need to do the following:

1) The first OUT SYS_REFCURSOR found in the parameter list will be the output of the Entity function. Therefore, you may need to wrap your SP or Function with a new SP that contains this OUT SYS_REFCURSOR

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

You can view a step-by-step guide on this issue: 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 information about this topic, see the "Entity Framework" section of the Oracle Developer Tools online help for Visual Studio.

Answer 4

I had a similar problem.

After much research, I found out that Oracle support for entity infrastructure is not yet designed to use complex data types as the return type from a stored procedure. It can be done; but it’s like touching your nose around your head. Therefore, if you use EF for convenience, it is best to avoid it and use stored procedures instead.

Finally, I decided to go with EF to create, update, and delete (since I need to implement a control chain) and a stored procedure to return result sets.

Answer 5

I don’t think that the oracle path with Entity infrastructure is so good if you want to execute stored procedures and functions with complex return types and binary fields.