OracleDataProvider: ORA-01861 Literal Does Not Match Format String

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
oraCmd.Parameters.Add(params(intParamCount))
Next
End If

' Code crashes here, giving ORA-01861
oraCmd.ExecuteNonQuery()

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

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

Addendum 27-March-2009
Looks like I could have save myself a lot of trouble by setting

oraCmd.BindByName = True

Tags: Atavism, OracleParameter