Home » SQL & PL/SQL » SQL & PL/SQL » error creating xml file
error creating xml file [message #256951] Tue, 07 August 2007 04:45 Go to next message
E.Peron
Messages: 1
Registered: August 2007
Junior Member
I have to create an xml file. I have created this procedure

create or replace package body write_xml is

v_FILENAME varchar2(100);
f_XML_FILE UTL_FILE.file_type;

procedure OpenXmlFile(p_dir_name varchar2) is
-- declarations
v_record_data varchar2(4000) := null;
v_model_no varchar2(4000) := null;
v_serial_no varchar2(50) := null;

v_count number :=0;

-- cursor to retrieve the order information based
cursor orders_cursor is
select t.model_no, t.serial_no
from tabella t;

begin
UTL_FILE.FCLOSE_ALL;
v_FILENAME := 'product.xml';
f_XML_FILE := UTL_FILE.fopen(p_dir_name, v_FILENAME, 'W');
-- write the header to the file.
v_RECORD_DATA := '<?xml version="1.0" encoding="UTF-8"?>';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

-- inserisce intestazione

v_RECORD_DATA := '<products';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

v_RECORD_DATA := 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);


v_RECORD_DATA := 'xsi:noNamespaceSchemaLocation="products_0_3.xsd" >';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

open orders_cursor;
loop
fetch orders_cursor
into v_model_no, v_serial_no;
EXIT WHEN orders_cursor%NOTFOUND;
v_count := v_count + 1;
v_RECORD_DATA := ' <product>';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

UTL_FILE.put_line(f_XML_FILE,
' <model-no>' || v_model_no ||'</model-no>');
UTL_FILE.put_line(f_XML_FILE,
' <serial-no>'|| v_serial_no ||'</serial-no>');
UTL_FILE.put_line(f_XML_FILE,
' </product>');
end loop;
close orders_cursor;

UTL_FILE.put_line(f_XML_FILE, '<file-footer>');
UTL_FILE.put_line(f_XML_FILE,
' <record-count>'|| v_count || '</record-count>');
UTL_FILE.put_line(f_XML_FILE,
' </file-footer>');
UTL_FILE.put_line(f_XML_FILE,
' </products>');

UTL_FILE.FCLOSE(f_XML_FILE);

-- exception handling --
EXCEPTION
WHEN UTL_FILE.INTERNAL_ERROR THEN
raise_application_error(-20500,
'Cannot open file :' || v_FILENAME ||
', internal error; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_OPERATION THEN
raise_application_error(-20501,
'Cannot open file :' || v_FILENAME ||
', invalid operation; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.INVALID_PATH THEN
raise_application_error(-20502,
'Cannot open file :' || v_FILENAME ||
', invalid path; code:' || sqlcode ||
',message:' || sqlerrm);
WHEN UTL_FILE.WRITE_ERROR THEN
raise_application_error(-20503,
'Cannot write to file :' || v_FILENAME ||
', write error; code:' || sqlcode ||
',message:' || sqlerrm);
end; -- end OpenXmlFile procedure

end write_xml;


CREATE OR REPLACE PACKAGE write_xml IS

procedure OpenXmlFile(p_dir_name varchar2);
END write_xml;

WHen I try I have the following error:

SQL> execute write_xml.OpenXmlFile ('/euro/diap47/xmldata');
BEGIN wewits.OpenXmlFile ('/euro/diap47/xmldata'); END;

*
ERROR at line 1:
ORA-20502: Cannot open file :product.xml, invalid path;
code:-29280,message:ORA-29280: invalid directory path
ORA-06512: at "OPS$DIAP47.WRITE_XML", line 129
ORA-06512: at line 1

The path exists...
Oracle version 9i.

Many thanks in advance for your help
Re: error creating xml file [message #256970 is a reply to message #256951] Tue, 07 August 2007 05:17 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.

Does your Oracle instance/listener owner has access rights to the directory?

Regards
Michel
Previous Topic: Packages
Next Topic: Select Dummy Rows?
Goto Forum:
  


Current Time: Tue Dec 06 02:50:14 CST 2016

Total time taken to generate the page: 0.21922 seconds