We have a set of tables that we can get the data from Oracle quickly by breaking the Clob into 4k chunks. I suspect a custom iDataReader somewhat like this:
SqlBulkCopy ColumnMapping multiple columns
is necessary. I'm struggling how to take, the clob parts, from the datareader, and join those together into a local varchar(max) field, while treating other columns normally. I have a branch path because not all table have such a field, and can be handled quickly the other path.
We've tried many avenues, including attempting pulling it directly in, joining on the oracle side after chopping, etc. this seems to be the FASTEST way to get the data.
(by at least an order or magnitude)
try
{
int hasCLOB = (int)Dts.Variables["User::HasCLOB"].Value;
if (hasCLOB == 0)
{
using (OleDbConnection connection = new OleDbConnection(Dts.Connections["GoldenGate"].ConnectionString))
{
using (OleDbCommand cmd = new OleDbCommand(Dts.Variables["User::BaseSelectSQL"].Value.ToString(), connection))
{
connection.Open();
using (OleDbDataReader odr = cmd.ExecuteReader())
{
using (SqlConnection SQLconn = new SqlConnection(Dts.Connections["ADO_PSImport"].ConnectionString))
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLconn, SqlBulkCopyOptions.TableLock, null))
{
SQLconn.Open();
bulkCopy.DestinationTableName = "dbo." + Dts.Variables["User::TableName"].Value.ToString();
bulkCopy.EnableStreaming = true;
bulkCopy.BatchSize = 5000;
//bulkCopy.BulkCopyTimeout = 0;
// Write rows from the source to the destination.
bulkCopy.WriteToServer(odr);
}
}
}
}
}
} else
{
// there is a clob field and table needs processed somewhat differently, .
}
}
the clob columns are as follows:
NVL(CAST(substr(DESCRLONG, 1, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART1,
NVL(CAST(substr(DESCRLONG, 4001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART2,
NVL(CAST(substr(DESCRLONG, 8001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART3,
NVL(CAST(substr(DESCRLONG, 12001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART4,
NVL(CAST(substr(DESCRLONG, 16001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART5,
NVL(CAST(substr(DESCRLONG, 20001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART6,
NVL(CAST(substr(DESCRLONG, 24001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART7,
NVL(CAST(substr(DESCRLONG, 28001, 4000) as VARCHAR2(4000)), ' ') AS CLOBPART8
The local field in this case is DESCRLONG but could be differerent, but the CLOBPARTS are common, and get pushed to a varchar(max). The non-CLOB pathway shown seems to work well as its based on ordinal position. Obviously that won't quite work with the many to one.
