XML nodes over 64K in size cannot be inserted

hi here is the result i am using from a cursor...

if r1.status = 'ExceptionOccured' then
                     p_spec := null;
                     htp.p('here11');
                     p := xmlparser.newparser;
                     xmlparser.setpreservewhitespace (p, true);
                     xmlparser.setvalidationmode (p, false);
                     htp.p('here12');
                     xmlparser.parseclob (p, r1.xml_data);
                     htp.p('here1');
                     xdoc := xmlparser.getdocument (p);
                     htp.p('here2');
                     xlist := xmldom.getelementsbytagname (xdoc, 'Exception');
                     xnode := xmldom.item (xlist, 0);
                     xnode := xmldom.getfirstchild (xnode);                      
                     p_spec := xmldom.getnodevalue (xnode);
                     htp.p(length(p_spec)); htp.p('<br>');
                     p_spec := replace(substr(p_spec, 1, instr(p_spec, chr(10)) - 1),'---&gt;','<br>');
                     htp.p(length(p_spec)); htp.p('<br>');
                     htp.tabledata (p_spec);
                     xmldom.freedocument (xdoc);
                     xmlparser.freeparser (p);
                elsif r1.pf=0 then
                     htp.tabledata ('failed');
                end if;                      
the problem is this loop runs only 6.5 times..though there are 200+ recorrs
its not a size issue for sure bec here is the print out i got

here11 here12 here1 here2 1162
219
here11 here12 here1 here2 3457
478
here11 here12 here1 here2 4605
802
here11 here12

ORA-31167: XML nodes over 64K in size cannot be inserted
ORA-06512: at "XDB.DBMS_XMLPARSER", line 190 ORA-06512: at "WEBSTART.TEST1", line 139 what is the prob exactly.
LVL 1
samir25Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
The code I posted propery executed against the file you sent me using 10.2.0.3.  If you have a different file, please send it to the same address and I'll take a look at it.

>>why is it that in your code u dont use a parser?

As I've mentioned, there are many ways to do the same things with XML and the DOM.  I just chose to not use the parser.  Sort of a personal preference.

>>i ran ur code.i still get the same error.

It errored against the exact file you sent me?

>>i think the prob is something else.  seems a bug in oracle

Not a bug.  You have some other node with more than 64K of text.

>>what are these statement doing?

I'm getting rid of the Log elements since one of them was more than 64K and I guessed that the others may be in different files.

>>before these operations adn after erase there is no change

ERASE does just that,  it erases the data.  It doesn't delete.  Basically it writes white-space to the area in question.  For XML that is OK since it only cares about proper tag layout and not white-space.
0
 
slightwv (䄆 Netminder) Commented:
What is the total size of the XML doc?

If possible can you upload the doc in its current format?  EE has an upload site to hold supporting files.  If you can upload the file, I'll provide the details on how to do it.

If you have to modify the contents for security reasons, please run the new doc through your process to verify the problem still occurs.
0
 
samir25Author Commented:
hi slightwv
my xml is around 2mb. how can i make cahnges to it. i am really concerned on security reasons to post it public.
i was thinking the p_spec was not getting empty. could that be a reason?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
The 2 meg doesn't worry me.  I figured you couln't upload it but I was hoping.

If you could somehow use search/replace to dummy up the document enough that you won't give out 'secrets' so we can all play with the same document it would help a lot.  If not, we'll keep trying...

It's been a while since I've played with the DOM but on a quick scan of the snippet you posted nothing jumps out at me.

I think it's the same problem you've had since you start working on this.  One of the nodes in the 2 meg XML doc has more than 64K of text.  I was skeptical that the DOM in Oracle would do something that the native EXTRACT wouldn't do but it was worth a shot.

I'm thinking the only other way around this is possibly an external procedure/function and use the DOM in Java ( or .Net or any one of ths supported external procedure languages ) to process this file.

Before you ask, no I've never used external procedures.  I can probably help with the .Net code but I've never played with .Net stored procedures and my Java is so rusty I'd have to say I don't even know it.
0
 
samir25Author Commented:
i can pass you the file on the email? the problem is not the size, the reason why i am so sure is that at that point where it stops, when i try to print that node i t prints...so its not a size problem its something else.
can some other expert help on this?
the loop stops at this point xmlparser.parseclob (p, r1.xml_data);
why?
0
 
slightwv (䄆 Netminder) Commented:
>>the reason why i am so sure is that at that point where it stops

The error message says it is the size of the node.  That's why I'd like to see the file and physically play with it.

>>i can pass you the file on the email?

Unfortunatly, no.  That violates the sites policy.  The data has to be available to all experts.  If you can get permission, I can give you an Experts-Exchange upload site but the data will be available to everyone at EE.
0
 
samir25Author Commented:
well i am surprised why EE would have any concerns on helping someone...and i am sure everyone understands the issue on posting comp data publically...i am sure somone can provide better feedback.

i am sure its not the size issue. either its some constraint with oracle 10.2 or below version or something which i am not able to catch.
0
 
slightwv (䄆 Netminder) Commented:
It's not that they don't want to help.  It just needs to be fair to all experts.  If you would send it to me, you would also have to send it to every expert that wanted it.  This is basically the same as uploading it to their server and having those same experts download it.

I really have to reproduce this on my end and to do that I need the file... SO...

It may get me in a little hot water but....
1: ZIP the file to save some space.
2: Change the .ZIP filename to something like .ZZZ (to fool email servers that won't pass through .ZIP attachemnts)
3: Send it to me:  slightwv<AT>experts-exchange<DOT>com

Also include the SQL as you have it.
0
 
slightwv (䄆 Netminder) Commented:
Unless I mis-counted, the LogText node for the TraceLog node is 73,870 characters.  That is over the 64K limit.

I'll play with the Oracle DOM a little but I don't think you'll be able to process this file in Oracle using any of the existing XML features.
0
 
samir25Author Commented:
i am looking for exception node???
0
 
slightwv (䄆 Netminder) Commented:
I'll continue to experiment but....  even using the DOM (and with the basic SQL calls), it has to load/parse the entire document.  I think that is the problem.

For any other processing do you care about and of the 'Log' nodes?

Just for grins and to save me a lot of testing, make a copy of the file you sent me and delete the entire TraceLog node and see if that runs through your code.

Let me know.  I may have a plan-B that won't be too difficult if it works.  We may be able to do a transformation (XSLT) to eliminate the Log nodes.

Plan-C would be straight forward and should work as well.  Just use DBMS_LOB.ERASE and similar substrings that you already use to erase the 'Log' nodes from the document then the old reliable XPATH SQL should work and you can forget all about the DOM.

0
 
samir25Author Commented:
right now i donot have access to the server any further help.
0
 
slightwv (䄆 Netminder) Commented:
That's cool.

Then if I understand:  All you care about are the 'Exception' nodes and those can appear just about anywhere.

Can they ever appear in the Log nodes?  MessageLog, StatusLog or TraceLog

Will you ever need the text of those nodes for any of your processing?
0
 
slightwv (䄆 Netminder) Commented:
I wanted to get you something to look at before I left for the weekend.

Given all the assumptions about the log nodes and there are 100 different ways to use the DOM, here's what I came up with.

Note:  I notice you use 'xmldom', etc...  Those are the old ways and while they're still around, they may go away soon.  Just add 'dbms_' to the calls and you'll be OK.

Using the text you emailed me, I loaded it into a table that looks like:
create table tab1 ( xml_clob clob);
------------------------------------------------------------
declare
      doc                        dbms_xmldom.DOMDocument;
      topnode                  dbms_xmldom.DOMNode;
      node                  dbms_xmldom.DOMNode;
      nodeList            dbms_xmldom.DOMNodeList;

      elem                  dbms_xmldom.DOMElement;
      text                  dbms_xmldom.DOMText;

      num_nodes            number;
      tmpClob                  clob;
      newClob                  clob;

      eraseAmount            number;

begin
      --get CLOB into variable I can work with
      select xml_clob into tmpClob from tab1;
      dbms_lob.createtemporary(newClob,TRUE);
      dbms_lob.open(newClob,dbms_lob.lob_readwrite);
      dbms_lob.copy(newClob,tmpClob, dbms_lob.getlength(tmpClob),1,1);
      dbms_lob.close(newClob);

      -- clean up MessageLog
      eraseAmount:= dbms_lob.instr(newClob,'</MessageLog>',1) - (dbms_lob.instr(newClob,'<MessageLog>',1)+11);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<TraceLog>',1)+11);

      -- clean up StatusLog
      eraseAmount:= dbms_lob.instr(newClob,'</StatusLog>',1) - (dbms_lob.instr(newClob,'<StatusLog>',1)+11);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<StatusLog>',1)+11);

      -- clean up TraceLog
      eraseAmount:= dbms_lob.instr(newClob,'</TraceLog>',1) - (dbms_lob.instr(newClob,'<TraceLog>',1)+10);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<TraceLog>',1)+10);

      -- Make it a DOM document
      doc := dbms_xmldom.newDomDocument(newClob);

      -- free CLOB since we no longer need it
      dbms_lob.freetemporary(newClob);

      -- Make it a Node
      topnode := dbms_xmldom.makeNode(doc);

      -- Grab All Exception nodes that are a decendant of the root node
      nodeList := dbms_xslprocessor.selectNodes(topnode,'descendant::Exception');

      num_nodes := dbms_xmldom.getLength(nodeList);
      dbms_output.put_line('Exceptions found: ' || num_nodes);

      for i in 0..num_nodes-1 loop
            node := dbms_xmldom.item(nodeList, i);
            dbms_output.put_line('Exception: ' || dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(node)));
      end loop;

      dbms_xmldom.freeDocument(doc);


end;
/
0
 
samir25Author Commented:
one ques for which i am very curios. why is it that in your code u dont use a parser? i will ur code.
0
 
samir25Author Commented:
i ran ur code.i still get the same error. though some extra values are getting printed. but its stops at some point and throws the error.
i think the prob is something else. whatever its driving me absoltely mad.i would really llike to knwo the real reason of this issue. seems a bug in oracle
0
 
samir25Author Commented:
what are these statement doing?
   -- clean up MessageLog
      eraseAmount:= dbms_lob.instr(newClob,'</MessageLog>',1) - (dbms_lob.instr(newClob,'<MessageLog>',1)+11);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<TraceLog>',1)+11);

      -- clean up StatusLog
      eraseAmount:= dbms_lob.instr(newClob,'</StatusLog>',1) - (dbms_lob.instr(newClob,'<StatusLog>',1)+11);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<StatusLog>',1)+11);

      -- clean up TraceLog
      eraseAmount:= dbms_lob.instr(newClob,'</TraceLog>',1) - (dbms_lob.instr(newClob,'<TraceLog>',1)+10);
      dbms_lob.erase(newClob,eraseAmount,dbms_lob.instr(newClob,'<TraceLog>',1)+10);



because when i printed the length of newClob before these operations adn after erase there is no change!!! why? and hence i have the same problem
0
 
slightwv (䄆 Netminder) Commented:
I received the latest file and am looking at it.

While I'm looking can you confirm that you only care about the Exception nodes for this app.  You don't need to display ANY other node?
0
 
slightwv (䄆 Netminder) Commented:
FYI:  In the latest file it was the following nodes that contained over 64K:

TxSRecord
RxSRecord


I deleted those and it ran fine.

I still want to know the answer to the question above.  I'll see if my idea will work while I wait.
0
 
slightwv (䄆 Netminder) Commented:
That was a quick failure.  Sorry but my plan-B won't work in Oracle.  I was thinking I might be able to transform the XML with a simple XSLT and only pull back the Exception nodes but all the XSLT code uses the XMLTYPE.  Since we can't get the data into an XMLTYPE, I can't transform it.

I now believe the only way to properly do this is with an EXTERNAL function/procedure.  Do you have any Java, .Net or C++ people in your shop?
0
 
samir25Author Commented:
yes...for this case i care only for 2 cases fails and exceptions.
an ex of fail file i have sent to u
0
 
samir25Author Commented:
i am a dotnet person myself. what do i need to do?i mean what is in ur mind?
0
 
samir25Author Commented:
also do u mean to say that any node > 64k will cause prob even if i dont refer it?
0
 
samir25Author Commented:
how did u loop thru to find this out?
0
 
slightwv (䄆 Netminder) Commented:
>>also do u mean to say that any node > 64k will cause prob even if i dont refer it?

Yes.  I mentioned this a while back:  "even using the DOM (and with the basic SQL calls), it has to load/parse the entire document."

Think about it:  The DOM loads up the entire document.  When you issue the XPATH call, it will have to look at each and every node to see it it matches.  It works fine until it trys to look into a node that exceeds the 64K limit.

>>i am a dotnet person myself. what do i need to do?i mean what is in ur mind?

Starting with 10g, you can create .Net stored procedures.  I've never done it but the papers say it's possible.

I'm thinking a couple of things.  I'm not sure which is better so I'll throw them both out and you can decide.

1:  Use the .Net XSLT to transform the original doc and pass back an XML doc to Oracle that only has the nodes you want.

2:  Use the .Net DOM and create something you can pass into the PL/SQL procedure.  I've passed CURSORS from PL/SQL to .Net so I'm sure you can pass something similar from .Net into PL/SQL.  Check out Assosciative arrays.
0
 
slightwv (䄆 Netminder) Commented:
oops....  missed one:

>>how did u loop thru to find this out?

That's the problem.  You can't with Oracle.  I used my favorite text editor and a quick manual scan to look for the 'bad' nodes.  Blocks of 64K+ text pretty much jump off the screen at you.  They're pretty easy to find.
0
 
samir25Author Commented:
well u provided informative feedback.i have exactly 1.5 days to finish this + couple of other things...dont have much time to explore in detail. but i will surely like to implement thru dotnet.
u can pass varibles from .net through parameterized pl.sql...and execute the procedure.though i dont know what i can achieve thru that?

0
 
slightwv (䄆 Netminder) Commented:
Again, I've only read about it. For all purposes it will look like a PL/SQL stored procedure but you are making an external call to .Net code.

Check out:
http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_sps.html

My thinking here is I don't believe the .Net DOM or XSLT has the 64K restriction for a node.  You should be able to fully process the file and get it into a format Oracle XML can handle.  Then it's a simple matter to extract the data.

The latest code you emailed me really doens't seem related to this question.  It is also missing some declarations.  For example:  xpar.
0
 
samir25Author Commented:
before i started on this i used asp.net /vb.net/odp.net with oracle stored procedure to run extract the excpetion node. i had been successful in that. i left it in between thinking it will be faster thru this way.i feel i am back to the same sitaution. i dont have too much time to explore and go back on hte old code. some similar work is done on asp for extracting the node in my app. all i need to do is modify to run xpath.i ve floated a fresh ques on asp.i hope someone replies and meanwhile i wil close this
0
 
slightwv (䄆 Netminder) Commented:
I've seen the new question and have already posted a warning in it.  I also see from your profile that you've been warned a few times about duplicate questions.

I don't want to turn this over to a Moderator so please don't do this any more.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.