MarshalDirectiveException reading OracleType.TimestampWithTZ

5 Jan 2006 4:50 PM

pfluft

Microsoft .NET Framework 2.0.50727
..NET Framework Data Provider for Oracle
Oracle9i Release 9.2.0.7.0

Some TimestampWithTZ values (e.g. "01-JAN-70 01.00.00.000000 AM
EUROPE/BERLIN") can not be read from an oracle database.
I receive the following Exception:

System.Runtime.InteropServices.MarshalDirectiveException: Cannot marshal
'parameter #5': Invalid managed/unmanaged type combination (Int16/UInt16 must
be paired with I2 or U2).
   at System.Data.Common.UnsafeNativeMethods.OCIDateTimeFromArray(OciHandle
hndl, OciHandle err, Byte[] inarray, UInt32 len, DATATYPE type, OciHandle
datetime, OciHandle reftz, Byte fsprec)
   at System.Data.OracleClient.OracleDateTime.GetBytes(NativeBuffer buffer,
Int32 valueOffset, Int32 lengthOffset, MetaType metaType, OracleConnection
connection)
   at System.Data.OracleClient.OracleDateTime.MarshalToDateTime(NativeBuffer
buffer, Int32 valueOffset, Int32 lengthOffset, MetaType metaType,
OracleConnection connection)
   at
System.Data.OracleClient.OracleColumn.GetDateTime(NativeBuffer_RowBuffer
buffer)
   at System.Data.OracleClient.OracleColumn.GetValue(NativeBuffer_RowBuffer
buffer)
   at System.Data.OracleClient.OracleDataReader.GetValues(Object[] values)
   at
System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset,
DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32
startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object
parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable,
IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32
startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at
WindowsApplication2.DataSet1TableAdapters.TEST_TIMETableAdapter.Fill(TEST_TIMEDataTable
dataTable) in C:\CS2\WindowsApplication2\DataSet1.Designer.cs:line 612
   at WindowsApplication2.Form1.Form1_Load(Object sender, EventArgs e) in
C:\CS2\WindowsApplication2\Form1.cs:line 29
   at System.Windows.Forms.Form.OnLoad(EventArgs e)
   at System.Windows.Forms.Form.OnCreateControl()
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ContainerControl.WndProc(Message& m)
   at System.Windows.Forms.Form.WmShowWindow(Message& m)
   at System.Windows.Forms.Form.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg,
IntPtr wparam, IntPtr lparam)

Output from oracle SQL Plus:

SQL> select change_dt,dump(change_dt) from test_time;

CHANGE_DT
---------------------------------------------------------
DUMP(CHANGE_DT)
---------------------------------------------------------
01-JAN-70 01.00.00.000000 AM EUROPE/BERLIN
Typ=181 Len=13: 119,170,1,1,1,1,1,0,0,0,0,133,252

01-JAN-70 01.00.00.000000 AM +01:00
Typ=181 Len=13: 119,170,1,1,1,1,1,0,0,0,0,21,60

The first value causes the problem. The second one can be read without
problems.
According to oracle documents bytes 11 and 12 hold Region id or Timezone
Hour/Minute. I think the Region id can not be handled by the data provider.

Is this a known Bug?

Best regards,
Herwig

6 Jan 2006 2:53 AM

Kevin Yu [MSFT]

Hi Herwig,

Did you used a select statement to select directly from a table and filled
to your app? Could you provide some of the SQL statement and your .net code
here?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

9 Jan 2006 9:57 AM

pfluft

I used the Designer to build my Application. But with the following console
application I get the same results:

Code:

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OracleClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime dt;

            using (OracleConnection conn = new OracleConnection("user
id=ebner;data source=a4h048sv3.pls;password=polar"))
            {
                conn.Open();

                OracleCommand cmd = new OracleCommand();

                cmd.Connection = conn;
                cmd.CommandText = "SELECT CHANGE_DT FROM TEST_TIME ORDER BY
COIL_ID";
                cmd.CommandType = CommandType.Text;

                using (OracleDataReader r =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ) {
                    while (r.Read())
                    {
                        dt = r.GetDateTime(0);
                        Console.WriteLine(dt);
                    }
                }
                conn.Close();
            }
        }
    }
}

Output:

2005-12-09 16:32:53

Unhandled Exception:
System.Runtime.InteropServices.MarshalDirectiveException: Cannot marshal
'parameter #5': Invalid managed/unmanaged type combination (Int16/UInt16 must
be paired with I2 or U2).
   at System.Data.Common.UnsafeNativeMethods.OCIDateTimeFromArray(OciHandle
hndl, OciHandle err, Byte[] inarray, UInt32 len, DATATYPE type, OciHandle
datetime, OciHandle reftz, Byte fsprec)
   at System.Data.OracleClient.OracleDateTime.GetBytes(NativeBuffer buffer,
Int32 valueOffset, Int32 lengthOffset, MetaType metaType, OracleConnection
connection)
   at System.Data.OracleClient.OracleDateTime.MarshalToDateTime(NativeBuffer
buffer, Int32 valueOffset, Int32 lengthOffset, MetaType metaType,
OracleConnection connection)
   at
System.Data.OracleClient.OracleColumn.GetDateTime(NativeBuffer_RowBuffer
buffer)
   at System.Data.OracleClient.OracleDataReader.GetDateTime(Int32 i)
   at ConsoleApplication1.Program.Main(String[] args) in
C:\CS2\ConsoleApplication1\Program.cs:line 29

Data used:

SQL> select * from test_time order by coil_id;

   COIL_ID
----------
CHANGE_DT
---------------------------------------------------------------------------
         1
09-DEC-05 04.32.53.750000 PM +01:00

         2
01-JAN-70 01.00.00.000000 AM EUROPE/BERLIN

SQL> desc test_time;
Name                                      Null?    Type
----------------------------------------- --------
----------------------------
COIL_ID                                   NOT NULL NUMBER(10)
CHANGE_DT                                 NOT NULL TIMESTAMP(6) WITH TIME
ZONE

SQL>

Best Regards,
Herwig

10 Jan 2006 9:33 AM

Kevin Yu [MSFT]

Hi Herwig,

Thanks for your code. I checked it, there seems to be nothing wrong.
However, With my research, I didn't find any related known issues on this.
Also, I don't have enough resource on this issue. In this case, I suggest
you try to contact Microsoft PSS for more information on it. You can find
the contact information from the following link:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."