Oracle: Creating a XML File

Following example illustrate a simple way to create a XML file on your UNIX box.
The UTL_FILE package can be used to read or write file from operating system. The UTL_FILE package has different subprograms which will help to read and write file from/to OS. The first and foremost step is to create a Directory path for your XML files folder. Follow the steps to create a directory alias.

CREATE
DIRECTORY:

In Oracle, the list of accessible directories as will be configured in ALL_DIRECTORIES view. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.
CREATE DIRECTORY "XML_DIR_ALIAS" AS '/xml/export';
GRANT READ ON DIRECTORY "XML_DIR_ALIAS" TO <USER>;


Note that XML_DIR_ALIAS name is the alias of the physically existing directory of '/xml/export'. So there must exists export directory. And to create directory the user must have DBA role or create directory privilege.





CREATE OR REPLACE PROCEDURE CreateXMLFile

IS

v_filename VARCHAR2(255);

v_xml_file UTL_FILE.file_type;

v_emp_no VARCHAR2(255);

v_fname VARCHAR2(255);

v_lname VARCHAR2(255);

v_sal NUMBER ;

v_dob DATE ;



CURSOR emp_cursor is
SELECT t.emp_no, t.fname, t.lname, t.sal , t.dob

FROM emp t;

BEGIN

v_filename := TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.xml';

v_xml_file := UTL_FILE.fopen('XML_DIR_ALIAS', v_filename, 'W'); --XML_DIR_ALIAS is a Directory alias


--Add Version Tag

UTL_FILE.put_line(v_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');



--Add top level node

UTL_FILE.put_line(v_xml_file, '<EMP>');



--Open the EMP cursor

OPEN emp_cursor;



--Loop through the emp cursor.

LOOP
FETCH emp_cursor

INTO v_emp_no, v_fname, v_lname, v_sal , v_dob;



EXIT WHEN emp_cursor%NOTFOUND;



UTL_FILE.put_line(v_xml_file, ' <EMP_RECORD >');

UTL_FILE.put_line(v_xml_file, ' <EMP_NO>' || v_emp_no || '</EMP_NO>');

UTL_FILE.put_line(v_xml_file, ' <FIRST_NAME>' || v_fname || '</FIRST_NAME>');

UTL_FILE.put_line(v_xml_file, ' <LAST_NAME>' || v_order_mode || '</LAST_NAME>');

UTL_FILE.put_line(v_xml_file, ' <SALARY>' || v_order_total || '</SALARY>');

UTL_FILE.put_line(v_xml_file, ' <DOB>' || TO_CHAR (v_dob,'YYYY-MM-DD') || '</DOB>');

UTL_FILE.put_line(v_xml_file, ' </EMP_ RECORD>');

END LOOP

--Close the EMP cursor

CLOSE emp_cursor;



UTL_FILE.put_line(v_xml_file, '</EMP>');

UTL_FILE.fclose(v_xml_file);



EXCEPTION
WHEN OTHERS THEN

raise_application_error(-22300,'Filed to Open :' || v_filename ||', Error:' || sqlcode ||':' || sqlerrm);
END CreateXMLFile;

UTL_FILE Package Exceptions:

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.