将Oracle UDT作为IN参数传递到Oracle存储过程 [英] passing Oracle UDT as IN parameter into Oracle Stored Procedure

问题描述

     我正在尝试使用ODP.NET将oracle UDT作为参数从.net传递到Oracle存储过程。我已经完成了在oracle UDT属性之间创建自定义类和映射，但是当使用此自定义
对象作为参数执行存储过程时，UDT属性被设置为NULL。请帮我解决这个问题。 

我可以在这里提供自定义类型实现和Oracle UDT类型。

自定义输入实施 

＃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整数
私有mIdCajaVinculada为整数
私有mGlosaMovimiento为字符串
私有mIdTipoMovimiento为整数
私有mIdModoRecaudacionPago为整数
 #Region" constructor" 
 Public Sub New（）
 Me.mIsNull = True 
 End Sub 
 #End Region 
 #Region" properties" 
 
< OracleObjectMappingAttribute（" MontoMovimiento"）> 
 Public Property MontoMovimiento（）As Double 
 Get 
 Return Me.mMontoMovimiento 
 End Get 
 Set（value As Double）
 Me.mMontoMovimiento = value 
结束集
结束属性
< OracleObjectMappingAttribute（" IdMoneda"）> 
 Public Property IdMoneda（）As Integer 
 Get 
 Return Me.mIdMoneda 
 End Get 
 Set（value As Integer）
 Me.mIdMoneda = value 
结束集
结束属性
< OracleObjectMappingAttribute（" IdCaja"）> 
公共财产IdCaja（）As Integer 
 Get 
返回Me.mIdCaja 
结束获取
设置（值为整数）
 Me.mIdCaja = value 
结束集
结束属性
< OracleObjectMappingAttribute（" IdCajaVinculada"）> 
公共财产IdCajaVinculada（）As Integer 
 Get 
 Return Me.mIdCajaVinculada 
 End Get 
 Set（value As Integer）
 Me.mIdCajaVinculada = value 
结束集
结束属性
< OracleObjectMappingAttribute（" GlosaMovimiento"）> 
 Public Property GlosaMovimiento（）As String 
 Get 
 Return Me.mGlosaMovimiento 
 End Get 
 Set（value As String）
 Me.mGlosaMovimiento = value 
结束集
结束属性
< OracleObjectMappingAttribute（" IdTipoMovimiento"）> 
公共财产IdTipoMovimiento（）As Integer 
 Get 
 Return Me.mIdTipoMovimiento 
 End Get 
 Set（value As Integer）
 Me.mIdTipoMovimiento = value 
结束集
结束属性
< OracleObjectMappingAttribute（" IdModoRecaudacionPago"）> 
公共财产IdModoRecaudacionPago（）As Integer 
 Get 
 Return Me.mIdModoRecaudacionPago 
 End Get 
 Set（value As Integer）
 Me.mIdModoRecaudacionPago = value 
结束集
结束属性
公共共享ReadOnly属性Null（）为T_MOVIMIENTOCAJAII 
获取
 Dim t_movimientocajaII为新T_MOVIMIENTOCAJAII（）
 t_movimientocajaII.mIsNull = True 
返回t_movimientocajaII 
结束获取
结束属性
 
公共ReadOnly属性IsNull As Boolean实现INullable.IsNull 
获取
返回Me.mIsNull 
 End Get 
 End Property 
 #End Region 
 
 Public Sub FromCustomObject（con as OracleConnection，pUdt As IntPtr）Implements IOracleCustomType.FromCustomObject 
 Or acleUdt.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）实现IOracleCustomType.ToCustomObject 
 MontoMovimiento = DirectCast（OracleUdt.GetValue（con，pUdt，" MontoMovimiento"），Double）
 IdMoneda = DirectCast（OracleUdt.GetValue（con，pUdt，&） ; IdMoneda"），Integer）
 IdC aja = 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" ;工厂类" 
< OracleCustomTypeMappingAttribute（" CAJA.T_MOVIMIENTOCAJAII"）> 
公共类T_MOVIMIENTOCAJAIIFacotry 
实现IOracleCustomTypeFactory 
 
公共函数CreateObject（）As IOracleCustomType实现IOracleCustomTypeFactory.CreateObject 
 Dim obj作为T_MOVIMIENTOCAJAII = New T_MOVIMIENTOCAJAII（）
返回obj 
结束函数
结束类
 #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" 
< OracleArrayMapping（）> 
公共财产Caja_T_MovimientoCajaII（）作为T_MOVIMIENTOCAJAII（）
获取
返回Me.mCaja_T_MovimientoCajaII 
结束获取
 
设置（值为T_MOVIMIENTOCAJAII（））
 Me.mCaja_T_MovimientoCajaII = value 
 End Set 
 End Property 
 
 Public ReadOnly Property IsNull As Boolean Implements INullable.IsNull 
 Get 
 Return Me.mIsNull 
结束获取
结束物业
公共共享ReadOnly物业空（）作为CAJA_MOVIMIENTOCAJAII 
获取
 Dim caja_movimientocajaII作为新CAJA_MOVIMIENTOCAJAII（）
 caja_movimientocajaII.mIsNull = True 
返回caja_movimientocajaII 
结束获取
结束物业
 #End Region 
 
 
 
 
 Public Sub FromCustomObject（con作为OracleConnection，pUd t作为IntPtr）实现IOracleCustomType.FromCustomObject 
 OracleUdt.SetValue（con，pUdt，0，Caja_T_MovimientoCajaII）
 End Sub 
 
 Public Sub ToCustomObject（con as OracleConnection，pUdt As IntPtr）实现IOracleCustomType.ToCustomObject 
 Caja_T_MovimientoCajaII = DirectCast（OracleUdt.GetValue（con，pUdt，0），T_MOVIMIENTOCAJAII（））
 End Sub 
 End Class 
 #End Region 
 #Region" Factory Class" 
< OracleCustomTypeMapping（" CAJA.MOVIMIENTOCAJAII"）> 
公共类CAJA_MOVIMIENTOCAJAIIFactory 
实现IOracleArrayTypeFactory，IOracleCustomTypeFactory 
 
 
公共函数CreateArray（numElems As Integer）As Array Implements IOracleArrayTypeFactory.CreateArray 
返回新的CAJA_MOVIMIENTOCAJAII（numElems -  1）{} 
结束函数
 
公共函数CreateStatusArray（numElems As Integer）As Array Implements IOracleArrayTypeFactory.CreateStatusArray 
返回Nothing 
结束函数
 
公共函数CreateObject（）As IOracleCustomType实现IOracleCustomTypeFactory.CreateObject 
返回新的CAJA_MOVIMIENTOCAJAII（）
结束函数
结束类
 #End Region 
 #End Region 
 

Oracle UDT

创建或替换类型CAJA.MOVIMIENTOCAJAII是Caja.T_MovimientoCajaII的表; 

创建或替换类型CAJA.T_MOVIMIENTOCAJAII是对象（
 MontoMovimiento Float NULL，
 IdMoneda NUMBER NULL，
 IdCaja NUMBER NULL，
 IdCajaVinculada NUMBER NULL，
 GlosaMovimiento VARCHAR2（50）NULL，
 IdTipoMovimiento NUMBER NULL，
 IdModoRecaudacionPago NUMBER NULL 
）; 

添加UDT参数 

 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在调试代码时有值

请帮助我... 。

谢谢和问候 

Rahul

解决方案

我将使用您不需要的支持私有变量解决公共属性get / set。

https：// docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/auto-implemented-properties

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"

        <OracleObjectMappingAttribute("MontoMovimiento")>
        Public Property MontoMovimiento() As Double
            Get
                Return Me.mMontoMovimiento
            End Get
            Set(value As Double)
                Me.mMontoMovimiento = value
            End Set
        End Property
        <OracleObjectMappingAttribute("IdMoneda")>
        Public Property IdMoneda() As Integer
            Get
                Return Me.mIdMoneda
            End Get
            Set(value As Integer)
                Me.mIdMoneda = value
            End Set
        End Property
        <OracleObjectMappingAttribute("IdCaja")>
        Public Property IdCaja() As Integer
            Get
                Return Me.mIdCaja
            End Get
            Set(value As Integer)
                Me.mIdCaja = value
            End Set
        End Property
        <OracleObjectMappingAttribute("IdCajaVinculada")>
        Public Property IdCajaVinculada() As Integer
            Get
                Return Me.mIdCajaVinculada
            End Get
            Set(value As Integer)
                Me.mIdCajaVinculada = value
            End Set
        End Property
        <OracleObjectMappingAttribute("GlosaMovimiento")>
        Public Property GlosaMovimiento() As String
            Get
                Return Me.mGlosaMovimiento
            End Get
            Set(value As String)
                Me.mGlosaMovimiento = value
            End Set
        End Property
        <OracleObjectMappingAttribute("IdTipoMovimiento")>
        Public Property IdTipoMovimiento() As Integer
            Get
                Return Me.mIdTipoMovimiento
            End Get
            Set(value As Integer)
                Me.mIdTipoMovimiento = value
            End Set
        End Property
        <OracleObjectMappingAttribute("IdModoRecaudacionPago")>
        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"
    <OracleCustomTypeMappingAttribute("CAJA.T_MOVIMIENTOCAJAII")>
    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"
        <OracleArrayMapping()>
        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"
    <OracleCustomTypeMapping("CAJA.MOVIMIENTOCAJAII")>
    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

解决方案

I'll just address the public property get/set with backing private variable that you don't need.

https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/auto-implemented-properties

这篇关于将Oracle UDT作为IN参数传递到Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！