Click here to Skip to main content
Click here to Skip to main content

Returning Multiple Ref Cursors from Oracle using DAAB

, 9 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
How to return multiple ref cursors from Oracle using DAAB

Introduction

This article explains how to work with Oracle stored procedure which returns multiple ref cursors using Data Access Application Block (DAAB) of Enterprise Library.

Background

DAAB is used in a variety of situations such as reading data for display, passing data through application layers, and submitting changed data back to the database system. It includes support for both stored procedures and in-line SQL. In other words, Data Access Application Block provides access to the most often used features of ADO.NET in simple-to-use classes.

Using the Code

(1) For Procedure Returning Single Ref Cursor

  • Add the reference to Microsoft.Practices.EnterpriseLibrary.Data.
  • Create the database object.
  • Create the DbCommand object and pass the name of the stored procedure.
  • If stored procedure takes any input parameter, pass that as shown in below code:
    Database db = DatabaseFactory.CreateDatabase("Connection String");
    DbCommand cmd = db.GetStoredProcCommand("PKG_TEST.EMAIL");
    db.AddInParameter(cmd, "varMODULEID", DbType.String);
    db.SetParameterValue(cmd, "varMODULEID", 1);
    DataSet ds = db.ExecuteDataSet(cmd);
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind(); 

Now, stored procedure "PKG_TEST.EMAIL" takes one input parameter varMODULEID and returns a single ref cursor. But as in the above code, I have not created any parameter for ref cursor.

So, the question arises, how it will come to know about the ref cursor ????

The answer is in the code of DAAB.

When we call the stored procedure which returns a single ref cursor, DAAB automatically creates a parameter of type cursor. The name of the automatically created parameter is cur_OUT. Here, one point should be kept in mind that the name of the ref cursor in your stored procedure must be cur_OUT. So, in case of stored procedure which returns single ref cursor, you need not pass ref cursor as parameter to DbCommand object.

(2) For Procedure Returning Multiple Ref Cursors

  • Add the reference to Microsoft.Practices.EnterpriseLibrary.Data.
  • Create the database object.
  • Create the DbCommand object and pass the name of the stored procedure.
  • If stored procedure takes any input parameter, set the value of that as shown in below code:
    Database db = DatabaseFactory.CreateDatabase("Connection String");
    object[] results = new object[3];
    DbCommand cmd = db.GetStoredProcCommand("PKG_TEST.DEPARTMENT", results);
    db.SetParameterValue(cmd, "varDEPTID", 1);
    DataSet ds = db.ExecuteDataSet(cmd);
    GridView1.DataSource = ds.Tables[0];
    GridView1.DataBind();
    GridView2.DataSource = ds.Tables[1];
    GridView2.DataBind();

When you create a DbCommand object for a stored procedure that returns multiple cursors, you must pass an array of objects to the GetStoredProcCommand method. The size of the array must be equal to the number of parameters required by the stored procedure.

In this case, store procedure "PKG_TEST.DEPARTMENT" takes one input parameter and returns two ref cursors. Here:

object[] results = new object[3];

I create an array of size three which signifies that my stored procedure requires three parameters.

So, when your stored procedure returns more than one ref cursor, you have to create an array of object where size of array should be equal to the number of parameters taken by your stored procedure.

History

  • 9th June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

himanshu2561
Web Developer
India India
g

Comments and Discussions

 
AnswerReturning Multiple Ref Cursors from Oracle only not using DAAB PinmemberGirish Lohiya13-Oct-11 5:23 
QuestionProblem with results array size when calling getStoredProcCommand [modified] PinmemberAndrei D.O14-Jul-11 7:56 
SuggestionRe: Problem with results array size when calling getStoredProcCommand PinmemberAndrei D.O14-Jul-11 8:10 
GeneralRe: Problem with results array size when calling getStoredProcCommand PinmemberAndrei D.O25-Jul-11 5:35 
GeneralThanks! PinmemberNate West30-Apr-10 6:40 
GeneralGood article Pinmemberreachpranish29-Mar-10 13:59 
GeneralMy vote of 1 PinmvpPete O'Hanlon10-Jun-09 12:32 
GeneralRe: My vote of 1 Pinmemberhimanshu256110-Jun-09 19:55 
GeneralRe: My vote of 1 PinmvpPete O'Hanlon10-Jun-09 23:00 
GeneralRe: My vote of 1 Pinmemberhimanshu256111-Jun-09 0:02 
GeneralRe: My vote of 1 PinmvpPete O'Hanlon11-Jun-09 0:37 
AnswerRe: My vote of 1 Pinmemberhimanshu256111-Jun-09 2:02 
GeneralRe: My vote of 1 PinmemberLikeABanshee17-Jul-09 4:54 
GeneralRe: My vote of 1 Pinmemberhimanshu256117-Jul-09 20:38 
GeneralRe: My vote of 1 Pinmemberbayerncoder20-Jul-12 11:43 
GeneralRe: My vote of 1 PinmemberChrisOnNet20-Jul-09 9:44 
GeneralRe: My vote of 1 Pinmemberhimanshu256120-Jul-09 20:21 
GeneralRe: My vote of 1 PinmemberEstarta9-Aug-09 8:21 
GeneralRe: My vote of 1 Pinmemberhimanshu25619-Aug-09 20:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411022.1 | Last Updated 9 Jun 2009
Article Copyright 2009 by himanshu2561
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid