Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help on sqlldr

3427576Jun 29 2017 — edited Jul 1 2017

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]

This post has been answered by Gaz in Oz on Jun 29 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 29 2017
Added on Jun 29 2017
9 comments
607 views