This discussion is archived
6 Replies Latest reply: Nov 14, 2012 8:33 AM by 932936 RSS

Import Oracle procedure to EntityFramework with a sys_refcursor output para

967262 Newbie
Currently Being Moderated
I've been struggling for a while with this, im following [this guide|http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm#t3]

This are the steps I have taken so far:

Create new console project

Add new EntityModel

Import "Test" Stored Procedure into Entity Model, sp looks like this:

CREATE OR REPLACE PROCEDURE CTACTE_WEB.TEST(P_SALIDA OUT NUMBER,
P_CURSOR1 OUT sys_refcursor,
P_CURSOR2 OUT sys_refcursor) IS
BEGIN
P_SALIDA := 15;
OPEN P_CURSOR1 FOR
SELECT 125.72 "DECIMAL", 150 "INTEGER", 'JUST PASSING BY' "TEXT" FROM DUAL;

OPEN P_CURSOR2 FOR
SELECT 'JUST SOME TEXT' "TEXT" FROM DUAL;

END TEST;
/

Add this to app.config:

<add name="CTACTE_WEB.TEST.RefCursor.P_CURSOR1" value="implicitRefCursor bindinfo='mode=Output'" />
<add name="CTACTE_WEB.TEST.RefCursorMetaData.P_CURSOR1.Column.0" value="implicitRefCursor metadata='ColumnName=DECIMAL;NATIVEDATATYPE=Number;ProviderType=Decimal'" />
<add name="CTACTE_WEB.TEST.RefCursorMetaData.P_CURSOR1.Column.1" value="implicitRefCursor metadata='ColumnName=INTEGER;NATIVEDATATYPE=Number;ProviderType=Int32'" />
<add name="CTACTE_WEB.TEST.RefCursorMetaData.P_CURSOR1.Column.2" value="implicitRefCursor metadata='ColumnName=TEXT;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
<add name="CTACTE_WEB.TEST.RefCursor.P_CURSOR2" value="implicitRefCursor bindinfo='mode=Output'" />
<add name="CTACTE_WEB.TEST.RefCursorMetaData.P_CURSOR2.Column.0" value="implicitRefCursor metadata='ColumnName=TEXT;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />

Then I try to import function from the Entity Model I created before but when I click obtain column information after selecting complex type nothing happens, it doesn't show the cursor like the obe tutorial.

Any help would be appreciated.

Thanks

EDIT:
I also asked this question on StackOverflow, here: http://stackoverflow.com/questions/12806485/import-oracle-procedure-to-entityframework-with-a-sys-refcursor-output-parameter

Edited by: 964259 on 09-oct-2012 13:55

Edited by: 964259 on 09-oct-2012 13:56

Edited by: 964259 on 09-oct-2012 13:56

Edited by: 964259 on 09-oct-2012 14:01
  • 1. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    15208 Explorer
    Currently Being Moderated
    Can you exit Visual Studio, reopen the solution/project, and try again?

    Someone tried the OBE tutorial and encountered the same issue.
    The trick is "exit VS and try again".
  • 2. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    967262 Newbie
    Currently Being Moderated
    ******I restarted VS several times yesterday knowing how buggy it is, but everytime was the same. But now when I tried to import the function, after I click the Obtain Column Information button, the dialog just closes and nothing else happens. I guess it crashes, but I have no idea what's wrong because theres no event log or message at all.

    Any idea what's happening now?****

    Ok, I got it working, I just removed the procedure and added it again and I imported the function now, and it appears to work.

    But now I have a problem, the function returns a collection of the complex type created(P_CURSOR1) but how do I obtain the value from P_CURSOR2 ?

    I thought the complex type would be passed as a parameter, but instead its the return type of the function, any idea how to fix this?

    Edited by: 964259 on 10-oct-2012 14:20

    Edited by: 964259 on 10-oct-2012 14:45

    Edited by: 964259 on 10-oct-2012 14:45

    Edited by: 964259 on 10-oct-2012 14:46
  • 3. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    15208 Explorer
    Currently Being Moderated
    It works for me with following. Please give it a try. I hope it helps.

    1. Use SQL*Plus and connect as scott/tiger@<tns_alias> to create the SP, TEST.

    CREATE OR REPLACE PROCEDURE TEST(P_SALIDA OUT NUMBER,
    P_CURSOR1 OUT sys_refcursor,
    P_CURSOR2 OUT sys_refcursor) IS
    BEGIN
    P_SALIDA := 15;
    OPEN P_CURSOR1 FOR
    SELECT 125.72 "DECIMAL", 150 "INTEGER", 'JUST PASSING BY' "TEXT" FROM DUAL;

    OPEN P_CURSOR2 FOR
    SELECT 'JUST SOME TEXT' "TEXT" FROM DUAL;

    END TEST;
    /

    2. My App.Config: (Either way works because the column names happen to be all in upper case. If it's mixed case, I think the one being commented out should be used.
    * After posting, I just realized that all double quotes around column names have been converted. They should be ampersand+quot+semicolon. *)

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    <connectionStrings>
    <add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=Oracle.DataAccess.Client;provider connection string=&quot;DATA SOURCE=inst1;PASSWORD=tiger;USER ID=SCOTT&quot;" providerName="System.Data.EntityClient" />
    </connectionStrings>
    <oracle.dataaccess.client>
    <settings>
    <add name="SCOTT.TEST.RefCursor.P_CURSOR1" value="implicitRefCursor bindinfo='mode=Output'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.0" value="implicitRefCursor metadata='ColumnName=DECIMAL;NATIVEDATATYPE=Number;ProviderType=Decimal'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.1" value="implicitRefCursor metadata='ColumnName=INTEGER;NATIVEDATATYPE=Number;ProviderType=Int32'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.2" value="implicitRefCursor metadata='ColumnName=TEXT;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
    <add name="SCOTT.TEST.RefCursor.P_CURSOR2" value="implicitRefCursor bindinfo='mode=Output'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR2.Column.0" value="implicitRefCursor metadata='ColumnName=TEXT;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
    <!--
    <add name="SCOTT.TEST.RefCursor.P_CURSOR1" value="implicitRefCursor bindinfo='mode=Output'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.0" value="implicitRefCursor metadata='ColumnName=&quot;DECIMAL&quot;;NATIVEDATATYPE=Number;ProviderType=Decimal'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.1" value="implicitRefCursor metadata='ColumnName=&quot;INTEGER&quot;;NATIVEDATATYPE=Number;ProviderType=Int32'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR1.Column.2" value="implicitRefCursor metadata='ColumnName=&quot;TEXT&quot;;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
    <add name="SCOTT.TEST.RefCursor.P_CURSOR2" value="implicitRefCursor bindinfo='mode=Output'" />
    <add name="SCOTT.TEST.RefCursorMetaData.P_CURSOR2.Column.0" value="implicitRefCursor metadata='ColumnName=&quot;TEXT&quot;;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
    -->
    </settings>
    </oracle.dataaccess.client>
    </configuration>

    Edited by: shsu on Oct 10, 2012 2:41 PM
  • 4. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    967262 Newbie
    Currently Being Moderated
    I got it working as well, I edited my last post so you can see the issue I'm experiencing now.

    Any idea how to pass the Complex Type as a parameter instead of return value?
  • 5. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    967262 Newbie
    Currently Being Moderated
    I agree with the post above, but I couldnt get the StoreGeneratedPattern="Identity" or StoreGeneratedPattern="Computed".

    Edited by: AndreiDvoynos on 14-nov-2012 6:09
  • 6. Re: Import Oracle procedure to EntityFramework with a sys_refcursor output para
    932936 Newbie
    Currently Being Moderated
    You may need to manually edit the .edmx file. I know with older versions of EF, that was the case.

    Also, make sure your triggers are setup properly and enabled in Oracle.

    EDIT: I should also point out that in order for this solution to work, there should be NO SPs on any add, modify, or delete actions for the table you're trying to write to. I'm letting EF figure the add/modify/delete stuff out on its own.

    Edited by: 929933 on Nov 14, 2012 8:30 AM