0 Replies Latest reply: Dec 15, 2012 9:00 AM by 947771 RSS

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

    947771
      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