We help IT Professionals succeed at work.

Bulk update from dotnet to oracle

praveen1981
praveen1981 asked
on
Hi

I have 2000 rows in a csv file with three columns , i want to update these in to oracle database table based on certain condition , i can do this by using looping which is not optimised, can you please suggest me for optimised code or query to update this.
Comment
Watch Question

Construct the Adhoc querry using Stringbuilder and insert the record as single short with Transaction scope

StringBuilder sb = new StringBuilder();
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");

then send the whole record as single to insert
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>then send the whole record as single to insert

You cannot chain commands like this in Oracle.

I believe you will need array binding:
http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59odpnet-085168.html

Author

Commented:
Construct the Adhoc querry using Stringbuilder and insert the record as single short with Transaction scope

StringBuilder sb = new StringBuilder();
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");
sb.Append("insert into TableName values('" + Val1+"','" + val2 + "','"+ val3 +"')");

then send the whole record as single to insert

can you please elobarate these, how can we send this whole to the stored Procedure  from dotnet
To send the input to Storeprocedure construct the string with the SP details as follows

StringBuilder sb = new StringBuilder();
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");
sb.Append("exec <<OracleStoreProcedure>>('" + Val1+"','" + val2 + "','"+ val3 +"')\n");

Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
@santhimurthyd

Again,

You cannot chain commands together like this in Oracle and pass the string to a single 'execute' method.

If you are going to keep suggesting this as a viable approach please post a simple working test showing it.
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Look up associative arrays to pass a 'list' into a procedure in a single call:

http://docs.oracle.com/cd/B14117_01/win.101/b10117/features004.htm

PL/SQL Associative Array
 
ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By Tables) binding.
 
An application can bind an OracleParameter, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature.

Author

Commented:
Hi
This solution is fine, but i have used the xml file as input to the stored procedure to update the bulk data.

Thanks for your solution.