Running an oracle stored procedure in c#

I am trying to run an oracle stored procedure with 2 input parameters and a sys_ref cursor as a return parameter (parameter3).

I am trying to parse the cursor data into an excel spreadsheet.

There is no datatype for return cursor.

How do I do this:

private void button2_Click(object sender, EventArgs e)
{
int i = 0;
int j = 0;
string data = null;
var conn = new OdbcConnection();
conn.ConnectionString = @"DSN=ABCD;" +
"Uid=ABCD;" +
"Pwd=*******";
var odbcCommand = conn.CreateCommand();
OdbcParameter ODBCParameter1 = new OdbcParameter("@FROMDATE", OdbcType.VarChar, 30);
ODBCParameter1.Value = "01/28/2020";
ODBCParameter1.Direction = ParameterDirection.Input;
OdbcParameter ODBCParameter2 = new OdbcParameter("@TODATE", OdbcType.VarChar, 30);
ODBCParameter2.Value = "01/29/2020";
ODBCParameter2.Direction = ParameterDirection.Input;
OdbcParameter ODBCParameter3 = new OdbcParameter("@SYS_REFCURSOR", OdbcType.
OdbcParameter ODBCParameter3 = new OdbcParameter("@sys_ref", OdbcType.VarChar, 300);
ODBCParameter3.Direction = ParameterDirection.Output;
OdbcCommand odbcCommand = new OdbcCommand("GETREPORT", conn);
odbcCommand.CommandType = CommandType.StoredProcedure;
odbcCommand.Parameters.Add(ODBCParameter1);
odbcCommand.Parameters.Add(ODBCParameter2);
OdbcDataAdapter da = new OdbcDataAdapter(odbcCommand);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds);
Excel._Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}
conn.Close();
xlWorkBook.SaveAs("testing.xls", Excel.XlFileFormat.xlWorkbookDefault);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
da.Dispose();
conn.Close();
MessageBox.Show("Excel File Created");
}

The stored procedure is as follows:

create or replace PROCEDURE GETREPORT
(
FROMDATE IN VARCHAR2,
TODATE IN VARCHAR2,
p_recordset OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_recordset FOR
SELECT d.materialcode, m.itemdesc, sum(d.quantity)
FROM tbldispensed d LEFT OUTER JOIN tblmasterinventory m ON m.materialcode = d.materialcode
WHERE TO_DATE(d.datedispensed, 'yy/mm/dd') BETWEEN TO_DATE(FROMDATE, 'mm/dd/yyyy')
AND TO_DATE(TODATE, 'mm/dd/yyyy')
GROUP BY d.materialcode, m.itemdesc;
END GETREPORT;

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty6y ago
Hey check the below link

https://www.c-sharpcorner.com/article/calling-oracle-stored-procedures-from-microsoft-net/
https://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c