Rob said...This is great, but in my case at least an extra line is required to get over the 32767 byte limit.
Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
Need to add:
Bia said...I have Oracle 9i installed,
but when i try to run this scrip:
SQL> SET SERVEROUTPUT ON
2 v_file Utl_File.File_Type;
3 v_xml CLOB;
4 v_more BOOLEAN := TRUE;
6 -- Create XML document from query.
7 v_xml := DBMS_XMLQuery.GetXML('SELECT table_name, tablespace_name FROM straturi WHERE rownum
9 -- Output XML document to file.
10 v_file := Utl_File.FOpen('D:\Bia', 'test1.xml', 'w');
11 WHILE v_more LOOP
12 Utl_File.Put(v_file, Substr(v_xml, 1, 32767));
13 IF Length(v_xml) > 32767 THEN
14 v_xml := Substr(v_xml, 32768);
16 v_more := FALSE;
17 END IF;
18 END LOOP;
22 WHEN OTHERS THEN
I get the following error:
ERROR at line 7:
ORA-06550: line 7, column 12:
PLS-00201: identifier 'DBMS_XMLQUERY.GETXML' must be declared
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
Could you please help me?
It's has to be loaded into the database. It also requires some Java to be loaded. It's not the most efficient way to do it. Better to use DBMS_XMLGEN if available.
If you have more questions, please use the forum. The comments are not for questions.
Andrew Fraser said...Fix for the 32k bug Rob reported above is at http://andrewfraserdba.com/2014/01/26/create-output-xml-data-files-from-oracle-with-dbms_xmlgen/
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!