1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
| ORACLE :
create or replace TYPE zzz_obj_ville AS OBJECT ( code_postal varchar2(5), ville varchar2(200)) not final;
create Table zzz_tab_ville ( code_postal varchar2(5), ville varchar2(200));
create or replace procedure zzz_procedure ( pin_ville in zzz_obj_ville )
is
begin
insert into zzz_tab_ville values ( pin_ville.code_postal, pin_ville.ville);
commit;
end;
*******************************************************************
VB.NET:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
Public Class clsTest
Implements IOracleCustomType, INullable
Private m_IsNull As Boolean
Private m_CodePostal As OracleString
Private m_Ville As String
<OracleObjectMappingAttribute("CODE_POSTAL")> _
Public Overridable Property CodePostal() As OracleString
Get
Return m_CodePostal
End Get
Set(value As OracleString)
m_CodePostal = value
End Set
End Property
<OracleObjectMappingAttribute("VILLE")> _
Public Overridable Property Ville() As String
Get
Return m_Ville
End Get
Set(value As String)
m_Ville = value
End Set
End Property
Public Sub FromCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.FromCustomObject
OracleUdt.SetValue(con, pUdt, "CODE_POSTAL", Me.CodePostal)
OracleUdt.SetValue(con, pUdt, "VILLE", Me.Ville)
End Sub
Public Sub ToCustomObject(con As OracleConnection, pUdt As IntPtr) Implements IOracleCustomType.ToCustomObject
'The ToCustomObject method is used to initialize a custom object from the
'specified Oracle
'Object or Collection by retrieving attribute or element values
'respectively through the OracleUdt.GetValue method.
Me.CodePostal = DirectCast(OracleUdt.GetValue(con, pUdt, "CODE_POSTAL"), OracleString)
Me.Ville = OracleUdt.GetValue(con, pUdt, "VILLE").ToString
End Sub
Public ReadOnly Property IsNull As Boolean Implements INullable.IsNull
Get
Return Me.m_IsNull
End Get
End Property
Public Shared ReadOnly Property Null() As clsTest
Get
Dim obj As New clsTest()
obj.m_IsNull = True
Return obj
End Get
End Property
Public Overrides Function ToString() As String
' TODO : Return a string that represents the current object
Return ""
End Function
Public Shared Function Parse(str As String) As clsTest
' TODO : Add code needed to parse the string and get the object represented by the string
Return New clsTest()
End Function
End Class
<OracleCustomTypeMappingAttribute("ZZZ_OBJ_VILLE")> _
Public Class clsTestFactory
Implements IOracleCustomTypeFactory
Public Function CreateObject() As IOracleCustomType Implements IOracleCustomTypeFactory.CreateObject
Dim obj As New clsTest()
Return obj
End Function
End Class
************************
' Code appelant
Dim MonTest As New clsTest()
MonTest.CodePostal = "cp"
MonTest.Ville = "ville"
Dim cmdProcedure As OracleCommand = New OracleCommand
Try
With cmdProcedure
.Connection = ...
.CommandType = CommandType.StoredProcedure
.CommandText = "<user proprio>.zzz_procedure"
Dim param As OracleParameter = New OracleParameter
param.OracleDbType = OracleDbType.Object
param.Direction = ParameterDirection.Input
' Note: The UdtTypeName is case-senstive
param.UdtTypeName = "<user proprio>.ZZZ_OBJ_VILLE"
param.Value = MonTest
cmdProcedure.Parameters.Add(param)
End With
cmdProcedure.ExecuteNonQuery()
Catch oraex As OracleException
...
Catch ex As Exception
...
End Try |
Partager