How to code a StoredProc that takes params and returns data?

[bclayshannon]

(This is a C# Windows forms app in Visual Studio 2010)

If I have an Oracle Stored Procedure like this:

Code: Select all

CREATE OR REPLACE PROCEDURE REPORT.CONN_THRU_DOTNET
( iStartDate IN DATE
, iEndDate IN DATE
, iCATEGORYID IN NUMBER
, C_REF IN OUT SYS_REFCURSOR
) AS
...

...and an OracleCommand component on my form that is set to a valid connection, has a CommandType of StoredProcedure, and its CommandText is set to the StoredProc shown (partially) above, what do I need to do to set its parameters in code?

The user selects the dates, and I want the result set to display in a DatagridView control.

I tried to adapt the code here:

http://www.devart.com/forums/viewtopic. ... 2e9ef99ed8

But the following won't compile - doesn't know what "pres" is - neither do I.

Code: Select all

private void button1_Click(object sender, EventArgs e)
        {
            //pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
            pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
            pRes.Direction = ParameterDirection.ReturnValue;

            oracleCommand1.Parameters.Clear();
            oracleCommand1.Parameters.Add("iStartDate", "1/1/2011");
            oracleCommand1.Parameters.Add("iEndDate", "12/12/2011");
            oracleCommand1.Parameters.Add("iCATEGORYID", 114);
            oracleCommand1.Parameters.Add(pRes);
            oracleCommand1.ExecuteCursor(); //ExecuteNonQuery();
        }

Also, the code from the link above has the final line calling .ExecuteNonQuery, but as the SP in question DOES contain a select statement and should reflect rows/result set/cursor, I changed it to .ExecuteCursor(). Is that right?

[bclayshannon]

OK, I guess I was tired last night; I easily fixed the error this morning, but am getting a new one when I try to run this C# code:

Code: Select all

private void button1_Click(object sender, EventArgs e)
        {
            OracleParameter pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
            pRes.Direction = ParameterDirection.ReturnValue;

            oracleCommand1.Parameters.Clear();
            oracleCommand1.Parameters.Add("iStartDate", "1/1/2011");
            oracleCommand1.Parameters.Add("iEndDate", "12/12/2011");
            oracleCommand1.Parameters.Add("iCATEGORYID", 114);
            oracleCommand1.Parameters.Add(pRes);
            oracleConnection1.Open();
            oracleCommand1.ExecuteCursor(); //ExecuteNonQuery();
        }

...namely:

Devart.Data.Oracle.OracleException was unhandled
Message=ORA-06550: line 2, column 13:
PLS-00306: wrong number or types of arguments in call to 'CONN_THRU_DOTNET'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Again, the data types expected are:

Code: Select all

( iStartDate IN DATE
, iEndDate IN DATE
, iCATEGORYID IN NUMBER
, C_REF IN OUT SYS_REFCURSOR
) 

I get that error whether I enter the date parameters as above or in this format:

"01-jan-2011"

[Shalex]

We recommend using the dbMonitor tool that performs per-component tracing of database events such as commit, rollback, SQL statement execute etc.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/oracle ... nitor.html

As it is logged in dbMonitor, your code produces the ReturnValue cursor parameter but it is InputOutput in your script. Also we recommend to set the parameters' type explicitly. Try this code:

Code: Select all

        OracleParameter pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
        pRes.Direction = ParameterDirection.InputOutput;

        oracleCommand1.Parameters.Clear();
        oracleCommand1.Parameters.Add("iStartDate", OracleDbType.Date).Value = new OracleDate(2011,1,1);
        oracleCommand1.Parameters.Add("iEndDate", OracleDbType.Date).Value = new OracleDate(2011,12,12);
        oracleCommand1.Parameters.Add("iCATEGORYID", 114);
        oracleCommand1.Parameters.Add(pRes);
        oracleConnection1.Open();
        oracleCommand1.ExecuteNonQuery();
        OracleDataReader reader = ((OracleCursor)oracleCommand1.Parameters["C_REF"].Value).GetDataReader();
        while (reader.Read()) {
            for (int i = 0; i < reader.FieldCount; i++) {
                Console.Write(reader.GetValue(i).ToString() + ' ');
            }
            Console.Write(Environment.NewLine);
        }

[bclayshannon]

Thanks for your response and code; how can I get a .Net DataGridView control to display the returned data?

[bclayshannon]

I still get that same err msg (System.ArgumentException was unhandled Message=Value with type Devart.Data.Oracle.OracleParameter not supported.) on the ExecuteNonQuery() line with this code:

Code: Select all

OracleParameter pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
            //pRes.Direction = ParameterDirection.ReturnValue;
            pRes.Direction = ParameterDirection.InputOutput; // <-- devArt's code

            oracleCommand1.Parameters.Clear();
            int iFromYear = dateTimePickerFrom.Value.Year;
            int iFromMonth = dateTimePickerFrom.Value.Month;
            int iFromDay = dateTimePickerFrom.Value.Day;
            int iToYear = dateTimePickerTo.Value.Year;
            int iToMonth = dateTimePickerTo.Value.Month;
            int iToDay = dateTimePickerTo.Value.Day;
            oracleCommand1.Parameters.Add("iStartDate", new DateTime(iFromYear, iFromMonth, iFromDay));
            oracleCommand1.Parameters.Add("iEndDate", new DateTime(iToYear, iToMonth, iToDay));
            oracleCommand1.Parameters.Add("iCATEGORYID", 114);
            oracleCommand1.Parameters.Add("c_ref", pRes);
            oracleConnection1.Open();
            //oracleCommand1.ExecuteCursor(); // waiting for reply as to why I'm getting an err msg here about param type mismatch
            oracleCommand1.ExecuteNonQuery(); // <-- devArt's code

(This is not a console app, so I left out the writing of the results to the console).

[bclayshannon]

I got this answer on stackOverflow:

"Looks to me like this page says the OracleDBType.Cursor type can't be used here:

An Oracle REF CURSOR. This type does not have corresponding .NET Framework type. The OracleDataReader object can be used to retrieve cursor content."

(http://stackoverflow.com/questions/9724 ... -supported)

So what could be used in place of OracleParameter to pass?

When I change it to this (I'm just flailing around like a harried chicken here):

oracleConnection1.Open();
OracleDataReader myReader = oracleCommand1.ExecuteReader();
OracleParameter pRes = new OracleParameter("C_REF", myReader);
pRes.Direction = ParameterDirection.InputOutput; // <-- devArt's code

oracleCommand1.Parameters.Clear();
int iFromYear = dateTimePickerFrom.Value.Year;
int iFromMonth = dateTimePickerFrom.Value.Month;
int iFromDay = dateTimePickerFrom.Value.Day;
int iToYear = dateTimePickerTo.Value.Year;
int iToMonth = dateTimePickerTo.Value.Month;
int iToDay = dateTimePickerTo.Value.Day;
oracleCommand1.Parameters.Add("iStartDate", new DateTime(iFromYear, iFromMonth, iFromDay));
oracleCommand1.Parameters.Add("iEndDate", new DateTime(iToYear, iToMonth, iToDay));
oracleCommand1.Parameters.Add("iCATEGORYID", 114);
oracleCommand1.Parameters.Add("c_ref", pRes);
while (myReader.Read())
{
;// Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(myReader.GetOrdinal("DName")));
}
// always call Close when done reading.
myReader.Close();

...I get nor err msgs, but I also get no data.

[bclayshannon]

OK, I finally got it working. These may not be elegant, but it does retreive data:

Code: Select all

            oracleCommand1.Parameters.Clear();
            int iFromYear = dateTimePickerFrom.Value.Year;
            int iFromMonth = dateTimePickerFrom.Value.Month;
            int iFromDay = dateTimePickerFrom.Value.Day;
            int iToYear = dateTimePickerTo.Value.Year;
            int iToMonth = dateTimePickerTo.Value.Month;
            int iToDay = dateTimePickerTo.Value.Day;
            oracleCommand1.Parameters.Add("iStartDate", new DateTime(iFromYear, iFromMonth, iFromDay));
            oracleCommand1.Parameters.Add("iEndDate", new DateTime(iToYear, iToMonth, iToDay));
            oracleCommand1.Parameters.Add("iCATEGORYID", 114);

            oracleConnection1.Open();
            OracleDataReader myReader = oracleCommand1.ExecuteReader();
            OracleParameter pRes = new OracleParameter("C_REF", myReader);
            pRes.Direction = ParameterDirection.InputOutput;
            
            oracleCommand1.Parameters.Add("c_ref", pRes);

            while (myReader.Read())
            {
                MessageBox.Show(myReader.GetString(0)); // + ", " + myReader.GetString(myReader.GetOrdinal("contactemail")));
            }
            myReader.Close(); 

Now, though: how can I retrieve all of the data, rather than just from one columns, as I'm doing above? The best case scenario would be to direct the result set to a DataGridView, but if I have to I can concat (stringFormat()) the various columns I want and programmatically add them to a Memo or some such...

[Pinturiccio]

According to your code you receive an exception in the following row:

Code: Select all

oracleCommand1.Parameters.Add("c_ref", pRes);

pRes has OracleParameter type. There is no overloaded method Add, which has the signature (string, OracleParameter).
According to advice from stackOverflow you create OracleDataReader before add incorrect parameter. So the behavior without the needed parameter is unpredictable. I'm posting a changed snipped of your code:

Code: Select all

OracleParameter pRes = new OracleParameter("C_REF", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.InputOutput; // <-- devArt's code
pRes.ParameterName = "c_ref";

oracleCommand1.Parameters.Clear();
int iFromYear = dateTimePickerFrom.Value.Year;
int iFromMonth = dateTimePickerFrom.Value.Month;
int iFromDay = dateTimePickerFrom.Value.Day;
int iToYear = dateTimePickerTo.Value.Year;
int iToMonth = dateTimePickerTo.Value.Month;
int iToDay = dateTimePickerTo.Value.Day;
oracleCommand1.Parameters.Add("iStartDate", new DateTime(iFromYear, iFromMonth, iFromDay));
oracleCommand1.Parameters.Add("iEndDate", new DateTime(iToYear, iToMonth, iToDay));
oracleCommand1.Parameters.Add("iCATEGORYID", 114);
oracleCommand1.Parameters.Add(pRes);
oracleConnection1.Open();

OracleDataReader myReader = oracleCommand1.ExecuteReader();
while (myReader.Read())
{
	MessageBox.Show(myReader.GetString(0)); // + ", " + myReader.GetString(myReader.GetOrdinal

("contactemail")));
}
myReader.Close();
oracleConnection1.Close();