Hi, I am trying to generate an XML file using the code below and receive the following error;
failed ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: invalid character 60 ('<') found in a Name or Nmtoken
Error at line 47102066
xml_result xmltype;
doc dbms_xmldom.DOMDocument;
ctx DBMS_XMLGEN.ctxHandle;
vv_exit_code varchar2(5);
vv_ctx_open varchar2(1) := 'N';
BEGIN
vv_exit_code := 'XML1';
ctx := dbms_xmlgen.newcontext(p_sql);
vv_ctx_open := 'Y';
DBMS_OUTPUT.put_line(vv_exit_code);
vv_exit_code := 'XML2';
DBMS_XMLGEN.SETCONVERTSPECIALCHARS (ctx,TRUE);
DBMS_OUTPUT.put_line(vv_exit_code);
vv_exit_code := 'XML3';
xml_result := dbms_xmlgen.getXMLType(ctx);
DBMS_OUTPUT.put_line(vv_exit_code);
IF xml_result is not null THEN
vv_exit_code := 'XML4';
doc := dbms_xmldom.newDOMDocument(xml_result);
DBMS_OUTPUT.put_line(vv_exit_code);
vv_exit_code := 'XML5';
dbms_xmldom.writeToFile(doc,p_dir||'/'||p_fileName, 'ISO-8859-1');
DBMS_OUTPUT.put_line(vv_exit_code);
vv_exit_code := 'XML6';
dbms_xmldom.freeDocument(doc);
p_xml_created := 'TRUE';
DBMS_OUTPUT.put_line(vv_exit_code);
ELSE
p_xml_created := 'FALSE';
END IF;
DBMS_XMLGEN.CLOSECONTEXT (ctx);
vv_ctx_open := 'N';
EXCEPTION
WHEN out_of_process_memory THEN
IF vv_ctx_open = 'Y' THEN
DBMS_XMLGEN.CLOSECONTEXT (ctx);
END IF;
gv_err_msg := substr(sqlerrm,1,2000);
DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
dbms_output.put_line('XML_EXPORT failed (out_of_process_memory exception) executing '||p_sql);
raise_application_error(-20906,'XML_EXPORT (out_of_process_memory exception) failed executing '||p_sql);
WHEN OTHERS THEN
IF vv_ctx_open = 'Y' THEN
DBMS_XMLGEN.CLOSECONTEXT (ctx);
END IF;
if xml_result is NULL then
gv_err_msg := substr(sqlerrm,1,2000);
DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
dbms_output.put_line('XML_EXPORT failed (xml results are NULL) executing '||p_sql);
raise_application_error(-20906,'XML_EXPORT (xml results are NULL) failed executing '||p_sql);
else
gv_err_msg := substr(sqlerrm,1,2000);
DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
dbms_output.put_line('XML_EXPORT failed (others exception) executing '||p_sql);
DBMS_OUTPUT.put_line('Export Directory is: '||p_dir||'/'||p_fileName);
raise_application_error(-20906,'XML_EXPORT (others exception) failed executing '||p_sql);
end if;
I have run the p_sql separately and have no issue. The p_sql is select * from <viewname> where date_id > '20100901' and date_id < '20100930'; (For security reasons I am choosing to leave off specific table/view/column names). I have run this procedure with other date ranges and it works for all the views we are using. I have examined all freeform data (data entered by the user without restriction) and none has a '>' in it during this time period.