Need help about .NET DataProvider for Oracle usage

Dear Everyone,

I have to write a function in C# that calls an oracle function that returns a cursor value, as well as one of its paramters is also OUT Cursor type. I want to fetch both the cursors in a single database call and then covert these cursors in two ArrayList objects. For this purpose I have written following oracle function and C# code to call that oracle function:

ORACLE FUNCTION STARTS: -----------------------

CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;

FUNCTION spOutCursorRetCursor(prmLASTNAME IN VARCHAR2, io_AllRec OUT T_CURSOR)
RETURN CURSPKG.T_CURSOR;

END CURSPKG;

CREATE OR REPLACE PACKAGE BODY CURSPKG AS

FUNCTION spOutCursorRetCursor(prmLASTNAME IN VARCHAR2, io_AllRec OUT T_CURSOR)
RETURN CURSPKG.T_CURSOR
IS
AllRec CURSPKG.T_CURSOR;
io_LNRec CURSPKG.T_CURSOR;
BEGIN

OPEN io_LNRec FOR
SELECT * FROM STORE.CUSTOMER a1 WHERE (a1.LASTNAME = prmLASTNAME);
io_AllRec := io_LNRec;

OPEN AllRec FOR SELECT * FROM STORE.CUSTOMER;
RETURN AllRec;

END spOutCursorRetCursor;

END CURSPKG;
/

----------------------- ORACLE FUNCTION ENDS

C# FUNCTION STARTS: -----------------------

public bool SpOutNumOutCursor(String strParamLASTNAME, ref ArrayList arrSelectedCusts, ref ArrayList arrAllCusts)
{
String strConn = "Data Source=diyadb;User Id=store;Password=store;"; // Connection string
StringBuilder commandText = new StringBuilder("CURSPKG.spOutCursorRetCursor"); // Command text

ArrayList objList = new ArrayList();
bool bFunctionStatus = false;

try
{
OracleConnection Conn = new OracleConnection(strConn);
OracleCommand DSCmd = new OracleCommand(commandText.ToString(),Conn);
Conn.Open();

DSCmd.CommandType = CommandType.StoredProcedure; // Set command type property

//Set IN parameter value
DSCmd.Parameters.Add("PRMPRMLASTNAME", OracleType.VarChar,256).Value = (String)strParamLASTNAME;
DSCmd.Parameters["PRMPRMLASTNAME"].Direction = ParameterDirection.Input;

//Set OUT parameter direction
DSCmd.Parameters.Add("IO_ALLREC", OracleType.Cursor).Direction = ParameterDirection.Output;

OracleDataReader dataReader = DSCmd.ExecuteReader();
// Code to fetch OUT parameter cursor in an arraylist
while (dataReader.Read())
{
ArrayList objInfo = new ArrayList();

for (int nCount = 0; nCount < dataReader.FieldCount; nCount++)
objInfo.Add(dataReader.GetValue(nCount));

objList.Add(objInfo);
}

arrSelectedCusts = objList; // Assign values to REF arrSelectedCusts parameter

objList = new ArrayList(); // Inialize objList to populate again for returned cursor

/*
* How may we fetch returned cursor from dataReader object so that
* we may populate objList.
*/

dataReader.Close();
Conn.Close();
bFunctionStatus = true;
}
catch(Exception ex)
{
bFunctionStatus = false;
System.Windows.Forms.MessageBox.Show(ex.Message); }
return bFunctionStatus;
}

// ----------------------- C# FUNCTION ENDS

Would you please help in completing this function so that I may fetch returned cursor value.

Thanks for paying attention.



Kashif

Hello,

Try posting your question in the ASP.NET forum. Maybe somewhere will see your post in there.

Hope that helps!
Danny