[ 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.

1. 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.

1. Defines variables to be utilized.
2. Accepts a directory name parameter that was defined within the package (see Listing 2).
3. Establishes a filename for the XML file we will create.
4. Defines the cursor.
5. Writes the cursor result to the XML file.
6. 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.
7. 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:

1. ending your loop;
2. closing your cursor; and
3. 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.

[ Download Database Code - Download C# Sample - Download VB.NET Sample ]

I have provided a test script that will allow you to now test your work. At this point, once this test script executes and has not thrown any exceptions, your XML file will be created on the file system.

NOTE: Before running this script, make sure the user you are using has write permissions to create the file in the specified directory.

In case if you had a permission problem in creating the file when you ran the script in Listing 5, then, as a Sys user, run the command "GRANT READ,WRITE ON DIRECTORY DATA_FILE_DIR TO <username>", and try again to run the script in Listing 5.

Listing 5: Test Script

begin
  article643.OpenXmlFile('C:\xmldata');
end;

If everything executed properly, you should have a well formed XML document similar to the following.

Figure 1

NOTE: Please make sure you have proper permissions on the 'xmldata' folder for accessing it from an ASP.NET page. If it's on another server, you have to share the folder to the web server and give proper permissions.

Execution via a Web Form

A fellow editor suggested an example of executing this package via a .NET solution would be beneficial, and once I thought this over I have to agree. Thus, I am going to provide an example of executing the package via a Web Form. I will provide sample code in both C# and VB.NET.

Figure 2

Note: The VB.NET Web Form looks exactly like the above figure.

The above Web Form contains a Button, a Label, and a DataGrid. The package is executed when the Button's Click event is fired.

Listing 6: C# Web Form Code-behind

private void Button1_Click(object sender, System.EventArgs e)
{
      OracleConnection dbConn = new OracleConnection(CONN_STRING);
      dbConn.Open();

      OracleCommand cmd = new OracleCommand("",dbConn);
      cmd.CommandText = "ASPAllianceArticles.Article643.OpenXmlFile";
      cmd.CommandType = CommandType.StoredProcedure;

      OracleParameter dirParam =
            new OracleParameter("p_dir_name", OracleDbType.Varchar2);
      dirParam.Value = "C:\\xmldata";
      cmd.Parameters.Add(dirParam);

      try
      {
            cmd.ExecuteNonQuery();
            StatusLabel.Visible = true;
            StatusLabel.Text = "XML Generation Successful";
      }
      catch (Exception ex)
      {
            StatusLabel.Visible = true;
            StatusLabel.Text = "<b>ERROR</b>: " + ex.Message.ToString();
      }
      finally
      {
            dbConn.Dispose();
      }
      BindXmlDataGrid();
}

private void BindXmlDataGrid()
{
      DataSet myDataSet = new DataSet();
      try
      {
            myDataSet.ReadXml("C:\\xmldata\\article643.xml");
            XmlDataGrid.DataSource = myDataSet;
            XmlDataGrid.DataBind();
      }
      catch (Exception ex)
      {
            StatusLabel.Visible = true;
            StatusLabel.Text = "<b>ERROR</b>: " + ex.Message.ToString();
      }
}

Listing 7: VB.NET Web Form Code-behind

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim dbConn As New OracleConnection _
        ("Data Source=portal;User ID=aspalliancearticles; Password=minime;")
    dbConn.Open()
    Dim cmd As New OracleCommand("", dbConn)
    cmd.CommandType() = CommandType.StoredProcedure
    cmd.CommandText() = "ASPAllianceArticles.Article643.OpenXmlFile"

    Dim param As New OracleParameter("p_dir_name", OracleDbType.Varchar2)
    param.Value = "C:\\xmldata"

    cmd.Parameters.Add(param)

    Try
       cmd.ExecuteNonQuery()
       StatusLabel.Visible = True
       StatusLabel.Text = "XML Generation Successful"
       BindXmlDataGrid()
    Catch ex As Exception
       StatusLabel.Visible = True
       StatusLabel.Text = "<b>ERROR</b>: " + ex.Message.ToString()
    Finally
       dbConn.Dispose()
    End Try
End Sub

Private Sub BindXmlDataGrid()
  Dim myDataSet As New DataSet
    Try
         myDataSet.ReadXml("C:\\xmldata\\article643.xml")
        XmlDataGrid.DataSource = myDataSet
        XmlDataGrid.DataBind()
    Catch ex As Exception
        StatusLabel.Visible = True
        StatusLabel.Text = "<b>ERROR</b>: " + ex.Message.ToString()
    End Try
End Sub

As in both cases of the selected language, the Button's Click event is fired, the package is executed, and the XML file is created on your local file system. As well, I added a BindXmlDataGrid() method, which in turn reads this newly created XML file and populates the DataGrid.

Summary

To recap, I have introduced the concept of utilizing the UTL_FILE package to create an XML file via PL/SQL. I covered creating your directory location both on the server and within your database schema instance. I have also provided sample scripts to establish your table, data, package, and package body, and finally a test script so you may test your effort. As well, I provided a Web Form solution in C# and VB.NET.

Resources

Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2)

SQL and PL/SQL Syntax and Examples

Oracle Error Message Lookup

Oracle9i Database List of Books (Release 2 (9.2))

XMLTutorial