Home » Developer & Programmer » JDeveloper, Java & XML » Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 500 (Oracle 10.2.0.4.0)
Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 500 [message #439876] Tue, 19 January 2010 16:19 Go to next message
ctultul@yahoo.com
Messages: 1
Registered: January 2010
Location: SFO Bay Area
Junior Member
I have a following script which is working and creating xml output when the rownum < 500, but as the (rownum < 1000) clause is used, it is throwing "ORA-31011: XML parsing failed" error.
There are about 2 million records that I need to extract in the xml file. Is there any way I can do that without creating 500 files?
I am new in XML DB and any help/suggestion/input will be highly appreciated.

Thanks.


----------------script--------------------

SET SERVEROUTPUT ON
DECLARE
v_xml XMLTYPE;
v_blob BLOB;
v_data_length NUMBER;

-- Loop Control Variables
v_offset NUMBER DEFAULT 1;
v_chunk CONSTANT NUMBER DEFAULT 4000;
-- v_ctx DBMS_XMLGen.ctxHandle;
v_file Utl_File.File_Type;

BEGIN
-- Create XML context.

SELECT XMLELEMENT("COMBO_NETWORK", XMLAGG (IFC_BILL_LOC))
INTO v_xml
FROM (
SELECT XMLELEMENT
("CLAIM",
XMLFOREST(
ib.clm_num as "CLM_NUM",
NVL(ib.old_clm_num,' ') as "ALT_CLM_NUM",
DECODE(ib.clm_stat_cde,1,'OPEN','CLOSED') as "CLM_STATUS",
ib.SSN as "SSN",
ib.CLMT_LAST_NAM as "LAST_NAME",
ib.CLMT_FIRST_NAM as "FIRST_NAME",
TO_CHAR(ib.CLMT_DOB,'RRRRMMDD') as "DOB",
TO_CHAR(ib.CLMT_DOI,'RRRRMMDD') as "DOI",
ib.ER_NAM as "EMPLOYER_NAME",
sflo.cde AS "DISTRICT_OFFICE",
ib.ADD_UPDTE_DEL_FLG as "ADD_UPDATE_DELETE",
XMLFOREST(ib.CLMT_FST_LINE_ADR||' '||ib.CLMT_SND_LINE_ADR as "STREET",
ib.CLMT_CITY_ADR AS "CITY",
ib.CLMT_STATE_ADR as "STATE",
ib.CLMT_ZIP_CDE_ADR as "ZIP",
' ' as "PHONE" ) AS "CLAIMNT_ADDRESS",
DECODE(ib.CLMT_GENDER,1,'M',2,'F',' ') as "CLAIMNT_GENDER",
XMLFOREST(ib.ADJ_LAST_NAM as "LAST_NAME",
ib.ADJ_FIRST_NAM as "FIRST_NAME",
au.PHONE_NUM as "PHONE",
au.USER_EMAIL as "EMAIL") as "ADJUSTER",
NVL(ib.CATASTROPHE_FLG,'N') as "CATASTROPHE_FLAG"
)
)
IFC_BILL_LOC
FROM IFACE_BILL_CLAIM_TRANS ib, SCIF_LOCATIONS sflo, APPLICATION_USERS au
WHERE ib.comb_transmit_dte IS NULL
AND ib.SFLO_IDN = sflo.idn
AND ib.adj_last_nam = UPPER(au.user_last_nam)
AND ib.adj_first_nam = UPPER(au.user_first_nam)
AND sflo.cde = 'NF'
AND rownum < 500); -- Failes parsing as rownum < 1000 is used.

-- Turn the XML into a BLOB
v_blob := v_xml.getblobval (1);
v_data_length := DBMS_LOB.getlength (v_blob);

-- Output XML document to file.
v_file := Utl_File.FOpen('/u08/test/testdev/interface/out', 'result1.xml', 'wb', v_chunk);
LOOP
EXIT WHEN v_offset > v_data_length;
UTL_FILE.put_raw (v_file, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
TRUE);

v_offset := v_offset + v_chunk;

END LOOP;

Utl_File.FClose(v_file);

EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- We won't write any data, or even open the file,
-- if the query return no rows
NULL;
END;
/
---------------------script end--------------------------

Following is the version of my database:

SQL> col comp_name format a45

SQL> SELECT comp_name, status, substr(version,1,10) as version
FROM dba_registry;

COMP_NAME STATUS VERSION
________________________________________
Oracle Database Catalog Views VALID 10.2.0.4.0
Oracle Database Packages and Types VALID 10.2.0.4.0
JServer JAVA Virtual Machine VALID 10.2.0.4.0
Oracle Database Java Packages VALID 10.2.0.4.0
Oracle XDK VALID 10.2.0.4.0
Oracle XML Database VALID 10.2.0.4.0

6 rows selected.

SQL>
Re: Getting "ORA-31011: XML parsing failed" using xmlAGG as rownum exceeds 500 [message #439974 is a reply to message #439876] Wed, 20 January 2010 07:25 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hope it helps you

sriram Smile
Previous Topic: Create oracle xml header
Next Topic: xmltype conversion
Goto Forum:
  


Current Time: Sun Dec 04 22:47:15 CST 2016

Total time taken to generate the page: 0.09620 seconds