Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

XML Generation in Oracle9i Using DBMS_XMLQUERY, DBMS_XMLGEN, SYS_XMLGEN and SYS_XMLAGG - Generate XML and perform XSL transformations with ease using the new XML features of Oracle9i.



Rob said...

This is great, but in my case at least an extra line is required to get over the 32767 byte limit.
After

Utl_File.Put(v_file, Substr(v_xml, 1, 32767));

Need to add:
Utl_File.FFLUSH(v_file);

Cheers,

Rob

Bia said...

I have Oracle 9i installed,
but when i try to run this scrip:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_file Utl_File.File_Type;
3 v_xml CLOB;
4 v_more BOOLEAN := TRUE;
5 BEGIN
6 -- Create XML document from query.
7 v_xml := DBMS_XMLQuery.GetXML('SELECT table_name, tablespace_name FROM straturi WHERE rownum
< 6');
8
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);
15 ELSE
16 v_more := FALSE;
17 END IF;
18 END LOOP;
19 Utl_File.FClose(v_file);
20
21 EXCEPTION
22 WHEN OTHERS THEN
23 DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
24 Utl_File.FClose(v_file);
25 END;
26 /

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?

Tim... said...

Hi.

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.

Cheers

Tim...

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/
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired