Imports System
Imports System.Text
Imports Oracle.DataAccess.Types
Imports Oracle.DataAccess.Client

Module Module1

    Sub Main()

        ' Step 1
        ' Establish connection using ODP.NET
        ' NOTE: Modify 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 = New OracleConnection(connectStr)
        connection.Open()
        Console.WriteLine("Connected to database")
        Console.WriteLine(" ")

        'Step 2
        ' Declare Oracle objects
        Dim txn As OracleTransaction
        Dim cmd As OracleCommand = New OracleCommand("", connection)
        Dim reader As OracleDataReader
        Dim clob As OracleClob

        Try
            ' Step 3
            ' Start a transaction
            txn = connection.BeginTransaction()

            ' Step 4
            ' Lock the result set using the "FOR UPDATE" clause
            cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"
            reader = cmd.ExecuteReader()

            ' Step 5
            ' Read data from OracleDataReader using a proper typed
            ' accessor to a Oracle LOB object

            reader.Read()
            clob = reader.GetOracleClob(0)
            Console.WriteLine("Old Data: {0}", clob.Value)
            Console.WriteLine(" ")

            ' Step 6
            ' Modify the CLOB column of the row
            Dim ending As String = " The end."


            ' Append CLOB data to the current OracleCLOB instance
            clob.Append(ending.ToCharArray(), 0, ending.Length)

            ' Release the lock
            txn.Commit()
            Console.Write("Updated to new data:")
        Catch e As Exception
            Console.WriteLine("Error: {0}", e.Message)
        End Try

        Try
            ' Step 7
            ' Fetch the modified data from the database
            ' Lock the complete resultset using the "FOR UPDATE" clause
            cmd.CommandText = "SELECT story FROM multimedia_tab FOR UPDATE"
            reader = cmd.ExecuteReader()
            reader.Read()
            clob = reader.GetOracleClob(0)
            Console.WriteLine(clob.Value)
            Console.WriteLine(" ")


            ' Step 8
            ' Reset data with the old value
            ' Start an Oracle transaction
            txn = connection.BeginTransaction()

            ' Clear the contents of the current instance of OracleCLOB object
            clob.Erase()

            ' Re-write a string to the old value
            Dim blr1 As StringBuilder = New StringBuilder()
            blr1.Append("'This is a long story. Once upon a time ...',")

            Dim oldData As String = blr1.ToString()

            ' Step 9

            ' Write the byte array into the OracleCLOB object
            clob.Write(oldData.ToCharArray(), 0, oldData.Length)

            ' Update the data and release the lock
            txn.Commit()
            Console.WriteLine("Old data again: {0}", clob.Value)
        Catch e As Exception
            Console.WriteLine("Error: {0}", e.Message)
        Finally
            ' Dispose OracleCommand object
            cmd.Dispose()

            ' Close and Dispose OracleConnection object
            connection.Close()
            connection.Dispose()
        End Try
    End Sub
End Module