Imports System
Imports System.Data
Imports Oracle.DataAccess.Client

Module Module1

    Sub Main()

        ' STEP 1
        ' NOTE:Substitute User ID, Password, Data Source
        ' as per your database setup
        Dim connectStr As String = "User Id=scott; Password=tiger; Data Source=orcl9i"

        ' Initialize connection
        Dim connection As OracleConnection
        connection = New OracleConnection(connectStr)
        connection.Open()

        'STEP 2
        ' Set command to execute Test_Arraybind
        ' database stored procedure
        Dim cmd1 As OracleCommand = New OracleCommand("", connection)
        cmd1.CommandText = "Test_Arraybind"
        cmd1.CommandType = CommandType.StoredProcedure

        ' STEP 3
        ' Initialize array with data
        Dim myArrayDeptNo As Int16() = {1, 2, 3}
        Dim myArrayDeptName As String() = {"Dev", "QA", "Facility"}

        ' Setting ArrayCount for command to 3 i.e. max.
        ' number of rows in the preceding arrays.
        cmd1.ArrayBindCount = 3

        ' STEP 4
        ' Instantiate Oracle parameter corresponding to DeptNo
        Dim deptNoParam As OracleParameter = New OracleParameter("deptno", OracleDbType.Int32)
        deptNoParam.Direction = ParameterDirection.Input

        ' Bind Array containing Department numbers
        ' "deptNoParam" Oracle Parameter
        deptNoParam.Value = myArrayDeptNo

        ' Add Oracle Parameter to Command
        cmd1.Parameters.Add(deptNoParam)

        ' Similarly bind Department Name parameter
        Dim deptNameParam As OracleParameter = New OracleParameter("deptname", OracleDbType.Varchar2)

        deptNameParam.Direction = ParameterDirection.Input
        deptNameParam.Value = myArrayDeptName
        cmd1.Parameters.Add(deptNameParam)

        ' STEP 5
        ' Execute the command calling stored procedure
        Try
            cmd1.ExecuteNonQuery()
            Console.WriteLine("{0} Rows Inserted", cmd1.ArrayBindCount)
        Catch e As Exception
            Console.WriteLine("Execution Failed:" + e.Message)
        End Try

        ' Step 6
        ' Clean-up DeptTab table data
        Dim cmd2 As OracleCommand = New OracleCommand("", connection)

        ' Delete all the rows from the DeptTab table
        cmd2.CommandText = "DELETE depttab WHERE deptno = :1"

        ' Bind with an array of 3 items
        cmd2.ArrayBindCount = 3


        Dim param1 As OracleParameter = New OracleParameter()
        param1.OracleDbType = OracleDbType.Int32
        param1.Value = myArrayDeptNo
        cmd2.Parameters.Add(param1)

        ' Execute the delete statement through command
        Try
            cmd2.ExecuteNonQuery()
            Console.WriteLine("Cleaned DeptTab table data")
        Catch e As Exception
            Console.WriteLine("Cleanup Failed:{0}", e.Message)
        Finally
            ' Dispose the OracleCommand objects
            cmd1.Dispose()
            cmd2.Dispose()

            ' Close and Dispose the OracleConnection object
            connection.Close()
            connection.Dispose()

        End Try

    End Sub

End Module