This discussion is archived
0 Replies Latest reply: Dec 15, 2012 7:00 AM by 947771 RSS

Field in data file exceeds maximum length in sqldevloper while migrating.

947771 Newbie
Currently Being Moderated
value used for ROWS parameter changed from 64 to 26
Record 1: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table DBO_ADVENTUREWORKSD
SQL*Loader: Release 11.2.0.2.0 - Production on Sat Dec 15 18:58:11 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Control File: control\dbo_AdventureWorksDW.DimProduct.ctl
Data File: data/dbo_AdventureWorksDW.DimProduct.dat
File processing option string: "str '<EORD>'"
Bad File: control\dbo_AdventureWorksDW.DimProduct.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DBO_ADVENTUREWORKSDW.DIMPRODUCT, 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
------------------------------ ---------- ----- ---- ---- ---------------------
PRODUCTKEY FIRST * CHARACTER
Terminator string : '<EOFD>'
PRODUCTALTERNATEKEY NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ProductAlternateKey, CHR(00), ' ', :ProductAlternateKey)"
PRODUCTSUBCATEGORYKEY NEXT * CHARACTER
Terminator string : '<EOFD>'
WEIGHTUNITMEASURECODE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:WeightUnitMeasureCode, CHR(00), ' ', :WeightUnitMeasureCode)"
SIZEUNITMEASURECODE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:SizeUnitMeasureCode, CHR(00), ' ', :SizeUnitMeasureCode)"
ENGLISHPRODUCTNAME NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:EnglishProductName, CHR(00), ' ', :EnglishProductName)"
SPANISHPRODUCTNAME NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:SpanishProductName, CHR(00), ' ', :SpanishProductName)"
FRENCHPRODUCTNAME NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:FrenchProductName, CHR(00), ' ', :FrenchProductName)"
STANDARDCOST NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "TRANSLATE (:StandardCost,'1,','1')"
FINISHEDGOODSFLAG NEXT * CHARACTER
Terminator string : '<EOFD>'
COLOR NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:Color, CHR(00), ' ', :Color)"
SAFETYSTOCKLEVEL NEXT * CHARACTER
Terminator string : '<EOFD>'
REORDERPOINT NEXT * CHARACTER
Terminator string : '<EOFD>'
LISTPRICE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "TRANSLATE (:ListPrice,'1,','1')"
SIZE_ NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:Size_, CHR(00), ' ', :Size_)"
SIZERANGE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:SizeRange, CHR(00), ' ', :SizeRange)"
WEIGHT NEXT * CHARACTER
Terminator string : '<EOFD>'
DAYSTOMANUFACTURE NEXT * CHARACTER
Terminator string : '<EOFD>'
PRODUCTLINE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ProductLine, CHR(00), ' ', :ProductLine)"
DEALERPRICE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "TRANSLATE (:DealerPrice,'1,','1')"
CLASS NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:Class, CHR(00), ' ', :Class)"
STYLE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:Style, CHR(00), ' ', :Style)"
MODELNAME NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ModelName, CHR(00), ' ', :ModelName)"
SQLDEVELOPER_CLOB_24 NEXT * CHARACTER
Terminator string : '<EOFD>'
ENGLISHDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:EnglishDescription, CHR(00), ' ', :EnglishDescription)"
FRENCHDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:FrenchDescription, CHR(00), ' ', :FrenchDescription)"
CHINESEDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ChineseDescription, CHR(00), ' ', :ChineseDescription)"
ARABICDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ArabicDescription, CHR(00), ' ', :ArabicDescription)"
HEBREWDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:HebrewDescription, CHR(00), ' ', :HebrewDescription)"
THAIDESCRIPTION NEXT 400 CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:ThaiDescription, CHR(00), ' ', :ThaiDescription)"
STARTDATE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "TO_TIMESTAMP(:StartDate, 'yyyy-mm-dd HH24:mi:ss.ff3')"
ENDDATE NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "TO_TIMESTAMP(:EndDate, 'yyyy-mm-dd HH24:mi:ss.ff3')"
STATUS NEXT * CHARACTER
Terminator string : '<EOFD>'
SQL string for column : "DECODE(:Status, CHR(00), ' ', :Status)"
SS NEXT * CHARACTER
Terminator string : '<EOFD>'

value used for ROWS parameter changed from 64 to 26
Record 1: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 6: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 8: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 9: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 10: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 11: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 12: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 13: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 14: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 15: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 16: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 17: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 18: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 19: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 20: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 21: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 22: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 23: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 24: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 25: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 26: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 27: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 28: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 29: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 30: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 31: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 32: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 33: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 34: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 35: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 36: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 37: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 38: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 39: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 40: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 41: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 42: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 43: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 44: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 45: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 46: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 47: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 48: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 49: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 50: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 51: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table DBO_ADVENTUREWORKSDW.DIMPRODUCT:
0 Rows successfully loaded.
51 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 250536 bytes(26 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 51
Total logical records rejected: 51
Total logical records discarded: 0

Run began on Sat Dec 15 18:58:11 2012
Run ended on Sat Dec 15 18:58:11 2012

Elapsed time was: 00:00:00.66
CPU time was: 00:00:00.10
W.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 6: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 7: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 8: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 9: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 10: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length
Record 11: Rejected - Error on table DBO_ADVENTUREWORKSDW.DIMPRODUCT, column SQLDEVELOPER_CLOB_24.
Field in data file exceeds maximum length

Please tel me how to solve the problem.
it has not migrated the table. when i checked the log this was the problem i found.
i am using oracle 11g express and migrating from sqlserver 2005.
please also tel me what other things i should consider before migrating becasue my database is quite big and has image cols too.

yours sincerely

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points