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

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

Postby bclayshannon » Thu 15 Mar 2012 00:29

(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
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Got past that err, now a new one

Postby bclayshannon » Thu 15 Mar 2012 15:42

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"
bclayshannon
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Postby Shalex » Thu 15 Mar 2012 17:15

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);
        }
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

How to get a .Net DataGridView control to display the data?

Postby bclayshannon » Thu 15 Mar 2012 17:24

Thanks for your response and code; how can I get a .Net DataGridView control to display the returned data?
bclayshannon
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Still get the same err msg

Postby bclayshannon » Thu 15 Mar 2012 17:30

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
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Others have seen (in your docs) contrary info

Postby bclayshannon » Thu 15 Mar 2012 20:39

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
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Got it working, but another question

Postby bclayshannon » Thu 15 Mar 2012 21:00

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...
bclayshannon
 
Posts: 29
Joined: Wed 14 Mar 2012 18:37
Location: Monterey, California

Postby Pinturiccio » Tue 20 Mar 2012 14:22

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();
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle