Search code examples
c#oracle-databasetypesdevartdotconnect

What format does Oracle expect for dates?

With the following C# code (using devArt's dotConnect for Oracle components):

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

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("iStartDate", "01-jan-2011");
oracleCommand1.Parameters.Add("iEndDate", "21-jan-2011");
oracleCommand1.Parameters.Add("iCATEGORYID", 114);
oracleCommand1.Parameters.Add(pRes);
oracleConnection1.Open();
oracleCommand1.ExecuteCursor();

...I'm getting:

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

The parameters are (copied from the Stored Procedure):

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

I assume it's the date vals that are causing a problem. What am I doing wrong here?

Solution

  • You should be using a DateTime, not a string at all. That's one of the points of using parameterized queries in the first place.

    oracleCommand1.Parameters.Add("iStartDate", new DateTime(2011, 1, 1));
oracleCommand1.Parameters.Add("iEndDate", new DateTime(2011, 1, 21));