I have an Oracle Stored Procedure that has a return cursor that I would like to access using EF 5. I have read multiple question from the site but still can not get it to work.
This is my oracle stored procedure:
CREATE OR REPLACE PROCEDURE GETINFO
(
"StoreId" IN varchar2 , OUT_RESULT OUT SYS_REFCURSOR
)
IS
sqlstmt VARCHAR2(2000);
BEGIN
sqlstmt := 'SELECT v."StoreId", v."ProductCategory" "Product", v."ProductId"
FROM SUPERMARKET v WHERE v."StoreId"='||"StoreId";
OPEN Out_Result FOR sqlStmt;
END GETINFO;
According to Oracle documentation, to access SYS_REFCURSOR, I have to explicitly declare it in my App.config, which I did:
<oracle.dataaccess.client>
<settings>
<oracle.manageddataaccess.client>
<version number="*">
<StoredProcedure schema="MYSCHEMA" name="GETINFO">
<refCursor name="OUT_RESULT">
<bindInfo mode="Output" />
<metadata columnOrdinal="0" columnName="StoreId" providerType="NUMBER" />
<metadata columnOrdinal="1" columnName="Product" providerType="Varchar2" />
<metadata columnOrdinal="2" columnName="ProductId" providerType="Varchar2" />
</refCursor>
</StoredProcedure>
</version>
</oracle.manageddataaccess.client>
</settings>
</oracle.dataaccess.client>
There may be other pieces hidden from Oracle documentation regarding meta data that I may have to include in the App.Config. As a result, I am still not able to access my specific SYS_REFCURSOR because when I clicked on Get Column Information, I got nothing and there was nothing in the complex combo box. According to Oracle, I should have to structure of the SYS_REFCURSOR to refer to.
My Entity Framework is 5.0, I use VS 2012.
What else do I need to include in the meta data?
My underlying SUPERMARKET table structure is:
Name Null Type
--------------- ---- ------------
StoreId NUMBER
ProductCategory VARCHAR2(20)
ProductId VARCHAR2(20)