oracle的passing,passing Oracle UDT as IN parameter into Oracle Stored Procedure

Hi,

I am trying pass oracle UDT as parameter into Oracle stored procedure from .net using ODP.NET. I have done creating custom classes and mapping between oracle UDT attributes, but when am executing the stored procedure with this custom

object as parameter the UDT attributes are get set as NULL. please could help me to overcome this.

i can provide the custom type implementation and and Oracle UDT type here.

Custom Type Implementation

#Region "CAJA.T_MOVIMIENTOCAJAII"

#Region "Custom Type"

Public Class T_MOVIMIENTOCAJAII

Implements INullable, IOracleCustomType

Private mIsNull As Boolean

Private mMontoMovimiento As Double

Private mIdMoneda As Integer

Private mIdCaja As Integer

Private mIdCajaVinculada As Integer

Private mGlosaMovimiento As String

Private mIdTipoMovimiento As Integer

Private mIdModoRecaudacionPago As Integer

#Region "constructor"

Public Sub New()

Me.mIsNull = True

End Sub

#End Region

#Region "properties"

Public Property MontoMovimiento() As Double

Get

Return Me.mMontoMovimiento

End Get

Set(value As Double)

Me.mMontoMovimiento = value

End Set

End Property

Public Property IdMoneda() As Integer

Get

Return Me.mIdMoneda

End Get

Set(value As Integer)

Me.mIdMoneda = value

End Set

End Property

Public Property IdCaja() As Integer

Get

Return Me.mIdCaja

End Get

Set(value As Integer)

Me.mIdCaja = value

End Set

End Property

Public Property IdCajaVinculada() As Integer

Get

Return Me.mIdCajaVinculada

End Get

Set(value As Integer)

Me.mIdCajaVinculada = value

End Set

End Property

Public Property GlosaMovimiento() As String

Get

Return Me.mGlosaMovimiento

End Get

Set(value As String)

Me.mGlosaMovimiento = value

End Set

End Property

Public Property IdTipoMovimiento() As Integer

Get

Return Me.mIdTipoMovimiento

End Get

Set(value As Integer)

Me.mIdTipoMovimiento = value

End Set

End Property

Public Property IdModoRecaudacionPago() As Integer

Get

Return Me.mIdModoRecaudacionPago

End Get

Set(value As Integer)

Me.mIdModoRecaudacionPago = value

End Set

End Property

Public Shared ReadOnly Property Null() As T_MOVIMIENTOCAJAII

Get

Dim t_movimientocajaII As New T_MOVIMIENTOCAJAII()

t_movimientocajaII.mIsNull = True

Return t_movimientocajaII

End Get

End Property

Public ReadOnly Property IsNull As Boolean Implements INullable.IsNull

Get

Return Me.mIsNull

End Get

End Property

#End Region

Public Sub FromCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.FromCustomObject

OracleUdt.SetValue(con, pUdt, "MontoMovimiento", MontoMovimiento)

OracleUdt.SetValue(con, pUdt, "IdMoneda", IdMoneda)

OracleUdt.SetValue(con, pUdt, "IdCaja", IdCaja)

OracleUdt.SetValue(con, pUdt, "IdCajaVinculada", IdCajaVinculada)

OracleUdt.SetValue(con, pUdt, "GlosaMovimiento", GlosaMovimiento)

OracleUdt.SetValue(con, pUdt, "IdTipoMovimiento", IdTipoMovimiento)

OracleUdt.SetValue(con, pUdt, "IdModoRecaudacionPago", IdModoRecaudacionPago)

End Sub

Public Sub ToCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.ToCustomObject

MontoMovimiento = DirectCast(OracleUdt.GetValue(con, pUdt, "MontoMovimiento"), Double)

IdMoneda = DirectCast(OracleUdt.GetValue(con, pUdt, "IdMoneda"), Integer)

IdCaja = DirectCast(OracleUdt.GetValue(con, pUdt, "IdCaja"), Integer)

IdCajaVinculada = DirectCast(OracleUdt.GetValue(con, pUdt, "IdCajaVinculada"), Integer)

GlosaMovimiento = DirectCast(OracleUdt.GetValue(con, pUdt, "GlosaMovimiento"), String)

IdTipoMovimiento = DirectCast(OracleUdt.GetValue(con, pUdt, "IdTipoMovimiento"), Integer)

IdModoRecaudacionPago = DirectCast(OracleUdt.GetValue(con, pUdt, "IdModoRecaudacionPago"), Integer)

End Sub

End Class

#End Region

#Region "Factory class"

Public Class T_MOVIMIENTOCAJAIIFacotry

Implements IOracleCustomTypeFactory

Public Function CreateObject() As IOracleCustomType Implements IOracleCustomTypeFactory.CreateObject

Dim obj As T_MOVIMIENTOCAJAII = New T_MOVIMIENTOCAJAII()

Return obj

End Function

End Class

#End Region

#End Region

#Region "CAJA.MOVIMIENTOCAJAII"

#Region "custom type"

Public Class CAJA_MOVIMIENTOCAJAII

Implements INullable, IOracleCustomType

Private mIsNull As Boolean

Private mCaja_T_MovimientoCajaII As T_MOVIMIENTOCAJAII()

#Region "cunstructor"

Public Sub New()

Me.mIsNull = True

End Sub

#End Region

#Region "properties"

Public Property Caja_T_MovimientoCajaII() As T_MOVIMIENTOCAJAII()

Get

Return Me.mCaja_T_MovimientoCajaII

End Get

Set(value As T_MOVIMIENTOCAJAII())

Me.mCaja_T_MovimientoCajaII = value

End Set

End Property

Public ReadOnly Property IsNull As Boolean Implements INullable.IsNull

Get

Return Me.mIsNull

End Get

End Property

Public Shared ReadOnly Property Null() As CAJA_MOVIMIENTOCAJAII

Get

Dim caja_movimientocajaII As New CAJA_MOVIMIENTOCAJAII()

caja_movimientocajaII.mIsNull = True

Return caja_movimientocajaII

End Get

End Property

#End Region

Public Sub FromCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.FromCustomObject

OracleUdt.SetValue(con, pUdt, 0, Caja_T_MovimientoCajaII)

End Sub

Public Sub ToCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.ToCustomObject

Caja_T_MovimientoCajaII = DirectCast(OracleUdt.GetValue(con, pUdt, 0), T_MOVIMIENTOCAJAII())

End Sub

End Class

#End Region

#Region "Factory Class"

Public Class CAJA_MOVIMIENTOCAJAIIFactory

Implements IOracleArrayTypeFactory, IOracleCustomTypeFactory

Public Function CreateArray(numElems As Integer) As Array Implements IOracleArrayTypeFactory.CreateArray

Return New CAJA_MOVIMIENTOCAJAII(numElems - 1) {}

End Function

Public Function CreateStatusArray(numElems As Integer) As Array Implements IOracleArrayTypeFactory.CreateStatusArray

Return Nothing

End Function

Public Function CreateObject() As IOracleCustomType Implements IOracleCustomTypeFactory.CreateObject

Return New CAJA_MOVIMIENTOCAJAII()

End Function

End Class

#End Region

#End Region

Oracle UDT

CREATE OR REPLACE TYPE CAJA.MOVIMIENTOCAJAII IS TABLE OF Caja.T_MovimientoCajaII;

CREATE OR REPLACE TYPE CAJA.T_MOVIMIENTOCAJAII IS OBJECT(

MontoMovimiento Float NULL,

IdMoneda NUMBER NULL,

IdCaja NUMBER NULL,

IdCajaVinculada NUMBER NULL,

GlosaMovimiento VARCHAR2(50) NULL,

IdTipoMovimiento NUMBER NULL,

IdModoRecaudacionPago NUMBER NULL

);

adding UDT parameter

Dim udtPara As OracleParameter = New OracleParameter("unMovimientoCaja", OracleDbType.Array)

udtPara.UdtTypeName = "CAJA.MOVIMIENTOCAJAII"

udtPara.Value = _objectMovimientoCajaOracle

udtPara.Direction =ParameterDirection.Input

_comandoOracle.Parameters.Add(udtPara)

_objectMovimientoCajaOracle have values while i debug the code

kindly help me ....

thanks and regards

Rahul