Re: Sending CLOBs to Oracle using VB/ADO

From: Tim Dale <dev_at_cavendish.co.uk>
Date: 28 Apr 2004 04:06:52 -0700
Message-ID: <7819e11a.0404280306.10dec4d6@posting.google.com>

Thanks for the responses so far - let's see if can reply to some of them .......

Niall Litchfield wrote
> That sounds like you are setting p_afp_clob to be an input parameter,
> when the spec says that it should be an output parameter?

The definition in the package (& body) is defintitely IN. I've also tried redefining the SP (and my code) to IN OUT, with the same result.

Ross MaKay wrote
> Tim, if you have no luck with chasing this one down, maybe try RAW
> instead of CLOB. IIRC, I needed to use RAW (or LONG RAW) for something I
> did on Oracle back when MDAC 2.5 was the most recent version. It is
> possible that either Oracle's or Microsoft's OLE-DB driver now supports
> CLOB, but back then it did not and you needed to use RAW.

Although I've created my own local copy of the SP to test with, the real SP is being supplied by a third party, so I can't change its definition, only the VB/ADO code at my end.

"Val Mazur" <group51a_at_hotmail.com> wrote in message news:<#cohdiMLEHA.3944_at_tk2msftngp13.phx.gbl>...
> Hi Tim
>
> What is your connection string?
>
> --
> Val Mazur
> Microsoft MVP
>

My connection string is generated by the following VB code :

        ElseIf (sProvider = "OraOLEDB.Oracle") Then
            sConnection = "PROVIDER=" & sProvider & ";Data Source=" &
sDatabaseName _
                       & ";User Id=" & sUserId & ";Password=" &

sPassword

Lastly, I've created a second (local) copy of the SP, with only one parameter
(the CLOB), so it looks like this :

    PROCEDURE main2 (p_afp_clob IN CLOB);

and I've tried an alternative bit of VB code which looks like this :

        oDbase.bSetProcedure "main2"
        oDbase.m_cmd.Parameters.Append

oDbase.m_cmd.CreateParameter("@p_afp_clob", adLongVarChar, adParamInput, Len(sXML), sXML)

        oDbase.m_cmd.Properties("SPPrmsLOB") = True
        If (oDbase.bExecute() = True) Then
            MsgBox "Execute Worked !!!"
        Else
            'Error
        End If
        oDbase.m_cmd.Properties("SPPrmsLOB") = False

but the results are the same !! Received on Wed Apr 28 2004 - 06:06:52 CDT