Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to read XML data stored in CLOB in Concurrent prog Output

User187042Sep 6 2011 — edited Jan 9 2012
Hi All,
I'm trying to Generate the XML Data as concurrent Program output. I have a PL/SQL package which generated the XML data by using SQL/XML functions. I'm storing the generated XML data in a CLOB variable. But when i try to read the data using fnd_file.put_line(fnd_file.OUTPUT,XML_Data) it doesn't display data more than 32767 bytes.
Please help me out to understand what could be done to read the data in chunks. I have tried many logic's to print chunks of data but the output tags are either chopped off and errors out saying Tag not found.
My logic is as below:

v_handler:= DBMS_XMLGEN.newContext(v_xml_query);

--Sets NULL handling options
DBMS_XMLGen.SetNullHandling(v_handler, dbms_xmlgen.EMPTY_TAG ) ;

--Set special character handling
DBMS_XMLGEN.setConvertSpecialChars(v_handler, TRUE);

-- Specified whether to use an XML attribute to indicate NULLness of particular entity in the XML document
DBMS_XMLGen.useNullAttributeIndicator(v_handler,TRUE);

-- set Checking invalid chars
DBMS_XMLGEN.setCheckInvalidChars(v_handler, TRUE);

-- get the xml data as required
v_xml_data:= DBMS_XMLGEN.getXMLtype(v_handler);

SELECT XMLROOT(v_xml_data.extract('/*'),VERSION '1.0').getClobVal() into v_new_xml_Data from dual;

-- get the length of the xml generated
v_clob_len := dbms_lob.getlength(v_new_xml_Data);

FND_FILE.PUT_LINE(FND_FILE.LOG,'The Clob length is :'|| v_clob_len);
-- logic to process string more than 32767 Processing by each character
v_offset :=1;
while (v_offset <= v_clob_len)
LOOP
v_char := dbms_lob.substr(v_new_xml_Data, 1, v_offset);
IF (v_char = CHR(10))
then
fnd_file.new_line(fnd_file.output, 1);
else
fnd_file.put(fnd_file.output, v_char);

end if;
v_offset := v_offset + 1;

END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The offset is :'|| v_offset);
FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);

THe above logic is for character by character which is a performance burden? Please let me know if there is any other work around for this
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 6 2012
Added on Sep 6 2011
3 comments
2,637 views