This discussion is archived
7 Replies Latest reply: Jun 3, 2013 2:01 AM by 1012415 RSS

Oracle Procedure returning Ref Cursor in Entity Framework 4

645504 Newbie
Currently Being Moderated
Hi experts,

I am very new to Entity Frame 4.0 ODP.net I am trying to get dataset from my Oracle Procedure having out parameter type Ref Cursor, in ADO.Net I did like : -

// get the OracleRefCursor from the output parameter
OracleRefCursor refcur = (OracleRefCursor)cmd.Parameters["cur_DataSet"].Value;

// get the DataReader using the OracleRefCursor
OracleDataReader dr = refcur.GetDataReader();

Now in Entity Framework I map all my tables and store procedures through wizard but store procedures showing only 'IN' parameter list, I also use 'Add function Import' to assign return type as Entity, but no luck ... please help
  • 1. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Make sure that you read the ODP.NET documentation about stored procedures. There are special web.config/app.config entries you must make.

    Here's the relevant section from the ODT README about Function Imports:

    Steps to add a function to the Entity Model using Add Function Import Dialog

    1. Generate an Entity Model from an Oracle Database. In the Entity Data Model Wizard or Update Wizard, when choosing your database objects, select some stored procedures (Oracle Stored Functions are not supported). To add function imports that return scalars, complex types, or entities, you will need to select stored procedures that return a ref cursor. Supported Stored procedures include procedures and package methods that do not have a return value, but may have OUT or IN OUT parameters. A ref cursor can be returned as an OUT or IN OUT parameter. Note: If the procedure returns multiple ref cursors, only the first one will be used as the return value.

    2. To add function imports that return scalars, complex types, or entities, configure the ref cursor information in the app.config.

    For a web project, configure the ref cursor information in the web.config. For more information on configuring ref cursor information, please see the ODP.NET documentation.

    3. Right-click the Entity Designer.

    4. Select "Add", then "Function Import".

    5. If the stored procedure returns a ref cursor, you can create a new complex type by selecting "Get Column Information" then selecting "Create New Complex Type".

    6. Select the type of collection returned and give the function import a name.

    7. Clicking on OK will add the function import to the conceptual model.

    Here's the a sample .NET config file for setting up the output implicit result set. This is for a stored procedure in the HR schema called UPDATESALARY. It returns a REF Cursor called NEW_SALARY with two columns: FIRST_NAME and SALARY from the HR.EMPLOYEES table. Once you include the information below, you can click on "Get Column Information" and you will see these columns. Oracle will publish an OBE that demonstrates Function Imports more clearly.

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    <connectionStrings>
    <add name="HREntities" connectionString="<long connectiong string>" providerName="System.Data.EntityClient" /></connectionStrings>
    <oracle.dataaccess.client>
    <settings>
    <add name="&quot;HR&quot;.&quot;UPDATESALARY&quot;.RefCursor.NEW_SALARY" value="implicitRefCursor bindinfo='mode=Output'" />
    <add name="&quot;HR&quot;.&quot;UPDATESALARY&quot;.RefCursorMetaData.NEW_SALARY.Column.0" value="implicitRefCursor metadata='ColumnName=FIRST_NAME;BaseColumnName=FIRST_NAME;BaseSchemaName=HR;BaseTableName=EMPLOYEES;NATIVE_DATA_TYPE=Varchar2;ProviderType=Varchar2;PROVIDER_DB_TYPE=String;ColumnSize=20'" />
    <add name="&quot;HR&quot;.&quot;UPDATESALARY&quot;.RefCursorMetaData.NEW_SALARY.Column.1" value="implicitRefCursor metadata='ColumnName=SALARY;BaseColumnName=SALARY;BaseSchemaName=HR;BaseTableName=EMPLOYEES;NATIVE_DATA_TYPE=Number;ProviderType=Int32;DataType=System.Int32;ColumnSize=8'" />
    </settings>
    </oracle.dataaccess.client>

    </configuration>
  • 2. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    BradBueche Newbie
    Currently Being Moderated
    I also would like to see an OBE on this.

    I have a reporting package/SP that is the 'engine' for my ad-hoc reporting web page. The SP returns a ref_cursor. The ref_cursor will always contain multiple fields and will sometimes contain hundreds to thousands of records.

    So I would like to see an OBE walk through of using all the GUI tools to work with SP's that take input parameters and returning a ref_cursor. My environment is c# and asp.net. I think I have enough code examples to do it in straight c# but oracle needs to have OBE's for all GUI building functions and especially for SP's (i.e. starting with the simple drag and drop gridview setup). I'd also like to see all of these use a connection string in the web.config. If oracle wants people to feel that its committed to supporting .NET then we need a lot more OBE's out there. The layout of the OBE's with all the extensive screen shots is great. Keep that up.
  • 3. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    Christian.Shay - Oracle Pro
    Currently Being Moderated
    Brad Bueche wrote:
    I also would like to see an OBE on this.

    I have a reporting package/SP that is the 'engine' for my ad-hoc reporting web page. The SP returns a ref_cursor. The ref_cursor will always contain multiple fields and will sometimes contain hundreds to thousands of records.

    So I would like to see an OBE walk through of using all the GUI tools to work with SP's that take input parameters and returning a ref_cursor. My environment is c# and asp.net. I think I have enough code examples to do it in straight c# but oracle needs to have OBE's for all GUI building functions and especially for SP's (i.e. starting with the simple drag and drop gridview setup). I'd also like to see all of these use a connection string in the web.config. If oracle wants people to feel that its committed to supporting .NET then we need a lot more OBE's out there. The layout of the OBE's with all the extensive screen shots is great. Keep that up.
    You just bumped a very old thread. In the future, please open a new one rather than bumping old ones..

    Here's the latest OBE that shows how to do this:
    http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm

    The rest of the OBEs:
    http://apex.oracle.com/pls/apex/f?p=44785:24:0::NO:24:P24_CONTENT_ID,P24_PREV_PAGE:5178,2
  • 4. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    946480 Newbie
    Currently Being Moderated
    No offense, but one of the main reasons I use LINQToSql, is that I can throw a a stored proc at it and it can determine the correct input and return types from that proc.

    But using the Oracle ODT, having to go into the config to tell it what the schema is, that it should already be able to obtain from a proc/func, is ludicrous. Not to mention a bit dangerous since you can end up with mismatched sizes or types..

    Give us a development tool we actually need, not what you think we need..

    If you are unsure about what i mean, ask yourself this:
    Why would I need to add all of the information about the return schema into the app.config about a stored proc that this tool is actually supposed to provide???

    I have an OUT ref cursor with multiple aliased columns from multiple tables in a somewhat complicated union query. Now i have to go back and validate those types manually, since I wasn't the one that created the package..

    grrr..

    Sigh.. Sorry, I'm grumpy and frustrated that this tool, really isn't a tool..
  • 5. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    946972 Newbie
    Currently Being Moderated
    "No offense, but one of the main reasons I use LINQToSql, is that I can throw a a stored proc at it and it can determine the correct input and return types from that proc.

    But using the Oracle ODT, having to go into the config to tell it what the schema is, that it should already be able to obtain from a proc/func, is ludicrous. Not to mention a bit dangerous since you can end up with mismatched sizes or types..

    Give us a development tool we actually need, not what you think we need..

    If you are unsure about what i mean, ask yourself this:
    Why would I need to add all of the information about the return schema into the app.config about a stored proc that this tool is actually supposed to provide???

    I have an OUT ref cursor with multiple aliased columns from multiple tables in a somewhat complicated union query. Now i have to go back and validate those types manually, since I wasn't the one that created the package..

    grrr..

    Sigh.. Sorry, I'm grumpy and frustrated that this tool, really isn't a tool.."

    Well said - totally agree. The current approach is so open to typos and wastes so much time. Just started a new job first time that I've used Oracle in 15 years - from a programmers point of view it doesn't seem to have progressed at all.
  • 6. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    944336 Newbie
    Currently Being Moderated
    Completely agree. We've had to give up using Stored Procedures using Entity because of the configuration nightmare. It would be awesome to be able to add the stored procedure and import all of the columns like all the other database solutions allow us to do.

    Though it's been 7+ months since the last update so I wouldn't expect this to happen any time soon.
  • 7. Re: Oracle Procedure returning Ref Cursor in Entity Framework 4
    1012415 Newbie
    Currently Being Moderated
    I found out, that your example is a bit misleading as for now.

    "NATIVE_DATA_TYPE" metadata parameter should be "NativeDataType", according to http://docs.oracle.com/cd/E11882_01/win.112/e18754/featImplRefCursor.htm

    The link provides list of available parameters and some examples on ref cursor binding to EF.

    Edited by: 1009412 on Jun 3, 2013 2:00 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points