6 Replies Latest reply: Nov 14, 2012 10:33 AM by 932936 RSS

    Import Oracle procedure to EntityFramework with a sys_refcursor output para

    967262
      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
          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
            ******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
              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
                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
                  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
                    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