EF5 how to access SYS_REFCURSOR in Oracle stored procedure

589 Views Asked by At

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.

enter image description here

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) 
0

There are 0 best solutions below