This discussion is archived
3 Replies Latest reply: Oct 12, 2012 10:33 AM by 967262 RSS

Oracle Proc in edmx via add function import is not working

926762 Newbie
Currently Being Moderated
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?

Legend

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