OraFAQ Forum: JDeveloper, Java & XML » ERROR ON XML PARSING....IN ORACLE

Home » Developer & Programmer » JDeveloper, Java & XML » ERROR ON XML PARSING....IN ORACLE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ERROR ON XML PARSING....IN ORACLE [message #140733]

Wed, 05 October 2005 07:29

vgadams@monsanto.com
Messages: 2
Registered: September 2005

Junior Member

Hi i am getting the following error can any body help in this....

the table structure as

select * from emp_xml as select * from emp where 1=0;

SQL> DECLARE
2 v_clob xmltype;
3 savCtx DBMS_XMLSTORE.ctxType;
4 v_rows NUMBER;
5 BEGIN
6 -- Query out the content
7 select xmlelement("EMPDETAILS",
8 xmlagg(xmlelement("EMP",
9 xmlforest(empno,job,mgr,hiredate,sal,deptno)))) into v_clob from emp;
10 -- Save the
11 savCtx := DBMS_XMLSTORE.newContext('emp_xml');
12 -- Set the update columns to improve performance
13 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'EMPNO');
14 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'ENAME');
15 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'JOB');
16 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'MGR');
17 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'HIREDATE');
18 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'SAL');
19 DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'DEPTNO');
20 -- Insert the
21 v_rows := DBMS_XMLSTORE.insertxml(savCtx,v_clob);
22 DBMS_XMLSTORE.closeContext(savCtx);
23 DBMS_OUTPUT.PUT_LINE(v_rows || ' rows inserted...');
24 END;
25 /
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "SYS.DBMS_XMLSTORE", line 88
ORA-06512: at line 21

Report message to a moderator

Re: ERROR ON XML PARSING....IN ORACLE [message #144297 is a reply to message #140733]

Tue, 25 October 2005 17:27

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

This is due to the fact you're trying to update a DATE column.

By default, XMLFOREST will convert a date column into an ANSI XML date format, which is :

SQL> SELECT XMLELEMENT("date", sysdate) FROM dual;

XMLELEMENT("DATE",SYSDATE)
---------------------------------------------------------
<date>2005-10-25</date>

So, DBMS_XMLSTORE comes along and tries to update a date column with this value, and can't since the format masks don't match your NLS settings, i.e.

SQL> DECLARE
  2    v_clob xmltype;
  3    savCtx DBMS_XMLSTORE.ctxType;
  4    v_rows NUMBER;
  5  BEGIN
  6    -- Query out the content
  7    select xmlelement("EMPDETAILS", xmlagg(xmlelement("EMP",
  8    xmlforest(empno, ename, job, mgr, hiredate, sal, deptno))))  into v_clob from emp;
  9  
 10    savCtx := DBMS_XMLSTORE.newContext('emp_xml');
 11    dbms_xmlstore.setRowTag( savCtx,'EMP' );
 12  
 13    dbms_xmlstore.setKeyColumn(savCtx, 'EMPNO');
 14  
 15    -- Set the update columns to improve performance
 16    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'EMPNO');
 17    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'ENAME');
 18    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'JOB');
 19    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'MGR');
 20    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'HIREDATE');
 21    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'SAL');
 22    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'DEPTNO');
 23  
 24    v_rows := DBMS_XMLSTORE.insertxml(savCtx,v_clob);
 25    DBMS_XMLSTORE.closeContext(savCtx);
 26    DBMS_OUTPUT.PUT_LINE(v_rows || ' rows inserted...');
 27  END;
 28  /
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "SYS.DBMS_XMLSTORE", line 88
ORA-06512: at line 24


SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

Session altered.

SQL> DECLARE
  2    v_clob xmltype;
  3    savCtx DBMS_XMLSTORE.ctxType;
  4    v_rows NUMBER;
  5  BEGIN
  6    -- Query out the content
  7    select xmlelement("EMPDETAILS", xmlagg(xmlelement("EMP",
  8    xmlforest(empno, ename, job, mgr, hiredate, sal, deptno))))  into v_clob from emp;
  9  
 10    savCtx := DBMS_XMLSTORE.newContext('emp_xml');
 11    dbms_xmlstore.setRowTag( savCtx,'EMP' );
 12  
 13    dbms_xmlstore.setKeyColumn(savCtx, 'EMPNO');
 14  
 15    -- Set the update columns to improve performance
 16    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'EMPNO');
 17    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'ENAME');
 18    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'JOB');
 19    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'MGR');
 20    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'HIREDATE');
 21    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'SAL');
 22    DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'DEPTNO');
 23  
 24    v_rows := DBMS_XMLSTORE.insertxml(savCtx,v_clob);
 25    DBMS_XMLSTORE.closeContext(savCtx);
 26    DBMS_OUTPUT.PUT_LINE(v_rows || ' rows inserted...');
 27  END;
 28  /

PL/SQL procedure successfully completed.

Regards

Report message to a moderator

Previous Topic:	Access Object type in Java class ?
Next Topic:	Find XDK version

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:17:30 CDT 2024