VBA读写Oracle的Blob数据

在处理Oracle的Blob、Clob、long raw数据时，用PLSQL Developer不容易直接将数据全部导出，尝试了一下用VBA将数据保存到文件中。

测试数据表Test包含4个字段MainID、clobField、longrawField、BlobField。建表sql略去，MainID是主键。

先用  select * from Test for update  在主键中插入数据1,2,3，其他字段为空。内容如下：

1. VBA写入Blob数据到数据库

本地有文件夹E:\Blob\，其中有文件名为 Test#MainID#BlobFiled#1 和Test#MainID#BlobFiled#2 的两个文件。目的是读取这两个文件到表Test的BlobField字段中对应MainID=1和MainID=2两行中。

工程中要添加两个引用以使用ADODB和FileSystemObject：

代码如下：

Public Sub VBClobInput()
    Dim sourcePath As String, targetPath As String, tempStr() As String
    Dim sqlStr As String, i As Long
    Dim connectStr As String, cnn As New ADODB.Connection, rs As ADODB.Recordset, cmd As New ADODB.Command, tnsNames As String
    Dim tableName As String, primaryField As String, primaryKey As String, clobField As String
    Dim byteContent() As Byte
    Dim fso As New FileSystemObject, rootFolder As Folder, blobFile As File
    
    tnsNames = """(DESCRIPTION =" & _
              "    (ADDRESS_LIST =" & _
              "      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))" & _
              "    )" & _
              "    (CONNECT_DATA =" & _
              "      (SERVICE_NAME = ORCL)" & _
              "    )" & _
              "  )"""
              
    connectStr = "provider=oraOledb.oracle;password=hehe;user id=worker;data source=" & tnsNames & ";persist security info=true"
    cnn.Open connectStr
    targetPath = "E:\Blob"
    Set rootFolder = fso.GetFolder(targetPath)
    For Each blobFile In rootFolder.Files
        Open blobFile For Binary As #1
            ReDim byteContent(1 To LOF(1)) As Byte
            Get #1, , byteContent
        Close #1
        tempStr = Split(blobFile.Name, "#")
        tableName = tempStr(0)
        primaryField = tempStr(1)
        clobField = tempStr(2)
        primaryKey = tempStr(3)
        Set rs = New ADODB.Recordset
        rs.ActiveConnection = cnn
        rs.CursorType = adOpenDynamic
        rs.LockType = adLockOptimistic
        rs.CursorLocation = adUseClient
        rs.Source = "select * from " & tableName & " where " & primaryField & " = '" & primaryKey & "' "
        rs.Open
        If Not rs.EOF Then
            rs.Fields(clobField).AppendChunk byteContent
            rs.Update
            rs.Close
        Else
            rs.Close
        End If
    Next
    cnn.Close
End Sub

在PLSQL Developer中查看结果：

说明：

• • 为了批量处理，blob文件的命名规则是“表名#主键字段名#Blob字段名#主键唯一值”，没有文件扩展名。当然可以自己定义规则。
  • 对clob和long raw类型可以使用相同的代码处理

2. VBA读取Blob数据并保存到文件

读取数据代码如下：

Public Sub VBClobOutput()
    Dim sourcePath As String, targetPath As String, fileName As String
    Dim oldTxtContent As String, newTxtContent As String, curRow As Long
    Dim sqlStr As String, tempStr As String, i As Long
    Dim connectStr As String, cnn As New ADODB.Connection, rs As New ADODB.Recordset, cmd As New ADODB.Command, tnsNames As String
    Dim tableName As String, primaryKey As String, clobField As String
    Dim clobContent As String
    Dim byteContent() As Byte
    
    tnsNames = """(DESCRIPTION =" & _
              "    (ADDRESS_LIST =" & _
              "      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.*.*)(PORT = 1521))" & _
              "    )" & _
              "    (CONNECT_DATA =" & _
              "      (SERVICE_NAME = ORCL)" & _
              "    )" & _
              "  )"""

    connectStr = "provider=oraOledb.oracle;password=hehe;user id=worker;data source=" & tnsNames & ";persist security info=true"
    tableName = "Test": primaryKey = "MainID": clobField = "BlobField"
    targetPath = "E:\Blob\1\"
    
    cnn.Open connectStr
    If cnn.State = 1 Then
        Set cmd.ActiveConnection = cnn
        sqlStr = "select " & primaryKey & "," & clobField & " from " & tableName
        cmd.CommandText = sqlStr
        Set rs = cmd.Execute
        If Not rs.EOF Then
            rs.MoveFirst
            While Not rs.EOF
                fileName = tableName & "#" & primaryKey & "#" & clobField & "#" & rs(0)
                byteContent = rs.Fields(clobField).GetChunk(rs.Fields(clobField).ActualSize) '虽然中间没有经过字符串转换，VB还是会在后台把任何可能的字符串转变成unicode
                Open targetPath & fileName For Binary As #1
                    Put #1, , byteContent
                Close #1
                rs.MoveNext
            Wend
        End If
    End If
    cnn.Close
End Sub

说明：这段代码可以处理Blob。但VB的后台字符串转换，会把clob中的字符自动转换为unicode编码，对于以UTF-8存储[一般xml都是这种格式]的clob处理时会比较麻烦。估计long raw也会存在这样的问题。我能想到的解决办法有3中：

1. 如果变更数据库字段类型不会对使用产生影响，可以把Clob用Blob代替。

2.如果不能更改数据库，可以用C#等语言编写代码绕过VB的unicode转换陷阱。

3.针对VB转换过的字符串，再使用API函数WideCharToMultiByte将Unicode转换为UTF-8。但是程序中直接判断是否需要转换比较麻烦，程序的适用性受限制。