Microsoft KB Archive/324577

= System.TimeSpan does not match Oracle 9i INTERVAL DAY TO SECOND data type =

Article ID: 324577

Article Last Modified on 3/13/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft ADO.NET 2.0

-



This article was previously published under Q324577



This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.OracleClient



SYMPTOMS
When you try to compare a string that is derived from a System.TimeSpan data type to an Oracle INTERVAL DAY TO SECOND data type, you may receive the following error message:

ORA-01867: the interval is invalid



CAUSE
When an Oracle 9i INTERVAL DAY TO SECOND data type is returned to the Microsoft .NET Framework, it is converted to a System.TimeSpan data type. This problem occurs because System.TimeSpan uses the &quot;dd.hh:mm:ss&quot; format (with a period after &quot;dd&quot;), and the Oracle 9i INTERVAL DAY TO SECOND data type uses the &quot;dd hh:mm:ss&quot; format (without a period after &quot;dd&quot;). Because the data types do not match, you cannot compare the strings of these data types.



RESOLUTION
To resolve this problem, use one of the following methods:   Build your own formatted string from System.TimeSpan as follows: string value = String.Format(&quot;{0} {1}:{2}:{3}.{4}&quot;, ts.Days, ts.Hours, ts.Minutes, ts.Seconds. ts.Milliseconds)  Bind System.TimeSpan as OracleType.IntervalDayToSecond, and then avoid the conversion.



STATUS
This behavior is by design.



Steps to reproduce the behavior
The following code requires the .NET Managed Provider for Oracle. To obtain the .NET Managed Provider for Oracle, visit the following Microsoft Web site:

.NET Managed Provider for Oracle

http://www.microsoft.com/downloads/details.aspx?FamilyID=4f55d429-17dc-45ea-bfb3-076d1c052524&DisplayLang=en

  Create the Oracle table in SQL Plus by using the following script: Create table IntervalDt2 (Col1 INTERVAL DAY(3) TO SECOND(2)); insert into intervaldt2 values('100 10:20:34.22'); Commit; /                     Follow these steps to create a Visual Basic Windows Application project: <ol style="list-style-type: lower-alpha;"> Start Microsoft Visual Studio .NET or Microsoft Visual Studio 2005.</li> On the File menu, point to New, and then click Project.</li> Click Visual Basic Projects under Project Types, and then click Windows Application under Templates. By default, Form1 is added to the project.

Note In Visual Studio 2005, click Visual Basic under Project Types.</li></ol> </li> On the Project menu, click Add Reference, and then set a reference to the System.Data.OracleClient namespace.</li> Drag three Button controls from the toolbox to the form.</li>  Add the following code at the top of the Code window: Imports System.Data.OracleClient </li>  Add the following code to Form1: Dim cn As New OracleConnection(&quot;Server=OracleAlias;UID=UID;PWD=PWD&quot;) Dim cm As New OracleCommand(&quot;Select * from IntervalDt2&quot;, cn) Dim da As New OracleDataAdapter(cm) Dim ds As New DataSet Dim dr As OracleDataReader

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Button1.Text = &quot;Show Problem&quot; Button2.Text = &quot;Solution 1&quot; Button3.Text = &quot;Solution 2&quot;

cn.Open Try da.Fill(ds, &quot;Interval&quot;) Catch myex As Exception MsgBox(myex.Message) End Try

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim cm2 As New OracleCommand(&quot;Select * from IntervalDT2 where Col1 = :P&quot;, cn) Dim da2 As New OracleDataAdapter(cm2) Dim Ds2 As New DataSet Dim x As String

x = ds.Tables(&quot;Interval&quot;).Rows(0)(0).ToString

cm2.Parameters.Add(New OracleParameter(&quot;P&quot;, OracleType.VarChar, 30, ParameterDirection.Input)).Value = x

Try da2.Fill(Ds2, &quot;IntervalP&quot;) Catch myex As Exception MsgBox(myex.Message) End Try

If Ds2.Tables(0).Rows.Count > 0 Then MsgBox(Ds2.Tables(&quot;IntervalP&quot;).Rows(0)(0).ToString) End If   End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim cm2 As New OracleCommand(&quot;Select * from IntervalDT2 where Col1 = :P&quot;, cn) Dim da2 As New OracleDataAdapter(cm2) Dim Ds2 As New DataSet Dim x As String

x = String.Format(&quot;{0} {1}:{2}:{3}.{4}&quot;, ds.Tables(&quot;Interval&quot;).Rows(0)(0).Days, ds.Tables(&quot;Interval&quot;).Rows(0)(0).Hours, ds.Tables(&quot;Interval&quot;).Rows(0)(0).Minutes, ds.Tables(&quot;Interval&quot;).Rows(0)(0).Seconds, ds.Tables(&quot;Interval&quot;).Rows(0)(0).Milliseconds) cm2.Parameters.Add(New OracleParameter(&quot;P&quot;, OracleType.VarChar, 30, ParameterDirection.Input)).Value = x

Try da2.Fill(Ds2, &quot;IntervalP&quot;) Catch myex As Exception MsgBox(myex.Message) End Try

If Ds2.Tables(0).Rows.Count > 0 Then MsgBox(Ds2.Tables(&quot;IntervalP&quot;).Rows(0)(0).ToString) End If   End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim cm2 As New OracleCommand(&quot;Select * from IntervalDT2 where Col1 = :P&quot;, cn) Dim da2 As New OracleDataAdapter(cm2) Dim Ds2 As New DataSet Dim x As String

cm2.Parameters.Add(New OracleParameter(&quot;P&quot;, OracleType.IntervalDayToSecond, 11, ParameterDirection.Input)).Value = ds.Tables(&quot;Interval&quot;).Rows(0)(0)

Try da2.Fill(Ds2, &quot;IntervalP&quot;) Catch myex As Exception MsgBox(myex.Message) End Try

If Ds2.Tables(0).Rows.Count > 0 Then MsgBox(Ds2.Tables(&quot;IntervalP&quot;).Rows(0)(0).ToString) End If   End Sub </li> Modify the OracleConnection string as appropriate for your environment.</li> Press F5 to compile and to run the program.</li></ol>

Keywords: kboracle kbsystemdata kbprb KB324577

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.