Skip to Main Content
  • Questions

  • SQL Developer Migration From TD to Oracle ( Using Inbuild Tool )

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kishlay.

Asked: November 25, 2015 - 9:46 am UTC

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

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom,

I am using Migration utility tool which comes with SQL Developer. I am trying to load data from TD to Oracle using Teradata_fexp.sh which actually exports the data from TD and another one is Teradata_fexpsqlldr.sh which actually loads the data calling the cntrl file.

When we generated the scripts using offline mode, it created the master.sql which contains all the objects.
using that we have created all the objects in Oracle.

Now when I am using cntrl file to load one of table, it gives me an error, that the column doesnt exists in the table. Its is not with this table but all the table.

Sample control file is where all the columns which starts from NL_ does not exists in the table.

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 CHAR(1),
ET_CODE_ID INTEGER EXTERNAL(12) NULLIF NL_ET_CODE_ID='Y',
NL_ET_CODE_UID CHAR(1),
ET_CODE_UID VARCHARC(6) NULLIF NL_ET_CODE_UID='Y',
NL_ET_CODE_DESP CHAR(1),
ET_CODE_DESP VARCHARC(6) NULLIF NL_ET_CODE_DESP='Y',
NL_ET_CODE_SHORT_DESP CHAR(1),
ET_CODE_SHORT_DESP VARCHARC(6) NULLIF NL_ET_CODE_SHORT_DESP='Y',
NL_COREP_REPORT CHAR(1),
COREP_REPORT VARCHARC(6) NULLIF NL_COREP_REPORT='Y',
NL_PPN_TMS CHAR(1),
PPN_TMS CHAR(19) NULLIF NL_PPN_TMS='Y',
F2_PPN_TMS FILLER RAW(8),
NL_IS_ACTIVE CHAR(1),
IS_ACTIVE CHAR(1) NULLIF NL_IS_ACTIVE='Y',
F2_IS_ACTIVE FILLER RAW(8),
NL_VERSION_START_MONTH CHAR(1),
VERSION_START_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_START_MONTH='Y',
NL_VERSION_END_MONTH CHAR(1),
VERSION_END_MONTH INTEGER EXTERNAL(12) NULLIF NL_VERSION_END_MONTH='Y',
NL_HASH_CODE CHAR(1),
HASH_CODE VARCHARC(6) NULLIF NL_HASH_CODE='Y'
)

The records exported from the TD 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>

Kindly help me where is the problem in cntrl file and why we got extra column in cntrl file.



and Chris said...

There is a bug in control file generation in SQL Developer. This is already filed (MOS note 1665640.1). The solution is to place a FILLER keyword after every NL_ and F#_ columns in the control file:

until the fix for bug Bug 18650253 - SQL*LOADER-466: COLUMN NL_COL2 DOES NOT EXIST IN TABLE GATEWAY.TESTCHAR. is included in one of the next upcoming SQL Developer releases please manually change the generated SQL*Loader control file and add to every NL_* and F#_ coluns the FILLER key word. 

Example:
The dat file contains:
oracle@zkupchv118:~/sqldev_teradata_13/Teradata_13_Offline/datamove/2014-04-23_12-49-54/Teradata/gateway/data> more *.dat
-++00001N000012Hello World!

The generated control file has:
IG FILLER CHAR(4) TERMINATED BY '',
col1 INTEGER EXTERNAL(6),
NL_col2 CHAR(1),
col2 VARCHARC(6) NULLIF NL_col2='Y'
)

Because the original table has 2 columns, but the dat file shows 4 we have 2 FILLER columns, hence need to change the control file to:


[oracle@emeagtw Teradata]$

=> add to ID (alreadxy has it...) and NL_ columns keyword FILLER:
IG FILLER CHAR(4) TERMINATED BY '',
col1 INTEGER EXTERNAL(6),
NL_col2 FILLER CHAR(1),
col2 VARCHARC(6) NULLIF NL_col2='Y'
)

Rating

  (1 rating)

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

Comments

Need more help on this

Kishlay Anand, November 25, 2015 - 4:01 pm UTC

The solution which you provided i have tried a lot, but it doesnt work.
The next error i m 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 sir. I am in big trouble
Chris Saxon
December 03, 2015 - 10:07 am UTC

This is answered at https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9524287800346156263
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.