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。但是程式中直接判斷是否需要轉換比較麻煩，程式的适用性受限制。