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
Accepted Answer
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
Popular Answer
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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
