Re: Large XML into a SP in Oracle

From: kindaska <kindaska_at_yahoo.com>
Date: 22 Apr 2003 03:19:08 -0700
Message-ID: <6500ee2f.0304220219.20967c52@posting.google.com>

Hi again to everyone,
Here's the VB code

strXML = "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?><MYTABLE>"

For lngIndex = 1 To UBound(arrRec01)     

    strXML = strXML & "<ROW num=" & Chr(34) & lngIndex & Chr(34) & ">"
    strXML = strXML & "<MYFIELDONE>" & myvar1 & </MYFIELDONE>"
    strXML = strXML & "<MYFIELDTWO>" & myvar2 & "</MYFIELDTWO>"
    strXML = strXML & "<MYFIELDTHREE>" & myvar3 & "</MYFIELDTHREE>"
    strXML = strXML & "<MYFIELDFOUR>" & myvar4 & "</MYFIELDFOUR>"    
    strXML = strXML & "</ROW>"

  Next lngIndex

  strXML = strXML & "</MYTABLE>"

'len(strXML)> 32Kb

    strsql = "{call pkg_test.p_insert(?)}" <- the SP parameter is a CLOB          Set oCmd = CreateObject("DBCnn.oCommand") <- Our class Command (it's equal to the ADODB.command)                                                     

    oCmd.createCommand <- make also a connection

    Call oCmd.CreateParameter(adLongVarChar ,len(strXML), adParamInput, strXML)

    Set rsTmp = oCmd.executeCommand(strsql)

and this is the SP

Package Body pkg_test
IS

PROCEDURE p_insert (f_xml1 IN clob) IS

 insCtx     DBMS_XMLSave.ctxType;
 righe   	number;

BEGIN
 insCtx := DBMS_XMLSave.newContext ('MYTABLE');  righe:= DBMS_XMLSave.insertXML(insCtx,f_xml1);  DBMS_XMLSave.closeContext(insCtx);
END; You already know the error...

Thanks

Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3EA0A490.EE404786_at_exesolutions.com>...
> charles b black wrote:
>
> > kindaska-
> > Can you send me a code snippet from VB so I can see how you pass the
> > clob from a vb procedure into a SP?
> >
> > cbb
> >
> > On 18 Apr 2003 01:13:52 -0700, kindaska_at_yahoo.com (kindaska) wrote:
> >
> > >> Whose ODBC driver are you using?
> > >HI,
> > >i'm not using an ODBC driver but ORAOLE DB with oracle 8.1.7 client
> > >
> > >I think the problem is focused on the DBMS_XML package. In fact i'm
> > >able to pass the clob from a vb procedure into a SP(or this is what it
> > >seems..)
> > >but when the sp try to insert the clob, that is an xml string over
> > >32K,
> > >it quits with this error:
> > >
> > >executeCommand({call pkg_MYPKG.p_ins(?)})|Errori:-147217900|ORA-29532:
> > >chiamata Java terminata a causa di un'eccezione Java non ottenuta:
> > >oracle.xml.sql.OracleXMLSQLException: Start of root element expected.
> > >ORA-06512: a "DBMS_XMLSAVE", line 67
> > >ORA-06512: a "PKG_MYPKG", line 31
> > >ORA-06512: a line 1|OraOLEDB
> > >
> > >i've controlled the xml, it's well formed.
> > >Another test i've done is to reduce the string to less than 32k, and
> > >transform the sp parameter from clob in a varchar2.. it's all fine, it
> > >inserts the xml.
> > >But if i turn the sp parameter again in clob, with the same xml, it
> > >gives to me the erroro above.
> > >Some suggestion?!
> > >Thanks again
>
> If it is not too large ... post it here if you don't mind so that everyone
> can see it.
>
> Daniel Morgan
Received on Tue Apr 22 2003 - 05:19:08 CDT