Archive

Archive for February, 2011

Working with Oracle UDTs through ODP.NET

February 25, 2011 Leave a comment

It just took me a solid two hours to work through this, so I figured I’d post it here for others to see if they need it.

CREATE OR REPLACE TYPE SSP01953.TYPE_CUSTOMER AS OBJECT   
(CUSTOMER_ID NUMBER,   
CUSTOMER_NAME VARCHAR2(200),   
CUSTOMER_ADDRESS VARCHAR2(200));
Imports Oracle.DataAccess.Types
Imports Oracle.DataAccess.Client

Public Class CustomerType
    Implements IOracleCustomType

    Public Overridable Sub FromCustomObject(ByVal con As Oracle.DataAccess.Client.OracleConnection, ByVal pUdt As System.IntPtr) Implements Oracle.DataAccess.Types.IOracleCustomType.FromCustomObject
        If Me.Name IsNot Nothing Then
            OracleUdt.SetValue(con, pUdt, "CUSTOMER_NAME", Me.Name)
        End If

        OracleUdt.SetValue(con, pUdt, "CUSTOMER_ID", Me.Id)

        If Me.Address IsNot Nothing Then
            OracleUdt.SetValue(con, pUdt, "CUSTOMER_ADDRESS", Me.Address)
        End If
    End Sub

    Public Overridable Sub ToCustomObject(ByVal con As Oracle.DataAccess.Client.OracleConnection, ByVal pUdt As System.IntPtr) Implements Oracle.DataAccess.Types.IOracleCustomType.ToCustomObject
        If Not OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_NAME") Then
            Me.Name = CStr(OracleUdt.GetValue(con, pUdt, "CUSTOMER_NAME"))
        End If

        If Not OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_ID") Then
            Me.Id = CDec(OracleUdt.GetValue(con, pUdt, "CUSTOMER_ID"))
        End If

        If Not OracleUdt.IsDBNull(con, pUdt, "CUSTOMER_ADDRESS") Then
            Me.Address = CStr(OracleUdt.GetValue(con, pUdt, "CUSTOMER_ADDRESS"))
        End If
    End Sub

    
    Public Property Name As String

    
    Public Property Id As Decimal

    
    Public Property Address As String

End Class
Imports Oracle.DataAccess.Types


Public Class CustomerTypeFactory
    Implements IOracleCustomTypeFactory

    Public Function CreateObject() As Oracle.DataAccess.Types.IOracleCustomType Implements Oracle.DataAccess.Types.IOracleCustomTypeFactory.CreateObject
        Return New CustomerType()
    End Function
End Class

Now you can use this with your standard commands.

Dim c As New CustomerType() With {.Name = "Jim", .Address = "543 S 14th St", .Id = 2}


        Using cmd As New OracleCommand("select type_customer(3, 'Scott', '123 Main St') from dual", mConn)
            mConn.Open()
            cmd.CommandType = CommandType.Text
            
            Using rdr = cmd.ExecuteReader()
                Do While rdr.Read()
                    c = CType(rdr.GetValue(0), CustomerType)
                Loop
            End Using
            mConn.Close()

        End Using

        LabelControl1.Text = CStr(c.Id)
        LabelControl2.Text = c.Name
        LabelControl3.Text = c.Address
Advertisements