Hello, thank you for your reply
1. What connection mode are you using: OCI or Direct?
>> Direct
2. Oracle server version.
>> 12c
3. Oracle table DDL script.
Code: Select all
CREATE TABLE TABLE1
(
RECORD_ID NUMBER(10) NOT NULL,
MD_IF_FILE_FK RAW(16) NOT NULL,
MD_IF_FILE_TYPE_FK RAW(16),
REPORTING_DT VARCHAR2(255 CHAR),
EXTRACTION_TYPE VARCHAR2(255 CHAR),
RECORD_TYPE VARCHAR2(255 CHAR),
LEGAL_ENTITY_CD VARCHAR2(255 CHAR),
CREDIT_CONTROL_AREA VARCHAR2(255 CHAR),
CUSTOMER_CD VARCHAR2(255 CHAR),
CUSTOMER_VAT_CD VARCHAR2(255 CHAR),
ON_BOOK_LEGAL_ENTITY VARCHAR2(255 CHAR),
SHIPTO_COUNTRY_NM VARCHAR2(255 CHAR),
INVTO_ISO_COUNTRY_ALFA2_CD VARCHAR2(255 CHAR),
SHIPTO_ISO_COUNTRY_ALFA2_CD VARCHAR2(255 CHAR),
BRAND VARCHAR2(255 CHAR),
BUSINESS VARCHAR2(255 CHAR),
CURRENCY VARCHAR2(255 CHAR),
CUSTOMER_CLS_CD VARCHAR2(255 CHAR),
CUSTOMER_TYPE_CD VARCHAR2(255 CHAR),
TRADING_PARTNER_CD VARCHAR2(255 CHAR),
CUSTOMER_NM VARCHAR2(255 CHAR),
OUTSTANDING_AMT VARCHAR2(255 CHAR),
NOTES_RECEIVABLE_AMT VARCHAR2(255 CHAR),
DIRECT_DEBIT_AMT VARCHAR2(255 CHAR),
PASSIVE_ITEM_AMT VARCHAR2(255 CHAR),
SUSPENDED_AMT VARCHAR2(255 CHAR),
PASTDUE_AMT VARCHAR2(255 CHAR),
PASTDUE_1_30_AMT VARCHAR2(255 CHAR),
PASTDUE_31_60_AMT VARCHAR2(255 CHAR),
PASTDUE_61_90_AMT VARCHAR2(255 CHAR),
PASTDUE_91_120_AMT VARCHAR2(255 CHAR),
PASTDUE_121_180_AMT VARCHAR2(255 CHAR),
PASTDUE_181_210_AMT VARCHAR2(255 CHAR),
PASTDUE_211_360_AMT VARCHAR2(255 CHAR),
PASTDUE_OVER_360_AMT VARCHAR2(255 CHAR),
UNALLOCATED_CREDIT_AMT VARCHAR2(255 CHAR),
LETTER_CREDIT_COVER_AMT VARCHAR2(255 CHAR),
INV_12M_AMT VARCHAR2(255 CHAR),
INV_MTD_AMT VARCHAR2(255 CHAR),
INV_YTD_AMT VARCHAR2(255 CHAR),
PAY_12M_AMT VARCHAR2(255 CHAR),
PAY_YTD_AMT VARCHAR2(255 CHAR),
PAY_MTD_AMT VARCHAR2(255 CHAR),
WRITEOFF_12M_AMT VARCHAR2(255 CHAR),
WRITEOFF_YTD_AMT VARCHAR2(255 CHAR),
WRITEOFF_MTD_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_1_30_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_31_60_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_61_90_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_91_120_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_121_180_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_181_210_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_211_360_AMT VARCHAR2(255 CHAR),
PASTDUE_XCR_OVER_360_AMT VARCHAR2(255 CHAR),
FUTUTREDUE_AMT VARCHAR2(255 CHAR),
FUTUREDUE_1_30_AMT VARCHAR2(255 CHAR),
FUTUREDUE_31_60_AMT VARCHAR2(255 CHAR),
FUTUREDUE_61_90_AMT VARCHAR2(255 CHAR),
FUTUREDUE_91_120_AMT VARCHAR2(255 CHAR),
FUTUREDUE_121_180_AMT VARCHAR2(255 CHAR),
FUTUREDUE_181_210_AMT VARCHAR2(255 CHAR),
FUTUREDUE_211_360_AMT VARCHAR2(255 CHAR),
FUTUREDUE_OVER_360_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_1_30_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_31_60_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_61_90_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_91_120_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_121_180_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_181_210_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_211_360_AMT VARCHAR2(255 CHAR),
FUTUREDUE_XCR_OVER_360_AMT VARCHAR2(255 CHAR),
BADDEBT_RESERVE_AMT VARCHAR2(255 CHAR),
PREPAY_AMT VARCHAR2(255 CHAR)
)
4. Please also describe data that is stored in SQL Server so that we can reproduce such a use case. For example, if there is a varchar(max) column in SQL Server, how big text values are stored there.
>>As you can see there are no big string, almost every field is loaded as Varchar2 255.
I also want you to be aware that in the meantime i've also done a test using the class OracleBulkCopy (ODAC) and it is as fast as SqlBulkCopy, here is the code snippet with OracleBulkCopy:
Code: Select all
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(conns);
conn.Open();
using (OracleBulkCopy loader = new OracleBulkCopy(conn))
{
loader.DestinationTableName = destinationTableName;
loader.BulkCopyTimeout = 600;
Oracle.DataAccess.Client.OracleGlobalization glob = Oracle.DataAccess.Client.OracleGlobalization.GetThreadInfo();
glob.DateFormat = "DD/MM/YYYY";
Oracle.DataAccess.Client.OracleGlobalization.SetThreadInfo(glob);
loader.Connection.SetSessionInfo(glob);
loader.BatchSize = dt.Rows.Count;
loader.WriteToServer(dt);
}
Thank you