Sending CLOBs to Oracle using VB/ADO

Can anyone help ?

I have to pass some XML to an Oracle 8i stored procedure, which is expecting a CLOB, using VB and ADO.

Only, it isn't working. Oracle keeps returning the error : ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments

(If I remove the parameter from the stored procedure and the call to it, it
all works fine. i.e. the other parameters are all correct.)

I've been hunting through Google and most people appear to have done this
without too many problems simply by setting the type of the ADO parameter to
adLongVarChar. I have done this -- it refuses to work.

Before I get any of the obvious replies, I have tried the following :

• I am using the Oracle OLEDB provider rather than the Microsoft one.
• I am using MDAC 2.6 (and have tried 2.8 as well).
• I have included the line : m_cmd.Properties("SPPrmsLOB") = TRUE
• I have upgraded to the latest Oracle provider 8.1.7.3.0. The readme file says this functionality was added at v8.1.7.0.0.

The SP definition is as follows :

  PROCEDURE main (p_afp_clob 	       IN  CLOB
  		  ,pn_contact_inc_id   IN  NUMBER
                  ,pn_gpms_id	       IN  NUMBER
		  ,pn_org_id	       IN  NUMBER
          	  ,pc_error_ind	       OUT VARCHAR2
		  ,pc_error 	       OUT VARCHAR2
          	  ,pc_ref	       OUT VARCHAR2
		  ,pc_prop_val	       IN  VARCHAR2 DEFAULT NULL
		  ,pn_expiration_time  IN  INTEGER  DEFAULT NULL);

and my VB code, which uses a wrapper functions, looks like :

    If (oDbase.bSetProcedure("main") = True) Then

        oDbase.m_cmd.Properties("SPPrmsLOB") = True

        Call oDbase.bAddProcTag("p_afp_clob", sXML, adLongVarChar)
        Call oDbase.bAddProcTag("pn_contact_inc_id", iContactIncId,
adNumeric)
        Call oDbase.bAddProcTag("pn_gpms_id", iGpmsId, adNumeric)
        Call oDbase.bAddProcTag("pn_org_id", iOrgId, adNumeric)
        Call oDbase.bAddProcTag("pc_error_ind", sErrorNo, adVarChar,
adParamOutput)
        Call oDbase.bAddProcTag("pc_error", sErrorText, adVarChar,
adParamOutput)
        Call oDbase.bAddProcTag("pc_ref", sRef, adVarChar,
adParamOutput)
        If Not IsMissing(sPropVal) Then
            Call oDbase.bAddProcTag("pc_prop_val", CStr(sPropVal),
adVarChar)
        End If
        If Not IsMissing(iTimeout) Then
            Call oDbase.bAddProcTag("pn_expiration_time",
CInt(iTimeout), adInteger)
        End If

        If (oDbase.bExecute() = True) Then
            ...

The wrapper function bAddProcTag() sets the direction (defaulted to adParamInput),
name, size and value of the parameter, and then appends the parameter to the ADO
parameters collection. This works fine for quite a number of stored procedures
(the remainer of which only use VARCHAR2 and NUMBER parameters) Received on Tue Apr 27 2004 - 09:46:46 CDT