.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

out of memory loading datatable from large oracle table

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :WCF

I've created a winform c# application that connects to an oracle database and loads a datatable with all the data in the oracle table then exports it out to a delimited file, which will then get imported to a local mysql database.  This works just fine when the oracle table isn't so large.  However I keep getting an out of memory exception when I populate my datatable with a large oracle table.  I can't manually export data from oracle then manually load to mysql because this has to be seamless to the user.  I'm having difficulties grasping how to solve this problem.  Is there a better solution then what I have to get from oracle to mysql? 

View Complete Post

More Related Resource Links

Bulk Copy From Datatable To SQL Server Table


 I have read the data of a excle file and captured the data into the dataset to a datatable, now that data is to be inserted into a SQL Server table using bulk copy option. I am using -

 public bool BulkEnterData(DataTable dt, string tblName)
        SqlBulkCopy bulk = new SqlBulkCopy(con);
        bulk.DestinationTableName = tblName;

        return true;

ERROR Getting-
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.) 

How to resolve the above problem or any other solution is available.


DataTable --> Oracle DB



Can someone offer advice on the below? 

I am connecting to an AR Server and pulling out data into a DataSet.  There will be 15 different DataTables within the set.  I would like to find a way to either insert the entire DataSet or each DataTable into an Oracle table.  The Oracle table has the same format as the DataTables I'm creating.

 private void button1_Click(object sender, EventArgs e)
                //Get Credentials
                ID = userNameTextBox.Text;
                PW = passWordTextBox.Text;

                connString = gstrConnectBaseLineStart + "UID=" + ID + ";PWD=" + PW + gstrConnectBaseLineEnd; //Connection string
                conn = new OdbcConnection(connString); //Create Connection
                conn.Open(); //Open Connection
                Console.Write("Connection Successful" + "\n");

                dataAdapter = new OdbcDataAdapter(SQL, conn);
                dataAdapter.Fill(IncidentsDataSet, "dataTableTest");

                DataTable dataTable = new DataTable();
                dataTable = IncidentsDataSet.Tables["dataTableTest"];

                dataGridView1.DataSource = dataTable;

                //Would like to create som

Help creating HTML table from dataTable when every nth dataTable row = 1 html table row... C#

Hello, Im trying to create an html table from data that is in a dataTable but Im having trouble coming up with the right logic. Every 3 rows in the dataTable needs to be 1 row in the html table.  Ive tried using foreach through the datatable with a modulus to determin every third dataTable row but i still run into snags because i am building the table using objects rather than using concatenated strings..(example TableRow tr = new TableRow  etc...). I cant add the cells to the rows when the row is only created in an if statement etc. Can someone post a good example in c# of how they would achieve this. The amount of data in the dataTable could change from time to time so the code cant rely on a pre determined size, unless you just need the total rows and columns, that can be dtermined when the dataTable is filled from the sqlDataReader. <table><tr><td>dataTable row 1 values</td><td>ataTable row 2 values</td><td>dataTable row 3 values</td></tr> <tr><td>dataTable row 4 values</td><td>dataTable row 5 values</td><td>dataTable row 6 values</td></tr>   Any help and example would be great. Ive been able to do similar tables in other apps but thats only when I use concatenated text strings to build the html table. Im trying to stay away from that this time

lookup: unable to allocate memory for hash table

Hi, I have been getting an error from a lookup component when I run it from the job agent. However, it works just fine from debug mode. the specific error is 0xC0208247: unable to allocate memory for hash table. I get the same error when I run this not in full-cach but in partial and no cache. Also, the default butter max row and buffer size in package are set to default. Does anyone have any idea.   Jon

Oracle ref cursor or temp table?

I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

SELECT COUNT(*) FROM [Table] from an Oracle database

Hi friends, I have problem when retrieving a result from SELECT COUNT(*) FROM [Table] from an Oracle database. When I try to put the result (single row) in a variable I get the following error message. [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowsSource": "Unsupported data type on result set binding RowsSource.". Pls help me Mahe

Import DataTable into SQL Table

I'm trying to parse a text file into a sql table.  I have the code to parse the file and display it in a datatable but for the life of me I can't figure out how to import it into a sql table.  All my previous experience has been with csv files and with schema.ini files.  This is new to me.  This is what the code to read the text file and display it in a datagridview: Dim dtTest As DataTable Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load dtTest = New DataTable DataGridView1.DataSource = dtTest.DefaultView End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim filename As String = "C:\Price.txt" Dim lstValues As New List(Of String) Try Using sr As StreamReader = New StreamReader(filename) While Not sr.EndOfStream Dim splits As String() splits = sr.ReadLine.Split(","c) dtTest.Columns.Add(splits(1), GetType(String)) lstValues.Add(splits(2)) End While End Using Catch ex As Exception End Try dtTest.Rows.Add(lstValues.ToArray) End Sub This is what the data in the text file looks like: DD/MM/YYYY HH:MM:SS,LAU4AB,245.35 DD/MM/YYYY HH:MM:SS,ROC06,123.45 DD/MM/YYYY HH:MM:SS,GRE033,87.5 The second item "LAU4AB is the column header for the first

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

When to use 'reader' versus a datatable, table adaptor?


I'm making the leap from using all the 'wizards' that are built into VS, and have been doing more with code-behinds.  But, I'm curious as to when/why I should use a reader, versus a databable, table adaptor to "manage" the data returned from a stored procedure.  Typically, the data is to be displayed on a webpage, and not written back to the database.  What's the difference?  When should I use one over another?  

Sizing a Pagefile on Servers with Large Amounts of Memory


I know the standard Microsoft recommendation is to make the pagefile at least 1.5 to 3 times larger then the amount of physical memory.  However, if you're talking about a server with lots of memory such as 16GB or 32GB, would following this rule be unnecessary.  With SQL 2000 running on Windows 2000 Server or Windows Server 2003 I typically see pagefile usage no more then 12% for a 2GB pagefile.  Anything over 15% means I need to look at other indicators to see if a memory bottleneck has developed.  If I have 32GB of physical memory and make the pagefile only 1.5 x 32GB I have a 48GB pagefile.  10% of this is 4.8GB, which I would hope I never see consumed.


Any thoughts?


Thanks,    Dave

hash table and datatable


I want to store the data in temp table, which is best way to use

hash table or datatable.

Looking forward the reply

How to avoid the memory exhausion for handling large Image files in C# ?


I have been developing a Form application for handling a large number Image files. The number of Image files could be more than 1,000 and each Image size is about 2MB. The code is as follows:

PictureBox[] pb = new PictureBox[iPictureBoxNumMax];
Label[] lb = new Label[iPictureBoxNumMax];

for (int i = 0; i < iPictureBoxNum; i++)
    lb[i] = new Label();
    pb[i] = new PictureBox();
    pb[i].ImageLocation = @sImageListAll[i];


for (int i = 0; i < iPictureBoxNum; i++)

(1) If the number of Image files is less than 300, the PictureBox generation code (the 1st portion) works. If the number is larger than that, an error message of "Memory exhausion" is displayed.

(2) However, the second protion of the code (pb[i].Dispose()) doesn't seem to clear, since the re-run of the first portion gives an error message of "Memory exhausion".

What should I do ?


Import Access table or Excel spreadsheet into Oracle table


I am trying to import from an Excel spreadsheet or an Access database into an Oracle table.  I used the SSIS Import and Export wizard to create the SSIS packages, but everytime that I attempt to run the package, SSIS stops once it gets to the Pre-Execute phase.  There are never any errors.  Am I doing something that cannot be done?

I'm using the following:  SSIS 2005, Microsoft Oracle OLEDB provider (MSDAORA.1), Excel and Access 2003

By the way, I am able to successfully export from the Oracle database to either Excel or Access using SSIS.


things to consider when loading a lot of data to a big table


What are the considerations when loading a large amount of data to a large table?

One thing to consider are the indexes because they need to get rebuilt. One advice I had from someone is to drop the index, load the data, and finally re-create the index. Is this faster than loading without dropping the index?

What other considerations or concerns are there? What about data isolation? If it take a while to load the data, are the users locked out from selecting from the table? I would try to test this, but not sure how I can come up with large enough data set to play with.

What is causing oracle datbase table failing to save inserted Record permenantly?


I have a table in orcale database where I am inserting records. I am able to insert a record in a table but it is not being saved permenantly.When inserting there is no error. what could be causing this?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend