Home » Developer & Programmer » JDeveloper, Java & XML » ERROR ON XML PARSING....IN ORACLE
ERROR ON XML PARSING....IN ORACLE [message #140733] |
Wed, 05 October 2005 07:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: ERROR ON XML PARSING....IN ORACLE [message #144297 is a reply to message #140733] |
Tue, 25 October 2005 17:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
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
|
|
|
Goto Forum:
Current Time: Mon Sep 09 08:53:32 CDT 2024
|