Wednesday, March 3, 2010

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 NameDescription
INVALID_PATHFile location is invalid.
INVALID_MODEThe open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLEFile handle is invalid.
INVALID_OPERATIONFile could not be opened or operated on as requested.
READ_ERROROperating system error occurred during the read operation.
WRITE_ERROROperating system error occurred during the write operation.
INTERNAL_ERRORUnspecified PL/SQL error
CHARSETMISMATCHA file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPENThe requested operation failed because the file is open.
INVALID_MAXLINESIZEThe MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAMEThe filename parameter is invalid.
ACCESS_DENIEDPermission to access to the file location is denied.
INVALID_OFFSETCauses 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_FAILEDThe requested file delete operation failed.
RENAME_FAILEDThe requested file rename operation failed.

No comments:

Post a Comment