Hi guys,
I am using sqlldr to insert 3000 records out of which 116 records are being rejected and are moving to .bad file as the data in the FORMULA column is inconsistent. I need sqlldr parameters or ctl file options are any other solution to insert the data of the following type into a field. I don't have control on input file as it is a server generated one. I tried option which I found on the web but didn't worked out.
CTL FILE:
OPTIONS (DIRECT=TRUE, SKIP=1)
UNRECOVERABLE LOAD DATA
TRUNCATE
INTO TABLE NGLAccounts TRAILING NULLCOLS
(
ACCOUNT terminated by ',' optionally enclosed by '"',
PARENT terminated by ',' optionally enclosed by '"',
ALIAS terminated by ',' optionally enclosed by '"',
VALID_FOR_CONSOLIDATIONS terminated by ',' optionally enclosed by '"',
DATA_STORAGE terminated by ',' optionally enclosed by '"',
TWO_PASS_CALCULATION terminated by ',' optionally enclosed by '"',
DESCRIPTION terminated by ',' optionally enclosed by '"',
FORMULA terminated by ',' optionally enclosed by '"',
FORMULA_DESCRIPTION terminated by ',' optionally enclosed by '"',
UDA terminated by ',' optionally enclosed by '"',
SMARTLIST terminated by ',' optionally enclosed by '"',
DATATYPE terminated by ',' optionally enclosed by '"',
HIERARCHYTYPE terminated by ',' optionally enclosed by '"',
ENABLE_DYNAMICCHILD terminated by ',' optionally enclosed by '"',
NOOF_POSSIBLE_DYNAMIC_CHILDREN terminated by ','
optionally enclosed by '"',
ACCESSGRANTEDTOMEMBERCREATOR terminated by ','
optionally enclosed by '"',
ALLOW_UPPER_LEVEL_ENTITY_INPUT terminated by ','
optionally enclosed by '"',
PROCESS_MGMT_ENABLED terminated by ',' optionally enclosed by '"',
ACCOUNTTYPE terminated by ',' optionally enclosed by '"',
TIMEBALANCE terminated by ',' optionally enclosed by '"',
SKIP_VALUE terminated by ',' optionally enclosed by '"',
EXCHANGE_RATE_TYPE terminated by ',' optionally enclosed by '"',
VARIANCE_REPORTING terminated by ',' optionally enclosed by '"',
SOURCE_PLAN_TYPE terminated by ',' optionally enclosed by '"',
PLANTYPE_FINPLAN terminated by ',' optionally enclosed by '"',
AGGREGATION_FINPLAN terminated by ',' optionally enclosed by '"',
DATASTORAGE_FINPLAN terminated by ',' optionally enclosed by '"',
FORMULA_FINPLAN terminated by ',' optionally enclosed by '"',
FORMULA_DESC_FINPLAN terminated by ',' optionally enclosed by '"',
PLANTYPE_REVPLAN terminated by ',' optionally enclosed by '"',
AGGREGATION_REVPLAN terminated by ',' optionally enclosed by '"',
DATASTORAGE_REVPLAN terminated by ',' optionally enclosed by '"',
FORMULA_REVPLAN terminated by ',' optionally enclosed by '"',
FORMULA_DESC_REVPLAN terminated by ',' optionally enclosed by '"',
PLANTYPE_CAPPROJ terminated by ',' optionally enclosed by '"',
AGGREGATION_CAPPROJ terminated by ',' optionally enclosed by '"',
DATASTORAGE_CAPPROJ terminated by ',' optionally enclosed by '"',
FORMULA_CAPPROJ terminated by ',' optionally enclosed by '"',
FORMULA_DESC_CAPPROJ terminated by ',' optionally enclosed by '"',
PLANTYPE_EMPRPT terminated by ',' optionally enclosed by '"',
AGGREGATION_EMPRPT terminated by ',' optionally enclosed by '"',
DATASTORAGE_EMPRPT terminated by ',' optionally enclosed by '"',
FORMULA_EMPRPT terminated by ',' optionally enclosed by '"',
SOLVEORDER_EMPRPT terminated by ',' optionally enclosed by '"',
FORMULADESCRIPTION_EMPRPT terminated by ',' optionally enclosed by '"',
OPERATION )
Sample Data in Formula column
IF ( NOT( @ISMBR(""Account_Visibility"", ""Account_Valid"", ""Spread"") ) )
@SUM( @CHILDREN(@CURRMBR(""Account"")) );
ENDIF
[OpenInputValueBlock]
[TimeBalanceFormula(""Spread_Actual_365"")]
[CloseInputValueBlock]