I am trying to retrieve an oracle field with datatype clob into a VB array without reformating the retrieval SQL string (simply SELECT * FROM my_table). I am using an ADO recordset.
The clob values are lost when I close the connection or do any other action with the recordset. How can I achieve this.
In one of my projects I tried to manipulate BLOBs and CLOBs using ADO, but without any success, until I switched to Oracle Objects 4 OLE. You need to install and configure Oracle client on your machine, the in project references add "Oracle InProc Server 4.0 Type Library".
Here's a sample code I used for BLOBs:
Dim OraSession As OracleInProcServer.OraSessionClass
Dim OraDatabase As OracleInProcServer.OraDatabase
Dim OraDynaset As OracleInProcServer.OraDynaset
Dim srcImg As OraBlob
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("myDBName", "login/password", 0&)
Set OraDynaset = OraDatabase.DbCreateDynaset("SELECT src_id, src_image from Sources where src_file_name like '%.png'", 0&)
With OraDynaset
If .RecordCount > 0 Then
.MoveFirst
While (Not .EOF Or .BOF)
Set srcImg = .Fields("src_image").Value
srcImg.CopyToFile ("C:\sources\" & srcID & ".png")
.MoveNext
Wend
End If
End With
Set OraDynaset = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing
