3 Replies Latest reply: Oct 12, 2012 12:33 PM by 967262 RSS

    Oracle Proc in edmx via add function import is not working

    926762
      I have a mandate to call a external oracle package from my local oracle db proc using Entity Framework. I have created a synonym for that. When I am trying to polpulate the oracle procedure data in my mvc screen using odp.net. The screen is closing automatically when I am trying to retive the column info by clicking "get column information" under "Add Function Import" in model browser.


      Following is my code snipet

      1. created a oracle stored proc to call the external proc

      CREATE OR REPLACE PROCEDURE USP_GET_DETAILS
      (
      IN_ID IN NUMBER,
      OUT_RESULT OUT SYS_REFCURSOR
      )
      IS

      OUT_FED_TAX_ID_NO VARCHAR2(50);
      OUT_PARTY_ID_NO NUMBER;
      OUT_PARTY_TYP_CD NUMBER;
      BEGIN

      PKG_GET_DETAILS.USP_GET_DETAILS_INFO (IN_ID, OUT_FED_TAX_ID_NO, OUT_PARTY_ID_NO);

      OPEN OUT_RESULT FOR
           SELECT OUT_FED_TAX_ID_NO, OUT_PARTY_ID_NO FROM DUAL;

      END USP_GET_DETAILS;



      2. added the proc "USP_GET_DETAILS" using "Update model using database" in my edmx file

      3. Now proc has been added to my edmx file under SSDL

      <Function Name="USP_GET_DETAILS" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="BCS_OWNER">
      <Parameter Name="IN_ID" Type="number" Mode="In" />
      </Function>

      4. added following code to my web.config file

      <oracle.dataaccess.client>
      <settings>

      <add name="BCS_OWNER.USP_GET_DETAILS.RefCursor.OUT_RESULT" value="implicitRefCursor bindinfo='mode=Output'" />
      <add name="BCS_OWNER.USP_GET_DETAILS.RefCursorMetaData.OUT_RESULT.Column.0" value="implicitRefCursor metadata='ColumnName=OUT_FED_TAX_ID_NO;NATIVEDATATYPE=Varchar2;ProviderType=Varchar2'" />
      <add name="BCS_OWNER.USP_GET_DETAILS.RefCursorMetaData.OUT_RESULT.Column.1" value="implicitRefCursor metadata='ColumnName=OUT_PARTY_ID_NO;NATIVEDATATYPE=Number;ProviderType=Decimal'" />
      </settings>
      </oracle.dataaccess.client>

      5. underModel Browser try to add the procedure under "Add Import Function", select "Complex Type". when i am clicking on "Get column information" button, the screen blank for some time and then auto closing the screen without displaying any column info. I would like to know if any thing wrong i am doing on my steps? please help?