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).