Bulk update from dotnet to oracle

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.
praveen1981Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor 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.
0
 
santhimurthydCommented:
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
0
 
slightwv (䄆 Netminder) 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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
praveen1981Author 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
0
 
santhimurthydCommented:
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");

0
 
slightwv (䄆 Netminder) 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.
0
 
praveen1981Author 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.