ODP.NET

Environment: Win2k3, oracle10g, .net 2.

I've tried out two roads,insert into a table with 20 columns.The code can't be run directly,it just describes my thought. Note *[Q n]* and answer me please.

1. All OracleCommands share a same connection
main()
{
OracleConnection cnn=new OracleConnection(connectionString);
Thread[] ths = new Thread[32]; //4 thread per cpu
for(int j=0;j<ths.Length;j++)
{
Thread th = ths[j] = new Thread(proc);
th.Start(cnn);
}
}
static object sync_obj = new object();
void proc(object param)
{
OracleConnection cnn = param as OracleConnection;
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = ...; //insert statement on a specific table,using parameters
lock(sync_obj) // *[Q1]* :Why lock is neccessary? Removing the line will result in ORA-01036 ocassionally,details at the end
{
cmd.ExecuteNonQuery();
}
}


2.One connection per OracleCommand
main()
{
Thread[] ths = new Thread[32]; //4 threads per cpu
for(int j=0;j<ths.Length;j++)
{
Thread th = ths[j] = new Thread(proc);
th.Start();
}
}
void proc(object param)
{
OracleConnection cnn=new OracleConnection(connectionString);
OracleCommand cmd = cnn.CreateCommand();
cmd.CommandText = ...; //insert statement on a specific table,using parameters

// *[Q2]* :Why this time lock is unneccessary for successful execution?
cmd.ExecuteNonQuery();
}

*[Q3]* Is it correct that INSERT statement does not lock the data table at all?
*[Q4]* As the code shows, is it the rule that only one INSERT into a same table can execute at the same time in one connection?

In fact, I want to insert thousands of records to a table,each thread may insert several hundreds.

I'm appreciate if you can provide detailed answer and I'm very glad that you can email the answer to llc3df5@gmail.com since I check the email more frequently than OTN forum.

*EXCEPTION DETAIL WHEN LINE [Q1] IS REMOVED*
Message="ORA-01036: Invalid variable/index"
Source="System.Data.OracleClient"
ErrorCode=-2146232008
Code=1036
StackTrace:
AT System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
AT System.Data.OracleClient.OracleParameterBinding.Bind(OciStatementHandle statementHandle, NativeBuffer parameterBuffer, OracleConnection connection, Boolean& mustRelease, SafeHandle& handleToBind)
AT System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
AT System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
AT System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
AT ConsoleApplication1.Program.proc(Object param) POS D:\testing\ConsoleApplication1\ConsoleApplication1\Program.cs:Line 92
AT System.Threading.ThreadHelper.ThreadStart_Context(Object state)
AT System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
AT System.Threading.ThreadHelper.ThreadStart(Object obj)