Passing a CLOB from VB using ADO

From: Scott Ferguson <scott_ferguson_at_cpr.ca>
Date: 4 Oct 2001 10:17:47 -0700
Message-ID: <94ee9cd6.0110040917.4833bf07@posting.google.com>

Hi

I'm having problems passing XML in a clob variable from VB using ADO to an Oracle PL/SQL package. I get the clob into Oracle but get an error when I try to parse the XML.

sys.xmlparser.parseClob(psrParserInstance, p_clobNotifyXML);

causes

?err.Description
ORA-20100: ORA-20100: ORA-20100: Error occurred while parsing: Start of root element expected.
ORA-06512: at "CMFAPP.PUBSUB_COUNTRY", line 169 ORA-06512: at line 1

However if I copy the contents of the clob to a varchar2 and then create a new clob variable and copy the varchar2 into it, it works.

      vxmltemp := sys.dbms_lob.substr(p_clobNotifyXML, 2000 , 1);
      dbms_lob.createtemporary(vclobtemp, TRUE, dbms_lob.session); 
      sys.dbms_lob.writeappend(vclobtemp, length(vxmltemp), vxmltemp);

Any thoughts as to if VB is missing a null terminating character or something?

Thanks

Scott

VB
Public Function Load(pstrXMLIN) As String Dim lobjADOCommand As ADODB.Command
Dim Prm1 As New ADODB.Parameter
Dim Prm0 As New ADODB.Parameter
Dim con As New ADODB.Connection
Dim lstrXML As String

Dim Oracon As ADODB.Connection

Set Oracon = CreateObject("ADODB.Connection")
Oracon.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "Data Source=orad02;" & _
                          "User ID=cmfapp;" & _
                          "Password=cmfapp;"

Oracon.Open

    Set lobjADOCommand = New ADODB.Command     With lobjADOCommand

        Set .ActiveConnection = goConnection


        .CommandType = adCmdText

        .CommandText = "{call " & pstrPackage & " (?,?)}"

        .Parameters.Append .CreateParameter("XMLIN", adBSTR,

adParamInput, Len(pstrXMLIN), pstrXMLIN)


        .Parameters.Append .CreateParameter("XMLOUT", adLongVarChar,

adParamOutput, 100000)


        .Properties("SPPrmsLOB") = True

        .Execute

    End With
    Load = lobjADOCommand.Parameters(1).Value end function

Oracle PL/SQL

   PROCEDURE RETRIEVE_COUNTRY ( p_clobNotifyXML IN clob,

                                p_clobCountryXML      OUT   clob)

   IS

BEGIN

      vxmltemp := sys.dbms_lob.substr(p_clobNotifyXML, 2000 , 1);
      dbms_lob.createtemporary(vclobtemp, TRUE, dbms_lob.session); 
      sys.dbms_lob.writeappend(vclobtemp, length(vxmltemp), vxmltemp);

      -- Get a new instance of the Parser
      psrParserInstance := sys.xmlparser.newParser;

      -- Process the XML 
      sys.xmlparser.parseClob(psrParserInstance, p_clobNotifyXML);

      -- Load the parsed Document
      docRequest := sys.xmlparser.getDocument(psrParserInstance);

      -- Free the Parser
      sys.xmlparser.freeParser(psrParserInstance); 

END; Received on Thu Oct 04 2001 - 12:17:47 CDT