OutOfMemoryException using NCLOB as Parameters
OutOfMemoryException using NCLOB as Parameters
Hello there!
I hope you can help me or give me an advice.
We are using dotConnect Version 6.60.258 and Oracle 11.2.
As we have to use Unicode, all fields in the database schema are NVARCHAR2 or NCLOB data types.
For 1 time we have to insert a large amount of data into the database.
This is done via DML-Statements and the data is passed by parameters.
And here we are receiving an OutOfMemoryException.
The Stack-Trace is:
bei System.IO.MemoryStream.set_Capacity(Int32 value)
bei System.IO.MemoryStream.EnsureCapacity(Int32 value)
bei System.IO.MemoryStream.Write(Byte buffer, Int32 offset, Int32 count)
bei Devart.Data.Oracle.OracleLob.Write(Byte buffer, Int32 offset, Int32 count)
bei Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, String A_13, ah A_14, Boolean& A_15)
bei Devart.Data.Oracle.OracleParameter.a(a0& A_0, Boolean A_1, OracleConnection A_2, Byte A_3, Hashtable A_4, ah A_5, Boolean& A_6, Int32 A_7)
bei Devart.Data.Oracle.OracleCommand.a(ag A_0, Int32 A_1, OracleParameterCollection A_2, ah A_3, Boolean& A_4)
bei Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
bei Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
Any idea what we can do to avoid this? Direct Mode is no solution for us.
Kind regards
Peter
I hope you can help me or give me an advice.
We are using dotConnect Version 6.60.258 and Oracle 11.2.
As we have to use Unicode, all fields in the database schema are NVARCHAR2 or NCLOB data types.
For 1 time we have to insert a large amount of data into the database.
This is done via DML-Statements and the data is passed by parameters.
And here we are receiving an OutOfMemoryException.
The Stack-Trace is:
bei System.IO.MemoryStream.set_Capacity(Int32 value)
bei System.IO.MemoryStream.EnsureCapacity(Int32 value)
bei System.IO.MemoryStream.Write(Byte buffer, Int32 offset, Int32 count)
bei Devart.Data.Oracle.OracleLob.Write(Byte buffer, Int32 offset, Int32 count)
bei Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, String A_13, ah A_14, Boolean& A_15)
bei Devart.Data.Oracle.OracleParameter.a(a0& A_0, Boolean A_1, OracleConnection A_2, Byte A_3, Hashtable A_4, ah A_5, Boolean& A_6, Int32 A_7)
bei Devart.Data.Oracle.OracleCommand.a(ag A_0, Int32 A_1, OracleParameterCollection A_2, ah A_3, Boolean& A_4)
bei Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
bei Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
Any idea what we can do to avoid this? Direct Mode is no solution for us.
Kind regards
Peter
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
There is not enough information for reproducing the issue.
Could you please specify the following:
1. DDL/DML script of your database
2. Parameters of your database:
2.1. NLS_LANGUAGE
2.2. NLS_CHARACTERSET
2.3. NLS_NCHAR_CHARACTERSET
3. if it possible, create and send us a test project and/or test data or their properties (a number of NClob values and their size).
Looking forward to your reply.
Could you please specify the following:
1. DDL/DML script of your database
2. Parameters of your database:
2.1. NLS_LANGUAGE
2.2. NLS_CHARACTERSET
2.3. NLS_NCHAR_CHARACTERSET
3. if it possible, create and send us a test project and/or test data or their properties (a number of NClob values and their size).
Looking forward to your reply.
Hello!
Thanks for you answer. I'll try to give you more information. But I'm sorry I can't give you a test project and test data because thats customer data.
But now some more information:
1. We have 124 tables in our database
2. There are 625 fields with datatype NVARCHAR
3. There are 131 fields with datatype NCLOB (avg Data: 5184,15 KB, 31623 rows)
4. There are 5 fields with datatype CLOB (avg Data: 6675,65 KB, 27516 rows)
5. There are 3 fields with datatype BLOB (avg Data: 582,07 KB, 664 rows)
6. NLS_LANGUAGE (database): AMERICAN
7. NLS_LANGUAGE (session): GERMAN
8. NLS_CHARACTERSET (database): AL32UTF8
10: NLS_NCHAR_CHARACTERSET (database): AL16UTF16
11: The Instant-Oracle-Client is used!
We do a conversion from an old database via .NET. There is a lot of data das has to be inserted (about 91500 rows in this sample).
All is done with OracleCommand and an Insert-DML-Statement using parameters. For each table (124) wie use the same Command-Object, but change the values of the parameters.
I hope this information will help you and you can give me an advice whats going wrong.
Thanks
Peter
Thanks for you answer. I'll try to give you more information. But I'm sorry I can't give you a test project and test data because thats customer data.
But now some more information:
1. We have 124 tables in our database
2. There are 625 fields with datatype NVARCHAR
3. There are 131 fields with datatype NCLOB (avg Data: 5184,15 KB, 31623 rows)
4. There are 5 fields with datatype CLOB (avg Data: 6675,65 KB, 27516 rows)
5. There are 3 fields with datatype BLOB (avg Data: 582,07 KB, 664 rows)
6. NLS_LANGUAGE (database): AMERICAN
7. NLS_LANGUAGE (session): GERMAN
8. NLS_CHARACTERSET (database): AL32UTF8
10: NLS_NCHAR_CHARACTERSET (database): AL16UTF16
11: The Instant-Oracle-Client is used!
We do a conversion from an old database via .NET. There is a lot of data das has to be inserted (about 91500 rows in this sample).
All is done with OracleCommand and an Insert-DML-Statement using parameters. For each table (124) wie use the same Command-Object, but change the values of the parameters.
I hope this information will help you and you can give me an advice whats going wrong.
Thanks
Peter
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Hello!
We load the values of the old database in a DataTable. Then the values (string) for each column/row is inserted in the new database (Oracle).
A small code-snippet to show the process:
Kind regards
Peter
We load the values of the old database in a DataTable. Then the values (string) for each column/row is inserted in the new database (Oracle).
A small code-snippet to show the process:
Code: Select all
OracleCommand Cmd = new OracleCommand("Insert into TABLEA(Col1) values (:PARAM)", (OracleConnection)Con);
OracleParameter Param1 = new OracleParameter();
Param1.ParameterName = "PARAM";
Param1.OracleDbType = OracleDbType.NClob;
Cmd.Parameters.Add(Param1);
for (int i = 0; i < Data.Tables[0].Rows.Count; i++)
{
DataRow Row = Data.Tables[0].Rows[i];
OracleParameter Par = Cmd.Parameters["PARAM"];
Par.Value = Convert.ToString(Row["COL1"]);
Cmd.ExecuteNonQuery();
}
Peter
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Thank you for the snippet of code. The managed heap could be overcrowded when you use DataTable. Use the ordinary
select command and DataReader instead of DataTable. For example, you can change your snippet of code in the following way:
select command and DataReader instead of DataTable. For example, you can change your snippet of code in the following way:
Code: Select all
OracleCommand comm = new OracleCommand("Select * from Table0", (oracleConnection)conn);
OracleDataReader reader = comm.ExecuteReader();
.......
OracleCommand Cmd = new OracleCommand("Insert into TABLEA(Col1) values (:PARAM)",
(OracleConnection)Con);
OracleParameter Param1 = new OracleParameter();
Param1.ParameterName = "PARAM";
Param1.OracleDbType = OracleDbType.NClob;
Cmd.Parameters.Add(Param1);
while(reader.Read())
{
Cmd.Parameters["PARAM"].Value = Reader.GetValue("COL1"));//return string for NClob;
Cmd.ExecuteNonQuery();
}
Last edited by Pinturiccio on Thu 29 Dec 2011 08:03, edited 1 time in total.
Thank you for your advice! Thats good to know.
For this case I can't change it because the source data is not a oracle-database and I have no DataReader. But its a good advice.
But what would you say: Is it better to dispose and recreate the OracleCommand and OracleParameters each time (for each loop)? Or is this a good solution as shown in the snippet (create it once and only modify the Value)?
Kind regards
Peter
For this case I can't change it because the source data is not a oracle-database and I have no DataReader. But its a good advice.
But what would you say: Is it better to dispose and recreate the OracleCommand and OracleParameters each time (for each loop)? Or is this a good solution as shown in the snippet (create it once and only modify the Value)?
Kind regards
Peter
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44