Converting CLOB to a usable String in VB.NET

I'm writing a tool the retrieves data from an Oracle database. The majority of the fields are VARCHAR2, but there's one particular field I want to retrieve that is a CLOB. I'm trying to convert the text in this CLOB to a string that I can then run a regex against it to isolate a certain bit of information and then display that information in the program's console. Unfortunately, I can't seem to figure out how to convert this CLOB into a usable string.

I've tried a lot of different things, but what I've got at the moment is:

    With OraDynaset
        If .RecordCount > 0 Then
            .MoveFirst()
            While (Not .EOF Or .BOF)
                m_StrThis = .Fields("This").Value
                clobSupportProcs = .Fields("That").Value
                m_strThat = (clobSupportProcs).ToString
                .MoveNext()
            End While
        End If
    End With

The field "This" works fine, as it's not a CLOB. The field "That" is the troublemaker. I've read in other posts t开发者_高级运维hat there's a way to stream this in chunks into something usable, but I've yet to see a clear example of how this is done.

I'd appreciate any CONSTRUCTIVE comments or help. I understand that using a CLOB wasn't necessary to begin with -- this database is over 10 years old however. I have no control over that. I understand that I'm connecting to the database in a less than elegant way. I don't care about that. I just need these two little bits of information. I'm not a pro, just a guy trying to write a tool that will make his life easier. Any help is much appreciated. Unhelpful comments are not.

Thanks!

I've worked with Oracle CLOBs with PowerShell using ODAC (Oracle Data Access Components).

http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

http://download.oracle.com/docs/cd/B28359_01/win.111/b28375.pdf

ODAC library will allow you to work with Oracle with VB.NET or C#.

1. Create a 'Oracle.DataAccess.Client.OracleConnection' object and connect.
2. Create a 'Oracle.DataAccess.Client.OracleCommand' query command object.
3. Get the reader object from the command object 'ExecuteReader()'
4. Iterate over the records 'while (reader.Read())'

5. Do processing here...

   $id = $reader.getstring(0) # DOCUUID
   $xmlDoc = [xml] $reader.getstring(1) # XML (THIS IS THE CLOB)
   $datasetName = $reader.getstring(2) # DATASETNAME
   

6. Here is how to update the clob (PowerShell code)

   $sql = "UPDATE GPT_METADATA SET XML = :1 WHERE DOCUUID = '" + $id + "'"
   $updateCmd = New-Object Oracle.DataAccess.Client.OracleCommand ($sql, $conn)
   
   $param = New-Object Oracle.DataAccess.Client.OracleParameter (
      "xml", #Name
      [Oracle.DataAccess.Client.OracleDbType]::Clob, #Type
      $xmlDoc.OuterXml, #Data
      'Input' #Direction
    )
   

7. Execute the update

   $newParam = $updateCmd.Parameters.Add($param) 
   $result = $updateCmd.ExecuteNonQuery()
   

I was finally able to figure this out by playing with the possible properties and methods.

The solution is:

Dim clobSupportProcs As OracleInProcServer._IOraClob
Dim clobSize As Integer
Dim clobOut As String

With OraDynaset
        If .RecordCount > 0 Then
            .MoveFirst()
            While (Not .EOF Or .BOF)
                m_StrThis = .Fields("THIS").Value
                clobSupportProcs = .Fields("THAT").Value
                clobSize = clobSupportProcs.size

                If clobSize > 0 Then
                    clobSupportProcs.Read(clobOut)
                    m_strThat= clobOut
                Else
                    m_strThat= "None"
                End If

                .MoveNext()
            End While
        End If
    End With

So basically, you read the clob to a string (clobOut).