Chinh Do

Bulk Operations Using Oracle Data Provider for .NET (ODP.NET)

First time here? Check out my greatest hits or look around in the archives, and consider subscribing to the latest posts via RSS or email. I am also on Twitter and Google Plus. Thanks for visiting.
2nd April 2014

Bulk Operations Using Oracle Data Provider for .NET (ODP.NET)

Putting up an old article I published on .NET Slackers.


In a typical multi-tier application, one of the biggest performance bottlenecks is the overhead of making round-trips to the database. Minimizing these round-trips is often the first area you should look at during performance tuning. Fortunately, the Oracle Data Provider for .NET (ODP.NET) makes it fairly easy to do this by providing several built-in methods to write and read data in bulk.

To run the code samples in this article, you will need to have:

  • ODP.NET 2.0 (download from here).
  • A table named “BULK_TEST”.

Here’s the script that creates the table BULK_TEST:

  EMPLOYEE_ID  NUMBER(10)                       NOT NULL,
  FIRST_NAME   VARCHAR2(64 BYTE)                NOT NULL,
  LAST_NAME    VARCHAR2(64 BYTE)                NOT NULL,
  DOB          DATE                             NOT NULL

Bulk Inserts Using Array Binding

The Array Binding feature in ODP.NET allows you to insert multiple records in one database call. To use Array Binding, you simply set OracleCommand.ArrayBindCount to the number of records to be inserted, and pass arrays of values as parameters instead of single values:

string sql = 
	"insert into bulk_test (employee_id, first_name, last_name, dob) "
	+ "values (:employee_id, :first_name, :last_name, :dob)";

OracleConnection cnn = new OracleConnection(connectString);
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.BindByName = true;

// To use ArrayBinding, we need to set ArrayBindCount
cmd.ArrayBindCount = numRecords;

// Instead of single values, we pass arrays of values as parameters
cmd.Parameters.Add(":employee_id", OracleDbType.Int32,
				   employeeIds, ParameterDirection.Input);
cmd.Parameters.Add(":first_name", OracleDbType.Varchar2,
				   firstNames, ParameterDirection.Input);
cmd.Parameters.Add(":last_name", OracleDbType.Varchar2,
				   lastNames, ParameterDirection.Input);
cmd.Parameters.Add(":dob", OracleDbType.Date,
				   dobs, ParameterDirection.Input);

As you can see, the code does not look that much different from doing a regular single-record insert. However, the performance improvement is quite drastic, depending on the number of records involved. The more records you have to insert, the bigger the performance gain. On my development PC, inserting 1,000 records using Array Binding is 90 times faster than inserting the records one at a time. Yes, you read that right: 90 times faster! Your results will vary, depending on the record size and network speed/bandwidth to the database server.

A bit of investigative work reveals that the SQL is considered to be “executed” multiple times on the server side. The evidence comes from V$SQL (look at the EXECUTIONS column). However, from the .NET point of view, everything was done in one call.

Bulk Inserts Using PL/SQL Associative Arrays

PL/SQL Associative Arrays (formerly PL/SQL Index-By Tables) allow .NET code to pass arrays as parameters to PL/SQL code (stored procedure or anonymous PL/SQL blocks). Once the arrays are in PL/SQL, you are free to use them in whichever way you wish, including turning around and inserting them into a table using the “forall” bulk bind syntax.

Why would you want to insert bulk records using PL/SQL Associative Arrays instead of the simple syntax of Array Binding? Here are a few possible reasons:

  • You need to perform additional work in PL/SQL, in addition to the bulk insert.
  • The application login does not have permission to perform the work, but you can grant the necessary privilege to a stored procedure.

The major drawback with using Associative Arrays is that you have to write PL/SQL code. I have nothing against PL/SQL, but it’s not part of the skill set of the typical .NET developer. To most .NET developers, PL/SQL will be harder to write and maintain, so you will have to weigh this drawback against the potential gain in performance.

In the following example, we use PL/SQL Associative Arrays to insert 1,000 records, and returning a Ref Cursor at the end. As you can see, there’s quite a bit of more code to write:

OracleConnection cnn = new OracleConnection(connectString);
OracleCommand cmd = cnn.CreateCommand();
string sql = "declare "
 + "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
 + "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
 + "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
 + "type t_dob is table of bulk_test.dob%type index by pls_integer; "
 + "p_emp_id t_emp_id; "
 + "p_first_name t_first_name; "
 + "p_last_name t_last_name; "
 + "p_dob t_dob; "
 + "begin "
 + "  p_emp_id := :emp_id; "
 + "  p_first_name := :first_name; "
 + "  p_last_name := :last_name; "
 + "  p_dob := :dob; "
 + "  forall i in p_emp_id.first..p_emp_id.last "
 + "    insert into bulk_test (employee_id, first_name, last_name, dob) "
 + "    values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
 + "  open :c1 for "
 + "  select employee_id, first_name, last_name, dob from bulk_test;"
 + "end;";
cmd.CommandText = sql;

OracleParameter pEmpId = new OracleParameter(":emp_id",
						numRecords, ParameterDirection.Input);
pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pEmpId.Value = employeeIds;

OracleParameter pFirstName = new OracleParameter(":first_name",
						OracleDbType.Varchar2, numRecords,
pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pFirstName.Value = firstNames;

OracleParameter pLastName = new OracleParameter(":last_name",
						OracleDbType.Varchar2, numRecords,
pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pLastName.Value = lastNames;

OracleParameter pDob = new OracleParameter(":dob",
						OracleDbType.Date, numRecords,
pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pDob.Value = dobs;

OracleParameter pRefCursor = new OracleParameter();
pRefCursor.OracleDbType = OracleDbType.RefCursor;
pRefCursor.Direction = ParameterDirection.ReturnValue;


int rows = cmd.ExecuteNonQuery();

OracleDataReader dr = ((OracleRefCursor) pRefCursor.Value).GetDataReader();            
while (dr.Read())
	// Process cursor

Ref Cursors

ODP.NET Ref Cursors are objects that point to Oracle server-side cursors (or result sets). The important thing to .NET developers is that a Ref Cursor can be converted to the familiar OracleDataReader. With Ref Cursors, the logic to open result sets can be written entirely in PL/SQL and the results returned to .NET via Ref Cursors.

Why would you want to use Ref Cursors, instead of just doing an ExecuteReader with a SELECT statement? Here are some possible reasons:

  • You need to perform additional work in PL/SQL before returning the result set(s).
  • The user does not have direct access to the table(s) in question.

Here is a real world example. Say you need to update a record in the Orders table and insert a new record into the OrdersAudit table at the same time. Instead of executing two database calls, you can wrap everything into an anonymous PL/SQL block and make one database call.

See the previous section for code example using a Ref Cursor.

Controlling FetchSize

Controlling the FetchSize property is another method to minimize server round-trips. When you read data from the server using the OracleDataReader object, ODP.NET retrieves the data for you in chunks behind the scene. By default, a 64K chunk of data is retrieved each time. However, you can change the chunk size by setting the FetchSize property. By increasing FetchSize, you will lower the number of data retrieval round-trips and increase the overall retrieval operation.

It’s typical to set the FetchSize property is to the RowSize (of the OracleCommand object) multiplied by the number of records expected:

OracleDataReader dr = cmd.ExecuteReader();
dr.FetchSize = cmd.RowSize * numRecords;
while (dr.Read())
	// Perform reads...

When working with a Ref Cursor, the OracleCommand.RowSize property is always zero. You either have to calculate the row size at design time or use reflection at run-time to determine the RowSize by looking at the instance variable m_rowSize on the OracleDataReader object:

FieldInfo fi = dr.GetType().GetField("m_rowSize"
	, BindingFlags.Instance | BindingFlags.NonPublic);
int rowSize = (int) fi.GetValue(dr);

On my development PC, when reading 10,000 records from BULK_TEST, setting FetchSize = RowSize * improves the total elapsed time by a factor of two over leaving FetchSize at the default value (985 vs. 407 milliseconds).


For clarity purposes, the example code in this article does not use the “using” pattern for IDisposable objects. It’s recommended that you always use “using” with IDisposable objects such as OracleConnection or OracleCommand.


In this article, we looked at how various bulk operations in ODP.NET 2.0 can help you improve the performance of your ODP.NET application. Being familiar with these techniques can help you plan, design and implement applications that meet performance goals.


posted in Dotnet/.NET - C#, Programming | 0 Comments

12th March 2014

Running MSDEPLOY from PowerShell

I had to spend quite a bit of time figuring this out. So hopefully this will help someone out there. To launch MSDEPLOY from PowerShell, make sure you escape any quote or comma characters.

The “2>&1” at the end allows Powershell to detect errors and bail out if you have $ErrorActionPreference = “Stop”

If you still have problems, download and use EchoArgs.exe to see exactly what PowerShell sees.



function Get-MSWebDeployInstallPath(){
  $path = (get-childitem "HKLM:\SOFTWARE\Microsoft\IIS Extensions\MSDeploy" | Select -last 1).GetValue("InstallPath")
  $path = "${path}msdeploy.exe"

  if (!(Test-Path "$path")) {
    throw "MSDEPLOY.EXE is not installed. See"

  return $path

$msdeploy = Get-MSWebDeployInstallPath

& $msDeploy -verb:sync -source:recycleApp `
  -dest:recycleApp=`"${webSiteName}`"`,recycleMode=StopAppPool`,wmsvc=${server}`,userName="${userName}"`,password="${password}" `
  -allowuntrusted -debug 2>&1

posted in Dotnet/.NET - C#, PowerShell | 0 Comments

3rd March 2014

Error 1053 starting Web Deployment Agent Service

If you are getting error 1053 starting “Web Deployment Agent Service” (part of Microsoft Web Deploy or MSDEPLOY), below is a possible fix.

This is the error I got when trying to start Web Deployment Agent Service: “Web Deployment Agent Service” on some of my servers. The exact error message is: “Windows could not start the Web Deployment Agent Service service on Local Computer. Error 1053: The service did not respond to the start or control request in a timely fashion.”

In the System Events Log, there are these generic service start-up error messages:

  • The Web Deployment Agent Service service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.”
  • A timeout was reached (30000 milliseconds) while waiting for the Web Deployment Agent Service service to connect.

The fix for me was to uninstall any previous version of Web Deploy, then re-install the latest version (Microsoft Web Deploy 3.5), and choosing “Complete” at the “Choose Setup Type” page.

My environment: Windows Server 2008 64-bit SP2.

posted in Dotnet/.NET - C#, Programming | 3 Comments

25th January 2013

An Export of My .NET RSS Feeds

If you are looking for some more .NET bloggers to subscribe to, check out my .NET Programming RSS Feeds. It’s a exported bundle from my Google Reader. I’ve subscribed to these feeds over several years so I am sure many of the blogs are no longer active.

posted in Dotnet/.NET - C#, Programming, Tips | 0 Comments

9th March 2012

Removing Excess Whitespace from a String

I was looking for the most efficient way to remove excess white space from a string and wrote the following benchmark. Guess which algorithm is faster?

const int iterations = 200000;
const string expr = " Hello    world! Why    are so    many spaces?  Testing One   two three    four    five.";

// Remove excess space using Regex
var doRegex = new Action(() =>
    for (int i = 0; i < iterations; i++)
        var newStr = Regex.Replace(expr, @"\s{2,}", " ");

// Remove excess space using Split/Join
var doSplit = new Action(() =>
    for (int i = 0; i < iterations; i++)
        var newStr = String.Join(" ", expr.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
var benchMark = new Func<string, Action, long>((name, a) =>
    var sw = Stopwatch.StartNew();
    Console.WriteLine(name + ": " + sw.ElapsedMilliseconds);
    return sw.ElapsedMilliseconds;

// Warming up
Console.WriteLine("Warming up.");

// Run benchmark
long regexElapsed = benchMark("Regex", doRegex);
long splitElapsed = benchMark("Split", doSplit);

On my PC, the Split method is about 7.5 times faster than Regex.


posted in Dotnet/.NET - C#, Programming | 14 Comments

6th August 2011

Transactional File Manager Version 1.2 .1 Released

Version 1.2 of Transactional File Manager is now available on CodePlex.

Here’s the release notes:

Release Notes

This is a minor bug-fix/minor refactor release.

  • Copy now uses the overwrite parameter correctly.
  • Fixes crashing problem on some machines due to use of GetsequentialGuid.
  • CreateDirectory now supports rolling back nested directories.
  • RollbackFile Rollback() may throw exception when original directory has been deleted.
  • Security issue when writing to event logs.


  • Upgrade solution/project files to Visual Studio 2010
  • Use NUnit instead of VSTS framework
  • Changes to support Visual Studio Express

Thanks to karbuke and dorong for their contributions.

posted in Dotnet/.NET - C#, Programming | 1 Comment

7th September 2010

Getting the Starting Day of the Week for Any Date

From my code snippets, here’s a function that will return the starting day of the week for any date:

/// <summary>
/// Gets the start of the week that contains the specified date.
/// </summary>
/// <param name="date">The date.</param>
/// <param name="weekStartsOn">The day that each week starts on.</param>
/// <returns>The start date of the week.</returns>
public static DateTime GetStartOfWeek(DateTime date, DayOfWeek weekStartsOn)
    int days = date.DayOfWeek - weekStartsOn;
    DateTime startOfWeek = days>=0 ? date.AddDays(-days) : date.AddDays(-7 - days);
    return startOfWeek;

posted in Dotnet/.NET - C#, Programming | 1 Comment

8th June 2010

Transactional File Manager Is Now On CodePlex

It’s my first open source project! I’ve gone open source with my Transactional File Manager. Check out the CodePlex link here.

Use any file system as a transactional file system! Transactional File Manager is a .NET API that supports including file system operations such as file copy, move, delete in a transaction. It’s an implementation of System.Transaction.IEnlistmentNotification (works with System.Transactions.TransactionScope).


More on Transactional File Manager in my original blog post on it. If you are interested in contributing to the project, let me know.

posted in Dotnet/.NET - C#, Programming | 3 Comments

10th May 2010

MSMQ Installation on Windows Server 2008 Fail with 0x80070643

If you get one of these errors starting MSMQ Server or installing MSMQ on Windows (Server 2008), this article describes a potential solution:

  • You cannot start MSMQ Service with the following entry in the Application Event Log: The Message Queuing service cannot start. The internal private queue ‘admin_queue$’ cannot be initialized. If the problem persists, reinstall Message Queuing. Error 0xc00e0001.
  • You cannot install MSMQ with the following error: Attempt to install Message Queuing Server failed with error code 0x80070643. Fatal error during installation. The following features were not installed: Message Queuing Services/Message Queuing Server.

    MSMQ Installation Result


For me, the solution was to delete the Registry key KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSMQ, then try the installation again.

See also:

posted in Dotnet/.NET - C#, MSMQ, Programming | 11 Comments

27th March 2010

Don’t hire ghost hunters who are afraid of ghosts

Your house is haunted. You can’t stand living with the ghosts anymore and decide to call in the professional, a ghost hunter. The ghost hunter comes, nicely equipped with all kinds of shiny ghost hunting equipment, and proceeds to immediately pass out and fall to to the floor with a loud thud at first sight of the ghosts.

Well, in a nutshell, that is the story of the new Event Viewer in Windows Vista and Windows Server 2008. The new Event Viewer looks very nice and all. Trouble is, it now won’t work when there is a syntax error in machine.config or other .NET Framework problems. Apparently, in Windows Vista and Windows 2008, someone at Microsoft decided to rewrite the Event Viewer utility (a perfectly usable and solid utility in previous versions of Windows) in .NET.

MMC could not create the snap-in. The snap-in might not have been installed correctly. Name: Event Viewer.

So, the lesson I learned from this is: don’t hire ghost hunters who are afraid of ghosts. Or, don’t write a utility designed to view monitoring and troubleshooting messages that can fail due to unnecessary dependencies. A critical system troubleshooting utility such as Event Viewer should be the last thing that fails.

What to do when you cannot run Event Viewer in Windows Vista/Windows Server 2008/or Windows 7?

If you get the following error:

MMC could not create the snap-in. The snap-in might now have been installed correctly. Name: Event Viewer. CLSID: FX:{b05566ad-fe9c-4363-be05-7a4cbb7cb510}

Try the following:

  • Fix any syntax errors in machine.config. Restore to a previously known working copy.
  • Rename EventVwr.exe.config in %WINDIR%\System32.

posted in Dotnet/.NET - C#, Programming | 1 Comment

  • Calendar

  • February 2017
    M T W T F S S
    « Dec