Calling a stored procedure with Ref cursor as output parameter using Enterprise Library 5.0

Question

I am new to both Oracle and Entlib.

I have written some code to call a stored procedure which works fine, however there's an issue when I am calling stored procedure with Ref cursor as output parameter.

Could anybody help me with some sample code to access stored procedure inside Oracle database using EntLib which returns a Ref cursor as output parameter?

Thanks.

Answer 1

You could look at the tests in the source code.

First define a cursor:

create or replace PACKAGE PKGENTLIB_ARCHITECTURE
IS
TYPE CURENTLIB_ARCHITECTURE IS REF CURSOR;
END PKGENTLIB_ARCHITECTURE;

Then create a stored procedure:

CREATE OR REPLACE PROCEDURE "GETCUSTOMERSVIEW" ( cur_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE)
    AS

BEGIN
   OPEN cur_OUT FOR
    SELECT *
    FROM Customers WHERE CustomerId in ('BLAUS','BLONP','BOLID')
    ORDER BY CustomerID;
END;
/

Note use of cur_out as the ref cursor name -- this is mandatory.

Then the c# code:

    [TestMethod]
    [DeploymentItem(@"Testfiles\Customers.xml")]
    [DeploymentItem(@"Testfiles\Products.xml")]
    public void RecordsAreReturnedWhenUsingStoredProc()
    {
        Database db = DatabaseFactory.CreateDatabase("OracleTest");
        string spName = "GetCustomersView";
        using (IDataReader reader = db.ExecuteReader(CommandType.StoredProcedure, spName))
        {
            int columns = dsCustomers.Tables[0].Columns.Count;
            int i = 0;
            while (reader.Read())
            {
                for (int j = 0; j < columns; j++)
                {
                    Assert.AreEqual(dsCustomers.Tables[0].Rows[i][j].ToString().Trim(), reader[j].ToString().Trim());
                }
                i++;
            }
        }
    }

The test uses this configuration file.

If you are using ODP.NET then you would want to switch from using the deprecated System.Data.OracleClient to Oracle.DataAccess.Client. You would need to use the EntlibContrib Oracle ODP.NET Data Provider. Also, you could look at the EntLibContrib Quickstart for sample ODP.NET configuration.