| Oracle XML Generation File Size Limit [message #92703] |
Tue, 07 December 2004 17:00  |
Vishal
Messages: 86
Registered: August 2000
|
Member |
|
|
Hi everyone,
I am trying to generate xml file from Oracle 9i procedure. But I am facing a problem while it is generating that xml file.
After a certain size of the xml file i.e. 33-34 kb, it is not appending data into the xml file.
Can you plz check this & let me know about your comment/suggestion.
Thanks
Regards
Vishal
------------------------------
PROCEDURE TEST1
IS
v_ctx DBMS_XMLQuery.ctxType;
v_file Utl_File.File_Type;
v_xml CLOB;
v_more BOOLEAN := TRUE;
BEGIN
-- Create XML context.
v_ctx := DBMS_XMLQuery.newContext('select rownum, catg_Cd, sub_Catg_name from sub_Category');
-- Set parameters to alter default Rowset and Row tag names and default case.
DBMS_XMLQuery.setRowsetTag(v_ctx, 'user_tables');
DBMS_XMLQuery.setRowTag(v_ctx, 'table');
DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);
-- Create the XML document.
v_xml := DBMS_XMLQuery.GetXML(v_ctx);
DBMS_XMLQuery.closeContext(v_ctx);
-- Output XML document to file.
-- 'UTL_ORACLE_TO_XML' is a oracle directory
v_file := Utl_File.FOpen('UTL_ORACLE_TO_XML', 'test1.xml', 'w');
WHILE v_more LOOP
Utl_File.Put(v_file, Substr(v_xml, 1, 3000));
IF Length(v_xml) > 3000 THEN
v_xml := Substr(v_xml, 3001);
ELSE
v_more := FALSE;
END IF;
END LOOP;
Utl_File.FClose(v_file);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
Utl_File.FClose(v_file);
END TEST1;
---------------------------------
|
|
|
|
| Re: Oracle XML Generation File Size Limit [message #92757 is a reply to message #92703] |
Fri, 07 January 2005 11:14  |
Jay Acharya
Messages: 1
Registered: January 2005
|
Junior Member |
|
|
Vishal,
Did you find a solution to your problem?
I am trying to do something similar and I keep on running into errors with substr and length of a CLOB type variable v_xml.
I am using PL/SQL on Oracle8i (8.1.7.0.0).
Are you using Oracle 8i or 9i?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
