[ Download Database Code - Download C# Sample - Download VB.NET Sample ]
This article will present the concept of utilizing the UTL_FILE package supplied in Oracle 9i or better. To comprehend this approach, you should be familiar with PL/SQL, SQL, and XML. While this article is simple and straightforward, my goal is to present an alternative that you may have not considered before.
Prepare Your Database
The first thing you need to do is establish the Oracle directory where we will be storing the XML file. This is accomplished by running the following statement at your SQL Plus command prompt. Note: All the SQL Scripts you need are provided in the sample code download.
Listing 1: Create Directory
create DIRECTORY DATA_FILE_DIR AS 'c:\xmldata';
Be sure that you create the physical folder 'xmldata' on your local file system. In my case, I created this folder on my local C: drive.
If you receive an "ORA-00955: name is already used by an existing object" error when you run the code in Listing 1, then use a name other than DATA_FILE_DIR (you will get this error if the DATA_FILE_DIR name is used by another object). If you use a name other than DATA_FILE_DIR, then wherever DATA_FILE_DIR is used in this article, in all those places you need to use the new name.
If you receive an "ORA-01031: insufficient privileges" error, this means you don't have sufficient privileges to create that folder. The best way to create the folder is to login as Sys and create the directory.
Now you need sample data to work with, so run the SQL script found within the sample code download to create your ALL_ORDERS table.
Listing 2: Establish Table and Data
DROP TABLE all_orders;
CREATE TABLE all_orders (
order_id NUMBER(12) PRIMARY KEY,
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6)
);
COMMENT ON COLUMN ALL_ORDERS.ORDER_STATUS IS
'0: Not fully entered, 1: Entered, 2: Canceled - bad credit,'
'3: Canceled - by customer, 4: Shipped - whole order,'
'5: Shipped - replacement items, 6: Shipped - backlog on items,'
'7: Shipped - special delivery, 8: Shipped - billed,'
'9: Shipped - payment plan, 10: Shipped - paid'
COMMENT ON COLUMN ALL_ORDERS.ORDER_MODE IS
'Mode of order : online or direct'
INSERT INTO all_orders (
ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS,
ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID )
VALUES (
2458, '17-AUG-99', 'direct', 101, 0, 78279.6, 153, NULL
);
..
commit;
The next step in this process is to create the package specification. Within this package I have one procedure that we will utilize.
-
OpenXmlFile - this procedure takes the parameter of the directory name that we established previously in Listing 1.
Next, execute the following statement.
Listing 3: Package
CREATE OR REPLACE PACKAGE Article643 IS
p_dir_name varchar2(13) := 'C:\xmldata';
procedure OpenXmlFile(p_dir_name varchar2);
END Article643;
Now that you have created the package, it is time to create the package body. If you recall, I spoke earlier of the procedure OpenXmlFile; the package body is where the processing of this procedure takes place.
Listing 4: Package Body
create or replace package body Article643 is
v_FILENAME varchar2(30);
f_XML_FILE UTL_FILE.file_type;
procedure OpenXmlFile(p_dir_name varchar2) is
v_record_data varchar2(4000) := null;
v_order_id varchar2(50) := null;
v_order_date varchar2(50) := null;
v_order_mode varchar2(50) := null;
v_order_total varchar2(50) := null;
cursor orders_cursor is
select t.order_id, t.order_date, t.order_mode, t.order_total
from all_orders t;
begin
--v_FILENAME := TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI') || '.xml';
v_FILENAME := 'article643.xml';
f_XML_FILE := UTL_FILE.fopen('DATA_FILE_DIR', v_FILENAME, 'W');
v_RECORD_DATA := '<?xml version="1.0" encoding="UTF-8"?>';
UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);
UTL_FILE.put_line(f_XML_FILE, '<ORDER_DATA>');
open orders_cursor;
loop
fetch orders_cursor
into v_order_id, v_order_date, v_order_mode, v_order_total;
EXIT WHEN orders_cursor%NOTFOUND;
UTL_FILE.put_line(f_XML_FILE, ' <ORDER_DETAILS>');
UTL_FILE.put_line(f_XML_FILE,
' <ORDER_ID>' || v_order_id || '</ORDER_ID>');
UTL_FILE.put_line(f_XML_FILE,
' <ORDER_DATE>' || v_order_date ||
'</ORDER_DATE>');
UTL_FILE.put_line(f_XML_FILE,
' <ORDER_MODE>' || v_order_mode ||
'</ORDER_MODE>');
UTL_FILE.put_line(f_XML_FILE,
' <ORDER_TOTAL>' || v_order_total ||
'</ORDER_TOTAL>');
UTL_FILE.put_line(f_XML_FILE, ' </ORDER_DETAILS>');
end loop;
close orders_cursor;
UTL_FILE.put_line(f_XML_FILE, '</ORDER_DATA>');
UTL_FILE.FCLOSE(f_XML_FILE);
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 Article643;
If you are not familiar with the UTL_FILE package, it is simply a mechanism that will allow you to read and write text files. Read more on the UTL_FILE package.
The OpenXmlFile procedure in Listing 3 is accomplishing seven basic steps.
-
Defines variables to be utilized.
-
Accepts a directory name parameter that was defined within the package (see Listing 2).
-
Establishes a filename for the XML file we will create.
-
Defines the cursor.
-
Writes the cursor result to the XML file.
-
Utilizes the FOPEN function of the UTL_FILE package to open the XML file and prepare it for writing our data. The FOPEN function accepts four parameters, however in this case I am only passing three parameters, which are the directory location, the filename, and the open mode which in this case is 'W' for write data. Read more on the FOPEN function.
-
Handles any exceptions that may occur.
As you can see in Listing 4, I have a simple SQL statement that defines the cursor I am using.
Once the cursor executes and I fetch the data from the database, I then assign the values from the cursor to the declarations that I had previously assigned via a looping process. At the same time I loop through the cursor, I begin writing my XML file to the local file system. There are three items that I must call to your attention. These are:
-
ending your loop;
-
closing your cursor; and
-
closing the UTL_FILE package.
I can not stress enough how important it is to close any and all resources as soon as they are no longer required.