Click here to Skip to main content
14,153,409 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I m trying to load data from one oracle table to other
Both tables are in different oracle database
tables dont have same column names
i m taking data in dataset and trying to inset in other
there are 28000 records
loading takes 8 minutes
this much time is not allowed
i m not allowed to use procedures
i cant access source table from destination tables database and viceversa
both tables dont have any indices
how can i minimize this time?
can i use oracle bulk insert and how?
i tried to search oracle bulk insert but it requires some Oracle.DataAccess.Client namespace
i dont have it
Posted
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I belive you asked the same question yesterday copy data between 2 oracle tables[^]

I usually do it like this:
public void WriteToServer( OracleConnection oracleConnection, string qualifiedDBName, DataTable dataTable )
{
 try
 {
   using ( OracleBulkCopy bulkCopy = new OracleBulkCopy( oracleConnection ) )
   {
     bulkCopy.DestinationTableName = qualifiedDBName;
     bulkCopy.WriteToServer( dataTable );
   }
 }
 catch ( Exception exc )
 {
  LogException( exc, MethodBase.GetCurrentMethod( ) );
  throw;
 }
}


As you can see it's fairly easy. You can create the DataTable in this manner:
public DataTable CreateDataTable()
{
 try
 {
  DataTable result = new DataTable();

  DataColumn idDataColumn = new DataColumn( "ID", typeof(decimal) );
  idDataColumn.AllowDBNull = false;
  result.Columns.Add(idDataColumn);
  DataColumn nameDataColumn = new DataColumn( "NAME", typeof(string) );
  nameDataColumn.AllowDBNull = false;
  result.Columns.Add(nameDataColumn);
  
  DataColumn[] keys = new DataColumn[1];
  keys[0] = idDataColumn;
  result.PrimaryKey = keys;

  return result;
 }
 catch (Exception exc)
 {
  LogException(exc, MethodBase.GetCurrentMethod());
  throw;
 }
}


Best regards
Espen Harlinn
   
Comments
Member 8081020 19-Jul-11 7:25am
   
Sir, thanks for giving me suggestion
but i m not getting OracleBulkCopy class in Oracle.DataAccess.Client;
M123SD 6-Jan-12 14:26pm
   
I am getting "The provider is not compatible with the version of Oracle client" error when I use Oraclebulkcopy. I am using ODAC 11.2.0.3.0. Please help.
Espen Harlinn 7-Jan-12 5:14am
   
Installing the oracle Data Access Client which included the Oracle.DataAccess.dll usually helps. Personally I prefer using a full client installation like Oracle Database 11g Release 2 Client (32 bit):
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html

or

Oracle Database 11g Release 2 Client (64-bit)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

I try to avoid the instant client
Wonde Tadesse 9-Jan-12 19:54pm
   
5+.Congratulation for your MVP.
Espen Harlinn 10-Jan-12 3:34am
   
Thank you, Wonde :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Oracle.DataAccess.Client This Namespace in ODP.Net. You can download from Here : Oracle site[^]
CP article on Bulk Insert:Bulk Insert using ODP.NET[^]
and some useful links
Link 1[^]
Link 2[^]
Link 3[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05 | 2.8.190518.1 | Last Updated 19 Jul 2011
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100