Skip to Main Content
  • Questions

  • SQL Developer Migration From TD to Oracle ( Using Inbuild Tool ) problem still with Filler Command

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kishlay.

Asked: November 25, 2015 - 4:33 pm UTC

Last updated: December 03, 2015 - 10:05 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom ,

As you said try add Filler command after every Nl_ column. So i did it but its not working any more.

The data is extracted is:
<EOFD>N<EOFD>+00000000043N<EOFD>000004ET57N<EOFD>000029Retail: All Retail exposures.N<EOFD>000017ET57 - CR SA v2.8N<EOFD>000010CR SA v2.8N<EOFD> 2015092214:00:09 <EOFD>N<EOFD> Y <EOFD>N<EOFD>+00000201507N<EOFD>+00000999912N<EOFD>00003235E3B5D5D6B9FF46505CC5325FE9480F<EORD>

The cntrl file is:

load data
infile '../data/accB2_Basel2.TB2C_DIM_ET_CODE.fexp.dat'
"str '<EORD>'"
into table accB2_Basel2.TB2C_DIM_ET_CODE
fields
trailing nullcols
(
IG FILLER CHAR(4) TERMINATED BY '<EOFD>',
NL_ET_CODE_ID FILLER CHAR(1),
ET_CODE_ID INTEGER EXTERNAL(12) NULLIF NL_ET_CODE_ID='Y',
NL_ET_CODE_UID FILLER CHAR(1),
ET_CODE_UID VARCHARC(6) NULLIF NL_ET_CODE_UID='Y',
NL_ET_CODE_DESP FILLER CHAR(1),
ET_CODE_DESP VARCHARC(6) NULLIF NL_ET_CODE_DESP='Y',
NL_ET_CODE_SHORT_DESP FILLER CHAR(1),
ET_CODE_SHORT_DESP VARCHARC(6) NULLIF NL_ET_CODE_SHORT_DESP='Y',
NL_COREP_REPORT FILLER CHAR(1),
COREP_REPORT VARCHARC(6) NULLIF NL_COREP_REPORT='Y',
NL_PPN_TMS FILLER CHAR(1),
PPN_TMS CHAR(19) NULLIF NL_PPN_TMS='Y',
F2_PPN_TMS FILLER RAW(8),
NL_IS_ACTIVE FILLER CHAR(1),
IS_ACTIVE CHAR(1) NULLIF NL_IS_ACTIVE='Y',
F2_IS_ACTIVE FILLER RAW(8),
NL_VERSION_START_MONTH FILLER CHAR(1),
VERSION_START_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_START_MONTH='Y',
NL_VERSION_END_MONTH FILLER CHAR(1),
VERSION_END_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_END_MONTH='Y',
NL_HASH_CODE FILLER CHAR(1),
HASH_CODE VARCHARC(6) NULLIF NL_HASH_CODE='Y'
)

And the error I am getting is:

Table ACCB2_BASEL2.TB2C_DIM_ET_CODE, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IG FIRST 4 CHARACTER
(FILLER FIELD)
Terminator string : '<EOFD>'
NL_ET_CODE_ID NEXT 1 CHARACTER
(FILLER FIELD)
ET_CODE_ID NEXT 12 CHARACTER
NULL if NL_ET_CODE_ID = 0X59(character 'Y')
NL_ET_CODE_UID NEXT 1 CHARACTER
(FILLER FIELD)
ET_CODE_UID NEXT * VARCHARC
NULL if NL_ET_CODE_UID = 0X59(character 'Y')
NL_ET_CODE_DESP NEXT 1 CHARACTER
(FILLER FIELD)
ET_CODE_DESP NEXT * VARCHARC
NULL if NL_ET_CODE_DESP = 0X59(character 'Y')
NL_ET_CODE_SHORT_DESP NEXT 1 CHARACTER
(FILLER FIELD)
ET_CODE_SHORT_DESP NEXT * VARCHARC
NULL if NL_ET_CODE_SHORT_DESP = 0X59(character 'Y')
NL_COREP_REPORT NEXT 1 CHARACTER
(FILLER FIELD)
COREP_REPORT NEXT * VARCHARC
NULL if NL_COREP_REPORT = 0X59(character 'Y')
NL_PPN_TMS NEXT 1 CHARACTER
(FILLER FIELD)
PPN_TMS NEXT 19 CHARACTER
NULL if NL_PPN_TMS = 0X59(character 'Y')
F2_PPN_TMS NEXT 8 RAW
(FILLER FIELD)
NL_IS_ACTIVE NEXT 1 CHARACTER
(FILLER FIELD)
IS_ACTIVE NEXT 1 CHARACTER
NULL if NL_IS_ACTIVE = 0X59(character 'Y')
F2_IS_ACTIVE NEXT 8 RAW
(FILLER FIELD)
NL_VERSION_START_MONTH NEXT 1 CHARACTER
(FILLER FIELD)
VERSION_START_MONTH NEXT 12 CHARACTER
NULL if NL_VERSION_START_MONTH = 0X59(character 'Y')
NL_VERSION_END_MONTH NEXT 1 CHARACTER
(FILLER FIELD)
VERSION_END_MONTH NEXT 12 CHARACTER
NULL if NL_VERSION_END_MONTH = 0X59(character 'Y')
NL_HASH_CODE NEXT 1 CHARACTER
(FILLER FIELD)
HASH_CODE NEXT * VARCHARC
NULL if NL_HASH_CODE = 0X59(character 'Y')

value used for ROWS parameter changed from 64 to 12
Record 1: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 2: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 3: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 4: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 5: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 6: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 7: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 8: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 9: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 10: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 11: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 12: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 13: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 14: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 15: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 16: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 17: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 18: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 19: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 20: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 21: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 22: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 23: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 24: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 25: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 26: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 27: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 28: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 29: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 30: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 31: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 32: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 33: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 34: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 35: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 36: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 37: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 38: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 39: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 40: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 41: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 42: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 43: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 44: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 45: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 46: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 47: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 48: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 49: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 50: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number
Record 51: Rejected - Error on table ACCB2_BASEL2.TB2C_DIM_ET_CODE, column ET_CODE_UID.
error converting character length field to a number

Kindly help me

and Chris said...

Oracle doesn't know that the <EOFD> strings are field terminators. Therefore it's trying to read these as the next fields

You have two options:

Remove the <EOFD> strings from the file or
Change the NL_* fields to be:

NL_* FILLER CHAR(1) TERMINATED BY '<EOFD>'


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.