[ 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