Got the above error calling a Stored Procedure via an OracleCommand.ExecuteNonQuery.
Not being an Oracle savant, I Googled the error and found it can come about when an Oracle TO_DATE function call cannot match the string literal with the supplied date picture (Excuse my atavistic lapse into COBOL jargon) in the Format string.
For example, TO_DATE(’23/02/2009′, ‘DD-MON-YY’) produces ORA-01861 because the Format string specifies three character month and two character years, but received a two character month and a four character year.
In my case. however, the crash occurred on line one of the Stored Proc where a DATE parameter was listed in the contract viz:
CREATE OR REPLACE pCalculateStuff(
inDodgyDate IN Date,
inStudentNumber IN VARCHAR2,
inOrg_Number IN VARCHAR2)
and yet Oracle was still complaining with the above error.
Moreover, the parameters were explicitly named in the OracleParamter array I was using to populate the OracleCommand object which called the Stored Proc. I was expecting the parameters to resolve by name but they didn’t.
What I found was that the order of the parameters in the OracleParameter array must match the order of the parameters in the Stored Procedure, even though the OracleParameters are all explictly named in the .NET code
I had the following:
oraParams(0) = New OracleParameter("inStudentNumber", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(0).Value = studentNum.ToString()
oraParams(1) = New OracleParameter("inOrgTradeAutoNum", OracleDbType.Varchar2, ParameterDirection.Input)
oraParams(1).Value = orgTradeAutoNum.ToString()
oraParams(2) = New OracleParameter("inDateProcessing", OracleDbType.Date, ParameterDirection.Input)
oraParams(2).Value = termStartDate
oraCmd.CommandText = strStoredProcName
oraCmd.CommandType = CommandType.StoredProcedure
If (params.Length > 0) Then
For intParamCount = 0 To params.Length - 1
' Code crashes here, giving ORA-01861
So, when I called ExecuteNonQuery on the StudentNumber was being bound to inDodgyDate, because it occupied parameter index 0 in the OracleParameters array so that ExecuteNonQuery threw ORA-01861 because ‘211946’ failed to parse into a Date
The fix was merely to re-order the position of the params in the OracleParameters array so that it matched the order in the Stored Procedure viz:
-- Make inDodgyDate the first parameter in the array
oraParams(0) = New OracleParameter("inDodgyDate", OracleDbType.Date, ParameterDirection.Input)
oraParams(0).Value = termStartDate
The name of the OracleParameter is irrelevant when parameter binding. The order in which they are added to the OracleCommand is the only thing that matters.
Looks like I could have save myself a lot of trouble by setting
oraCmd.BindByName = True