Oracle Parameter not supported

Question

I'm getting this err msg:

System.ArgumentException was unhandled Message=Value with type Devart.Data.Oracle.OracleParameter not supported.

...with this code:

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

    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();

Updated in response to the comment:

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):

    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.

Update to the update:

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

    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...

score 3 · Accepted Answer · answered Mar 15 '12 at 17:44

3

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.

Unless, of course I'm misunderstanding the documentation, which is entirely possible given I don't do C#/DevArt.

answered Mar 15 '12 at 17:44

DCookie

42,630
11
83
92

That's funny; the cats from devArt (the dotConnect component cats) didn't know or notice that... – B. Clay Shannon-B. Crow Raven Mar 15 '12 at 20:23
OK, I added followin info above. – B. Clay Shannon-B. Crow Raven Mar 15 '12 at 20:37
I'm adding another followin bit above; it's basically working (retrieving data), but it's not pretty. – B. Clay Shannon-B. Crow Raven Mar 15 '12 at 21:02
Just a guess: do you need to add the columns you want returned as additional parameters? – DCookie Mar 15 '12 at 21:50

Oracle Parameter not supported

1 Answers1