MarshalDirectiveException reading OracleType.TimestampWithTZ

[Guest]

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

 

[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."

 

[Guest]

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

 

[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."