. NET correct handling of ORA-01000 Problems

The first time I used. NET and ORACLE for task operations, the Data Driver used was Oracle Data Provider for. NET and Managed Driver (Release 11.2.0.3.50 Beta)

The problem occurs in the same scenario as that of others. It occurs when OracleCommand is used to obtain data in a 70 thousand-time loop.

Query related materials,

1. Set the number of CURSOR for ORACLE itself

 

 

SQL> alter system set open_cursors = 3000 scope = both;
 

 

 

2. Close the corresponding COMMAND (together with closing the connection) after each call in the program ). But the problem also occurs.

The Code is as follows:

 

[Csharp]
Parallel. ForEach (dtMainXM. AsEnumerable (), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable (); <P> string ID = pDataRowXMK ["ID"]. ToString ();
Using (var pSConn = GetSourceConnection ())
{
Using (OracleCommand cmdSubXM = new OracleCommand ())
{
CmdSubXM. Connection = pSConn;
CmdSubXM. CommandText = "select * from tableXX where ID = '" + ID + "'";
Using (var iReader = cmdSubXM. ExecuteReader (CommandBehavior. CloseConnection ))
{
DtDetail. Load (iReader );
}
}
}
} </P>

Parallel. ForEach (dtMainXM. AsEnumerable (), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable (); string ID = pDataRowXMK ["ID"]. ToString ();
Using (var pSConn = GetSourceConnection ())
{
Using (OracleCommand cmdSubXM = new OracleCommand ())
{
CmdSubXM. Connection = pSConn;
CmdSubXM. CommandText = "select * from tableXX where ID = '" + ID + "'";
Using (var iReader = cmdSubXM. ExecuteReader (CommandBehavior. CloseConnection ))
{
DtDetail. Load (iReader );
}
}
}
} Resources are released during each parallel processing in the above Code, but the problem still occurs (the database is also set to 3000 CURSOR ).

Finally, we tried to analyze the problem and modify the code. The main problem was that cmdSubXM. CommandText was concatenated into strings. After the code is changed to the parameter mode, the preceding problem does not occur.

[Csharp]
Parallel. ForEach (dtMainXM. AsEnumerable (), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable ();
String ID = pDataRowXMK ["ID"]. ToString ();
Using (var pSConn = GetSourceConnection ())
{
Using (OracleCommand cmdSubXM = new OracleCommand ())
{
CmdSubXM. Connection = pSConn;
CmdSubXM. CommandText = "select * from tableXX where ID =: ID ";
// Modify the parameter Mode
CmdSubXM. Parameters. Add ("ID", ID );
Using (var iReader = cmdSubXM. ExecuteReader (CommandBehavior. CloseConnection ))
{
DtDetail. Load (iReader );
}
}
}
}

Parallel. ForEach (dtMainXM. AsEnumerable (), (pDataRowXMK) =>
{
DataTable dtDetail = new DataTable ();
String ID = pDataRowXMK ["ID"]. ToString ();
Using (var pSConn = GetSourceConnection ())
{
Using (OracleCommand cmdSubXM = new OracleCommand ())
{
CmdSubXM. Connection = pSConn;
CmdSubXM. CommandText = "select * from tableXX where ID =: ID ";
// Modify the parameter Mode
CmdSubXM. Parameters. Add ("ID", ID );
Using (var iReader = cmdSubXM. ExecuteReader (CommandBehavior. CloseConnection ))
{
DtDetail. Load (iReader );
}
}
}
}

Through the processing of SQL statements before and after, I personally understand that it is still a problem caused by ORACLE's SQL parsing plan. I use SQL parameters to cache the plan to the maximum extent. The previous method has no reusability.