User-Defined Type encounters "Unsupported column datatype"

JWu

Below is the hierarchy of my c# classes:

Grand parent:

public abstract class UserDefinedType : IOracleCustomType

{

[DataMember]

public string DatabaseTypeName;

/// <summary>

/// This could be the name of the stored procedure and function.

/// </summary>

[DataMember]

public string CommandText;

/* **********************************************************

* Interface inherited methods

*/

public abstract void FromCustomObject(OracleConnection con, IntPtr pUdt);

public abstract void ToCustomObject(OracleConnection con, IntPtr pUdt);

}

Parent:

public class PartEntity : UserDefinedType

{

protected const string udtWorkOrder = "work_order";

[DataMember]

[OracleObjectMapping("work_order")]

public string WorkOrder { get; set; }

[DataMember]

public string CorningPartNumber { get; set; }

[DataMember]

public PartEntity Parent {  get; set; }

[DataMember]

public Measurement Weight {  get; set; }

[DataMember]

public Measurement Length { get; set; }

[DataMember]

public Measurement Width { get; set; }

[DataMember]

public Measurement Diameter { get; set; }

[DataMember]

public Measurement Thickness { get; set; }

/* **************************************************

* Constructor

* **************************************************

*/

public PartEntity(string workOrder)

{

WorkOrder = workOrder;

}

/* **************************************************

* Overridden methods

*/

public override void FromCustomObject(OracleConnection con, IntPtr pUdt)

{

throw new NotImplementedException();

}

public override void ToCustomObject(OracleConnection con, IntPtr pUdt)

{

throw new NotImplementedException();

}

}

Child:

public class MSMSampleEntity : PartEntity

{

public const string udtName = "msm_sample";

public const string INSERT = "portal_ops.PROC_CMAP_MSM_SAMPLE_INS";

private const string udtMeasuredDate = "measured_date";

[DataMember]

[OracleObjectMapping(udtMeasuredDate)]

public DateTime MeasuredDateTime { get; set; }

private const string udtAnalysisType = "analysis_type";

[DataMember]

[OracleObjectMapping(udtAnalysisType)]

public string AnalysisType {  get; set; }

private const string udtSampleType = "sample_type";

[DataMember]

[OracleObjectMapping(udtSampleType)]

public string SampleType { get; set; }

/* *************************************

* Constructor

*/

public MSMSampleEntity() : this("") { }

public MSMSampleEntity(string workOrder) : base(workOrder)

{

DatabaseTypeName = "msm_sample";

}

/* **************************************

* Interface implementation methods

*/

public override void FromCustomObject(OracleConnection con, IntPtr pUdt)

{

OracleUdt.SetValue(con, pUdt, udtWorkOrder, WorkOrder);

OracleUdt.SetValue(con, pUdt, udtMeasuredDate, SampleType);

OracleUdt.SetValue(con, pUdt, udtSampleType, SampleType);

OracleUdt.SetValue(con, pUdt, udtAnalysisType, AnalysisType);

}

public override void ToCustomObject(OracleConnection con, IntPtr pUdt)

{

WorkOrder = OracleUdt.GetValue(con, pUdt, udtWorkOrder) as string;

MeasuredDateTime = (DateTime)OracleUdt.GetValue(con, pUdt, udtMeasuredDate);

SampleType = OracleUdt.GetValue(con, pUdt, udtSampleType) as string;

AnalysisType = OracleUdt.GetValue(con, pUdt, udtAnalysisType) as string;

}

}

[OracleCustomTypeMapping(MSMSampleEntity.udtName)]

public class MSMSampleEntityFactory : IOracleCustomTypeFactory

{

public IOracleCustomType CreateObject()

{

return new MSMSampleEntity();

}

}

Sending the Child the ODAC

public void ExecuteCommand(UserDefinedType udt)

{

// setting up the parameter

OracleParameter param = new OracleParameter();

param.Direction = ParameterDirection.Input;

param.UdtTypeName = udt.DatabaseTypeName;

param.DbType = DbType.Object;

// this needs to stay at the end...if you move it ahead of the

// previous line, you will get

// "Value does not fall within the expected range" error

param.Value = udt;

Command.CommandText = udt.CommandText;

Command.CommandType = CommandType.StoredProcedure;

Command.Parameters.Add(param);

if (Command.Connection.State == ConnectionState.Closed)

{

Command.Connection.Open();

}

Command.ExecuteNonQuery();

Command.Dispose();

Command.Connection.Close();

}

The stack trace:

OracleParameter.PreBind()

OracleCommandImpl.Initialize.ParamInfo

...

Can anyone advise what am I missing?  Thanks!

Alex Keh-Oracle

If you are using managed ODP.NET, that would explain the error as managed ODP.NET does not yet support UDTs.

Alex Keh-Oracle

If you are using managed ODP.NET, that would explain the error as managed ODP.NET does not yet support UDTs.

JWu

Thank you...I found that out late last night.    Where's the best place or how to "monitor" the progress  w/ the ODP.NET?

Alex Keh-Oracle

You can follow us on Twitter @OracleDOTNET. There's a GitHub issue tracking this feature request: https://github.com/oracle/dotnet-db-samples/issues/56