- 3,715,654 Users
- 2,242,820 Discussions
- 7,845,479 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 467 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 247 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
User-Defined Type encounters "Unsupported column datatype"
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!
Best Answer
-
If you are using managed ODP.NET, that would explain the error as managed ODP.NET does not yet support UDTs.
Answers
-
If you are using managed ODP.NET, that would explain the error as managed ODP.NET does not yet support UDTs.
-
Thank you...I found that out late last night. Where's the best place or how to "monitor" the progress w/ the ODP.NET?
-
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