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

Advertisements

Tags: ,

8 Responses to “OracleDataProvider: ORA-01861 Literal Does Not Match Format String”

  1. How to Get Six Pack Fast Says:

    The style of writing is very familiar . Have you written guest posts for other bloggers?

    • baraholka1 Says:

      Six Pack,

      Thanks for taking the trouble to leave a comment.
      No, I haven’t guested for other blogs. I would love to read my blog ‘twins’.
      Could you please link to an example of another blog entry you thought might be mine ?

      Regards,

      Barra

  2. Radi Ganesh Says:

    Thanks for your valuable insight regarding ‘the order of the parameters in the OracleParameter array’ – that is what really helped me solve my prb ORA-01861 problem. I had searched for hours and found nothing worked – then i saw your comment and sure enough, that did it! Thanks a bunch again! – Radi

    • baraholka1 Says:

      Radi –

      Very happy to be of assistance.
      Please be sure to keep Biting The Wax Tadpole ๐Ÿ™‚

      – Barra

  3. Dev Says:

    Hi,

    Thanks for pointing out the obvious. I thought i was going mad.

    I personally find it a dumb implementation of reading out the params. They all have names! But there probably is a reason for this implementation. I just can’t think of any. ๐Ÿ™‚

    Kind regards,

    • baraholka1 Says:

      DWGMA,

      Obvious is my business.
      Please come back for more fixes to obvious problems, boners and general stuff-ups.
      NEXT: What to do if you’ve set yourself on fire.

      – Barra

  4. Layla Says:

    I adore you. This was my problem too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: