Bulk insert/update

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 11
Joined: Mon 09 Nov 2009 21:26

Bulk insert/update

Post by mkashyap » Mon 09 Nov 2009 21:40

I am new to dotConnect and have downloaded a trial version to test it out to see if it fits our needs. We are looking at dotConnect for Oracle.

What is the best way to do a bulk insert or update? Do we have to write a PL/SQL package to do that or is there another way. There is a way to do this with ORacle dataaccess dll by setting the insert statement as the command text of the command object and passing the insert values as arrays.

When I try something similar with dotConnect, it does not seem to work. I get a ORA-01484: arrays can only be bound to PL/SQL statements.

Here is what I am trying to do:

Dim arCompID() As String = {"T1", "T2", "T3"}
Dim arDesc() As String = {"Test1", "Test2", "Test3"}

Dim prmComp As New OracleParameter
prmComp.OracleDbType = OracleDbType.VarChar
prmComp.ParameterName = "p1"
prmComp.Value = arCompID

Dim prmDesc As New OracleParameter
prmDesc.OracleDbType = OracleDbType.VarChar
prmDesc.ParameterName = "p2"
prmDesc.Value = arDesc

cmd.CommandType = CommandType.Text
cmd.CommandText = "Insert into F21.fadm_company (company_id, description) values (:p1, :p2)"

Any help is appreciated. We are trying to use dotConnect in the direct mode and not use Oracle client.


Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Nov 2009 13:07

Please call the ExecuteArray method. For detailed information, please refer to http://www.devart.com/dotconnect/oracle ... yBind.html .

Posts: 11
Joined: Mon 09 Nov 2009 21:26

Post by mkashyap » Thu 12 Nov 2009 16:10

Thanks for your response. It worked.

One question on how .ExecuteArray works---this method takes an input parameter which is essentially the number of records that needs to be inserted. Does it hit the database that many times or is it that once it reaches the database, it executes the statements that many times?


Site Admin
Posts: 8241
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 16 Nov 2009 11:57

The main advantage of using array binding is that only one connection to the server is used to execute several queries, which speeds up queries execution compared to consecutive invocation. During the ExecuteArray invocation, query and parameters reach the database once, and actual execution is performed at the database side.

Posts: 5
Joined: Mon 08 Jan 2007 13:40

What about transaction functionality in ExecuteArray ?

Post by ptzedakis » Tue 08 Dec 2009 14:14

Hello Shalex,

If we don't use ExecuteArray(int iters) and we need to insert a number of records in an ORACLE table, we would use an OracleTransaction if we wanted to make sure that either ALL records have been inserted or NO records at all. Then according to whether the command successfully commit or rollback, we would be notified and act accordingly.

If we do use ExecuteArray(int iters) to insert many rows to a table, is it guaranteed that ALL records will be inserted? What happens if during OracleCommand.ExecuteArray(int iters) an OracleException is thrown in our application? How do we ensure that in such case NO records have been written to the ORACLE db ?

Thank you for your help.

Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 09 Dec 2009 12:57

The error occurred while inserting multiple rows will cancel the whole operation because the OracleCommand.ExecuteArray function uses array DML operations.

Post Reply