Returning an Oracle Data Set using Enterprise Library 2.0
We recently ran into some challenges returning the results of a stored procedure from Oracle using the Enteprise Library. The basic elements of conducting this operation is that the stored procedure on the Oracle side must a output a specific parameter in order for the Enterprise Library Data Access Applicaiton block to render it to the calling code as a data set.
Unlike SQL Server where each SELECT statement gets rendered as a data table automatically, Oracle either outputs a cursor or output variables. They key lies in ensuring that the returned data from the Oracle stored procedure comes back to the application as a reference cursor, AND that it has a name that the EntLib DAB expects.
Ensure that the stored procedure/package has an OUT variable of a reference cursor type named cur_OUT. The Enterprise Library, when called will investigate the command object for the existance of this parameter (you don't have to specify it). If it does not find it, it will add it and then expect the Oracle stored procedure to return the results as a reference cursor named cur_OUT.