Oracle’s Direct Path Loads in ODP.NET

by Granville Bonyata on May 8, 2012

By Nicholas Geiger and Granville Bonyata

Long time Oracle users will know that SQL*Loader and external tables are a great way to quickly load large files into tables using Oracle’s Direct Path Load.

But for .NET shops, there may be a lot of security tied to the IIS servers that would be circumvented by copying files to the db server and loading them from it. Additionally, the .NET team may need to handle all the related tasks, including doing bulk file loads.

To that end, Oracle has provided OracleBulkCopy to do Direct Path loads using ODP.NET. Here’s a brief example of it in action:

First set up the database:

create table PackageItem
Description varchar2(30) null,
WeightInPounds number(4, 0) not null

Now create a data file with these entries and store in c:\Temp\Data.txt:

Whole Milk                       5
Oatmeal                          1

And here’s the C# code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Oracle.DataAccess;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace OracleBulkCopyExample
    public partial class _Default : System.Web.UI.Page
        // This connection string includes a Data Source value 
        //  that removes the need to use a tnsnames.ora file.
        private String OracleConnectionString = "User Id=nick;Password=nick;"
            + "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST="
            + "(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=NICK)));";

        private DateTime StartBulkLoadTime;

        protected void Page_Load(object sender, EventArgs e)
            // Set UploadTable.
            DataTable UploadTable = new DataTable();
            UploadTable.Columns.AddRange(new DataColumn[] {
                new DataColumn("Description", Type.GetType("System.String")),
                new DataColumn("WeightInPounds", Type.GetType("System.Int32"))

            // Declare and instantiate StreamReaderInstance.
            using (StreamReader StreamReaderInstance 
                = new StreamReader(@"C:\Temp\Data.txt"))
                // Use StreamReaderInstance to gather lines of data from the data file Data.txt.
                while (!StreamReaderInstance.EndOfStream)
                    String Line = StreamReaderInstance.ReadLine(); // Get the next line.                    
                    DataRow Row = UploadTable.NewRow(); // Get a new row for the DataTable.

                    // Set column Description with the value from the row.
                    Row["Description"] = Line.Substring(0, 30).TrimEnd(' ');

                    // Set column WeightInPounds with the value from the row.
                    Row["WeightInPounds"] = Line.Substring(30, 4).TrimStart(' ');

                    UploadTable.Rows.Add(Row); // Add the row to UploadTable.

            // Declare and instantiate bulkCopy.
            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(OracleConnectionString))
                bulkCopy.DestinationTableName = "PackageItem"; // Set DestinationTableName.

                // Map the columns from UploadTable to the Oracle table PackageItem.
                bulkCopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("Description", "Description"));
                bulkCopy.ColumnMappings.Add(new OracleBulkCopyColumnMapping("WeightInPounds", "WeightInPounds"));

                    StartBulkLoadTime = DateTime.Now;

                    // Perform copy from UploadTable to Oracle table PackageItem.
                catch (Exception ExceptionFromBulkCopy)
                    Int32 ErrorRowTextStartIndex = 14;
                    Int32 ErrorRowTextLength = ExceptionFromBulkCopy.Message.IndexOf("'", ErrorRowTextStartIndex) - ErrorRowTextStartIndex;
                    String ErrorRowText = ExceptionFromBulkCopy.Message.Substring(ErrorRowTextStartIndex, ErrorRowTextLength);

                TimeSpan TimeElapsed = DateTime.Now - StartBulkLoadTime;

Previous post:

Next post: