6 Replies Latest reply: Dec 13, 2012 9:13 PM by sb92075 RSS

    "Field in data file exceeds maximum length" : sqlloader

    vasanth
      Hi
      When trying to load to Northwind_CHECK_CONSTRAINTS_A3 table in dbo_a3 schema using sqlloader im getting the below error in the log file of the loader

      Control file :

      italics
      load data
      infile /home/oracle/Northwind_CHECK_CONSTRAINTS.csv
      into table Northwind_CHECK_CONSTRAINTS_A3
      fields terminated by ','optionally enclosed by '"' trailing nullcols
      (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, CHECK_CLAUSE)

      table :

      create table Northwind_CHECK_CONSTRAINTS_A3
      (
      CONSTRAINT_CATALOG char(1000),
      CONSTRAINT_SCHEMA char(1000),
      CONSTRAINT_NAME char(1000),
      CHECK_CLAUSE char(1000)
      );


      sqlloader command : sqlldr dbo_a3/dbo_a3 control=/home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl log=/home/oracle/check.log


      Log file :


      SQL*Loader: Release 11.2.0.2.0 - Production on Fri Dec 14 01:07:02 2012

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

      Control File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl
      Data File: /home/oracle/Ext_tables/Northwind_CHECK_CONSTRAINTS.csv
      Bad File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.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 NORTHWIND_CHECK_CONSTRAINTS_A3, 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
      ------------------------------ ---------- ----- ---- ---- ---------------------
      CONSTRAINT_CATALOG FIRST * , O(") CHARACTER
      CONSTRAINT_SCHEMA NEXT * , O(") CHARACTER
      CONSTRAINT_NAME NEXT * , O(") CHARACTER
      CHECK_CLAUSE NEXT * , O(") CHARACTER

      Record 1: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 2: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 3: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 4: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 5: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 6: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 7: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 8: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 9: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 10: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
      Field in data file exceeds maximum length
      Record 11: Discarded - all columns null.

      Table NORTHWIND_CHECK_CONSTRAINTS_A3:
      1 Row successfully loaded.
      10 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      1 Row not loaded because all fields were null.


      Space allocated for bind array: 66048 bytes(64 rows)
      Read buffer bytes: 1048576

      Total logical records skipped: 0
      Total logical records read: 12
      Total logical records rejected: 10
      Total logical records discarded: 1

      Run began on Fri Dec 14 01:07:02 2012
      Run ended on Fri Dec 14 01:07:02 2012

      Elapsed time was: 00:00:00.10
      CPU time was: 00:00:00.02


      h1 Please help.........

      Edited by: vasanth on Dec 14, 2012 2:12 AM
        • 1. Re: "Field in data file exceeds maximum length" : sqlloader
          vasanth
          I Even tried these below options in control file still the same error persists
          _
          COL1 CHAR(500) NULLIF COL1=BLANKS,
          COL2 CHAR(500) NULLIF COL2=BLANKS,
          _

          +
          CUSTRE_REMARK_TEXT CHAR(1000) nullif CUSTRE_REMARK_TEXT
          = '(null)'
          +

          Am i giving wrong datatype for the column h1 CONSTRAINT_CATALOG h1

          but in the csv file all the 8 rows for the column CONSTRAINT_CATALOG is Northwind ....I think because of error in this column only whole loading is not happening..


          Kindly help guys.....i m getting exhausted !
          • 2. Re: "Field in data file exceeds maximum length" : sqlloader
            vasanth
            Hi Friends

            I somehow managed to shrugg off the errors while loading and loaded the datas , but still only one column is populated in the table ..

            Pls see my log below

            SQL*Loader: Release 11.2.0.2.0 - Production on Fri Dec 14 03:18:27 2012

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

            Control File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.ctl
            Data File: /home/oracle/Ext_tables/Northwind_CHECK_CONSTRAINTS.csv
            Bad File: /home/oracle/control/Northwind_CHECK_CONSTRAINTS.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 NORTHWIND_CHECK_CONSTRAINTS_A3, 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
            ------------------------------ ---------- ----- ---- ---- ---------------------
            CONSTRAINT_CATALOG FIRST 6000 , O(") CHARACTER
            CONSTRAINT_SCHEMA NEXT * , O(") CHARACTER
            CONSTRAINT_NAME NEXT * , O(") CHARACTER
            CHECK_CLAUSE NEXT * , O(") CHARACTER

            value used for ROWS parameter changed from 64 to 37
            Record 11: Discarded - all columns null.

            Table NORTHWIND_CHECK_CONSTRAINTS_A3:
            11 Rows successfully loaded.
            0 Rows not loaded due to data errors.
            0 Rows not loaded because all WHEN clauses were failed.
            1 Row not loaded because all fields were null.


            Space allocated for bind array: 250712 bytes(37 rows)
            Read buffer bytes: 1048576

            Total logical records skipped: 0
            Total logical records read: 12*


            Only the row CONSTRAINT_CATALOG which i declared as long in table definition is populated ..
            • 3. Re: "Field in data file exceeds maximum length" : sqlloader
              rp0428
              >
              When trying to load to Northwind_CHECK_CONSTRAINTS_A3 table in dbo_a3 schema using sqlloader im getting the below error in the log file of the loader
              . . .
              create table Northwind_CHECK_CONSTRAINTS_A3
              (
              CONSTRAINT_CATALOG char(1000),
              CONSTRAINT_SCHEMA char(1000),
              CONSTRAINT_NAME char(1000),
              CHECK_CLAUSE char(1000)
              );
              . . .
              Record 1: Rejected - Error on table NORTHWIND_CHECK_CONSTRAINTS_A3, column CONSTRAINT_CATALOG.
              Field in data file exceeds maximum length
              . . .
              Only the row CONSTRAINT_CATALOG which i declared as long in table definition is populated ..
              >
              You've posted enough times to know you need to provide your 4 digit Oracle version.

              Why are you using CHAR datatypes instead of VARCHAR2?

              The error says that the data is too long for the column. You didn't post any data for us to look at to see what you are loading.

              Can you manually insert one of the rejected records into the table?
              • 4. Re: "Field in data file exceeds maximum length" : sqlloader
                jgarry
                If you don't specify the length of each field in the control file, the default is 255.

                See MOS SQL*Loader Problems Loading Variable Length Fields Using CHAR and VARCHAR Datatypes [ID 160002.1]
                • 5. Re: "Field in data file exceeds maximum length" : sqlloader
                  vasanth
                  Hi dB version is

                  SQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 14 07:42:05 2012


                  I AM ABLE TO INSERT DATA MANUALLY.......
                  • 6. Re: "Field in data file exceeds maximum length" : sqlloader
                    sb92075
                    vasanth wrote:
                    Hi dB version is

                    SQL*Plus: Release 11.1.0.7.0 - Production on Fri Dec 14 07:42:05 2012


                    I AM ABLE TO INSERT DATA MANUALLY.......
                    Different clients have different limitations.
                    I can be a data dependent error.


                    how ca we reproduce what you report?