Re: OLEDB Provider does not return CLOB data

[Quoted] In article <80ac6502.0205122342.725be56c_at_posting.google.com>, Hans.troost_at_solvay.com says...
> Hi all,
>
> I hope someone can help me out, since I've a severe problem and a
> question.
>
> 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
>

You didn't mention why you want to avoid GetChunk in a loop. It seems to work just fine. If you don't want the loop, just call GetChunk with a chunk size larger than your biggest CLOB.

If you use a Command instead of a Recordset, I think you'll still find yourself using GetChunk sooner or later.

• Tom