OutOfMemoryException using NCLOB as Parameters

dape2801 · Post by **dape2801** » Fri 16 Dec 2011 08:15

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

Pinturiccio · Post by **Pinturiccio** » Wed 21 Dec 2011 13:21

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.

dape2801 · Post by **dape2801** » Thu 22 Dec 2011 07:26

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

Pinturiccio · Post by **Pinturiccio** » Fri 23 Dec 2011 15:33

Could you tell us how you get and send values of the NClob type from the old database to the new one? How do you fill the parameters before executing the insert command? A snippet of code will be appreciated.

dape2801 · Post by **dape2801** » Wed 28 Dec 2011 07:36

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:

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();
                    }

Kind regards
Peter

Pinturiccio · Post by **Pinturiccio** » Wed 28 Dec 2011 14:30

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:

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();
                }

dape2801 · Post by **dape2801** » Wed 28 Dec 2011 14:51

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

Pinturiccio · Post by **Pinturiccio** » Thu 29 Dec 2011 12:47

In our opinion, creating OracleCommand and OracleParameters for each iteration is not as good as the solution presented in my snippet.

dape2801 · Post by **dape2801** » Mon 02 Jan 2012 15:42

Hello,

thanks for your input.
I've redesigned the code a little bit and now we don't get this error.
So it seems that we've found a solution.

Thanks for your help again.
Kind regards
Peter