Oracle CLOB and ADO: how to retrieve data? 
Author Message
 Oracle CLOB and ADO: how to retrieve data?

Hi all,

I hope someone can help me out, since I've a severe problem and a

I try to retrieve an Oracle CLOB field to use it for several purposes,
but I fail in doing it.

Environment: VB6 SP5, MDAC 2.5x (can be upgraded to 2.7 if necessary)
and ORACLE OLEDB Provider 8.1.7

I wrote a simple and small program that on load retrieves 10 records
and tries to use the CLOB-field. To help myself, the program generates
a messagebox about the CLOBfield that now says:

Name: ClobFld
Type: 201       (which seems to be OK)
TypeName: Null  (???)
Defined size: 2147483647
Actual size: 0
Long: True
MayDefer: False
Chachedeferred: False

The program has a form with an ADO Data Control named Adodc1 on it

1. What do I do wrong and how to get it running? When I copy the
SQL-statement in TOAD or SQLPLUS I get the desired result. I think it
has to do with the mapping of the datatypes.
2. I want to avoid retrieving the CLOB-field in a loop, using the
GetChunk-method. As far as I know there are ways to do so, something
like using an ADODB-Command object and setting some parameters before
executing the query, but I have no idea how to do this.
(Please don't respond with the usage of a stored procedure: I can not
use that solution, since we now have more than 1 Oracle
Instance/Schema-combinations with this type of data and expect more of
them. The final program will allow the user to choose an
Instance/Schema combination and I don't want to write/maintain stored
procedures for each Instance/Schema combination.)

Here is my code, please take a look at it:

Private dbCon As ADODB.Connection
Private Rst As ADODB.Recordset

Private Sub Form_Initialize()

    Set dbCon = New ADODB.Connection

    With dbCon
        .ConnectionString =
"Provider=OraOLEDB.Oracle.1;OSAuthent=1;Data Source=DatabaseName"
    End With

    Set Rst = New ADODB.Recordset
    Rst.Open "Select REGNO, ClobFld from Schema.Table where REGNO
between 91 and 100", dbCon, adOpenStatic, adLockReadOnly
End Sub

Private Sub Form_Load()
    Set Adodc1.Recordset = Rst
End Sub

Private Sub Form_Terminate()
    Set dbCon = Nothing
    Set Rst = Nothing
End Sub

Private Sub Adodc1_MoveComplete(ByVal adReason As
ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

    Dim MsgStr As String

    Dim sResult As String
    Const lChunkSize as Long = 100
    Dim lFieldLength as Long
    Dim lCharsRead as Long

    If Not Rst.BOF And Not Rst.EOF Then
        With Rst.Fields("ClobFld")
            MsgStr = "Name: " & .Name & Chr(13)
            MsgStr = MsgStr & "Type: " & .Type & Chr(13)
            MsgStr = MsgStr & "Type: " & .Type & Chr(13)
            MsgStr = MsgStr & "Typename: " & TypeName(.Value) &
            MsgStr = MsgStr & "Defined size: " & .DefinedSize &
            MsgStr = MsgStr & "Actual size: " & .ActualSize & Chr(13)
            MsgStr = MsgStr & "Long: " & CBool(.Attributes And
adFldLong) & Chr(13)
            MsgStr = MsgStr & "Maydefer: " & CBool(.Attributes And
adFldMayDefer) & Chr(13)
            MsgStr = MsgStr & "Cachedeferred: " & CBool(.Attributes
And adFldCacheDeferred) & Chr(13)
        End With
        MsgBox MsgStr

        lFieldLength = Rst.Fields("ClobFld").ActualSize
        lCharsRead = 0
        While lCharsRead < lFieldLength
                sResult = sResult &
                lCharsRead = lCharsRead + lChunkSize
    End If

End Sub

Thanks in advance,

Hans Troost

Sun, 24 Oct 2004 15:00:59 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Oracle CLOB data / ADO problem

2. Use ADO to Access BLOB or CLOB fields in Oracle

3. NEED HELP with ORACLE CLOB Datatype and ADO 2.7

4. Writing / Reading to CLOB fields in Oracle using ADO

5. ADO CLOB Oracle

6. Oracle 8.0.5/CLOB/BLOB and ADO

7. Use ADO to Access BLOB or CLOB fields in Oracle

8. Help - ADO and Oracle CLOB datatype???

9. Oracle and CLOB data type

10. VB and Oracle CLOB data

11. How to retrieve Blob data in Oracle using ado

12. Oracle CLOB in ASP pages


Powered by phpBB® Forum Software