samir25
asked on
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.setpreservewhite space (p, true);
xmlparser.setvalidationmod e (p, false);
htp.p('here12');
xmlparser.parseclob (p, r1.xml_data);
htp.p('here1');
xdoc := xmlparser.getdocument (p);
htp.p('here2');
xlist := xmldom.getelementsbytagnam e (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),'--->','<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.
if r1.status = 'ExceptionOccured' then
p_spec := null;
htp.p('here11');
p := xmlparser.newparser;
xmlparser.setpreservewhite
xmlparser.setvalidationmod
htp.p('here12');
xmlparser.parseclob (p, r1.xml_data);
htp.p('here1');
xdoc := xmlparser.getdocument (p);
htp.p('here2');
xlist := xmldom.getelementsbytagnam
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),'--->','<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.
ASKER
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?
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?
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.
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.
ASKER
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?
can some other expert help on this?
the loop stops at this point xmlparser.parseclob (p, r1.xml_data);
why?
>>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.
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.
ASKER
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.
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.
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-exchan ge<DOT>com
Also include the SQL as you have 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-exchan
Also include the SQL as you have it.
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.
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.
ASKER
i am looking for exception node???
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.
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.
ASKER
right now i donot have access to the server any further help.
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?
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?
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(n ewClob,TRU E);
dbms_lob.open(newClob,dbms _lob.lob_r eadwrite);
dbms_lob.copy(newClob,tmpC lob, 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,era seAmount,d bms_lob.in str(newClo b,'<TraceL og>',1)+11 );
-- clean up StatusLog
eraseAmount:= dbms_lob.instr(newClob,'</ StatusLog> ',1) - (dbms_lob.instr(newClob,'< StatusLog> ',1)+11);
dbms_lob.erase(newClob,era seAmount,d bms_lob.in str(newClo b,'<Status Log>',1)+1 1);
-- clean up TraceLog
eraseAmount:= dbms_lob.instr(newClob,'</ TraceLog>' ,1) - (dbms_lob.instr(newClob,'< TraceLog>' ,1)+10);
dbms_lob.erase(newClob,era seAmount,d bms_lob.in str(newClo b,'<TraceL og>',1)+10 );
-- Make it a DOM document
doc := dbms_xmldom.newDomDocument (newClob);
-- free CLOB since we no longer need it
dbms_lob.freetemporary(new Clob);
-- Make it a Node
topnode := dbms_xmldom.makeNode(doc);
-- Grab All Exception nodes that are a decendant of the root node
nodeList := dbms_xslprocessor.selectNo des(topnod e,'descend ant::Excep tion');
num_nodes := dbms_xmldom.getLength(node List);
dbms_output.put_line('Exce ptions found: ' || num_nodes);
for i in 0..num_nodes-1 loop
node := dbms_xmldom.item(nodeList, i);
dbms_output.put_line('Exce ption: ' || dbms_xmldom.getnodevalue(d bms_xmldom .getfirstc hild(node) ));
end loop;
dbms_xmldom.freeDocument(d oc);
end;
/
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(n
dbms_lob.open(newClob,dbms
dbms_lob.copy(newClob,tmpC
dbms_lob.close(newClob);
-- clean up MessageLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
-- clean up StatusLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
-- clean up TraceLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
-- Make it a DOM document
doc := dbms_xmldom.newDomDocument
-- free CLOB since we no longer need it
dbms_lob.freetemporary(new
-- Make it a Node
topnode := dbms_xmldom.makeNode(doc);
-- Grab All Exception nodes that are a decendant of the root node
nodeList := dbms_xslprocessor.selectNo
num_nodes := dbms_xmldom.getLength(node
dbms_output.put_line('Exce
for i in 0..num_nodes-1 loop
node := dbms_xmldom.item(nodeList,
dbms_output.put_line('Exce
end loop;
dbms_xmldom.freeDocument(d
end;
/
ASKER
one ques for which i am very curios. why is it that in your code u dont use a parser? i will ur code.
ASKER
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
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
ASKER
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,era seAmount,d bms_lob.in str(newClo b,'<TraceL og>',1)+11 );
-- clean up StatusLog
eraseAmount:= dbms_lob.instr(newClob,'</ StatusLog> ',1) - (dbms_lob.instr(newClob,'< StatusLog> ',1)+11);
dbms_lob.erase(newClob,era seAmount,d bms_lob.in str(newClo b,'<Status Log>',1)+1 1);
-- clean up TraceLog
eraseAmount:= dbms_lob.instr(newClob,'</ TraceLog>' ,1) - (dbms_lob.instr(newClob,'< TraceLog>' ,1)+10);
dbms_lob.erase(newClob,era seAmount,d bms_lob.in str(newClo b,'<TraceL og>',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
-- clean up MessageLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
-- clean up StatusLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
-- clean up TraceLog
eraseAmount:= dbms_lob.instr(newClob,'</
dbms_lob.erase(newClob,era
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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.
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.
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?
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?
ASKER
yes...for this case i care only for 2 cases fails and exceptions.
an ex of fail file i have sent to u
an ex of fail file i have sent to u
ASKER
i am a dotnet person myself. what do i need to do?i mean what is in ur mind?
ASKER
also do u mean to say that any node > 64k will cause prob even if i dont refer it?
ASKER
how did u loop thru to find this out?
>>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.
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.
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.
>>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.
ASKER
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?
u can pass varibles from .net through parameterized pl.sql...and execute the procedure.though i dont know what i can achieve thru that?
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.
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.
ASKER
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
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.
I don't want to turn this over to a Moderator so please don't do this any more.
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.